MS. Access - VBA SQL string not returning any records

Question

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?

have you tried to add some soace before FROM and WHERE that looks suspicious — nbk
– nbk, Commented Sep 22, 2022 at 14:03
Hint: Set a break point and execute ? "WHERE (tbl_BOM_Requirments.PrtNmber_LinkField) = "" & PrtNbrGt""" in the immediate window to see what your resulting string looks like. Then try things like ? "", ? """" and ? "a""b" to understand how escaping of double quotes works in VBA. — Heinzi
– Heinzi, Commented Sep 22, 2022 at 15:15

JNevill · Accepted Answer · 2022-09-22 14:10:35Z

1

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.

answered Sep 22, 2022 at 14:10

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nbk · Accepted Answer · 2022-09-22 15:37:33Z

1

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

edited Sep 22, 2022 at 15:37

answered Sep 22, 2022 at 14:14

nbk

49.6k8 gold badges32 silver badges53 bronze badges

2 Comments

Heinzi Over a year ago

...and if you don't use parameters, you should at least escape any single quotes occurring in your string, i.e. "WHERE ... = '" & Replace(PrtNbrGt, "'", "''") & "'".

nbk Over a year ago

@Heinzi he has part numbers so they will not have single quotes, but i added the double single quote is someone comes across with single quotes

Collectives™ on Stack Overflow

MS. Access - VBA SQL string not returning any records

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related