Store two columns from a combo box into two different data fields?

Question

Let's say you have a combo box that has cities in column 1, and state in column 2. Setting up the combo box to start sets it to store column 1 in the "city" data field, but you also want it to store column 2 in the "state" column.

The current idea I have, which I'm stuck on how to implement, is to have a hidden text box read the selection of the third column of the combo box, and have an event action on the combo box to update the data field with whatever is in the hidden text box.

And yes I recognize this is against best data practices for data duplication and references, but I work in the government and have to assume that at some point this entire database is going to be exported to Excel. It's a choice between data duplication and missing data for the next person to do this job.

Answer 1

Hi,

if I understand you correctly you don't need a hidden text box, just the state field in the recprd source of the form and 1 line of code in the AfterUpdate event of the combo:

Me!State = Me!MyCombo.Column(1)

The index for the columns start at 0, so 1 is the second column.

---

Karl

Access Developers Conference

Answer 2

Hi Robert,

Is this for a Value List combobox? If the combobox row source is based on another table, then exporting the data to Excel should not be a problem by simply joining the two tables together in a query and exporting the query into Excel.

Just my 2 cents...

Answer 3

> Is this for a Value List combobox? If the combobox row source is based on another table, then exporting the data to Excel should not be a problem by simply joining the two tables together in a query and exporting the query into Excel.

Unfortunately "should" is a dangerous word around where I work. I have to assume it's going to be exported/extracted in the crudest way possible. Unfortunately I don't yet have any meaningful understanding of how relational databases work. In this case it is pulling from another table, but let's use the city/state example. Let's say I store the value of "3" for city/state, which looks up "New York, New York." However I'm then told "Give me a report of all records in New York state." I would have ZERO idea how to do that.

I have to assume whoever has this job after me will have zero experience in Microsoft Access, because the "next to zero" experience I had was enough to get me the job that I'm vastly over-qualified for.

Answer 4

1 being the second column is news to me, because I have "bound column" set to 2, and the column goes ID, Value I need, other value. It stores the second selected column. Is it different in the SQL than Access interface?

As I understand, what you're proposing would put the value of a particular column of the combo box into the box, but it's not going to store that box anywhere. So in the city/county example, selecting the city would put the county into the text box on the form, but wouldn't store the county data in the data table.

Where am I reading this wrong?

"Me!State" is saying to put into the box what is on the other side of the equation

"Me!MyCombo.Column(1)" is selecting the data that has been selected in the combo box.

I also don't understand where this is supposed to go. If it goes into the combo box, won't that just change the value in the combo box? If it goes into a text box, that won't update when the combo box is changed.

Answer 5

Hi Robert,

It is unfortunate when you're stuck between a rock and a hard place. It is very kind of you to think about the next guy/gal but perhaps you can still do so while making the "right" choices now. If I expect the next person after me to have no idea how to use a database, I might try to build some "simple" features where I can to make it easier for them to use it. I know you said you are not an expert yet, but perhaps with our help, you could be one by the time the next person arrives. If so, making the right decisions now should only benefit everybody else down the line.

Just my 2 cents...

Answer 6

Let's say you have a combo box that has cities in column 1, and state in column 2. Setting up the combo box to start sets it to store column 1 in the "city" data field, but you also want it to store column 2 in the "state" column.

Such a table would is not normalized to Third Normal Form.  State is determined by city, so is transitively dependant on the key, leaving the table wide open to update anomalies.  The formal definition of 3NF is:

Third Normal Form: A relvar is in 3NF if and only if it is in 2NF and every non-key attribute is nontransitively dependent on the primary key.

Loosely speaking, in the language of the relational model, a relvar (relation variable) equates to a table, a tuple to a row (record) and an attribute to a column (field).

You can either show the state in an unbound control in the form with a ControlSource property of:

=cboCity.Column(2)

where the RowSource property of cboCity is like this:

SELECT CityID, City, State
FROM Cities INNER JOIN States
ON States.StateID = Cities.StateID
ORDER BY City;

The Column property is zero-based, so Column(2) references the third column, State.  Note BTW that, as city names can legitimately be duplicated, they must be identified by a distinct numeric key, usually an autonumber.*

Or you can use a separate unbound combo box to select the state, correlating the cboCity combo box with this so that the user firstly selects a state and then selects a city from a list restricted to those cities in the selected state.  You'll find an example in DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the form within the section on 'Building the user interface' includes correlated combo boxes to select a country, region (analogous to state) and city, the first two being unbound.  Note also how code in the NotInList event procedures of each allows you to add a country, region or city not currently represented in the database by typing the new name into the combo box.

