I’m having trouble trying to get the value from a combobox returned in a VBA SQL statement.
The combobox is called [Customer Purchase Order] and the query is Qry_MHR_v1_Filter
I think i need to add .Column to the end of it, but stops the sql working…
Any suggestions on how to get a comboox value into recordset?
Dim Testing As Integer
Dim Cnter As Integer
Dim Jnum2 As String
Dim strSQL2 As String
Dim rs2 As DAO.Recordset
Dim POnum As String
strSQL2 = "SELECT [Job Number] , Qry_MHR_v1_Filter.[Customer Purchase Order] FROM Qry_MHR_v1_Filter WHERE [Packed] = True And [MHRSnt]= False And [SndEml] = True "
Set rs2 = CurrentDb.OpenRecordset(strSQL2)
Testing = 0
Cnter = 1
With rs2
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
While (Not .EOF)
MsgBox (Testing)
Testing = Testing + Cnter
Jnum2 = rs2![Job Number]
POnum = rs2![Customer Purchase Order]
Concatenate the combobox value into the SELECT clause and give it a column name. Replace FormName with the name of the form that has the combobox.
Dim strCPO as String
strCPO = Me.[Customer Purchase Order]
or
strCPO = Forms![formname]![Customer Purchase Order]
strSQL2 = "SELECT [Job Number] , '" & strCPO & "' AS [Customer Purchase Order] FROM Qry_MHR_v1_Filter WHERE [Packed] = True AND [MHRSnt]= False AND [SndEml] = True"
If your combobox has the value abc then the string will evaluate as:
SELECT [Job Number] , 'abc' AS [Customer Purchase Order] FROM Qry_MHR_v1_Filter WHERE [Packed] = True AND [MHRSnt]= False AND [SndEml] = True
If your combobox has values like “O’Brian” then you will need to replace any single quote chr with 2 single quotes via Replace(FormName.[Customer Purchase Order],”‘”,”””)
If you have alot of scenarios like this in your app then consider using a function to properly delimit all data types for SQL Trying to use variables as values vba to sql
2