MS Access - Text box vs Combo box-CodePudding

I want to present some information in a form, read only, using a field which contains a numeric index to another table. The other table contains the text description that I wish to present on my form.

Using a Combo Box I can set the object's Row Source to select the ID and text description fields from the source table, bind the query to the first column and use the Column Count parameter to display the text description. To make the object read only I can set Enabled to No. However, the drop-down arrow remains visible of course, as this is a Combo Box.

Using a Text Box I cannot work out how to display anything other than the numeric index value.

My query then - is it possible to hide the drop-down arrow when using a Combo Box in this way, or alternatively (and preferably), is there a way to use the ID field as an index with a Text Box?

CodePudding user response：

As we all know, a combo box does do the translation of the PK "id" to the other table, and thus allows display of some other typical description box.

but, for a continues form, picking list, or just display as read only?

Then build a query like this:

So, the above query will pull in that extra description/text column for you. In this case the Hotel Name. So at one time, the data came from a form that most certainly did use a combo box. But, for a report, or some other form (espeiclly some display list, or read only as you note), then just join in that text colum from the other table.

You are now free to use a plain jane text box, and you are free to include/display that information in a standard text box. The forms record source will have/include that text column from the other table as a simple text box.

Also note VERY close that the query is what we call a LEFT join. We do this, since some of the combo box original column may not have a selection. thus the left join - it will STILL include the main table rows - even if the "translate" to the other child table has no value.

So in above query, RIGHT click on that join line, and we selected this option:

And then now how the query builder shows a arrow head. In fact, as a result you find that a VERY HIGH % of such queries you build will be left join. (just think left side table - always included - and the child table - optional included).

So, now you are free to drop in a text box into that "display" form, continues form,, or report - and you have a plane jane text box for display of that other table "text or description" field. And you don't need a combo box for this approach.

CodePudding user response：

i will answer your last question with regards to "..... or alternatively (and preferably), is there a way to use the ID field as an index with a Text Box?"

Ms Access has a dlookup function, which can lookup a value in another table using the current value from the active form/table.

Example of dlookup function syntax are below

for numeric data type use

DLookup("FieldName" , "TableName" , "Criteria = n")

or for string data type use DLookup("FieldName" , "TableName" , "Criteria= 'string'")

or for data data type use DLookup("FieldName" , "TableName" , "Criteria= #date#")

Parameters or Arguments FieldName A field, calculation, control on a form, or function that you wish to return. TableName The set of records. This can be a table or a query name. Criteria Optional. It is the WHERE clause to apply to the TableName.

example is DLookup("[UnitPrice]", "Order Details", "OrderID = 10248")

i hope this helps, so you can use the id value from textbox control.