How do I return data instead of the Record ID?

[Neil_in_Japan]

I feel foolish - but here goes:

I have a form. That form has a combo box. That combo box is supported by a field in a table called Issues. That field is a Lookup field to another table called Contact List.

So my form combo box is populated via field in a table that references another table.

This works fine - and I can drop down and pick a person etc. Email address show up.

The form field is bound to column 1.


The issue:

I want to send an email to the person as selected by the combo box.

This too works, but the email TO: field contains the Record Number, not the text of the field.? How do I get the text of the combo box - which I can see, to appear in the TO field.

I am using a Dlookup:

=DLookUp("[MCCS Managers]","Issues","[ID]=" & Nz([ID],0))

this returns a ' 1 ' which is the record ID number from the Issues table.

MCCS Managers is the field name, Issues is the table, ID of course...

To be redundant, this cascaded field lookup works fine in the form. But my DLookup returns a ' 1 '. (the ID column, not the 'text' column.)

how do I get the text column to return?

Thanks

Neil

 

[JHB]

.. That form has a combo box. That combo box is supported by a field in a table called Issues. That field is a Lookup field to another table called Contact List.
..
how do I get the text column to return?

By not using lookup fields in a table, or get the value from the lookup field column (which contain the Email-adr. TO), in the form where you've the lookup field.

 

[Neil_in_Japan]

It is frustrating to me that I can 'see' the data just fine in the form - but I cannot get to it. It is display only.

So, essentially you have to create two or more lookups in different forms to get to the same data?

I create a table to store the information
I create a form to see/add info to the table.
I create a query to see the table data, which ignores the record number
I create a second form that uses the query information to actually manipulate the data to send, etc...

Just wanna make sure I get the process down. I might have to undo / redo quite a few lookups to get this to work.

 

[JHB]

It is frustrating to me that I can 'see' the data just fine in the form - but I cannot get to it. It is display only.
..

That is the Evil of the LookUp fields (I never use them).

 

[namliam]

Not too mention that Dlookups can really put a brake on your database, they really slow down things once you get into more and more records.

 

[Neil_in_Japan]

I found a solution - kind of a hack - I am using unbound hidden text fields with default value =[FieldName].[Column](2)

Works like a champ...

DLookups are slow though..

 

[tsbergeron]

I am new to Access. Would you please explain your "hack" solution a little better? I have the queries showing the text value but when I create a form or report, it shows the id number.

 

[Neil_in_Japan]

Sure:

I created an unbound text field and hid it. Let's call it txtDropDown. For the Default value, I used the 'drop down' field's value:

=[dropdown].[Column](2)

Also in the Control Source:

=[dropdown].[column](2)

Then in the drop down field, EVENT Tab, I made a macro entry in the 'after update':

Requery
Control Name = txtDropDown

That will update the txtDropDown with the new selection.

Let me know if it helps... .Good Luck!