Using a ComboBox selection to complete 3 text boxes. Please Help

[Guest]

I have a ComboBox (cboSupplierName) that will list supplier names and I would like it to perform a SQL Select statment to find that record from the Supplier Contact Information table and Display the Supplier ID and Email in Text boxes on the form. Here is the code I have for getting the value for one of the text box txtSupplierID

Dim Supplierid As Strin
Supplierid = "SELECT [Supplier Contact Information].[Supplier_Number]" &
"FROM [Supplier Contact Information] " &
"WHERE [Supplier Contact Information].[Supplier_Name] = '" & cboSupplierName.Value & "';
txtSupplierID.Value = Supplieri

For some reason the SQL stament will not execute. The Syntax of the stament is correct. Please help.

 

[Gerald Stanley]

VB and SQL are two separate languages. If you wish to
execute a SQL statement within VB you have to use DAO or
ADO execute method or DoCmd.RunSQL.

The usual way of addressing your problem of populating
other controls based upon a selection in a combo box is to
include this extra information in the RowSource SQL of the
comboBox then reference it via the column property.

By way of example,

cboSupplierName.RowSource = "SELECT supplier_name,
supplier_Number FROM [Supplier Contact Information]"

In the combo box's AfterUpdate eventHandler,

txtSupplierID.Value = cboSupplierName.Column(1)

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
I have a ComboBox (cboSupplierName) that will list

supplier names and I would like it to perform a SQL Select
statment to find that record from the Supplier Contact
Information table and Display the Supplier ID and Email in
Text boxes on the form. Here is the code I have for
getting the value for one of the text box txtSupplierID:

Dim Supplierid As String
Supplierid = "SELECT [Supplier Contact

Information].[Supplier_Number]" & _

"FROM [Supplier Contact Information] " & _
"WHERE [Supplier Contact

Information].[Supplier_Name] = '" & cboSupplierName.Value &
"';"

txtSupplierID.Value = Supplierid

For some reason the SQL stament will not execute. The

Syntax of the stament is correct. Please help.

 

[Gerald Stanley]

Although you know that it is a SQL statement, to VB it is
just a string. It is only when the string is placed in a
'recognised' object like DAO, ADO or DoCmd, that VB knows
that some interaction is taking place with the database.
It is only DAO and ADO that provide an object for
information returned from the execution of the SQL which is
why you cannot use DoCmd.RunSQL for SELECT queries.

Hope That Helps
Gerald Stanley MCSD