Queries in access... 1

[Maldini]

I know how to make basic queries in access, which take in parameters and use that parameter as the search item... thats all and well but doesn't especially suit what I want now...

What if I have a form (used to display record at a time of my table), where there are many fields and I need to use several of the fields on this form as the parameters?

For example, I might have to search for a record with a specific date and person, and these items I'll have in textboxes on my form... how do I pass these onto a query and refer to them?
Also, would this mean I no longer make the query in the table view/wizard and instead have to write the code out in vba?

Thx. (sorry for the stupid question, I'm not familiar with access yet)
Maldini

 

[Steve101]

Maldini,

Firstly, the only stupid question is the one that is not asked.

To make a query work off the values associated with an open form is quite simple. If your form is called frmYourForm and it contains two controls called txtYourTextBox1 and txtYourTextBox2, then in the query you simply refer to these objects as:

Forms!frmYourForm!txtYourTextBox1 and
Forms!frmYourForm!txtYourTextBox2

and so on. So long as the form is open when you invoke the query, the parameters will be resolved.

A couple of other notes:

(a) If your form name and/or control names contain special characters (eg. spaces), then surround them with square brackets; eg. Forms![frmYour Form]![txtYourControl]

(b) If you're using controls which represent dates, its often good to use the CVDate function to resolve against a date field (eg. CVDate(Forms!frmYourForm!txtYourTextBox2)

(c) The form must of course be open for the above to work, but it can be minimised or hidden. This is often a powerful way of passing parameters transparently to queries without bothering the user (if you're designing a user interface).

Hope that this helps,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Maldini]

Thx for the answer, I think I know how to do it now

Maldini