Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pass Form Data to Query Using VBA

Posted on 2013-11-19
7
Medium Priority
?
882 Views
Last Modified: 2013-11-21
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!
0
Comment
Question by:mickferrari
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 21
ID: 39661485
There are several ways to handle this.

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.RepairIDControlNameHere")


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
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39661624
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.Filter= "RepairID = " & me.RepairID
Reports!YourReportName.Filteron= true

Thus not touching the Query :)

jaffer
0
 

Author Comment

by:mickferrari
ID: 39664132
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
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 1500 total points
ID: 39664581
mickferrari,

Which event are you using to  execute TempVars.Add “RepairID”, Me.RepairID ? Are you using the On Click event of a text box control?

Is the control on the form named RepairID (this may not be the same names as the field in the control source)?


@jaffer,  Filtering at the query level is usually more efficient than filtering at the form or report level. You want to retrieve as few records as possible at the query level.

It is also more efficient to use the Where parameter of the DoCmd.OpenReport over applying a filter later.  

Example:

Docmd.Openreport "Your Report Name", acviewpreview, , "RepairID = " & me.RepairID

Open in new window

0
 

Author Comment

by:mickferrari
ID: 39664757
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.
0
 
LVL 21
ID: 39664856
Thanks for the update. Glad to hear you got it working.

Good luck with your project.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 39667125
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question