The purpose of my procedure is to build an SQL statement with the BuildCriteria function to filter a query. The criteria is supplied from unbound form controls on a form.
The procedure uses a 'Select Case...End Select' clause that cases the control type. The challenge is that a text box control can hold more than a string/text data type. Additionally, the control is not bound.
I have a text box control that stores a date. Therefore, an error is generated when the BuildCriteria([control], db data type = text (dbText), value) tries to pass a date data type to the function.
My question is how can I iterate through my unbound controls and return the data type of each, and use that type of building my SQL statement.
The procedure is included below:
Code:
Private Sub cmbSetFilter_Click()
'The following procedure was copied and modified from
'https://learn.microsoft.com/en-us/office/troubleshoot/access/build-dynamic-query-from-search-form
On Error Resume Next
Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String
Dim strField As Field
'Initialize the WHERE clause variable
sWhereClause = " WHERE "
'Start the first part of the SELECT statement.
sSQL = "SELECT * FROM ActivityLog "
'This code prints the name of each control type listed in the case
'The case criteria identifies the type of control that is required to parse
'the BuildCriteria method.
'To use the test code, uncomment the following lines between
'the Test Code phrases.
'Test Code
'For Each ctl In Me.Controls
' With ctl
' Select Case .ControlType
' Case acTextBox
' Debug.Print .Name
' Case acComboBox
' Debug.Print .Name
' End Select
' End With
'Next ctl
'Test Code
'Loop through each control on the form to get its value
For Each ctl In Me.Controls
With ctl
'Grab value from various Control types and concatenate those values for SQL statement
Select Case .ControlType
Case acTextBox
.SetFocus
strField = Form.ActiveControl.ControlSource
Debug.Print strField
'Build the WHERE clause
'The If statement determines if the For loop has executed and within the loop criteria has
'alread been assigned such that Else statement catches the clause after the first iteration
'of the loop.
If sWhereClause = " WHERE " Then
sWhereClause = sWhereClause & BuildCriteria(.Name, dbText, .Text)
Else
sWhereClause = sWhereClause & " AND " & BuildCriteria(.Name, dbText, .Text)
End If
Debug.Print sSQL & sWhereClause
Case acComboBox
.SetFocus
'Build the WHERE clause
'The If statement determines if the For loop has executed and within the loop criteria has
'alread been assigned such that Else statement catches the clause after the first iteration
'of the loop.
If sWhereClause = " WHERE " Then
sWhereClause = sWhereClause & BuildCriteria(.Name, dbText, .Text)
Else
sWhereClause = sWhereClause & " AND " & BuildCriteria(.Name, dbText, .Text)
End If
End Select
End With
Next ctl
'Assign a text box to display the query that was created by the procedure
Me.txtSQL = sSQL & sWhereClause
Me![Activity Entry Datasheet].Form.RecordSource = sSQL & sWhereClause
Me![Activity Entry Datashett].Requery