Form field to display query lookup value

[Blitznb]

Struggling here.

Have a table (Customers) which has lookup fields for codes. This is expanded to a query (customers extended). The query displays the lookup value just fine. I have a form which is split and is used to search for customers based on a number of things including the codes. Unless I use a combo box I cant get the results to display anything other than the ID or hidden value of the lookup value. Using a combo box puts multiple drop down arrows on the form. I truely would like to not have this. Can you display lookup values in a text box of a form?

I have tried =DLookUp("Code","CodesSIC","[ID]) and multiple variations. I believe the situation is, without binding the text box to SIC1, SIC2, or SIC3 it doesnt know what field to equate it to.

Any suggestions for displaying the lookup value in forms text box?

 

[GinaWhipp]

If I understood you correctly...

If numeric...

=DLookup("FieldFromTableOrQuery", "YourTableOrQuery", "[FieldFromTableOrQuery]=" & Me![FieldFromForm])

If text...

=DLookup("FieldFromTableOrQuery", "YourTable", "[FieldFromTableOrQuery]='" & Me![FieldFromForm] & "'")

OR

If a Combo Box…

=[ComboNameHere].[Column](PutColumnNumberHere)

…and remember Combo Boxes start with 0 not 1.

 

[Blitznb]

Hello Gina......Thank you for the reply and review. I think the issue I am having is the field that displays a look up value (That i am assigning DLOOKUP too) is not specific to the form. It is based on search results.


So when I write it as Me![SIC1] it just shows up blank.

Ill try all the objects you provided. It is numeric, but that shows blank.

 

[Blitznb]

Maybe a better way to ask is

I need to bind a text box to [SIC1] and show a lookup value of
=DLookup("Code", "CodesSIC", "[ID]") All numeric.

 

[GinaWhipp]

Okay are saying that the text box on the form that you want to run the DLookup value against is itself a DLookup? Because this...

=DLookup("Code", "CodesSIC", "[ID]=" & Me![SIC1])

...should work.

 

[Blitznb]

Fear I maybe confusing the situation. I attached a screenshot of the form.

In the Entry Data portion. The SIC1 field is a combo box that gets its values from table [CodesSic]. When selected and "ran" the Results section displays the right records, but the SIC1 field displays the ID or hidden value when I am looking for

. The #Name? error is a result of using.
 
=DLookup("Code", "CodesSIC", "[ID]=" & Me![ClientID])
 
I also tried this as text version. This also displays the #Name? error.
 
If I bind the SIC 1 Results text box to [SIC1] the ID shows. I want it to show the code just like the SIC 1 Combo box in the entry data portion without the drop down arrows. Guess its not as easy as it seems it should be.

 

[GinaWhipp]

Try changing the field name to txtSIC 1 or cboSIC 1 or anything but that is not the name of anyting in the DLookup function.

 

[Blitznb]

Changing the search text fields to txtName seems to work very well. Thank you.

 

[GinaWhipp]

Most welcome... just remember when you see #NAME? that usually means Access is confused because you either named a field and enclosed the same nam in a function OR you used a Reserved Word or both. Just in case, a list of Reserved Words...

http://allenbrowne.com/AppIssueBadWord.html