using SQL in VBA to retrieve data

[sovereign]

Afternoon,

So im looking for a bit of help.

i want to retrieve some data from a table i have pieced together a bit of code but get an error to few parameters.

Dim db As dao.Database
   Dim Lrs As dao.Recordset
   Dim LSQL As String
   Dim Lname As String
   
   'Open connection to current Access database
   Set db = CurrentDb()
   
   'Create SQL statement to retrieve value from contacts table
   LSQL = "SELECT Contacts.Cn " & _
            "FROM Contacts " & _
            "WHERE (((Contacts.Cc)=[Forms]![frmClaims]![frmClaims_ThirdParty]![thirdPartyCc]))"
   Debug.Print LSQL
   Set Lrs = db.OpenRecordset(LSQL)
   
   'Retrieve value if data is found
   If Lrs.EOF = False Then
      Lname = Lrs("cn")
   Else
      Lname = "Not found"
   End If
   
   Lrs.Close
   Set Lrs = Nothing
   
   getgst = Lname
   
   Debug.Print getgst

any help or pointing to the right direction would be appreciated.

when i paste the SQL into a query it works fine

[pbaldy]

The form reference needs to be concatenated into the string, adding delimiters if a text data type.

[sovereign]

The form reference needs to be concatenated into the string, adding delimiters if a text data type.

Thank you for the quick response.

sorry i am new at this have only used the SELECT a couple of times.

what do you mean by this?

Regards

Sorry have now done that thanks, i just needed to think about what you was saying rather than just post back.

regards

[pbaldy]

If the data type is numeric:

"WHERE Contacts.Cc= " & [Forms]![frmClaims]![frmClaims_ThirdParty]![thirdPartyCc]

if text

"WHERE Contacts.Cc= '" & [Forms]![frmClaims]![frmClaims_ThirdParty]![thirdPartyCc] & "'"

[John_G]

If frmClaims_ThirdParty is a sub-form on frmClaims, your syntax is not correct - you cannot refer to a sub-form by its name. You have to use the name of the sub-form control on the main form (i.e. the control which contains the sub-form). It would look something like this:

[Forms]![frmClaims]![subform_container].form![thirdPartyCc]

Replace subform_container with the actual name of your control. Notice the additional bit, .form.

[pbaldy]

Sorry have now done that thanks, i just needed to think about what you was saying rather than just post back.

regards

Just noticed this. Does this mean you have it working now?

[sovereign]

Just noticed this. Does this mean you have it working now?

yeah i did get it working perfectly!

Thank you!

[pbaldy]

Happy to help!

[sovereign]

Happy to help!

now you mention it i have just started a new thread you could lend your knowledge too