Selecting Criteria from Combo and Returning Results 1

[weberbill]

Here is my situation...
I have an APP table and a CAT table. I also have a REC table. The APP and CAT tables have only one field and populate combo boxes on a form. The REC table has the following fields; "app", "cat", "company".

All I want to do is have the selections that are made in the combo boxes populate a list box on the form as I make my selection, in "real time". I have the query done and the query works I know that. But how do I get the information to populate the list box after making my selection.

I would NOT be against using a Command Button or something that would "auto-refresh" would be great too.

 

[SeeJane]

Hi,

You can use the combo box's AfterUpdate event something like:
Me.Listbox.Value = Me.Combobox.Value

As an aside, I am wondering why you need to populate the list box when you've already made a selection from the combo box. Having 2 controls to hold the same information can confuse users and lead to entry mistakes, over-writing, etc, and generally make your life difficult. I don't know enough about your db to make specific suggestions, but I do suggest that you look into using either the combo or list box exclusively.

Good luck with it all,
CJ

 

[jryan224]

weweber3

In the after update of the combo box, try requery.

Jdemmer
God Bless

 

[weberbill]

What I am doing is using the two combo boxes as parts of a query... here is the query in english

Select * from REC where combo1=app and combo2=cat

REC is the table and 'app' and 'cat' are fields in the REC table. The REC table has the following fields; app,cat,company,address,city,state,zip

So I want the query to use the combo boxes for qualifiers in the query and then return the company,address,city,state and zip to the list box.

 

[SeeJane]

Now I get it...

Try the following as the Row Source for your list box (row source type should be table/query)...The general idea is that you need to concatenate a SQL string based on values from your form. It should update just fine on it's own as you enter now stuff or navigate through the records

"SELET * FROM Rec WHERE Rec.App = '" & Forms![Your Form]![AppComboBox].Value & "' AND Rec.Cat = '" & Forms![Your Form]![CatComboBox].Value & "';"

Good luck, hope this is more helpful
Cj

 

[weberbill]

Well I have tried that. It seems to work the first time and then as I change the combo boxes the list box does not update according to them. For example when the criteria is NOT met by the query I would expect to see "No Results" or a Blank Listbox. Instead I see the old information.

I would be happy to send you my Database and have you take a look at it. All I have done so far is this part, so I shouldn't be complicated to look at. If you would like it, please email me at bweber@precisionsupply.com

Thank you,
Bill Weber

 

[jryan224]

weweber3 ,
Did you try requery? Basically you want to force the query that populates the list box to update itself with the new data.

I had suggested in the after update of the combo boxes, but maybe in the got focus of the listbox.

Jdemmer

 

[weberbill]

How do I requery?

 

[jryan224]

weweber3,
It was not requery, it was refresh that worked.

I used a parameter query for the list box and used me.refresh in the got focus of the list box. It will update when it gets focus. I tried many ways to get it to update when you change a combo box, but could not.


I will email you the db I created to test this.

Jdemmer
God Bless