How to run simple select statements in VBA (MS Access)

[rk0000]

I am very new to VBA and MS Access. I need run a select statement and get the data into some string.

SQL = "Select CustomerName from Customers"

SQL = SQL & " WHERE CustomerID = '" & StrCustID & "' "

I need to run this query and get the value of CustomerName in to some string say (strName). How can i do that?

Thanks

 

[Remou]

Hi
This looks very like a case for Dlookup:
DLookup Function Examples
faq705-4978

 

[LittleSmudge]

Remember that DLookUp() - infact DAnything() is a very slow process.

For JUST ONE data value it is marginally faster than the alternative, however, if you need more than one field from the same domain then open a recordset

Dim db as Database
Set db = Currentdb
Dim rst As DAO.Recordset

Set rst = Db.OpenRecordSet("SELECT Field1, Field2, Field3 " _
                         & "FROM tblName " _
                         & "WHERE Field4 = 'foo'")

txtControl1 = rst!Field1
txtControl2 = rst!Field2
txtControl3 = rst!Field3

rst.Close
Set rst = Nothing
set db = Nothing

'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.