MS. Access - VBA SQL string not returning any records-CodePudding

As part of a larger project I'm trying to copy records from one table to another but I'm stuck on the first step trying to get the records from the 1st table

My strSQL string in VBA is not returning any record's at the .RecordCount but it should be 2

The SQL from the query builder is

SELECT tbl_BOM_Requirments.ID, tbl_BOM_Requirments.PrtNmber_LinkField, tbl_BOM_Requirments.RequiredMaterialPrtNum, tbl_BOM_Requirments.RequiredMaterialDescription FROM tbl_BOM_Requirments WHERE (((tbl_BOM_Requirments.PrtNmber_LinkField)="PRT468"));

That works fine.. PRT468 is the current record - in VBA I'm using a variable PrtNbrGt

Private Sub Command15_Click()

    Dim rstSource   As DAO.Recordset
      Dim rstInsert   As DAO.Recordset
      Dim fld         As DAO.Field
      Dim strSQL      As String
      Dim lngLoop     As Long
      Dim lngCount    As Long
      Dim PrtNbrGt  As String
           
      PrtNbrGt = Me.SCSPartNumb
 
    strSQL = "SELECT tbl_BOM_Requirments.RequiredMaterialPrtNum" & vbCrLf & _
    "FROM tbl_BOM_Requirments" & vbCrLf & _
    "WHERE (tbl_BOM_Requirments.PrtNmber_LinkField) = "" & PrtNbrGt"""
    
            Set rstInsert = CurrentDb.OpenRecordset(strSQL)
    
      Set rstSource = rstInsert.Clone
      With rstSource
        lngCount = .RecordCount
        For lngLoop = 1 To lngCount

Has anyone got any suggestions why it's not returning any records?

CodePudding user response：

You need to replace the double quotes in your working SELECT with single quotes as the doubles quotes are in use to delimiter the string

strSQL = "SELECT tbl_BOM_Requirments.RequiredMaterialPrtNum " & vbCrLf & _
"FROM tbl_BOM_Requirments " & vbCrLf & _
"WHERE (tbl_BOM_Requirments.PrtNmber_LinkField) = '" & Replace(PrtNbrGt, "'", "''") & "'"

But your code should have goven you some errors anyway

but you can use parameters see https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/parameters-declaration-microsoft-access-sql

CodePudding user response：

You are tangled up in double quote inception. Instead:

"WHERE (tbl_BOM_Requirments.PrtNmber_LinkField) = """ & PrtNbrGt & """"

You could also do:

"WHERE (tbl_BOM_Requirments.PrtNmber_LinkField) = " & CHR(34) & PrtNbrGt & CHR(34)

That's a little easier to read. CHR(34) that is used in this example is a literal double quote.