Queries based on Combo Box Items

[tjr222]

Heres what I have:

cboCompany - combo box that contains 1 thru any number of
4 - digit Invoice Numbers. (This depends on what is
selected in another combo box)

A subform that shows pricing information and is linked by the
4- digit Invoice Number.

So for example if cboCompany contains Invoice #'s 1234, 4321,
and 5555, I CAN display only one at a time.

What I would like to do is display all the Invoices in cboCompany at one time. Also it is important to note that the number of invoices may be three (as in example) or any number.

To reiterate in an attempt to make myself as clear as I can. I want to run a query that will show every record with the Invoice #'s that match whatever is in cboCompany.

Thank you for any response,

Tom

 

[Jack Cowley]

The Invoices should be related to the Company by CompanyID not the invoice number. CompanyID should be the Foreign Key in your Invoice table. Now selecting a Company will show all of their invoices.

hth,
Jack

 

[tjr222]

Thanks for your response, but my I think I already do have it related in that manner.

I have three tables, 'Company' with CName as the key and fields address, city, state, zip, etc.

Table two is called 'Invoice' with INum as key Cname as foreign key - related to company in a many to 1. Then the table also has fields date, ponum.

Finally table three is Product table with pnum as key and related to inum in a many to 1. The additional fields include description, amount, paid and due.

Now having said that I am trying to make a payment form that when I select a company name from a combo box it will display all the items in the product table (description, amount, paid and due) for all the invoices the company has not just the current one.

Therefore company A may have invoices with Inum = 2222 and Inum = 4444, when I select company A i would like both 2222 and 4444 to be selected.

Now I have a combo box that can be filled with all the Inums of a company. So Company A would have 2222 and 4444, while Company B may have 5555, 6666, 7777 and more.

If it is possible to set up a loop or something that would go through the combo box and place each of the INums in a query criteria with 'or' inbetween each one then I think that will work.

Well hopefully I made my problem clearer and thanks again for the response or any future response.

- Tom

 

[Jack Cowley]

Create a query with your 3 tables and drag the items from the Products table to the QBE grid and the CustomerID from the Customer table. Select a CustomerID in the query line and run the query and you will see all the Product information for that customer. You do not need to select the invoices if your joins between tables are correct.

hth,
Jack