Create Dynamic SQL Statements Using VBA
One of the more syntactically tricky procedures in VBA is to create a SQL statement using a combination of table or query elements along with dynamic variables. SQL statements are created for many uses, but two of the more common reasons are:
(1) Updating a Recordsource property for a combo
box or other control
(2) Creating a SQL query for a record set
Three examples in the code below illustrate the VBA syntax to create a SQL statement. In these examples, I put the actual translated SQL statement in comments immediately below the SQL statement to show how they are actually rendered to the Jet Engine. Notice the use of the double-quote characters. The code below consists of "snippets" of a larger program and all the variables are not defined, since some of them are in the Global area of the program and others are form objects.
Program Code
Option Compare Database
Option Explicit
Option Base 1
' ******************************************************************
' Example 1 - The Row Source Is Dynamically Changed on the Combo Box
' "Enter Event" using a Predefined Query in the
' SQL Statement
' ******************************************************************
Private Sub cmbComboBox_Enter()
' ******************************************************************
' When The Combo Box Is Entered, Set Up Values to Be Displayed
' ******************************************************************
cmbComboBox.RowSource = _
"SELECT qryStylesForDropDownBox.Style FROM qryStylesForDropDownBox ORDER BY [Style];"
End Sub
' ******************************************************************
' Example 2 - Row Source Is Dynamically Changed on the Combo Box
' Enter Event Using A Code-Created SQL Query --
' Note How The """" Translates to One " in the Resulting
' SQL Statement
' ******************************************************************
Private Sub cmbDefectCategory_Change()
strDefectCategory = cmbDefectCategory.Value
cmbSubCategory.RowSource = _
"SELECT tblDefectSubCategory.DefectSubCategory, tblDefectSubCategory.SubCategoryDescription" _
& " FROM tblDefectSubCategory WHERE tblDefectSubCategory.DefectCategory = " _
& """" & strDefectCategory & """" & " ORDER BY tblDefectSubCategory.SubCategoryDescription;"
' ******************************************************************
' The Above Statemnt Is Translated To the Following SQL Statement
' Given that strDefectCategory = "E"
' ******************************************************************
'SELECT tblDefectSubCategory.DefectSubCategory, tblDefectSubCategory.SubCategoryDescription
'FROM tblDefectSubCategory
'WHERE tblDefectSubCategory.DefectCategory = "E"
'ORDER BY tblDefectSubCategory.SubCategoryDescription;
End Sub
' ******************************************************************
' Example 3 - A Dynamic SQL Statement Is Created For A Query
' Recordset. Again, Note the Use of """"
' ******************************************************************
Private Sub cmdSelectDefectCode_Click()
Dim db As Database
Dim recIn As Recordset
Dim strSQL As String
intDefectCode = lstDefectDescriptions.Value
strSQL = _
"SELECT tblDefectCodes.DefectDescription FROM tblDefectCodes " & _
"WHERE (tblDefectCodes.DefectCode=" & intDefectCode & _
" AND tblDefectCodes.DefectCategory=" & """" & strDefectCategory & """" & ");"
' ******************************************************************
' The Above Statemnt Create The Following SQL String
' Given That intDefectCode = 2 and strDefectCategory = "E"
' ******************************************************************
'SELECT tblDefectCodes.DefectDescription
'FROM tblDefectCodes
'WHERE (tblDefectCodes.DefectCode=2 AND tblDefectCodes.DefectCategory="E");
Set db = CurrentDb()
Set recIn = db.OpenRecordset(strSQL)
If recIn.EOF Then
strDefectDescription = "No Matching Description Found"
Exit Sub
End If
strDefectDescription = recIn!DefectDescription
recIn.Close
db.Close
Set recIn = Nothing
Set db = Nothing
On Error GoTo Err_cmdCloseForm_Click
DoCmd.Close
Exit_cmdCloseForm_Click:
Exit Sub
Err_cmdCloseForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseForm_Click
End Sub
Examples Of SQL Statements For Querydefs
' ************************************************************************************************* ' This will show matching, records in table 1 not in table 2, and records in table 2 not in table 2. ' Sample of UNION Functionality ' ************************************************************************************************* SELECT Table1.ID, Table1.Date, Table1.Code, "Equal" AS Equal FROM Table1 INNER JOIN Table2 ON (Table1.Date = Table2.Date) AND (Table1.ID = Table2.ID) UNION SELECT Table1.ID, Table1.Date, Table1.Code, "Table1Only" AS Table1Only FROM Table1 LEFT JOIN Table2 ON (Table1.ID = Table2.ID) AND (Table1.Date = Table2.Date) WHERE (((Table2.Code) Is Null)) UNION SELECT Table2.ID, Table2.Date, Table2.Code, "Table2Only" AS Table2Only FROM Table1 RIGHT JOIN Table2 ON (Table1.Date = Table2.Date) AND (Table1.ID = Table2.ID) WHERE (((Table1.Code) Is Null)); ' ************************************************************************************************* ' Sample Of Select Within Select ' ************************************************************************************************* SELECT User, UserCount FROM (SELECT Table1.USER_NBR As User, Count(Table1.USER_CODE) AS UserCount FROM Table1 GROUP BY Table1.USER_NBR HAVING (Count(Table1.USER_CODE))=1) As A INNER JOIN Table1 ON A.User = Table1.USER_NBR WHERE (((Table1.USER_CODE)="00000"));