Text Box vs Combo Box

[Kiwi-Wombat]

In a small application I am using there are two tables. In the main table there is a relationship with another. The main table stores only the Record ID of the item in the second table but a look up box displays the item name. Standard stuff.

On the associated form, if I use a combo box, the item name displays OK but I specifically don't want a combo box. If I use a text box it only displays the Record ID.

How do I show the item name in a text box? Is this something done in the expression builder?

Thanks in advance

 

[RichO]

If I'm understanding your question correctly, if the item name is an actual field in your table you should be able to add a text box bound to that field.

 

[Kiwi-Wombat]

Sorry, bad post

Attached is a very simple database. Form "Names1" (data entry mode), is how it looks with a combo box

Form "Names2" (viewing mode) is what happens when I put a text box instead of the combo box. How do I get the salutation field in the "Names2" form to show the actual salutation?

 

[RichO]

Make a new query and select both Names and Salutations. The 2 tables are already linked by SalutationID.

Then add FirstName, LastName, Salutation (Not ID) and ContactsID as fields.

Save this query and use it as the record source for your form. Salutation will now be an available field for your text box.

 

[Kiwi-Wombat]

Thanks, that bit I understand

That was a simple example, now take it one step forward.

Assume that the form is more complex with 20 fields that is used for viewing and data entry. I have the switch between modes working well but the salutation is a combo box necessary for data entry. In viewing mode I want to hide the combo box and replace it with the text box but to show the single salutation for that record. (I know how to hide and show the fields at the appropriate times)

Can I use the query just for one text box on the form?

 

[RichO]

You can use the Choose function if all you have is those few to pick from. Put this in the control source of your textbox:

=Choose([SalutationID],"Mr","Mrs","Miss","Ms")

 

[Kiwi-Wombat]

Unfortunately that was only an example and the choice could be limitless.

The form is used for data entry and viewing. In data entry mode the fields are sunken and in viewing the fields are flat. All this works quite happily but one field is a look up combo from another table (linked by ID).

My main gripe is that this one box always looks like a combo box no matter what effect is used.

I just want it to look flat like the other fields when in viewing mode and I assumed that the only way to do this was by putting a text box there. There may be another way to do it though.

 

[RichO]

OK here's another possibility...

If your Saluation table is an accurate example of the table in your actual DB (a text value based on a number), you can use DLookup to get the correct value from the table into a textbox.

=DLookUp("Salutation","Salutation","[SalutationID] = " & [SalutationID])

 

[Pat Hartman]

I would not use DLookup(). I would base the form on a query that joins the necessary tables. That way you can choose the numeric "key" value from the base table and the text "display" value from the lookup table. This is what querying is all about. Reuniting what normalization has cast asunder.

 

[RichO]

Actually, that was the first suggestion I made but Kiwi seemed to be looking for other alternatives.

 

[KenHigg]

=Choose([SalutationID],"Mr","Mrs","Miss","Ms")

.
.
.
.
cool!

 

[Pat Hartman]

Actually, that was the first suggestion I made but Kiwi seemed to be looking for other alternatives.

- Kiwi-Wombat didn't understand your post and probably doesn't understand mine either but we'll see

 

[Kiwi-Wombat]

Actually the DLookUp works but there is quite a perceptible delay in displaying the answer

Most of my posting is done while I am at work but the database is at home (not allowed to be put on work network) so I have to wait till I get home time zone (Australia).

I will try both suggestions tonight (my time) and see what happens

Thanks for your collective help so far.

 

[Kiwi-Wombat]

The query works. Not sure why it didn't first time I tried but did last night

Thanks again for your assistance