SQL in VBA

[Guest]

Hi,
I am tring to enter the following SQL query in VB. I am recieving
a variable from a combobox, I need the SQL to run the value from
the combobox. The SQL should look up the value and come back with
the corresponding number from that table. All help greatly appreciated,
from an inexperienced user of access and VBA.
Thanks,
Conor.

Dim SQL As String
strManufacturer = Me.Manufacturer.Value

SQL = "Select ManID " & _
"From TabManufacturer.ManID " & _
"WHERE TabManufacturer.Manufacturer = ' & strManufacturer & '"

DoCmd.RunSQL SQL

 

[Allen Browne]

Concatenate the value into the string.

If Manufacturer is a text field, try:
SQL = "Select ManID " & _
"From TabManufacturer.ManID " & _
"WHERE TabManufacturer.Manufacturer = '" & strManufacturer & "';"

RunSQL is for action queries, and this is a SELECT query, so you lost me in
the end.

 

[Guest]

Manufacturer is a text field and ManID is a number.
So what your say is, RunSQL is for update, delete etc only.
How can i run a SELECT SQL with in VBA.

 

[Guest]

If you are only trying to retrieve one number, then the DLookup would be a
better route.

SomeNumber = Nz(DLookup("[ManID]", "tblManufacturer", _
"[Manufacturer= '" & strManufacturer & "'"),0)

I usually wrap a DLookup in the Nz functon to avoid returning a Null to a
non Variant data type. If the Manufacturer is not in the table, the Nz will
convert the Null to 0.

Now, this is only good if you want to return one number for the
manufacturer. If there can be more tahn one ManID, you can run the SQL, but
to use it, you have to open it as a recordset:

Set rst = Currentdb.OpenRecordset(SQL)

I would suggest you change SQL to strSQL. SQL is the name of a property of
the QueryDef object and Access might get confused about it.

 

[Tim Ferguson]

SQL = "Select ManID " & _
"From TabManufacturer.ManID " & _
"WHERE TabManufacturer.Manufacturer = ' & strManufacturer & '"

The FROM clause should contain a table name, not a column name:

SELECT ManID
FROM tabManufacturer
WHERE Manufacturer = "Eric"


but DMax is probably even easier.


Hope that helps


Tim F

 

[Tim Ferguson]

SQL = "Select ManID " & _ ....

DoCmd.RunSQL SQL

Sorry, I missed this too: RunSQL is used for action queries (delete,
create, update, select into, etc); if you want to select records then you
need to OpenRecordset them.

All the best


Tim F