Use SQL to open a record

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...

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]
 
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.

John Vinson said:
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
Click to expand...

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]
Click to expand...
 
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

John Vinson said:
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
Click to expand...

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]
Click to expand...
 
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
Click to expand...

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]
 
Back
Top