Solved Fill in form fields based on a combo box.

[Thel888]

Hello!

I'm resorting to the Forum to ask me a question about the automatic completion of fields in a form, based on a combo box.

I have a table called TabClient with the following fields and columns in this order:
Client_ID => First Column = AutoNumber (Primary Key).
Number_Security => Second Column = Short Text.
Full_Name => Third column = Short Text.
Phone => Fourth column = Short Text.
Mobile => Fifth column = Short Text.
Whatsapp => Sixth column = Short Text.

And I have a form called Frm_CadServ with the following fields:
cboProp => Combo box that receives data from an SQL query (SELECT TabClient.Full_Name, TabClient.Number_Security FROM TabClient ORDER BY TabClient.Full_Name and displays only the Full_Name in it.
txtReq => Text box.
txtPhone => Text box.
txtMobile => Text box.
txtWhatsapp => Text box.

In the After updating event of txtReq, I put this code:

Me.txtPhone = DLookup("Phone", "TabClient", "Phone=" & Me!cboProp.Column(4) & "'")
Me.txtMobile = DLookup("Mobile", "TabClient", "Mobile=" & Me!cboProp.Column(5) & "'")
Me.txtWhatsapp = DLookup("Whatsapp", "TabClient", "Whatsapp=" & Me!cboProp.Column(6) & "'")

When I run it, I get the message "Runtime error 3075. Syntax error in string in query expression 'Phone='".

I already looked for the solution in some forums, I tried several expressions, but none worked.

Does anyone know what I'm doing wrong?

 

[theDBguy]

Hi. Welcome to AWF!

You can make it easier if you just add all the columns you need from the table into your Combobox. You can hide them, if you don't want the user to see them. You can then simply refer to those hidden columns in your textbox to display the information based on the selected item from the combobox.

 

[mike60smart]

Why not add the addition fields required to the Select statement:-

SELECT TabClient.Full_Name, TabClient.Number_Security ,TabClient.Phone , TabClient.Mobile, TabClient.Whatsapp FROM TabClient

Then in the AfterUpdate of the Combobox use:-

Me.Phone = Me.cboName.Column(3)
Me.Mobile = Me.cboName.Column(4)
Me.Whatsapp= Me.cboName.Column(5)

 

[Gasman]

You are notmatching your single quotes.
But why look up phone with criteria of phone= ?
Same with the other lookups?

 

[Thel888]

Great!
The combined ideas of the two of you (theDBguy and mike60smart) solved the problem!
Very grateful to all of you!
Problem solved!

 

[Thel888]

You are notmatching your single quotes.
But why look up phone with criteria of phone= ?
Same with the other lookups?

Thank you for your attention.
It was my fault for writing a large and unnecessary code, the solution was much simpler and is registered there in the answer.
Gratitude!