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:
Code:
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:
Code:
'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:
Code:
Dim ChangeUserID As String
ChangeUserID = Replace("[TABLENAME].[USER ID]=100", "100", "50")