BTW I spent 27 years working in government, and always ensured that applications were correctly designed.  Never did we experience any difficulties in exporting data to other file formats.

* not absolutely true.  It is always theoretically possible to use composite 'natural' keys, as illustrated in the ComboDemo file in the same OneDrive folder.  In reality I would not envisage anyone doing so, however.

---

Ken Sheridan, Stafford, England

Answer 7

Perhaps I should just say what I'm actually working on instead of using examples.

In a form, someone has to select an accounting code for a certain transaction (System A).  However a particular system we have to use refers to these transactions by a different code (System B). I have a table of the relations between system A and B accounting codes. I want to make it so when someone selects the System A accounting code in a dropdown, it saves the System B accounting code in the data table too. So when someone selects the accounting code, it will store the Column 2 value under "System A number" and the Column 3 value under "System B number" in the data table.

System A to B is a one-to-many relationship, because sometimes multiple codes in system A fall under the same code in system B.

If you had asked me a year ago to list the top 100 things I might be doing for a living in a year, Access programming would not have been on the list... and yet here I am.

Edit

As I understand it, wouldn't the better way to go be an "after update" event of "Set the value of (system B code) to (Column 3 of what was selected) and refresh"?

Answer 8

Whether it's cities and states or System A codes and System B codes is irrelevant.  The principle which you appear to have difficulty grasping is that you do not store an attribute in a table where that attribute is determined by another non-key attribute in the table.  All non-key attributes must be determined solely by the whole of the primary key of the table.  Otherwise the table contains redundancy and is at risk of update anomalies.

In your case CodeA is a determinant of CodeB, so only CodeA should be stored in the table.  In your form you simply have to make its RecordSource property a query which joins the table to the System B table on the code columns.  Provide that you have a correctly enforced relationship between the tables on these columns, a text box control in the form bound to CodeB will automatically show the correct value once a CodeA value is inserted.  The CodeB control can be made read only to prevent a user inadvertently trying to edit it by setting its Locked property to True and its Enabled property to False.

---

Ken Sheridan, Stafford, England

Answer 9

I completely agree that by best practices say I shouldn't store the data twice, because it's data duplication and susceptible to update anomalies. Unfortunately I have to build the system RIGHT NOW to my level of skill, not to what my skill will be in a month.

For example, let's say I build it your way, where the System B code isn't stored separately. I'm then given a task, "Show me all entries where the system B code is 1234567". Without the data duplication, I simply don't know how I'd do that. I roughly understand how to get a text box to show the correct value for System B, but then how would I search for a System B value?

I do intend to lock down the display of the System B number, but I also need to be able to search by that number.

So for right now, how do I pull a value from a certain column of a combobox and set a certain field to that data value? I think putting something like that in an "after update" on the System A combobox would accomplish what I'm looking for.

And again, I totally agree, this is terrible database design, but I have to assume that at some point the database is going to have its data exported in a way that belongs in a horror movie, and it's often a case of either duplicate or missing data.

Edit: For context, I deal with several databases where the users input their data directly into the table, and then ask me why their "This needs more validation than a teenager at hot topic" data doesn't work for queries. My solution was to create a form that looks like the table and blame the new look on the Office 365 upgrade.

Answer 10

It's important to understand that in relational-speak a table is not necessarily a base table, but can also be the result table of a query.  So when you join the two tables on the code columns and return all columns from the System A table and the CodeB column from the referenced System B table, the query returns a result table which is exactly the same as would have been the case if you had redundantly stored CodeB in the System A table.  You would simply have to restrict the query to:

WHERE CodeB = 1234567

to return a subset of rows with that value for CodeB, though in reality you would use a parameter rather than hard coding the value, which would allow the query to be used to return the subset of rows for any CodeB value entered by the user at runtime.

You can of course export the result table of a query in exactly the same way as you would export a base table.

---

Ken Sheridan, Stafford, England

Answer 11

I got the answer in another thread. I had to set an on-update event of the dropdown of

Me.Field3 =  Me.ComboxBoxName.column(2)

So that takes the third column (since it starts at 0) and puts it in Field3.

And again, this is horribly against best practices and is data duplication. I'm not happy about having to do this, but I'm not going to 'stand up for best practices' until I'm out of my probationary period in the organization :)

Answer 12

As far as getting just the state out of a string that is formatted like xxxx, yyyy
you could just use LIKE and a wildcard, for example:
LIKE "*, New York"
which would take anything ending with comma, blank, and New York
You could even shorten it to just
LIKE "*New York"
since the state is always at the very end of the string.