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

Microsoft AccessVB Script

Last Comment

ckangas7

8/22/2022 - Mon

peter57r

4/12/2008

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

4/12/2008

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

4/12/2008

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

peter57r

4/12/2008

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

ckangas7

4/14/2008

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.