Simple Form/Table Question

[zadar24]

My first post from a newbie.

I have created a form where I need to pull data out of a table multiple times.
The table is "Customer". The form has various other fields which work fine.
The tables are already fully populated.

My form looks like this:

Date:
Company Name:
Customer 1:
Customer 2:
Customer 3:

All the choices are drop downs. My issue is that when I select John Doe in Customer 1, it populates John Doe in Customer 2 and 3. I want to be able to select 3 different names from the same Customer table.

Please advise,
Thanks in advance.

[Scott Price]

A combo box has both a control source and a record source. The general way to do this is to leave the control source blank, and set the record source to a sql statement or query. SELECT [ColumnName] FROM [TableName], should do the trick.

Being that the combo box is now 'unbound', meaning that it's control source is blank, you'll need to write a spot of code to tell the database what to do with the values that you choose in each combo box.

Regards,
Scott

[Megalog]

From what you've said, I believe that all 3 of your Customer fields are tied to the same control source, seeing how all 3 update upon selecting just one.

Make sure they have unique control sources, or if this is an unbound form, then no control sources at all.

[zadar24]

Your explainations help. It worked. Thanks.

Can you elaborate on the additional code I need to write and where?
I looked around to see if anyone else posted the same type of question and I couldn't see anything.

Could you point me to the right direction?

Thanks

[Scott Price]

The code really depends on what you want to do with these three combo boxes.

Do you want to use them as a filter for a query? A description of what you are trying to accomplish with them would help us help you :-)

Regards,
Scott

[zadar24]

Yes, sorry about that. I would like the data which comes off the form to be able to go into a query that I built off the form.

The query right now grabs all the entries from the drop down form, but now I would like to add this new field into my existing query.

Let me know if you need any more info.

[Scott Price]

Please post the SQL of your query, and the names of your combo boxes.

Just to clarify this: When you choose three names in your three combo boxes (one each) you want your query to return just the records associated with those three names, right?

If you choose only two names, you want only two records returned, etc?

Regards,
Scott

[zadar24]

I've kinda modified my query/form since my initial post but the same problem remains.

My form now allows for my to select names in Customer and Customer2 and Customer3 from the same table. I now want them to go into my Query.

To clarify what I need: I want my Query to show all 3 Customers, but it now only shows Customer. All I really need is to be able to show Customer2 and Customer3 in the query.


Thanks



SELECT [Client List].Title, Date.Date, Address.Address, [Return Customer].[Return Customer], Customer.Customer

FROM Customer INNER JOIN ([Return Customer] INNER JOIN (Address INNER JOIN ([Client List] INNER JOIN [Date] ON [Client List].Date = Date.ID) ON Address.ID = [Client List].Address) ON [Return Customer].ID = [Client List].[Return Customer]) ON Customer.ID = [Client List].Customer

ORDER BY [Client List].Title;

[Scott Price]

You will need to add the WHERE clause to include the entries of the three combo boxes. The SQL will end up looking more like this:

WHERE Customer.Customer = Forms![FormName]![ComboBoxName] OR Customer.Customer = Forms![FormName]![ComboBoxName2] OR Customer.Customer = Forms![FormName]![ComboBoxName3]
 

This will end up before the ORDER BY clause.

Regards,
Scott

PS to use the CODE tags, simply select your sql statement, then click on the # icon on the top of the reply window. Thanks!