selecting a different value from the one a user sees on a form

[sillyr]

Hi I have a form where i have a dropdown box. The user selects either:
No
Yes-Observer
Yes-Technician
The table (LU Observer Present Lookup) that is used for the dropdown box has three fields:
ID
Observer Database Code (a number)
Observer_Presen t (text)

The Observer Present column is what the user selects on the form. I want to write a code that selects the Database Code that corresponds to the Observer Present selected. The ID field is the same for both entries.
I tried using Dlookup- but am not getting results


Private Sub OBSERVER__PRESE NT_AfterUpdate( )
Dlookup("OBSERV ER DATABASE CODE",
"LU OBSERVER PRESENT LOOKUP","[ID]=
FORMS!OBSERVER PRESENT")

End Sub


Thanks for any help

[puppydogbuddy]

Under Access object naming standards, you should avoid using spaces and special characters and "entire name" upper case lettering.If you do use spaces, the name must be enclosed by brackets.

http://www.acc-technology.com/namconv.htm

The above notwithstanding , your main problem is the punctuation you used in referencing the Form object.

If the [ID] field is numeric, try this syntax for the DLookup:

Private Sub OBSERVER__PRESENT_AfterUpdate()
Dlookup("[OBSERVER DATABASE CODE]",
"[LU OBSERVER PRESENT LOOKUP]","[ID]= " & FORMS![OBSERVER PRESENT])

If the [ID] field is text, try this syntax for the DLookup:

Private Sub OBSERVER__PRESENT_AfterUpdate()
Dlookup("[OBSERVER DATABASE CODE]",
"[LU OBSERVER PRESENT LOOKUP]","[ID]= '" & FORMS![OBSERVER PRESENT] & "'")

[DonRayner]

Hi I have a form where i have a dropdown box. The user selects either:
No
Yes-Observer
Yes-Technician
The table (LU Observer Present Lookup) that is used for the dropdown box has three fields:
ID
Observer Database Code (a number)
Observer_Presen t (text)

The Observer Present column is what the user selects on the form. I want to write a code that selects the Database Code that corresponds to the Observer Present selected. The ID field is the same for both entries.
I tried using Dlookup- but am not getting results


Private Sub OBSERVER__PRESE NT_AfterUpdate( )
Dlookup("OBSERV ER DATABASE CODE",
"LU OBSERVER PRESENT LOOKUP","[ID]=
FORMS!OBSERVER PRESENT")

End Sub


Thanks for any help

If you base your combo box on a query you can have the query use both the
Observer_Presen t (text) and Observer Database Code (a number) in that order. Then on the combo box properties for data select the bound column to 2 and in the properties for format select the column count to 1.

This way the user is presented with Observer_Presen t(text) but the actual value of the combo box is Observer Database Code (a number).

[sillyr]

Thanks for your help. I went with the second option of changing the number of bound columns and column widths. Of course now I have a new problem. The table for the dropdown box has two columns- one is a text and the other is numeric. I tried to make a query to sum of the results, but it is not working because it thinks the values are text. Is t here a way for me to convert the values to numeric so I can sum them up.