Selecting data from a drop down box in one field, enters a value in a 2nd field

[mkdrep]

I have a simple customer address form. I have a [County} field and [Market_Number] field on the form (see attached) which allows me determine where my customers are located.
Currently, my employees have to select the county where the customer is located and then look on our NY State map which shows what market # to use for chosen county. As you might imagine, this does lead to some operator errors.

All I want to have happen is when an employee choses a county, in the example I used "Livingston", the market # field will automatically be populated with the #2 which is the market where Livingston County presides in our system. I want it to be store in the Market# field for later use as well.

I would think there would be any easy way to do this, but I don't know what keywords to insert in this forum to get the answer I need. Thank you in advance for any help you can give me.

 

[Gasman]

Bring in the Market as an extra field in the County combo.
Then in the AfterUpdate event of the combo, populate the Market control with the relevant column from the combo.
NB: Combo columns start at 0.

 

[XPS35]

To do this, it's necessary to have a table that specifies which market a county belongs to. I don't understand from the question whether this is the case.
If it is, then you don't need to do anything. The county-market relationship is already established, and you don't need to specify it again with the client.

 

[Gasman]

TBH if a county has just one market, then no neeď to store it, unless it could change?

 

[Ken Sheridan]

I want it to be store in the Market# field for later use as well.

If the County determines the Market #, storing the Market # in a column in addition to the County would introduce redundancy, and the table would not be normalized to Third Normal Form (3NF), which requires all non-key columns to be functionally determined solely by the primary key of the table, leaving it open to the risk of update anomalies.

The correct solution would be to return the Market # column in the County combo box's RowSource query, e.g.

SELECT CountyID, County, [Market #]
FROM Counties
ORDER BY County;

You can then add an unbound text box to the form, inferencing the combo box's Column property in the text box's ControlSource property. The Column property is zero based, so the Market # column would be Column(2). The text box's ControlSource property would therefore be like this:

=[cboCounty].[Column](2)

Whenever you need to refer to the Market # column anywhere in the database you can do similarly, or join the Counties table to the referencing table in a query, and return the Market # column in the query.

If, on the other hand, the County does not determine the Market #, i.e. the latter could change for the county in question, but you would want the original Market # in earlier rows in the referencing table to be retained regardless of any changes in the referenced Counties table, then the text box should be bound to a County # column in the referencing table, and a value assigned to it in the combo box's AfterUpdate event procedure:

Me.[Market #] = Me.cboCounty.Column(2)