Displaying query result on the same form as selection fields

[Joe M.]

Hi. I have a form with several fields that are referenced by a query. When I
populate the form's text boxes and other controls and then run the query then
the query displays the results. I would like the query results from certain
fields to populate separate text boxes reserved to display the query results
on the same form. The query always returns one row of results. For example,
the query contains fields: product, description and location. I would like to
hit a cmdbutton on the form and have these fields displayed in text boxes
(prod, desc and loc) on the form. I do not want the user to scroll thru
records or anything like that, just display the results in these text boxes.
Can someone help? Thanks! JM

 

[Jeff Boyce]

One approach to doing this is to create a form bound to a query, and have
that query look to a combobox on the form for selection criterion.

Set the source of the combobox to the items from which you wish the user to
select. In the combobox's AfterUpdate event, requery the form (using
something like: Me.Requery).

Here's how it behaves:

* You open the form, it's empty (because the combobox is empty, therefore
the query is empty, and the form shows that)
* You select an item from the combobox
* Access requeries the form, which re-runs the underlying query, which
now finds a value in the combobox, and gets that record (NOTE: gets a
single record, not the entire recordset)
* Because the form actually has a record behind it now, the values show
in the form's controls (but only if you've bound them to the recordset's
fields).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joe M.]

OK. I am using combo boxes and check boxes as my criteria. I guess my results
text boxes are to be bound to my query. But where do I put the me.requery, in
the click event of the cmd button?

 

[Joe M.]

I tried something but it didn't work. In the control source of my results
text box I used the expression builder so what's in there now is
=Query1![Product]. In my "lookup" cmd button click event I put Me.Requery.
When I run the form #NAME? appears in the bound text box. When I click the
lookup button, nothing happens. What else do I need to do? Thanks again, JM.

 

[Jeff Boyce]

You might want to take another look at the rough outline of design I
offered. It doesn't sound much like what you're using.

The Me.Requery command goes in the AfterUpdate event of the combobox (just
one).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joe M.]

dJeff,

I am using 3 combo boxes and an option group to send data to some text boxes
so my selection criteria for my query does not come from only one control. (I
mentioned in my original post.) Therefore I did not think that placing Me.
Requery in the AfterUpdate event of one combo box would work. Your solution
seemed to address using only one combo box for selection criteria not
multiple criteria. I do not want the query to run until all the selection
criteria has been entered. That's why I am using a cmd button to trigger the
running of the query. I tried to bind the form to my query but when I run the
form it appears as blank and none of the controls are visible.

Many thanks,
JM

 

[Jeff Boyce]

I would check the form's properties.

I might approach it differently, but if you search on-line for "search
form", you will probably find several approaches that have worked.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP