VBA-Openquery

[John Jiang]

I have a command,and I click it,it needs to open a query ,but for some
conditions(for example:"name"="tom" or "sex"="male" etc)

What's the VBA should be?

Thanks!

John Jiang
2007-03-14

 

[Guest]

John,

We would need more information before we could answer your question. Like
where are the values of "Tom" or "Male" comming from? Are they already
supplied by the user or do they need to be supplied upon clicking the command
button?

You say "it needs to open a query", is this a predefined query or are you
wanting to develope an SQL statement based on selections or data provided by
the user?

There can be multiple answers to your question based on what you are really
wanting to do. Please post more details and perhaps someone will be able to
assist you.

 

[John Jiang]

Mr B,

Thank you for your answer.
There is a predefined query, the values of fields are already supplied
by the user,and "names" is a field of the query.
Otherwise,the user have Textbox1 and Command button,and will enter the
data into Textbox1(for axample: "Tom" or "John" etc )

My question is:
When the user clicks the command button, how to open the predefined
query where the field "names"=Textbox1

Thanks

John Jiang
2007-3-14

 

[Guest]

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.

 

[Daniel]

If you have a predefined parameter query, then you can also pass that
textbox's value to the parameter query.

I agree that normally you would want to do something with the query
(e.g. filter your current form or open a new form), but here's the
code to pass a parameter to a query. It also includes how to assign a
form's recordset, if that's what you want.

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb().QueryDefs("[your query with a paremeter]")

qdf.Parameters("[your parameter]") = me.textbox1.value

Set forms![form to open].Recordset = qdf.OpenRecordset()

qdf.close
set qdf = nothing

Note that the query isn't 'run' until you execute the
qdf.OpenRecordset() method.

HTH,
Daniel

 

[John Jiang]

Thank you very much!

John Jiang
07-03-14

Mr B,

Thank you for your answer.
There is a predefined query, the values of fields are already
supplied by the user,and "names" is a field of the query.
Otherwise,the user have Textbox1 and Command button,and will enter
the data into Textbox1(for axample: "Tom" or "John" etc )

My question is:
When the user clicks the command button, how to open the predefined
query where the field "names"=Textbox1

Thanks

John Jiang
2007-3-14