Use SQL to open a record

[Guest]

Hello:
If I create an SQL string - Private mySQL As String, and use it to get a
record based on what a user enters into a combo box by doing the following:
mySQL = "SELECT * FROM Company Where " _ & " Company name Like '*" &
Me![comboSelect] & "*'"
How can I then get the Company ID (primary key) from the record that mySQL
returns?
Thanks.
Jade

 

[John Vinson]

Hello:
If I create an SQL string - Private mySQL As String, and use it to get a
record based on what a user enters into a combo box by doing the following:
mySQL = "SELECT * FROM Company Where " _ & " Company name Like '*" &
Me![comboSelect] & "*'"
How can I then get the Company ID (primary key) from the record that mySQL
returns?
Thanks.
Jade

Unless you're doing something ELSE with strSQL, I'd suggest a much
simpler approach:

lngCompanyID = DLookUp("CompanyID]", "[Company]", "[Company Name] LIKE
""*" & Me![comboSelect] & "*""")

Note that Company Name must be put into brackets in your SQL string;
and that the SQL string might return zero, one, or many records
depending on the text in the combo box.

Could you explain the rationale here? What are the values returned by
ComboSelect, and why are you doing a wildcard search for any company
containing that value?

John W. Vinson[MVP]

 

[Guest]

Thanks John.
I am trying to find the company based on the name that the user enters in
the combo box. Your approach is simpler. I will use it instead.
Thanks, J.

Hello:
If I create an SQL string - Private mySQL As String, and use it to get a
record based on what a user enters into a combo box by doing the following:
mySQL = "SELECT * FROM Company Where " _ & " Company name Like '*" &
Me![comboSelect] & "*'"
How can I then get the Company ID (primary key) from the record that mySQL
returns?
Thanks.
Jade

Unless you're doing something ELSE with strSQL, I'd suggest a much
simpler approach:

lngCompanyID = DLookUp("CompanyID]", "[Company]", "[Company Name] LIKE
""*" & Me![comboSelect] & "*""")

Note that Company Name must be put into brackets in your SQL string;
and that the SQL string might return zero, one, or many records
depending on the text in the combo box.

Could you explain the rationale here? What are the values returned by
ComboSelect, and why are you doing a wildcard search for any company
containing that value?

John W. Vinson[MVP]

 

[Guest]

Hi John:
If the number of records returned is greater than one, how can I create a
temporary list box on the fly?
Thanks,
J

Hello:
If I create an SQL string - Private mySQL As String, and use it to get a
record based on what a user enters into a combo box by doing the following:
mySQL = "SELECT * FROM Company Where " _ & " Company name Like '*" &
Me![comboSelect] & "*'"
How can I then get the Company ID (primary key) from the record that mySQL
returns?
Thanks.
Jade

Unless you're doing something ELSE with strSQL, I'd suggest a much
simpler approach:

lngCompanyID = DLookUp("CompanyID]", "[Company]", "[Company Name] LIKE
""*" & Me![comboSelect] & "*""")

Note that Company Name must be put into brackets in your SQL string;
and that the SQL string might return zero, one, or many records
depending on the text in the combo box.

Could you explain the rationale here? What are the values returned by
ComboSelect, and why are you doing a wildcard search for any company
containing that value?

John W. Vinson[MVP]

 

[John Vinson]

Hi John:
If the number of records returned is greater than one, how can I create a
temporary list box on the fly?
Thanks,
J

I don't know, because I have no idea how your tables or your form are
structured. Care to give me a little insight?

If all you want to do is find the record for a particular company, you
can use the Combo Box Wizard. You don't need ANY queries, or any code
other than what the wizard builds for you. Try creating a combo box
using the toolbox - be sure the magic wand icon is selected - and
choose the option "Use this combo to find a record".

John W. Vinson[MVP]