Combining VBA with SQL

[PrincessBretti]

Ok heres my code:

Dim strSQL As String
strSQL = "select [First name], [last name] from [contact details]
where [client reference number] = "
& Me.Client_Reference_Number

Me.txtName = "Appointment Details for " & strSQL

But of course this just types out the sql statement instead of running it and appending it to the text...

How can I make the sql execute and append on to the text?

I am at work and for some reason none of the Access help files will work... so I can't work this out myself

Anybody???

Cheers

Princess Bretti

 

[PearlGI]

The following should work.

Dim strSQL As String, db as Database, rs as Recordset
strSQL = "select [First name], [last name] from [contact details]
where [client reference number] = "
& Me.Client_Reference_Number

Set db=CurrentDb
Set rs=db.OpenRecordSet(strSQL)
Me.txtName = "Appointment Details for " & rs![First Name] & " " & rs!{Last Name]


HTH

 

[Atomic Shrimp]

Alternativel'y, a 'quick and dirty' non-SQL method would be:

Me.txtName = "Appointment Details for " & DLookup("[First name] & ' ' & [last name]","[contact details]","[client reference number] = " & Me.Client_Reference_Number)

Mike