In MS Access VBA, how to get field values when a single field has multiple columns

Question

I am working with an Access database in MS Access 2016 that I inherited from someone else. One of the tables has a field (EthnicID) that contains 2 values: the first column contains a numeric value which is the ID and the second column contains a string value with is the description associated with the ID number. For example: 1,"African-American/Black"; 2,"Caucasian"; 3,"Hispanic/Latino"; etc. The values are from a lookup table named tbl_Ethnicity.

The table that contains the [EthnicID] field (tblClients) shows the following Field Properties in Table Design in the "Lookup" tab: Display Control: Combo Box Row Source Type: Table/Query Row Source: tbl_Ethnicity Bound Column: 1 Column Count: 2 Column Widths: 0";1" The "General" tab shows the following Field Properties: Field Size: Long Integer Format: Default Value: 0

As you might expect given the above properties, when I view tblClients the EthnicID field/column shows a string value.

However, I am writing some code to generate a new table based on values pulled from tblClients along with values from various other tables (let's not get into a discussion about whether or not this is the best approach!). The process involves generating a new recordset which I then loop through. In the SQL used in the VBA to generate the recordset (rst), I have it select "tblClient.EthnicID" and I want the string value to be returned. However, rst!EthnicID always returns a numeric value, not the string value. I've also tried rst.Fields(9) and rst.Fields(9).Column(1) and rst.Fields(9, 1). Either those don't work or they also return a numeric value; changing the 1 to a 2 does not return a string either (and I'm pretty sure the columns are zero-based).

Is it possible to get the SQL to return the string value from the 2nd column of the table which is displayed in the table rather than the numeric value?

(I do realize I could use a DLookup or a Choose statement in the VBA, but since the string value is already showing in the table, I thought it would be far more efficient to just get the string value directly rather than looking up the string value in tbl_Ethnicity again.)

I've searched the web and didn't find an answer, though I'm probably using the wrong words in the search.

Thanks for any ideas!

Parfait Parfait · Accepted Answer · 2019-08-01T21:35:19

Simply incorporate another JOIN in your query from the tblClients to the lookup table, tbl_Ethnicity, to return the corresponding string value to EthnicID. Adjust actual columns and table names below as needed. There is no multiple column field occurring here. Use the Query Designer if you need to add more JOIN clauses.

SELECT ..., e.EthnicityStringValue
FROM tblClients c
INNER JOIN tbl_Ethnicity e ON c.EthnicID = e.ID

Essentially, this ID serves as a foreign key from clients table to the primary key of lookup table, all perfectly reasonable setup of a relational database such as the classic Customers-Orders relationship.

Those drop down comboboxes are for human readability since we are asking the user to add or update this foreign key in clients table. Instead of asking users to pick from a list of meaningless numbers (1, 2, 3, ...), the string values help as meaningful links: African-American/Black, Caucasian, Hispanic/Latino, .... Therefore, the comboboxes show the string value but hide the corresponding ID but ultimately saves this hidden number value in table and not the displayed string.

In MS Access VBA, how to get field values when a single field has multiple columns

1 Answers