Using form controls for Query criteria
Using forms in an Access Database design is the best way to simplify the user experience and increase efficiency while working with the database. If you are publishing tools for end users that may not know anything about Access or databases in general, you will want to make it as user friendly as possible. The last thing you want is for end users to have to manipulate and edit query objects in order to get to the data they are looking for.
Dynamic Criteria
Observe the text [Criteria] in the above query. This will cause Access to pop up a prompt when this query is run.
This will do the job if the query your editing is not needed very often. If you have multiple queries being fired off that require the same criteria passed, or multiple criteria to edit, you are much better off using a form control to pass the data.
Directly Referencing a Control
A control can be called directly in the query by using this syntax: [Form]![form name]![control name]
For simple query outputs this is just fine, however you may find that this is unacceptable as a source for a crosstab query, or when called from ADODB code in a Visual Basic function. For those you will need to pass along the criteria from a Visual Basic function.
Pulling Control Data from Visual Basic
A short function can be written to return a form control’s value.
Public Function GetVal(Form As String, control As String)
On Error GoTo trap
GetVal = Eval(“[Forms]![” & Form & “]![” & control & “]”)
Exit Function
trap:
GetVal = Null
End Function
This code will return return the value of the specified control on the specified form, and will work just fine nested in an ADODB or crosstab query. Note that the error trap is setup to return Null on any error. This will happen if Access does not have a form or control that matches the text you provided, or that form is closed or in design view and otherwise unreadable at the time. An alternative to returning Null would be to return the asterisk character “*”. If you choose to do that, you can maintain the functionality of your queries by using the Like switch in your criteria, such as “Like GetVal(“Form”,”Control). That way if the form is closed, GetVal returns an asterisk and Like interprets that as a wildcard, resulting in all records coming back.