SELECT SQL Query with variables

[AccessDev]

Dear All,

I am trying to perform a SELECT query in access vba to show me customer account numbers in a msgbox.

I get a Run-time error '13': Type mismatch.

I would appreciate any help. Please see below for examples of my code.


'CustomerID is a Global Variable that gets it's value from a DLookup that gets triggered after a combobox has been selected.

The combobox does the following
'===START==================
Private Sub AfterUpdate_cboCustomer
Dim iCustID as Integer
iCustID = DLookup("ID", "tblCustomers", "Customer='" & Me.cboCustomer & "'")
iCustID = CustomerID
'Returns the Customer's ID Perfect

'=========START==============
Dim strSQL As String
strSQL = "SELECT AccNo FROM tblAccNo WHERE CustID" = CustomerID
DoCmd.RunSQL(strSQL)' Errors Here

'=======END==================
End Sub
'========END=============

 

[stallyon]

shouldnt it read:

CustomerID = iCustID


rather than

iCustID = CustomerID

 

[allan57]

'***Dim iCustID as Integer
'***iCustID = DLookup("ID", "tblCustomers", "Customer='" & Me.cboCustomer & "'")
'***iCustID = CustomerID
'***Returns the Customer's ID Perfect
'***You do not need the above code as you using the CustomerID for your criteria.


'Dim strSQL As String
'***strSQL = "SELECT AccNo FROM tblAccNo WHERE CustID" = CustomerID
'***The above has been modified to the following, see if that works
strSQL = "SELECT AccNo FROM tblAccNo WHERE CustID=" & CustomerID
DoCmd.RunSQL(strSQL)
End Sub

 

[AccessDev]

I have tried the following now I get another error.

Run-time error 3129
Invalid SQL statement; expected DELETE,INSERT,PROCEDURE,SELECT or UPDATE

 

[allan57]

This is caused by the SELECT statement, RUNSQL can only be used with action queries. You need to use a recordset or dlookup if you wish to return data.