Change Data in a Query Criteria Field using a Macro

[CJBills]

In Access 2007, I would like to write a macro to change data in one Criteria field of a Query.

In my example, the Query is called "Query", the Field is called "ID", and the Criteria is numbered "100".
(In SQL "([TABLE NAME].[ID])=100").

I'd like to change the Criteria using a macro to "50".

Thanks, in advance.

 

[TerryHogarth21]

In Access 2007, I would like to write a macro to change data in one Criteria field of a Query.

In my example, the Query is called "Query", the Field is called "ID", and the Criteria is numbered "100".
(In SQL "([TABLE NAME].[ID])=100").

I'd like to change the Criteria using a macro to "50".

Thanks, in advance.

When you say Macro, I'm assuming you mean VBA because I don't think you can do so via Macro. Theoretically you could just prompt the user to input the number for the criteria in the query like this.

 SELECT * FROM TABLENAME WHERE ID =[ENTER ID]

But in any case, the way to do it with VBA is to change the query def

So say you have your query called "Query" and the SQL is literally this

 SELECT * FROM TABLENAME WHERE ID = 100

You could put this in a module and then run it

Sub ChangeQueryDef()'Change the definition of a Query
Dim QD As QueryDef


Set QD = CurrentDb.QueryDefs("Query")
QD.SQL = "SELECT * FROM TABLENAME WHERE ID = 50"


End Sub

This would change the WHERE ID = 100 to 50, but you could've just changed it in the query itself or put the parameter like the original example I posted to make your life easier if it were changing constantly.

 

[CJBills]

Indeed, VBA is what I meant. Thank you very much for your reply and direction. This comment is long, but additional advice and guidance would be gratefully appreciated; this issue has been plaguing me.

Following the directions, my results were unsuccessful.

1) I would need to retain End User error, and would not be able to do the first suggestion:

 SELECT * FROM TABLENAME WHERE ID =[ENTER ID]

2) The second suggestion, scripting VBA to change the query def, either ran without changing the intended criteria (no criteria changed), or resulted in an error.

I inputted the following in a module, then ran it:

'Change the definition of a Query
Sub ChangeQueryDef()
Dim QD As QueryDef

Set QD = CurrentDb.QueryDefs("Query")
QD.SQL = "SELECT * FROM TABLENAME WHERE ID = 50"

End Sub

ERROR ONE / ATTEMPTED SOLUTION ONE:
Using “*” did not work (Run-time error ‘ 3265’: Item not found in this collection), so I omitted it; I have not been able to input it effectively, if you have suggestions or explanations, please let me know.

ERROR TWO:
The content intended to change is at the bottom of the query’s SQL script, which includes 50+ query fields. The result of the module did not change the intended field criteria; nothing changed, in fact.

ERROR THREE:
The module cleared all the query’s content, then replaced it with “SELECT FROM TABLENAME WHERE ID = 50", where TABLENAME and ID are unique to my example.

ERROR FOUR / ATTEMPTED SOLUTION TWO:
To remedy the issue of the VBA script replacing everything or changing nothing, I inputted the entire SQL script. Each time I inputted the entire Query SQL Script the VBE lines ran out of room, causing red-text because of line length limitations. To fix this I used quote-comma-underscore combinations (ie “CONTENT", _) to extend the line, as can be done in Excel; this did not work.

ATTEMPTED SOLUTION THREE:
I thought that maybe adding another variable would work, but it has not:

Dim ChangeUserID As String
ChangeUserID = Replace("[TABLENAME].[USER ID]=100", "100", "50")

 

[TerryHogarth21]

When you go from Access's SQL syntax to VBA the string will change. You might as well go into design mode of the query and paste it into here if you need help with it.

Look at Mr. Browne's tip where you can create the conversion from SQL to VBA. I recommend this as it is very handy to convert.

Microsoft Access tips: Copy SQL statement from query to VBA