Fill List box from combo box value

[gowans07]

Hi,
I'm trying to create a form in which a user selects a Customer from a Combo box and then can select a "Dongle" (or Product they have) from a list box. Below is a screen shot of the relationship between the tables.
Also here is a screen shot of the form in which the combo/list box are found:
Finally i've tried creating a parameter query to solve this but had no luck, i set the control source of the list box to the query.
Thanks for the help, can provide more information if needed
TG
Attachments

 

[wjburke2]

It sounds like you want to have your first combo box to requery your Dongle combo after update. This should retreive the correct dongles to your second combo box.

Make your Source for your cmboDongle
SELECT Dongle_ID.tbl_Dongle
FROM tbl_Dongle
WHERE (Customer Name)=Me.cmbCustomer) ;


Private Sub CustSel_AfterUpdate()
Me.cmboDongle.Requery
End Sub

 

[sparks80]

Hi,

This should be quite easy to achieve.

I presume you have populated the Customers combobox with records from the Customers table.

I would set the row source for your dongle listbox to the following:

SELECT Dongle ID, Creation Date FROM Tbl_Dongle WHERE ([Customer Name] = Forms!YourFormName!cmbCustomer)

And then on the AfterUpdate event for the Customer combobox enter the following code:

Me!cmbDongle.Requery

 

[gowans07]

Thanks for the reply, is that record or control source for the SQL statement? Also adding in the .AfterUpdate event produces activex errors. Any idea how to solve?

 

[sparks80]

The control source is the field in your table where the data will be stored. If this is left blank then the combobox will be unbound

The record source gives the list of values that will appear in the drop down box, so this is where you need to place the SQL code.

Can you tell me what the run-time error number and description is, although this may be fixed when you set the row source correctly.

 

[gowans07]

Okay, have added the SQL into CmbDongle into the Row Source, is this correct? The error has now gone, although the combo box is not filled with the expected data. I get two pop up parameter boxes asking me to enter "DongleID" "CreationDate" and "CustomerName".

 

[sparks80]

Sorry, you will need to enter the names exactly as they appear in the table, like this:

[Customer Name]
[Dongle ID]
[Creation Date]

A useful tip:
In general rule it is best not to use spaces in your field names, it is much better to use the underscore symbol instead.

But for now try this:
SELECT [Dongle ID], [Creation Date] FROM Tbl_Dongle WHERE ([Customer Name] = Forms!YourFormName!cmbCustomer)

And you need to replace YourFormName with the name of your form.

 

[gowans07]

No problem, okay that parts all sorted. I still cant get it to display the query results.
1) Added Me!CmbDongle.Requery
2) Added SQL tp CmbDongle's Row Source

Have i missed anything ?

 

[sparks80]

Would it be possible to post a copy of the database and I'll take a look for you.

 

[gowans07]

yeh thats fine, give us a few mins and i'll post it back up

 

[gowans07]

Okay here it is, i've removed all the other tables and anything irrelevant for the moment

 

[sparks80]

Hi,

The issue is with the customer combobox - you have used the customer ID as the bound field. This means that when the query checks the value of this combobox it get's the customer's number, and not their name.

You have two choices:
1 - change the bound column of cmbCustomer to 2
2 - add the customer ID to the Dongle table rather than customer name.

I suggest option 2. Add "Customer_ID" to the dongle table and remove "Customer Name". Then link the customer table and dongle table using the customer ID. Lastly change the SQL for the dongle combobox to this:

SELECT [Dongle ID], [Creation Date] FROM Tbl_Dongle WHERE ([Customer_ID] = Forms!YourFormName!cmbCustomer)

 

[gowans07]

It works, thank you very much! Kicking myself now i changed the Customer ID to Customer Name in the Dongle table earlier. Thanks again for your time. Slowly learning my way further round access

 

[sparks80]

No worries.

Another piece of useful advice is to link tables using a numerical field rather than text.
In your case it is much better to use "Customer ID" than "Customer Name".

In this case you will have one customer ID (autonumber), and many dongles with their associated Customer ID (number, long integer).
When you have many records you will find this is much faster than using text.