Executing select statement in vba

[barboza]

We have created a form from our custody table called frmCustody.

The form has a text box called - txtClntNum
and a field called client.

I have the following statement:

Option Compare Database
Dim strLabID As Long, strClient As String


Private Sub txtClntNum_AfterUpdate()

strClient = "Select [ClientName] from Clients where [ClientNum] = txtClntNum"
[CLIENT] = strClient

End Sub

ClientName is the client's name stored in a table called Clients.
ClientNum is the client number as stored in the Clients table.

The idea is to enter the clients identification number (txtClntnum), and have the correct client found in the Clients table. Once the client is found we want to store the clients name in strClient, and then have it appear in the client field in frmCustody.

The only thing that ever shows up in the client field is whatever is in quotes after strClient =
It seems the statement is never evaluated.

Any help would be greatly appreciated.
Thanks

 

[RuralGuy]

Try letting the ComboBox Wizard help you.

 

[Bungle]

Open a recordset using the SQL then populate strClient and text box from that.
dim dbs as database
dim rst as recordset
set dbs=CurrentDb
set rst=dbs.OpenRecordset("Select [ClientName] from Clients where [ClientNum] = " & txtClntNum & ";"
strClient=rst!ClientName
set rst=nothing
set dbs=nothing

 

[Bungle]

Sorry, missing ) from the end!

The & part is not a line break, it's because txtCIntNum is a variable and so has to be parsed outside the SQL string.

You can miss the final & ";" off without any problems, it's just good practice to put it there so you know that's the end of the SQL, but it's needed if you have a text variable because the final part then becomes
[ClientTxt] = '" & txtClntTxt & "';"

Obviously if [ClientName] is [PWSNAME] then the next line is strClient=rst!PWSNAME