CombBox values SELECTion

[tangentkid]

Hi all,

Apologies for the total Newbie question. This is my very first Access Database and I've run into problems right from the off!

I have 2 tables: Customer & Address
Amongst other things the tables contain the following fields:
Customer: CustomerID, ShippingAddressIDRef, InvoiceAddressIDRef
Address: AddressID, StreetAddress, CustomerIdRef

The idea is for a number of addresses to be associated with each customer.
When choosing a Shipping address for the customer the user is presented with a ComboBox which is populated from the Address.StreetAddress field of all Address records that have their CustomerIDRef field matching the CustomerID. i.e. all the addresses that are associated with that customer. When a street address is chosen the appropriate AddressID is then stored in the ShippingAddressIDRef field of the Customer record.

Much the same is then true when choosing an Invoice Address.

I've created a ComboBox that shows the Address.StreetAddress field and hides the Address.AddressID field. When a StreetAddress is chosen the ShippingAddressIDRef in the Customer record is set correctly. So far so good...

The problem is that I don't seem to be able to filter the entries that appear in the ComboBox to only show addresses that relate to the current company i.e. where the Address.CustomerIDRef field matches the CustomerID in the current Customer record.

I've tried putting the following SELECT in the Row Source property of the ComboBox:

SELECT Address.AddressID, Address.Street
FROM Address INNER JOIN Customer ON Address.CustomerIDRef = Customer.CustomerID;

However the effect is show all addresses for which Address.CustomerIDRef is non-zero rather than equal to Customer.CustomerID

What am I doing wrong?

Thanks.

 

[dkinley]

Sounds like you are trying to use cascading combo boxes; however I am a bit confused by the statement "However the effect is show all addresses for which Address.CustomerIDRef is non-zero rather than equal to Customer.CustomerID".

Does this mean you want to show all addresses regardless of the customer?

If not, here is a thread with a demo of cascades ...
http://www.access-programmers.co.uk/forums/showthread.php?t=154680

If so, then remove the customer table from your row source and just use the address table where the customer reference ID is greater than zero.

-dK

 

[tangentkid]

Hi dk,

Thanks for the lightning fast reply. I'm just leaving work here in the UK and hoped for an answer by morning, not before I put my coat on!

I guess that you have misunderstood. I don't want to do cascading ComboBoxes, just a single ComboBox that only shows the addresses that relate to the current customer.

The effect that I get when using the SELECT statement shown in my previous post is to restrict addresses to all those that have Address.CustomerIDRef set to a value other than 0. Whereas I'm trying to restrict the list to those where Address.CustomerIDRef is equal to the CustomerID in the current Customer record. i.e. I end up getting nearly all the addresses as there are very few that have Address.CustomerIDRef set to 0.

When the database is running there should be no addreses that have Address.CustomerIDRef set to 0 but for the purposes if finding out what's going on I set one to 0 just as a test. A value of 0 in this field indicates that the address is associated with no customers as there is no customer with a CustomerID of 0.

Does that make any sense?

 

[dkinley]

Okay, I think I get it. If you have the Customer ID stored on the form with the combo box, you can set the where clause of the address table reference ID to = Me.txtControlNameWith Customer ID.

In this manner, instead of a cascading combo box based on a combo box, it is based on a control on the form to filter the address list correctly. That sound right?

-dK

 

[HiTechCoach]

dk,

In this manner, instead of a cascading combo box based on a combo box, it is based on a control on the form to filter the address list correctly. That sound right?

Sounds exactly like cascading combo boxes to me.

If the customer selection is a combo box control and you want to filter a address combo box control based on a value in the customer combo box, then this is called cascading combo boxes.

Example:
The row source of the second combo box will reference the first combo box.

SELECT Address.AddressID, Address.Street
FROM Address INNER JOIN Customer ON Address.CustomerIDRef = forms!YouFormName.CustomerComboBox;

 

[tangentkid]

Hi dk,

Umm....
The line that you quoted doesn't come from either of my posts. You must be thinking of somebody else!

I do indeed want to filter an address ComboBox based on a value but that value is a field within the current record in the Customer Table and not a value selected from a higher-level ComboBox.

I may have totally misunderstood how Access works and there is no such thing as the 'current' record from the Customer Table but this is how it works in FileMaker Pro which is my background. I apologise if I'm completely missing something here!

I don't quite understand the syntax of part of your Code but I tried to substitute your suggested

forms!YourFormName.CustomerComboBox

with

forms!CustomerForm.CustomerID

where my form is called CustomerForm and the field showing the customer ID for the current record is called CustomerID. However, when I try to save this, I get a Syntax Error in JOIN operation message.

BTW I'm running Access 2003, in case this uses different syntax.

Thanks.

 

[dkinley]

HiTech was quoting one of my posts, also the version shouldn't matter in this instance. The reference, Forms!..., is sorta making use of a current record (when it is requeried - explained next). I have attached a demo.

Since we haven't gotten to this part but what is critical, when you move from record to record (unless you are only opening a specific record) you must refresh the combo box. This is done on the OnCurrent() event of the form and by the code ...

Me.cboComboID.Requery

I didn't format the combo box properly because I am showing the main ID for demo purposes. But you can see the mechination of what I think it is you are trying to do.

Hope that helps,
-dK

 

[tangentkid]

Hi dk,

First apologies for my late night post yesterday. I hadn't spotted that it wasn't you who included the quote... from you!

Thanks very much for the demo. This, together with your explanation, made it clear what I need to do. I've now changed my db accordingly and it works!

In retrospect, I think that I've jumped in way too deep, way too quickly as I hadn't expected this to involve event handling. However it has given me a crash introduction to this plus shown that the effect can be achieved using a SELECT WHERE rather than defining a relationship between the tables and using an INNER JOIN. A better method overall I guess.

Thanks so much for your help and patience. It's much appreciated.

Cheers,
- tk

 

[dkinley]

No problem! You have resolution and picked up a new tip on the way - overall success in my book.

Good luck with your project!

-dK