Here is a simple example of the method I was talking about.
Instead of having a paramter query, you can use a form to enter your criteria and build you query dynamically ("on-the-fly"). So let's assume that we have a simple form with a Combo Box (that is named "cboCriteria" in this example) where the user select's the criteria (could also use a Text Box for free form entry), and a Command Button (named "cmdProcess") that they click after entering the criteria to run.
This first block of code (which is attached to the Click event of the Command Button) builds the SQL code for the query, assigns that code to a query, counts the number of records that query will return and will open a form (if it returns more than zero) or return a Message Box (if it returns nothing).
Code:
Private Sub cmdProcess_Click()
' Build SQL code based on criteria entered in combo box on form (cboCriteria)
Dim mySQL As String
mySQL = "SELECT [Table1].* FROM [Table1] WHERE [Table1].Field1=" & Chr(34) & cboCriteria & Chr(34) & ";"
' Assign SQL code to designated query (Query1)
CurrentDb.QueryDefs("Query1").SQL = mySQL
' Check count of records that query returns and opens form or return MsgBox
If MyRecordCount("Query1)>0 Then
DoCmd.OpenForm "Form1"
Else
MsgBox "No records match the criteria entered"
End If
End Sub
This second block is simply a User Defined Function that the first block of VBA code uses to count the records in any given query.
Code:
Function MyRecordCount(myQuery As String) As Long
' Counts records in a query
Dim myRecords As Long
Dim myRS As DAO.Recordset
Set myRS = CurrentDb.OpenRecordset(myQuery)
If Not myRS.EOF Then
myRS.MoveLast
MyRecordCount = myRS.RecordCount
Else
MyRecordCount = 0
End If
Note: You must select the "Microsoft x.x DAO Reference Library" to use this code (where x.x is some numeric code - just pick whichever one is present).
Also, this is just a very simplified example. In reality you may want to add more conditions or sort options to your SQL code that is being built, and you may want to add some verifications to make sure they are making valid entries.
I also assumed that the Criteria is a text value. If it is nermic or boolean, you do not need the Chr(34) code (which just returns double-quotes).
Hope this helps!
