Need help with cascading

[matey56]

Hello,
I have a lookup table with two fields (Location and Code). Every location has a corresponding code. In my form, Location is a combo box and when I select a location I would like the Code field to display the corresponding code from the table. How do I write the syntax for that?

[June7]

How is this a cascading issue? Can a Code have multiple locations?

What exactly are you saving into record? Is that field also named Code? I would advise not using exact same field name in multiple tables.

Possibly all you need is an expression in textbox that references combobox column that contains Code: =cbxLocation.Column(1)

If you want to provide db for analysis, follow instructions at bottom of my post.

[matey56]

Ok, that was way easier! LOL

[June7]

An alternative is to include lookup table in form RecordSource, bind textbox to Code field and set its properties Locked Yes and TabStop No.
But if there is no need to complicate RecordSource, don't.

[ArviLaanemets]

An alternative is to have combo's RowSource as query like (I assume you will have location code as ControlSource of combo):

SELECT LocationCode, Location & ": " & LocationCode FROM YourLookupTable ORDER BY 2 // in case LocationCode is a string
or
SELECT LocationCode, Location & ": " & CStr(LocationCode) FROM YourLookupTable ORDER BY 2 // in case LocationCode is an integer

Your combo's value will be LocationCode, but the second column is what is displayed and contains both location and it's code

[matey56]

Ok, I went with the easy route. I put =cbxLocation.Column(1) as the control source for my Code field. It's very close to working the way I need, but my Location field has duplicates. In other words, each location could have multiple codes. When I switch between these duplicate Locations the Code doesn't change. It always defaults to the first Code in the list. I hope that makes sense.

How do I fix that?

[June7]

It should pull Code from whichever item is selected in combobox. Which column is Code in? Column(x) index begins with 0. If Code is in column 2 its index is 1.
Maybe Avril's suggestion in post 5 would be better.

Again, you could provide db for analysis.

[matey56]

It should pull Code from whichever item is selected in combobox. Which column is Code in? Column(x) index begins with 0. If Code is in column 2 its index is 1.
Maybe Avril's suggestion in post 5 would be better.

Again, you could provide db for analysis.

The code is in column 2 so the index is correct. The codes populate correctly. It just doesn't change when I toggle between the duplicate Locations in the list.

For example, when I select "San Antonio" in the Location combo box, the Code fields updates to SA456. Perfect. However, if I switch the combo box to the second "San Antonio" row, the Code field should update to SA457 but it doesn't. It stays as SA456.

Location Code

San Antonio	SA456
San Antonio	SA457
San Antonio	SA458

[mike60smart]

Upload a copy of the Db so we can see

[matey56]

Upload a copy of the Db so we can see

I really wish I could but there's too much proprietary data. :-(

[Welshgasman]

Cannot see that happening TBH if the codes actually are different.
You could try setting it in the after update event of the combo?
We do not need to see everything, just enough for the issue?

Surely location and codes are not proprietary? :-(

You can also use this on a COPY of your data.

Here is a quick knockup with your data. That works as it should?

[ArviLaanemets]

The code is in column 2 so the index is correct. The codes populate correctly. It just doesn't change when I toggle between the duplicate Locations in the list.

For example, when I select "San Antonio" in the Location combo box, the Code fields updates to SA456. Perfect. However, if I switch the combo box to the second "San Antonio" row, the Code field should update to SA457 but it doesn't. It stays as SA456.

Location Code

San Antonio	SA456
San Antonio	SA457
San Antonio	SA458

When you do like I adviced, the combo displays selections
...
"San Antonio: SA456"
"San Antonio: SA457"
"San Antonio: SA458"
...

When you start typing into combo "San Antonio...", then at some moment (e.g. when you have typed something like "San Ant"), those selections will be at top of active list of them, and you can easily select the right one.

But have location codes (e.g. SA456, or SA457, or SA458) as primary key, or indexed as an unique value, in your lookup table, and as foreign keys in any other tables you want it linked to - otherwise you will be in a world of hurt! Locations (e.g. San Antonio") must be present only in lookup table!

[matey56]

Cannot see that happening TBH if the codes actually are different.
You could try setting it in the after update event of the combo?
We do not need to see everything, just enough for the issue?

Surely location and codes are not proprietary? :-(

You can also use this on a COPY of your data.

Here is a quick knockup with your data. That works as it should?

I think I'm getting close. Now, when I change the location it changes it for all records in the DB. How do I make it so it's only changing it for the current record?

[mike60smart]

So your Combobox is not bound to a Control Source?

[Welshgasman]

I think I'm getting close. Now, when I change the location it changes it for all records in the DB. How do I make it so it's only changing it for the current record?

We have NO IDEA as to what you are doing!
It sounds like a continuous forms issue, but that is just a wild guess.
Where are you even updating the DB with that expression?