SQL editing

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

I want to be able to edit a querie's SQL statement using
VBA and parameters entered by a user on a form. In other
words, the criteria within the query is not static, and
will change dependent upon what the user wishes to search
for. I do not wish to simply run the query and prompt the
user to enter values, but would rather the values be
entered, then a button click event to activate the written
query SQL with the proper parameters passed. I do not
know how to do this, and have not found a way. If anyone
knows how this can be done, any help would be much
appreciated. Also, if it cannot be done, please let me
know so I can pursue other means. Thank you.
 
You can address the saved query as follows. Notice that you can also
change the entire SQL part of it, and you can create a new querydef that
is not one of the saved ones.

Dim Qry as DAO.Querydef
Set Qry = CurrentDb.Querydefs("MyQueryName")
Qry.SQL = Qry.SQL & " WHERE Field1 LIKE '*" & strCriteria & "'"

Hope this gives you a starting point. I assumed that when executing the
above code you already have strCriteria containing the criteria entered
by the user.
Pavel
 
To add to the information that Pavel provided, you can
also assign parameter values after setting your query
definition object (assuming that the original query
contained parameters), such as:

Qry.Parameters("YourParameterName1") = YourVariable1
Qry.Parameters("YourParameterName2") = YourVariable2

Of course you could assign string or numeric values
directly rather than using variables on the right side of
the expression. Or, you could use form controls or field
values.

Not sure if you will need to do this, but thought I would
mention it just in case.

HTH, Ted Allen
 
Back
Top