Query result to a form

[Joe M.]

I have a form with 2 text boxes and a cmd button and a query with 2 fields;
field 1 is ID# and field 2 is DESCRIPTION. I want the user to enter the ID#
in text box1 and have the matching DESCRIPTION found in the query to be
returned and filled in text box 2 when the cmd button is pushed. The ID#
field is unique so only one result will be returned. Can someone give me the
steps to accomplish this?

Thanks,
Joe M.

 

[Stefan Hoffmann]

hi Joe,

I have a form with 2 text boxes and a cmd button and a query with 2 fields;
field 1 is ID# and field 2 is DESCRIPTION. I want the user to enter the ID#
in text box1 and have the matching DESCRIPTION found in the query to be
returned and filled in text box 2 when the cmd button is pushed. The ID#
field is unique so only one result will be returned. Can someone give me the
steps to accomplish this?

This should work:

Private Sub cmdButton_Click()

txtBox2.Value = DLookup("[DESCRIPTION]", _
"yourQuery", _
"[ID#] = '" & Replace(Nz(txtBox1.Value, ""), "'", "''")) & "'")

End Sub


mfG
--> stefan <--

 

[Joe M.]

Stefan,

I did a copy and paste of your code but got a syntax error. Can you help
please?

Many thanks,
Joe M.

hi Joe,

I have a form with 2 text boxes and a cmd button and a query with 2 fields;
field 1 is ID# and field 2 is DESCRIPTION. I want the user to enter the ID#
in text box1 and have the matching DESCRIPTION found in the query to be
returned and filled in text box 2 when the cmd button is pushed. The ID#
field is unique so only one result will be returned. Can someone give me the
steps to accomplish this?

This should work:

Private Sub cmdButton_Click()

txtBox2.Value = DLookup("[DESCRIPTION]", _
"yourQuery", _
"[ID#] = '" & Replace(Nz(txtBox1.Value, ""), "'", "''")) & "'")

End Sub


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Joe,

I did a copy and paste of your code but got a syntax error. Can you help
please?

Where does the error shows up? Can you post your complete pasted code?


mfG
--> stefan <--

 

[Joe M.]

Stephan, here's the code:

txtBox2.Value = DLookup("[DESCRIPTION]","Query1","[ID#] = '" &
Replace(Nz(txtBox1.Value, ""), "'", "''")) & "'")


Thanks,
Joe M.

hi Joe,

I have a form with 2 text boxes and a cmd button and a query with 2 fields;
field 1 is ID# and field 2 is DESCRIPTION. I want the user to enter the ID#
in text box1 and have the matching DESCRIPTION found in the query to be
returned and filled in text box 2 when the cmd button is pushed. The ID#
field is unique so only one result will be returned. Can someone give me the
steps to accomplish this?

This should work:

Private Sub cmdButton_Click()

txtBox2.Value = DLookup("[DESCRIPTION]", _
"yourQuery", _
"[ID#] = '" & Replace(Nz(txtBox1.Value, ""), "'", "''")) & "'")

End Sub


mfG
--> stefan <--

 

[John W. Vinson]

I have a form with 2 text boxes and a cmd button and a query with 2 fields;
field 1 is ID# and field 2 is DESCRIPTION. I want the user to enter the ID#
in text box1 and have the matching DESCRIPTION found in the query to be
returned and filled in text box 2 when the cmd button is pushed. The ID#
field is unique so only one result will be returned. Can someone give me the
steps to accomplish this?

Thanks,
Joe M.

Ummmm...

Why?

Are you trying to store the description redundantly in a second table? If so,
don't; it's not needed, will waste space, and can lead to discrepancies such
as the description in your first table being edited and now not matching the
stored description in the second table.

You can *display* the description in an unbound textbox on the form by putting
a combo box based on your query onto the form. If the ID is a meaningless
numeric ID, set its columnwidth to zero to just display the description; if
you want to see both the id and the description, use the combo box to display
(or select) the ID and put a textbox on the form with a controlsource

=comboboxname.Column(1)

to display the second field in the combo's query (it's zero based).