TextBox data from a linked table (1 Viewer)

When I create a Form using the Access2007 wizard and choose the Multiple-objects feature I end up with a form with a top section and a Detail Section in which there are multiple records from the linked table tabAmplifier. One of those tabAmplifier.fields is a ComboBox from the table itself. The form is showing this ComboBox with the little down arrow for a user to click and choose. The actual field in the main table is a number field linked to another table with a Relation between the two. Hence, when the user in the form change the field value to another Text the main table field is changed to another number representing this text. The SQL query delivering the fields value is:

SELECT tabSub.lngSubIndex, tabSub.txtSubType
FROM tabSub
ORDER BY tabSub.lngCategory;

So, the ComboBox receive two fields, and deliver to the linked table tabAmplifier.lngSubType a numeric value from tabSub.lngSubIndex
Because I assigned column size to 0cm;3cm for the ComboBox the user only see the txtSubType but in reality is choosing the lngSubType value.

Now the Challenge: In my form I Do Not want the user to update that particular value from the ComboBox, Instead I want him to use another ComboBox which is already implemented and work fine. The problem I have is that I need to change the ComboBox to a TextBox that I will VBA prevent access other then select and copy if need be. So I tried to change that ComboBox to a TextBox. My problem now is that the TextBox is showing the field value lngSubType, a number instead of the txtSubType, a text. I want to keep showing the text value there since the number is not representative of the actual number value, but I need to keep recording in that field the actual number value, because that is how the tabAmplifier is built.

The Question: How can I change that ComboBox to a TextBox that will show me the text value and not the number value related to that text value?

Or can you propose another method to achieve what I need?

Thanks for your help

three options

easiest way is to leave as a combobox and set its enabled property to false and locked property to true.

Alternative is to have a textbox and modify your recordsource to join to the tabSub table and return the field to be displayed - again setting the textbox to disabled and locked. You may also need to modify the form recordset type to 'dynaset - inconsistent updates'

Or use a dlookup as the controlsource to a textbox


Sounds like you are using lookups in your table design but do have an understanding of the implications. So you are presumably aware of the potential issues

http://access.mvps.org/access/lookupfields.htm
https://bytes.com/topic/access/answers/204202-need-alternative-lookup-fields

I wish I had gotten those two links before. I would have saved me so much aggravation. This truly is an illumination. Now I have to understand the whole concept and implement a Query instead of working directly with my table, and probably get rid of those lookup in my table. I have a lot of rethinking to do.
Thanks again for the tips.