Use VBA to change query criteria 2

[WaltW]

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

 

[PHV]

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

 

[flyover789]

Good

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:

http://www.tek-tips.com/viewthread.cfm?qid=1118666&page=2

HTH,
fly

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

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[LittleSmudge]

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:-

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.

 

[ZmrAbdulla]

Starting from here

http://www.fontstuff.com/access/acctut14.htm

may lead you to a good understanding of SQL+VBA.

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[Dor100]

Will any of the methods shown or linked to here solve this problem:

http://tek-tips.com/viewthread.cfm?qid=1119357&page=1

?

 

[WaltW]

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

 

[PHV]

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

 

[WaltW]

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

 

[flyover789]

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.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]