Combo Boxes

[Alan Mitchell]

Help please.

I have three tables - Customers, Addresses, Items.

Customers has one field - customer name.
Addresses has two fields - customer name and address.
These tables link on a 1 to many relationship on customer
name.

Table Items has 3 fields - Item, CustomerName,
CustomerAddress.

I want to
1) Enter an item description
2) Choose the customer name from a drop down list or
combo box based on the customers table.
3) Choose the customer address from a drop down list or
combo box based on addresses table FILTERED BY THE
ALREADY CHOSEN CUSTOMER NAME.

I have linked both the tables Customers and Addresses to
Itmes on a 1 to many basis.

I am now stuck.

Help.

PS I realise this isn't a very well framed question but
I am at the limit of mu knowledge here.

 

[Graham R Seach]

Alan,

1. In the form's Current event, add teh following code:
Me.cboAddresses.RowSource = ""
Me.cboItems.RowSource = ""

2. In cboCustomers' AfterUpdateEvent, add the following code:
Me.cboAddresses.RowSource = "SELECT DISTINCT Address FROM tblAddresses
WHERE CustomerName = """ & Me.cboCustomers & """ ORDER BY Address"

2. In cboAddresses' AfterUpdateEvent, add the following code:
Me.cboItems.RowSource = "SELECT DISTINCT Item FROM tblItems WHERE
CustomerName = """ & Me.cboCustomers & """ AND Address = """ &
Me.cboAddresses & """ ORDER BY Item"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

Graham,

Thank you very much for your response. Unfortunately I
am a basic Access user and don't know how to add code in
the way you describe. This is very much a reflection on
me rather than your response.
I suppose I am really asking is there any way to do what
I need without coding?

 

[Graham R Seach]

Alan,

No, there's no way of doing this without coding.

To add code, do the following:

1. Open the form in design view.
2. Double-click the form to display the Properties dialog box.
3. Select the All tab.
4. Select Customers combo box, and change its Name property to
"cboCustomers".
5. Select the Addresses combo box, and change its Name property to
"cboAddress".
6. Select the Items combo box, and change its Name property to "cboItems".
7. Click on the darker section (outside the form limits).
8. Select the Events tab on the Properties dialog.
9. Click in the On Current event.
9a. You'll see 2 little buttons appear on the right-hand-side. Click the
rightmost button.
10. If the Choose Builder dialog box displays, select Code Builder, and
click OK.
11. Enter the following code between the Private Sub... and End Sub lines:
Me.cboAddresses.RowSource = ""
Me.cboItems.RowSource = ""

12. Close the code window by clicking the red X (top right-hand-side).
13. Select cboCustomers.
14. In the Properties dialog, click in the After Update event, and repeat
Steps 9a and 10.
15. Enter the following code between the Private Sub... and End Sub lines:
Me.cboAddresses.RowSource = "SELECT DISTINCT Address FROM
tblAddresses WHERE CustomerName = """ & Me.cboCustomers & """ ORDER BY
Address"

NOTES:
(a) If "tblAddress" is not the name of the Addresses table,
rename it in the above code.
(b) If "Address" or "CustomerName" are not the names of the
table fields being used, rename them in the above code.
(b) Make sure the code is all on one line (the newsreader may
wrap the text).

16. Close the code window by clicking the red X (top right-hand-side).
17. Select cboAddress.
18. In the Properties dialog, click in the After Update event, and repeat
Steps 9a and 10.
19. Enter the following code between the Private Sub... and End Sub lines:
Me.cboItems.RowSource = "SELECT DISTINCT Item FROM tblItems WHERE
CustomerName = """ & Me.cboCustomers & """ AND Address = """ &
Me.cboAddresses & """ ORDER BY Item"

NOTES:
(a) If "tblAddress" is not the name of the Addresses table,
rename it in the above code.
(b) If "Item", "Address" or "CustomerName" are not the names of
the table fields being used, rename them in the above code.
(b) Make sure the code is all on one line (the newsreader may
wrap the text).

20. Close the code window by clicking the red X (top right-hand-side).
21. Save the form.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia