Form input not being recognized by query

[funderburgh]

I maintain a grade book application that uses many queries whose results are determined by "school year". Most of these are reports and I have a combo box on the Print form for that allows the user to select the school year. The criteria field of the several queries derived by school year is:

[Forms]![Main Navigation]![Print Form]![SchoolYear]
SchoolYear being the combo box control.

They work fine.

I have now added a function to export data to Excel. This is done in a VBA module and I am using a query to select data for the record set I use to write to Excel:

Set objRst = Application.CurrentDb.OpenRecordset(strQueryName)

When I hard code the school year in the query criteria field (i.e. "2012-2013") the process works fine, but if I revert the query to point to the print form field as above, I get an empty recordset.

The Excel export is executed from a control on the Print Form, so the form is open and the combo has data showing, just as it is when a report is run whose data is derived from a query.

When I execute the query from the VBA module, the query is not getting the school year selected on the Print form passed to it properly. I can't figure out why.

Any help is appreciated, thanks.

 

[SOS]

First, the form needs to be open. Second, you can take the criteria out of the query and just use this to open the report:

DoCmd.OpenReport "ReportNameHere", acViewPreview, , "[SchoolYearFieldNameHere]=" & Chr(34) & Forms!Main Navigation![Print Form]!SchoolYear & Chr(34)

And in your export:

Dim strSQL As String

strSQL = "Select * From [" & strQueryName & "] Where SchoolYear = " & Chr(34) & Forms!Main Navigation![Print Form]!SchoolYear & Chr(34)

Set objRst = Application.CurrentDb.OpenRecordset(strSQL)

 

[funderburgh]

I appreciate your response, but in order to run the module that writes to Excel the Print Form must be open, that is where the control is to execute the module. The module that writes to Excel is not a report, it is VBA code. It is not necessary for me to create the SQL string - I have a complicated query that does that, the code:

Set objRst = Application.CurrentDb.OpenRecordset(strQueryName)

builds the record set from the existing query - it works fine with cirteria hard coded.

Let me know if I am missing something from your answer, and thanks for the attention.

 

[JHB]

Show the SQL-string from the query.
Is the value in the combobox on the format like "2012-2013"?

 

[funderburgh]

Exactly. Of course this is not new in my application. I have many reports that are derived by school year (almost all). Report cards and transcripts for example. This application is about 7 years old.

When the Print form is open and there is data selected in the combo school year box, one can open any of the queries that the usual reports are based on, examine the SQL and the school year data is included.

This is the same for the queries I am using for the VBA module that exports to Excel. If the Print Form is open and data selected in the school year combo I can open the query and examine the SQL and see the school year data included. Also, I can run the query and get the correct extracted results.

The problem I am having is that when the query is run via the VBA module, the school year selected on the Print form is not getting to the query. This is the issue I am stuck on.

 

[JHB]

The problem I am having is that when the query is run via the VBA module, the school year selected on the Print form is not getting to the query. This is the issue I am stuck on.

You didn't show the SQL-string from the query.

 

[funderburgh]

I can view the SQL string when I open the query in design view and select the SQL view. I don't need to copy it here to show that the school year data is included, it is, and the query (there are actually two queries, one for high school and one for middle school) work perfectly when run directly when the Print form is open and a school year is selected in the combo box) or when the school year data is hard-coded in query criteria. Whe the school year is hard coded in the criteria, the VBA module that exports the recordset to Excal works fine, but when the query is revised to point the th Print Form combo box, the record set is empty. I appreciate the help and attention, but I am not sure what you are asking. I cannot "see" the SQL as the VBA module is running.

 

[JHB]

I can view the SQL string when I open the query in design view and select the SQL view. I don't need to copy it here to show that the school year data is included, it is, and the query (there are actually two queries, one for high school and one for middle school) work perfectly when run directly when the Print form is open and a school year is selected in the combo box) or when the school year data is hard-coded in query criteria. Whe the school year is hard coded in the criteria, the VBA module that exports the recordset to Excal works fine, but when the query is revised to point the th Print Form combo box, the record set is empty. I appreciate the help and attention, but I am not sure what you are asking. I cannot "see" the SQL as the VBA module is running.

It is you decision if you want to show the SQL string or not, without seeing the SQL string, I can't help you, sorry.
I need to see in the SQL string (design view), if the reference to your form and combo box is correct.
But why are you not copy one query you know runs perfect, and use that copy for the export to Excel.