Using SQL & VBA in MS ACCESS to run a query

Question

I need the correct syntax (with all the quotation marks) for Me.frmButtons.Form.Button01.caption in the SQL-string.

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

Answer 1

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.

Answer 2

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"