Pass Form Data to Query Using VBA
Hello,
I have the following problem. I have a form that filters the results of job tickets, and I would like to be able to click a value that appears in the RepairID column which will then run an "On Click" VBA event that will store the RepairID that I just clicked. That then stored RepairID will then be used to run a query. The RepairID only needs to be temporarily stored until the next "On Click" event is ran.
This is my envisioned way of it working however if someone has a better method I would like to know it.
Thanks!
I have the following problem. I have a form that filters the results of job tickets, and I would like to be able to click a value that appears in the RepairID column which will then run an "On Click" VBA event that will store the RepairID that I just clicked. That then stored RepairID will then be used to run a query. The RepairID only needs to be temporarily stored until the next "On Click" event is ran.
This is my envisioned way of it working however if someone has a better method I would like to know it.
Thanks!
Hi Mick
Usually the Query is not a final destiny, but a Record Source for a Form or a Report,
if that is the case, then you can send the Report (or Form) Filter directly from your open Form, like this:
docmd.openreport "Your Report Name", acviewpreview
Reports!YourReportName.Fil
Reports!YourReportName.Fil
Thus not touching the Query :)
jaffer
Usually the Query is not a final destiny, but a Record Source for a Form or a Report,
if that is the case, then you can send the Report (or Form) Filter directly from your open Form, like this:
docmd.openreport "Your Report Name", acviewpreview
Reports!YourReportName.Fil
Reports!YourReportName.Fil
Thus not touching the Query :)
jaffer
ASKER
Hi TheHiTechCoach,
I have tried your solution but I'm getting an error (please see attachment).
This is the code I am using to store the data:
TempVars.Add “RepairID”, Me.RepairID
Untitled.jpg
I have tried your solution but I'm getting an error (please see attachment).
This is the code I am using to store the data:
TempVars.Add “RepairID”, Me.RepairID
Untitled.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
TheHiTechCoach,
I've got it working now, thanks a lot for your help.
Just to answer your other questions. I am using an onclick event to a text box and the control name is RepairID.
I've got it working now, thanks a lot for your help.
Just to answer your other questions. I am using an onclick event to a text box and the control name is RepairID.
Thanks for the update. Glad to hear you got it working.
Good luck with your project.
Good luck with your project.
Hi TheHiTechCoach
I know that :)
My way is an option, to avoid having a few similar queries, but with different criteria's :)
And yes, the criteria can be set from the DoCmd.OpenReport line,
but there is also an ORDERBY command that can be sent using my way, which cannot be set using DoCmd.OpenReport command :)
jaffer
I know that :)
My way is an option, to avoid having a few similar queries, but with different criteria's :)
And yes, the criteria can be set from the DoCmd.OpenReport line,
but there is also an ORDERBY command that can be sent using my way, which cannot be set using DoCmd.OpenReport command :)
jaffer
1) In all version of Access you can simply reference the form control in the query. This does require the form to remain opened. It can be set to not be visible.
Example in Query where:
Where RepairID = Eval("forms.Yourformname.R
2) If Access 2007 and later you can use teh TempVars collection as temporary storage. This does not require the form to remain opened.
In the form:
TempVars.Add “RepairID ”, Me.RepairIDControlNameHere
This can be set with VBA code or a Macro
Example use in a query:
Where RepairID = TempVars!RepairID