Auto Populate SQL with Run-Time Error 3464

[excellenthelp]

Hello Accessers,



I am attempting to figure how to find and fix the error in the code I created below:

Public Function PopulateACAAddress()Dim stACAName As String
Dim stCity As String
Dim stState As String
Dim stCountry As String
Dim stSQL As String


If IsNull(Screen.ActiveForm.[ACAID].Value) Then
  
    MsgBox "Please select a value for the Certifier Name-Abbreviation drop list. An address can not be obtained if a certifier is not currently selected.", vbCritical
Else
        
        Dim MyDB As Database, RS As Recordset
        Set MyDB = DBEngine.Workspaces(0).Databases(0)
        
        stNextUser = ""
        stACAName = Screen.ActiveForm.[ACAID].Value
        
        
        stSQL = "SELECT * FROM qrySupport_PopulateACAAddress WHERE [ACAID] = '" & stACAName & '""
        Set RS = MyDB.OpenRecordset(stSQL)     <--- gets highlighted in debugger
        lngRSCount = RS.RecordCount
        
        If lngRSCount <> 0 Then
            RS.MoveFirst
             If IsNull(Trim(RS.Fields("City").Value)) Then
                stCity = ""
             Else
                stCity = Trim(RS.Fields("City").Value)
             End If
             
             If IsNull(Trim(RS.Fields("State").Value)) Then
                stState = ""
             Else
                stState = Trim(RS.Fields("State").Value)
             End If
             
             If IsNull(Trim(RS.Fields("Country").Value)) Then
                stCountry = ""
             Else
                stCountry = Trim(RS.Fields("Country").Value)
             End If
            
        End If
        
        With Screen.ActiveForm
            .[City] = stCity
            .[State] = stState
            .[Country] = stCountry
        End With
        
End If
End Function

After clicking on the button to auto populate the textboxes, the error below pops up:
Run-time error '3464': Data type mismatch in criteria expression.

I have tried with and without the quotations in this line below:

stSQL = "SELECT * FROM qrySupport_PopulateACAAddress WHERE [ACAID] = '" & stACAName & '""

What could the issue be?

[pbaldy]

There's an error in your code. It would be one of these, depending on whether ACAID is numeric or text:

​stSQL = "SELECT * FROM qrySupport_PopulateACAAddress WHERE [ACAID] = " & stACAName

stSQL = "SELECT * FROM qrySupport_PopulateACAAddress WHERE [ACAID] = '" & stACAName & "'"

[orange]

What is
qrySupport_PopulateACAAddress

[excellenthelp]

pbaldy, you got it! It was numeric.

Thanks!!!

Marking this as SOLVED.

[pbaldy]

Happy to help!