asked on

DoCmd.SendObject acSendQuery command problems

Have the below sub assigned to a button on a form in Access. It runs but am having some issues with it.

1. Is there any way that I can pass parameters with this command to the query? I want the parameters to be passed automatically without the user having to type them in.

2. For some reason after running this my store_report query is erased. The query still shows as an object, but if you open it the sql contents are all gone, thus the query will not run. I've noticed that this sometimes happens if I right click on the query and do an export to an excel file. After doing the export the sql contents of the query dissappear. Any way to prevent that?

3. Can I specify an excel template file to send the data to and specify what cells to begin inserting data to?

Sorry for so many questions. Am assigning max points. I think I am probably going to need different code to do all this, but I have no idea what I need.

Private Sub Email_Click()
DoCmd.SendObject acSendQuery, "store_report", acFormatXLS, _
    "john@cane.com", "c.nore@gmail.com", , _
    "Store Reports", "Here are your reports.", True
End Sub

Open in new window

peter57r

To include parameters you would need to set them up in a form and then refer to the form controls in the query:
forms!formname!controlname

I have never come across the problem in 2. So can't help with that.

3. You can't do any of that in a sendobject command.

ckangas7

ASKER

Can you suggest a different command or set of commands to help me do this? I want the parameters to come from an array, and the data will need to be sent to an exel template. I will need to control at what point on the spreadsheet the data insert begins. Is this possible in Access VB or do I need to look into another solution?

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

peter57r

I'm off the air for a while.... if no-one else has completed this I'll look at it again later.

ckangas7

ASKER

So where is the array being populated in your example? Example:

Let's say the store_report query object looks like this:
Select store_id, store_name, qty, deposit from t_psd_imports.

The array for my where clause on this query will be populated by the sql statement:
Select Distinct store_id from t_psd_Imports.

So very roughly my VBA code would look somethng like:

Private Sub openreports()
DIM wherearray AS String
DIM arraynum AS Int
wherearray = (Select Distinct store_id from t_psd_Imports)
For arraynum = 1 To UBound(wherearray)
DoCmd.OpenReport "Stores Report", PrintMode, , store_id = wherearray(arraynum)
Next
End Sub

So hopefully this sub would open a seperate report for each distinct store_id in the t_psd_imports table. The report is based on the store_report query. So the where clause would get passed to that. I know this code is very bad. Just trying to show you what I want to do.