Querydef: Am i going about this in the right way?

[Darrenc]

I can normally find all the answers to my questions by searching for them, unfortunately this little problem has me stumped.
I'm not even sure I’m going about this in the right way, which is why I’m posting here.

I'm creating a database, and the basic concept is I want to be able to create a temporary excel file and email this file to a designated user.
All the information I need is held in a table:

SupplierCode
SupplierEmail
SupplierOwner
etc.

The excel file is generated by a query, I only want to use 1 query for all the different suppliers in the table.
To do this I need to do is pass a stored variable strSuppCode to the query. Like I say, I have most of it working, apart from passing the variable to the query.
I've used querydef before in another database, without any problems, but on that occasion I was getting the value from a form.

Question is, what is the syntax to pass a variable into a query.
There are also other parameters in the query, which will be the same for each supplier, do I have to reference these too?

This is this bit of code I’ve been failing to get working.

Public Function ExportReportsExcelFormat(szTempbook, szQueryName, strEmailAddress, strSupplierName, strSuppCode)

    Dim szFullTempPath As String
    Dim rstq As Recordset
    Dim dbs As Database
    Dim QD1 As QueryDef
    
    szFullTempPath = "T:\TempReports" & szTempbook
     
'On Error GoTo Err_ModifyExportedExcelFileFormats

    Application.SetOption "Show Status Bar", True

    vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting export file... please wait.")
[COLOR="Red"]    Set dbs = CurrentDb 'Set Current Database
        Set QD1 = dbs.QueryDefs(szQueryName) 'Set QD1 to define the query parameter for the named query
        With QD1
        .Parameters([SupplierCode]) = strSuppCode
        End With
    Set rstq = QD1.OpenRecordset 'Set rst as the lines returned from the updated query
    DoCmd.OutputTo acOutputQuery, szQueryName, acFormatXLS, szFullTempPath, False[/COLOR]

    Dim xlApp As Object
    Dim xlSheet As Object

    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(szFullTempPath).Sheets(1)
    
    With xlApp
            .Application.Sheets(szQueryName).Select
            .Application.Cells.Select
            .Application.Selection.ClearFormats
            .Application.Rows("1:1").Select
            .Application.Selection.Font.Bold = True
            .Application.Cells.Select
            .Application.Selection.RowHeight = 12.75
            .Application.Selection.Columns.AutoFit
            .Application.Range("A2").Select
            .Application.ActiveWindow.FreezePanes = True
            .Application.Range("A1").Select
            '.Application.Selection.AutoFilter
            .Columns(2).DateFormat = "dd-mmm-yy"
            .Columns(7).DateFormat = "dd-mmm-yy"
            .Application.ActiveWorkbook.Save
            .Application.ActiveWorkbook.Close
            .Quit
    End With

    Set xlApp = Nothing
    Set xlSheet = Nothing

    vStatusBar = SysCmd(acSysCmdClearStatus)
    
    Call SendEMail(szTempbook, strEmailAddress, strSupplierName)
    Kill szFullTempPath
Exit_ModifyExportedExcelFileFormats:
    Exit Function

Err_ModifyExportedExcelFileFormats:
    vStatusBar = SysCmd(acSysCmdClearStatus)
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_ModifyExportedExcelFileFormats
    
End Function

The bit highlighted in red is where its all going wrong.

Any thoughts on this would be greatly appreciated.

 

[namliam]

try changing this:
Dim rstq As Recordset
Dim dbs As Database
Dim QD1 As QueryDef

to
Dim rstq As DAO.Recordset
Dim dbs As DAO.Database
Dim QD1 As DAO.QueryDef

and offcourse adding the DAO reference... other than that it looks ok to me... but I may be wrong offcourse...

 

[workmad3]

As another quick suggestion. You only use dbs once, and its set to CurrentDB. Why not use

Set QD1 = CurrentDB.QueryDefs(szQueryName)

instead and remove all the excess stuff for the dbs variable?

 

[KeithG]

I noticed you said there are other parameters also in your query, is this correct? If so you need to set a value to those also.

 

[Darrenc]

