In Access I have a SQL passthrough query that returns an entire table filtered by first name.
Example
SELECT * FROM dbo.Original_Data
WHERE first_name = 'Mike';
I am trying to write some VBA that on click of a button takes the passthrough query and replaces 'Mike' with any name I put in (this will later be done by a combo box but that's another step).
I have this where TempPT is the name of the passthrough query.
Private Sub Command12_Click()
TempPT = Replace(TempPT, "Mike", "Sam")
DoCmd.OpenQuery "TempPT"
End Sub
This doesn't work.
Is there a way that whatever I type in where I have 'Sam' is what the passthrough query will filter by?
I am using passthrough because the DB is huge and clogs up Access.
You need to edit the SQL
property of the query. Probably something like this:
With CurrentDb().QueryDefs("TempPT")
.SQL = Replace(.SQL, "Mike", "Sam")
End With
DoCmd.OpenQuery "TempPT"