Creating, Displaying, then Deleting a Query in VB

[Rubius]

I have a form that has a bunchof different fields. You can enter search data into each field to search a table. Lets say that each piece of searchable data is a number.<br>So we have 1, 2, 3, 4, 5<br>Now I want to make a bunch of buttons that will display tables based on a query that uses certain search data.<br>So using the numbers there would be a ton of buttons with the search group on each, so lets say there labeled. 1, 2, 3, 4, 5, 1-2, 1-3, 1-4, 1-5 etc. I obviously wouldn't make more then 10 buttons, so don't think I'm that nutty! Anyway..I don't want to have a query in the database for each of these buttons. How can I make a temp query in vb code, that displays the table of results, but doesn't save anything to the database, and the displayed table is readonly.<br>I guess I need a way to in vb, create a query based on SQL code (which has references to the fields in the form), display the results in a readonly table, then delete the query or if there is a way, make the SQL string run and show a table without a query..<br>Well now that I have even confused myself, I'll leave you with that. <br>Any questions just ask!<br><br>thanks!!

 

[DougP]

Why don't you make a VBA query that looks at the Fields &quot;ID&quot; or ordinal position instead of it's Field &quot;name&quot;<br>Like so:<br>= tdf.Fields(0)&nbsp;&nbsp;'&lt;&lt;&lt;&lt; is the first field in the Table<br>= tdf.Fields(1)&nbsp;&nbsp;' second field<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Rubius]

Private Sub Command23_Click()<br><br><br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim dbs As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim qdfTemp As QueryDef<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rst As Recordset<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Set dbs = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;Set qdfTemp = dbs.CreateQueryDef(&quot;&quot<br><br>&nbsp;&nbsp;&nbsp;&nbsp;' Open Recordset using temporary QueryDef object and<br>&nbsp;&nbsp;&nbsp;&nbsp;' print report.<br>&nbsp;&nbsp;&nbsp;&nbsp;SQLOutput &quot;SELECT [SR-LOG].PONumber, [SR-LOG].InvoiceNumber, [SR-LOG].Vendor, [SR-LOG].[Part Rec'd], [SR-LOG].NumofPieces, [SR-LOG].[Date Rec]&quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;FROM [SR-LOG]&quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;WHERE [SR-LOG].[Date Rec] Between &quot; & Me!date1 & &quot; And &quot; & Me!date2, qdfTemp<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;dbs.close<br><br>End Sub<br><br>Function SQLOutput(strSQL As String, qdfTemp As QueryDef)<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rst2 As Recordset<br><br>&nbsp;&nbsp;&nbsp;&nbsp;' Set SQL property of temporary QueryDef object and open<br>&nbsp;&nbsp;&nbsp;&nbsp;' a Recordset.<br>&nbsp;&nbsp;&nbsp;&nbsp;qdfTemp.SQL = strSQL<br>&nbsp;&nbsp;&nbsp;&nbsp;<b>Set rst2 = qdfTemp.OpenRecordset</b><br><br>&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print strSQL<br><br>&nbsp;&nbsp;&nbsp;&nbsp;With rst2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Enumerate Recordset.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do While Not .EOF<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;&nbsp;&nbsp;&nbsp;&nbsp;&quot; & !PONumber & &quot; &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;!InvoiceNumber & &quot; &quot; & !Vendor & &quot; &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;![Part Rec'd] & &quot; &quot; & !NumofPieces & &quot; &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;![Date Rec]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.close<br>&nbsp;&nbsp;&nbsp;&nbsp;End With<br><br>End Function<br><br>Ok, this is the code I'm using (bummed it from the northwind example), and I think it should do what I want it to do. Create a temp query, display the results and thats it...but I keep running into an error at the bolded line. Says Division by Zero when it trys to openrecordset. I know that there should be data there because when I make a regular query and put in the 2 dates it works fine. Could it be my sql code is incorrect somehow?<br>Or DougP, if your way is a lot easier, which it probably is could you please elaborate on what you mean? How would I print certain data using those commands??<br>Thanks!!