Solved 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)

 

[mkdrep]

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.

I do not have a table which market a county belongs to, but I can easily create one. thank you for the suggestion

 

[mkdrep]

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

Market Area has multiple counties in it. I think I know what I need to do. Thank you for your reply

 

[mkdrep]

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)

In my example, the county does determine the market. I'll work with your first suggestion. Thank you for taking the time to reply

 

[Uncle Gizmo]

I'll work with your first suggestion.

You might find this video helpful, it demonstrates how to set up the combo box and the separate text box. Interestingly, you don't need the separate text box to store any information in the table you just leave the information in the combo box ...

Get Info From a Combo-box 1 - Nifty Access​

 

[Gasman]

Market Area has multiple counties in it. I think I know what I need to do. Thank you for your reply

You might need what they call a cascading combo in that case?
Where one combo defines what is available in another combo?

In that case it might be better to select Market, and then County?

 

[Ken Sheridan]

Market Area has multiple counties in it. I think I know what I need to do. Thank you for your reply

That begs the question whether the reverse is also true, and a county can encompass more than one market areas in whole or in part. An analogy would be UK counties and postal areas, where postal area boundaries can overlap county boundaries. At my last address I lived in Staffordshire, and in postal area WA. Postal area WA also includes part of West Midland county however (this county no longer exists in fact, as it now comprises a bunch of unitary authorities, but we still use the old county name informally). So the relationship type is a binary many to many one.

If, in your case, a market area boundary can overlap with a county boundary then the same would be true. A binary many to many relationship type is modelled by a table which resolves it into two unary one to may relationship types, so you'd need a table with two foreign key columns, County and Market #, referencing the primary keys of Counties and MarketAreas tables. In your form you'd need two correlated combo boxes as Gasman suggests, and the user would first select a county in one combo box, and then a market area in the second combo box, whose list would now be restricted to those market areas in which the county is wholly or partly located. In many cases I'd guess the list would of one county only, but not always. The attached file illustrates how correlated combo boxes can be used to select a country, then a region, and finally a city.

If, on the other hand, the whole of a county can be in one market area only, then the relationship type is one to many, so the user would select the county, and the market area in which it's located would be shown in an unbound text box as I described earlier in post #5.

 

[mkdrep]

You might find this video helpful, it demonstrates how to set up the combo box and the separate text box. Interestingly, you don't need the separate text box to store any information in the table you just leave the information in the combo box ...

Get Info From a Combo-box 1 - Nifty Access​

thank you!

 

[XPS35]

Since this topic has been open for a while, I've had plenty of time to think about it
What I'm thinking now is that you undoubtedly also record the customer's place of residence. This also establishes the county, and with the county, the market. To prevent input errors and minimize user effort, you should only need to specify the city with the customer. The county and market are then fixed.
However, this requires that, in addition to a table of counties and markets I mentioned earlier, you also have a table of cities and counties.
Your datamodel could look something like: