Form Button will not run OpenRecordSet VBA-CodePudding

I am trying to use the following VBA code to run a select query in MS Access.

Private Sub ManuReport_Click()
Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim StrSQL As String

Set dbs = CurrentDb

strSQL = "SELECT " & _
         "dbo_VENDOR1.ITEM_NO," & _
         "dbo_VENDOR1.ITEM_PRICE," & _
         "dbo_VENDOR2.ITEM_NO," & _
         "dbo_VENDOR2.ITEM_PRICE," & _
         "dbo_VENDOR1.MANUFACTURER_ITEM_NO," & _
         "dbo_VENDOR1.MANUFACTURER," & _
         "dbo_VENDOR1.ITEM_NAME " & _
         "From dbo_VENDOR2 " & _
         "INNER JOIN dbo_VENDOR1 " & _
         "ON dbo_VENDOR2.MANUFACTURER_ITEM_NO = dbo_VENDOR1.MANUFACTURER_ITEM_NO " & _
         "WHERE dbo_VENDOR1.MANUFACTURER IN ('MANUFACTURER CODE') " & _
         "And dbo_VENDOR1.ITEM_PRICE > dbo_VENDOR2.ITEM_PRICE "

Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenDynaset)
End Sub

I have added this to a button in MSACCES to pull this information from a linked SQL database. I have also been having issues with adding references to form text boxes but I may submit that as a separate question. Whenever I press the button, nothing happens. I don't even get an error screen. I have seen other answers where the issue seems to be how the OpenRecordSet is being used but I am having trouble understanding how I can apply it to this code. The query itself does work when I create a separate query in Access so I am not sure where the problem is. I reformatted the SQL portion of the code to make it easier to read here, but I have it formatted as a single line in the actual VBA code.

CodePudding user response：

It looks like you want to open a query in Access for display based on a SQL string

The following function will create a query based on the SQL string

Function createQry(qryName As String, sSQL As String)

    Dim qdf As QueryDef
        
    ' Delete existing query
    On Error Resume Next
    CurrentDb.QueryDefs.Delete (qryName)
    On Error GoTo 0
    
    Set qdf = CurrentDb.CreateQueryDef(qryName, sSQL)
    
End Function

If you use this code in your posted code like that

Private Sub ManuReport_Click()
    Dim dbs As DAO.Database
    Dim rsSQL As DAO.Recordset
    Dim StrSQL As String

    Set dbs = CurrentDb

    StrSQL = "SELECT " & _
        "dbo_VENDOR1.ITEM_NO," & _
        "dbo_VENDOR1.ITEM_PRICE," & _
        "dbo_VENDOR2.ITEM_NO," & _
        "dbo_VENDOR2.ITEM_PRICE," & _
        "dbo_VENDOR1.MANUFACTURER_ITEM_NO," & _
        "dbo_VENDOR1.MANUFACTURER," & _
        "dbo_VENDOR1.ITEM_NAME " & _
        "From dbo_VENDOR2 " & _
        "INNER JOIN dbo_VENDOR1 " & _
        "ON dbo_VENDOR2.MANUFACTURER_ITEM_NO = dbo_VENDOR1.MANUFACTURER_ITEM_NO " & _
        "WHERE dbo_VENDOR1.MANUFACTURER IN ('MANUFACTURER CODE') " & _
        "And dbo_VENDOR1.ITEM_PRICE > dbo_VENDOR2.ITEM_PRICE "

    'Set rsSQL = dbs.OpenRecordset(StrSQL, dbOpenDynaset)
    
    Dim qryName As String
    qryName = "qryTest"
    
    ' close the query in case it is open in Access
    DoCmd.SetWarnings False
    DoCmd.Close acQuery, qryName
    DoCmd.SetWarnings True
   
    ' Create the query based on the SQL string
    createQry qryName, StrSQL
    ' Open the query in Access for display
    DoCmd.OpenQuery qryName, acNormal, acReadOnly
End Sub