Thanks for you prompt replies.
I can't believe I’ve not referenced DAO in my variables, I’ve been there before and it caused a lot of pain before. Thanks for pointing that one out namliam.

I'm not using .parameters correctly, I’m getting a "Item not found in this collection" error.
I've tried looking in the access help on how to use that property, but it won't show me any info on QueryDef
I had a suspicion that I would have to reference all the parameters in the query. It should be ok if I can get the .parameters working.

Also, after the recordset has been created, how do reference it in the Docmd.OutputTo so I can create the excel file.

Thanks All.

 

[KeithG]

.Parameters([SupplierCode]) = strSuppCode

try this;

.Parameters("SupplierCode")=strSuppCode

 

[Darrenc]

Thanks KeithG, that worked. I did try that earlier, I guess it was the DAO reference tripping me up before.

The only problem I have now is

DoCmd.OutputTo acOutputQuery, szQueryName, acFormatXLS, szFullTempPath, False

The code above is going to bypass the QueryDef. I know i'll have to change the OutPut type, i'm not sure which reference to use, or how the reference the recordset. How do I OutPut the the recordset to my excel file?

Thanks Again.

 

[KeithG]

If I were you I would make a form that pops up and have a text box to enter the Supplier code and make a button to export the your query. Then I would set the query to look up the parameter value of the Supplier Code from the text box on the form.

 

[Darrenc]

Normally that would be acceptable, but this particular database needs to be fully automated.

I have a form that will run 24 hours a day, this form has a timer event that triggers the code at a certain time and a certain day. Then for each of our suppliers it will create an excel spreadsheet and email that out to pre-defined email addresses.

I've had the whole thing working ok by each supplier having its own query, and the code looping through each supplier.
Then my boss pointed out that wouldn't it be better if you could 'pick up' the supplier code in the table, and use that as the parameter in the query (All the queries are the same, apart from the supplier code).

Doing it that way I would only need 1 query to do all the suppliers that are in the table, and I wouldn't have to hard code all the suppliers and their queries.

Potentially we could have up too 800+ suppliers in the table, so my previous method wasn’t really an option.

So I’m back to trying to pass that parameter to the query, and then using that query to create the excel spreadsheet.

 

[KeithG]

I don't see how my solution and what you need done differ because you are going to prompt the user for a Supplier ID, Correct?

 

[Darrenc]

Sorry, perhaps I didn't explain that very well. This database has to run without any user intervention.
I need to be able to leave it running on a server 24/7 and just check everyone once in a while to see if its fallen over.

 

[KeithG]

So you are going to send a spreadsheet to every client in the table, not just one that is selected?

 

[Darrenc]

It will be every client in the table. The table quite small for now, there's only 8 records in there.
Like I say, potentially it could grow considerably.

I've attached the database, maybe it will help to take a look.

This could be a powerful tool if I could this working.

Thanks for your time KeithG, its apreciated.

P.S. Please excuse the poor codeing. I know my VBA skills need some serious refining, its getting better though

 

[KeithG]

So you want to export an Excel Report for each SupplierCode in tblEmailInformation?

 

[Darrenc]

Yep, that’s right.
I'll gladly concede I’m going about it the wrong way, it was the best way I could think about doing this

 

[namliam]

If I am reading this correctly you are allready using a form (for the timer)...

So simply make a (hidden) textbox that will contain the ID you want to query. Then base the query of that hidden textbox

 

[Darrenc]

Thanks namliam, I thought about doing that last night, I’m sure that will work.

To be honest though, its not the most eloquent solution, I was hoping that I’d learn a cleaner method of passing a variable to a query and using that to create my excel sheets.

Then again, its better have have a solution than none at all

 

[Darrenc]

I'm being thick, its early and my brain hasn't engaged yet.

The problem isn't passing the variable to the query anymore, both methods work now. The problem is using the recordset to create my excel sheet.

DoCmd.OutputTo acOutputQuery, szQueryName, acFormatXLS, szFullTempPath, False

Using the above method will just bypass any of the parameters i've declared, i can't seem to reference the recordset because its looking for a query.

 

[namliam]

Yep, but you solve that by using the form way...