Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Use VBA to change query criteria 2

Status
Not open for further replies.

WaltW

MIS
Jun 14, 2000
130
US
Using VBA in MS Access 2003, how would I go about changing the criteria for a single field in an existing query? This process would be initiated by a click event on a form. Thanks for any help offered!

Walt
 
A common way is to use a parametized query and thus no code needed.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Good [morning]

imho there are scenarios where it can make sense to change the query slq via VBA, e.g. if you don't want user interaction and grab the criteria from somewhere automatically ...

WaltW: using the search function is always recommended.
here is the answer to your particular question:


HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.
[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]
 
I regularly change the Where clause directly in existing queries 'on the fly' using VB code

This is because they are PassThrough queries linked to a MySQL database and I want to pass the where clause parameters automatically - without user involvement.

I use a globally defined proc as below:-
Code:
Public Sub ParamToPT(strQueryName As String, strClause As String)
Dim strSQL As String
Dim intPosn As Integer
   
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(strQueryName)

strSQL = qd.SQL
' in case the existing version has been saved with a Where Clause
' Strip the Where clause off the end
intPosn = InStr(strSQL, "WHERE")
If intPosn > 0 Then
    strSQL = Left(strSQL, intPosn - 1)
End If
' Now add the new Where clause
strSQL = Trim(strSQL) & " WHERE " & strClause

' now update the query code itself
qd.SQL = strSQL

Set qd = Nothing
Set db = Nothing

End Sub




In your main code use this as

Call ParamToPT("qryMyModifyableQuery", "FieldName = 'XYZ'")

And to remove the selection
Call ParamToPT("qryMyModifyableQuery", " -1")


'ope-that-'elps.


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.
 
Thanks to all who responded. I couldn't do the parameter query because there's no interaction with the user. I have a form set up with a command button that, when clicked, exports a series of Excel files with the same structure but slightly different info. Hence the need to modify the criteria in the query used to do the Excel export (using TransferSpreadsheet). Because of your help, I now have a working procedure. Thanks again!

Walt
 
I couldn't do the parameter query
even with hidden textbox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Hmmmm. Maybe I haven't thought the parameter query through enough - I'd love a non-programming solution. PH, could you elaborate? I'm basically pulling info from one field in the records in one query and using that field info to change the criteria in another query for the Excel export. How would I set up the parameter, then modify the parameter to get the right info in the query? Thanks!

Walt
 
dear colleagues,
this is off topic, but I'm gonna fly to Chicago, IL, tomorrow and be in Deerfield, IL, until next friday (Sep, 16)
... business travel ...
anyone of the tek tips coreteam send me a note if there is an opportunity to meet ...

Best regards,
Martin

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.
[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top