Need advice for using SQL & VBA in MS ACCESS to run a query

user_15428580 0
Viewed 71

Please do not ask questions, it is a long and complicated story :-)

I just need the correct syntax (with all the quotation marks) for Me.frmButtons.Form.Button01.caption in the SQL-string. Thanks. This one doesn't work:

Private Sub Button01_Click()
             
Dim strsql As String
            
strsql = "SELECT * FROM table01 WHERE fldName = ""Me.bForm.Form.Button01.caption""    
ORDER BY FldName"

Me.mForm.Form.RecordSource = strsql
Me.mForm.Form.Requery
                
End Sub
ms-query ms-access forms vba sql
2 Answers

In VBA the SQL is a string, but not the variables and objects, so this objects must be concatenated to the string and the character to cancatenate strings is "&"

The SQL sentence should be like this:

strsql = "SELECT * FROM table01 WHERE fldName = '" & Me.bForm.Form.Button01.caption & "' ORDER BY FldName"
user_158872700

This should work:

Private Sub Button01_Click()
             
    Dim strsql As String
            
    strsql = "SELECT * FROM table01 " & _
    "WHERE fldName = '" & Me!bForm.Form!Button01.Caption & "' " & _
    "ORDER BY FldName"
    Me!mForm.Form.RecordSource = strsql
                
End Sub

Also, you should give your buttons meaningful names.

user_35272970
Related