John,
Normally you do not just open a query from a command button. The query
would be used as a record source for a form which would display the records
retruned by the query. When used like this, you would simply apply a filter
to your form for the "Name" = "John" or whatever.
With all that said, that does not mean that you cannot do what you are
wanting to do. To do this, you will need to redefine the query. In the "On
Click" event of your command button, you would use VBA code like this:
'*******Start Code**********
'define a string variable to hold the value
'supplied by the user in the textbox
Dim strName As String
'define a string variable to hold the sql statement
Dim strSql As String
'If there is a value in the testbox
If Not IsNull(Me.TextBox1) Then
'read the value from the textbox
strName = Me.TextBox1
'start with the sql of the exising query, but
'using the value stored in the variable to
'provide the value from the user as the criteria
strSql = "SELECT tblCustomers.*, tblCustomers.Name " _
& "FROM tblCustomers " _
& "WHERE (((tblCustomers.Name)='" & strName & "'));"
'next, redefine the existing query using the sql string
'variable that now has the user defined criteria
CurrentDb.QueryDefs("qryMyQuery").SQL = strSql
'actually open the query
DoCmd.OpenQuery "qryMyQuery"
End If
In the code above, my sql statement is based on a table named
"tblCustomers". You will use your sql statement from your existing query.
The "qryMyQuery" above is the name of my query. Just replace the
"qryMyQuery" with the name of your query.
The sql statement that you want to start out with can be optained from your
existing query by displaying the sql view of your query and just copy and
paste it into the code. You will need to modify it using the variable for the
name as I have done in my example.
