Memo Box not populating properly based on other combo and list boxes (1 Viewer)

[GMSRE]

Hello,
I am working in Access 2007.
I was able to create County combo box County that limits its list based on the selection made in State combo box. I was also able to create a County ID list box that limits its list based on the selection made in County combo box. For example the State combo box contains the states. The County combo box B contains the county names. The County ID list box contains the COUNTY ID numbers. I selected row source type Table/Query and entered the following code in row source.

To filter the proper county associated with the state - [Forms]![Form1]![State]

To filter the proper county id number associated with the county - [Forms]![Form1]![County]

I created a Description memo box to string the state name, county name, and county ID number. Here is the code I used.

Private Sub State_AfterUpdate()
Me.County = Null
Me.County.Requery
Me.County = Me.County.ItemData(0)
End Sub

Private Sub County_AfterUpdate()
Me.CountyID = Null
Me.CountyID.Requery
Me.CountyID = Me.CountyID.ItemData(0)
[Description] = Me.State & " " & Me.County & " " & Me.CountyID
End Sub

The problem is that the county ID shows up in place of the county name in the Description memo.

For example this is the result I am looking for in the memo box “Arizona Good 0001”.
I am getting this result instead “Arizona 001 0001”.

Please help.

Thank you.

GMSRE

 

[boblarson]

Sounds like a common problem with using lookups defined at table level. See here for why that is not good:
http://www.mvps.org/access/lookupfields.htm
Or you may not have included the table that has the description values in it as part of your combo's row source. And instead of selecting the id column you would select the description column as part of the query.

 

[GMSRE]

Sounds like a common problem with using lookups defined at table level. See here for why that is not good:
Or you may not have included the table that has the description values in it as part of your combo's row source. And instead of selecting the id column you would select the description column as part of the query.

Hi Bob,

I do have an input table that I created. I opened a blank form added the combo and list boxes one at a time. How can I tie in the table I created? What code would be good to use to get what I am looking for? Thank you for getting back to me quickly and your help.

GMSRE

 

[Vistoro]

If you have an update, use Append._