very easy - combo box or list box

[AlastairH]

im a bit mift, i have a contact table with data of address's and a store id that is unique. I have connected to this another table with correspondence relating to the store on it. this second table has the store id also and is connected to the first via this. the join says all data from the contact table and only those from the coro table that are equal.
next i have a form relating to a query run off the two tables giving me the basic info for the contacts at the top. then a sub form below with the info of the corospodence in it.
the problem i have is that when i put a combo or list box on the main form to select a store from i get the same store appearing in the combo box 5 or six times, ie as many times as there are appearances in the corospondence table. when i only want the store to come up once so i can select that store and have all the info for that store to come up and appear in the main form and sub forms. i know this is easy to fix i just cant remember how to do it!
can any one help?
thanks
Alastair

 

[SOS]

It sounds like you are taking your combo information from the wrong place.

Check this sample to see if it helps.

 

[Mr. B]

AlastairH,

First, you don't want to use a query that is pulling data from both tables. You need to set the record source of your main form to be the coro table (the table that has the storeID and other info. Then set the record soruce for your sub form to be the contacts table. Then make sure that the sub form is linked to the main form using the StoreId field from both forms. This will link the two forms so that when you select a specific record in the main form, the related records for that store will be displayed in the sub form.

Next, use the Wizard when creating the combo box and specify that you want to use the results of the combo box to lookup a record in the main form.

HTH

 

[AlastairH]

Thank you for both replies both helped, the first one i realised i had my main join wrong and corrected. the second on was actually the problem. my main query included both tables and as the second table had lots of different correspondence relating to the first all those instances were coming up. Have fixed as you suggested thank you very much.

Can i further this a little. so now i want to select two criteria that the form will base the info on. the first is the client then the store. Do i make a group query that the main form bases it self on taking from the contacts, that way giving me a list of just the client names and then the rest on sub forms within subforms?? does this make sense. should i attach the database with very little data in?

 

[Mr. B]

I don't think I undersand your next question. Please post the database with more details.

 

[AlastairH]

please find the database attached. The form i am making is called correspondence. What i am aiming to do is be able to fist select the client name and then select the store i am after and for the form to bring up all the information for this store, so i can look at their sales and add correspondence. a further down the line i would like to add a sales chart and also stock levels of our product available. and much further down the line possibly purchase orders and invoices etc linking all up. but for now i would just like to be able to get the form to pull up the info based on the criteria as above. Thanks heaps for all your help
Cheers
Alastair

 

[Mr. B]

AlastairH,

I only had a couple of minutes to look at the database. Your form seems to be working for what you have so far. As for adding the second criteria, I would need to understand your data a little more.

Unfortunately I am going to be out of my office all day tomorrow and will not be able to look at this. Perhaps someone else may be able to help.

 

[Simon_MT]

I didn't know that the Farmers Trading Company was still operating?

Here's how I would handle it

I would start with a Menu loaded on database open.

I would have table:

Customers (so there is one FTC)
Outlets (Contact Details) linked into Customers
Outlets Action (combine Catalogues + Correspondence) with Action Flag
Outlets Personnel
Outlets Sales with Flag to denote Order / Sales / Credit Note.
Outlets Status

On Outlets Action you have a lookup on Outlets Personnel

I would have a Dialogue Form with Fields:
Customer - a query with just Customers (eg. FTC)
Outlet - a query with just Outlets (eg. Whangaparoa, qualified as all FTC Outlets)

You could include Outlet Personnel as well so if that person is calling you see any recent correspondence etc.

If Outlet is blank you see all FTC activity otherwise only that Outlet.

On the Outlet Form you have two Subforms:
Outlets Sales (including Orders)
Outlets Action

You end up with a more structured approach matching up Outlets to Customers.

Simon

 

[AlastairH]

Thanks heaps ill have a look at this and change my structure. You must be a Kiwi or at least been to New Zealand? yes Farmers is still around. I must stress here that all the info in the test database is made up, ie fictional for testing purposes, just in case i get called up on this by anyone working at Farmers or any where else.
thanks a lot for everyones help,
Cheers
Alastair

 

[Simon_MT]

Then I won't mention the Warehouse - oops!

Simon

 

[AlastairH]

lol.......................