Select Query using VBA (1 Viewer)

[TheCrusher]

Can anyone suggest what method would be the best for the following scenario:

In my form I have a serial number field. When a serial number is entered in this field and the focus is lost, I would like it to search and underlying table for a match of that serial number. If it finds a match or matches, I would like it to print preview a report of these matches. If it doesn't find any matches, I would like it to do nothing and focus to the next field in my form. My solution right now is to call a query on the "lost focus". Problem with this is that a query results table pops up everytime, even if there isn't a match. I would like to program this with VBA rather than call a Design Query but I don't see how. The DoCmd.RunQuery only works on Update and Delete queries but not select ones (please correct me if I'm wrong).

Can anyone help me with this. I'm not asking for anyone to code this for me, but point me in the right direction as for what functions to use and overall structure of my function.

Thanks
Doug McC

 

[GJT]

Create a recordset in the lost_focus event and if the recordset is valid open the report window form.

i.e. Lost_Focus :
Set rs = currentDb.Openrecordset(yoursql)
If Not rs.EOF Then
' recordset has content! Matches found
' open preview window
Open frm_Preview
Endif

In the form preview window, set the form recordsource property to the same sql as you have just called in the lost focus event.

 

[TheCrusher]

Thanks for your help! This worked great!