Requery a form with new criterior

[nostr4d4m]

Hej,

So i am making a advanced search with subform - top part is search criterior fields and bellow it a subform of search results. Now i have a need to use Multi-select List box. I have code to get all the selected values and build it into the string. Than lounch a query in new window based on selected values. But i would like to requery my old form insted of opening the new window.

is there any way to do that?
Can i set a query criterior to be a VBA value

[jzwp11]

Assuming that your main form is unbound and your subform is bound to a query, you, can with VBA, build the query dynamically based on the search criteria provided by the user and then assign the query as the record source for the subform. Constructing the query in code can be a little tricky. Here is the basic structure of the code. Assuming that you have a button on your main form to conduct the search, the code would go behind the on click event of the button.

Dim mySQL as string
 
'your code to get the items from the list box
 
mySQL= " SELECT..."
mySQL= mySQL & " WHERE field="  & stringvariableholdinglistboxitems
 
me.subformname.form.recordsource=mySQL

[nostr4d4m]

Ok it is working. But how do i now export this query ?
DoCmd.OutputTo acOutputQuery and
DoCmd.TransferSpreadsheet
Exports original query and all my criterior disapears. Can i somehow target the query that i currently see in the form

[jzwp11]

You did not mention anything about exporting. I assume that you want to export the results of the query (i.e. the same records that are displayed in the subform) not the query itself. The query that you create in code, exists only in that code. If you want to use the docmd to export from that query, you have to save the query you created in code as a query in Access & then do the export. The following is a code snippet that does that. This code creates a query called myQuery does the export and then deletes the myQuery after the export is complete.

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String
Dim strQDF As String
Dim strFile As String
Set dbs = CurrentDb
            
strSQL = "SELECT * FROM table1"
strSQL = strSQL & " WHERE daterec=#" & Me.cboStartDate & "# AND enddate=#" & Me.cboEndDate & "# and company='" & Me.company & "'"
                        
strFile = "mySpreadsheet.xls"
strQDF = "myQuery"
 
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, strQDF, strFile
        
dbs.QueryDefs.Delete ("myQUERY")
dbs.Close
 Set dbs = Nothing

[evander]

Okay, I'm not here to help. I actually browsed at this thread. jzwp11, You're a genius. I'll remember your example. Glad I can benefit from someone else's experience. Thanks.

[jzwp11]

evander,

I'm glad you were able to find it useful. The code was actually from someone else and I was just helping troubleshoot it.