S
Secret Squirrel
I'm trying to build a SQL statement using only the checkboxes on my form that
are true. Here's what I have so far but the SQL is also being built with the
checkboxes that are false. How can I change the code to only have it build
the SQL with just the checkboxes that are true?
On Error Resume Next
Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String
Me.txtSQL = Null
'Initialize the Where Clause variable.
sWhereClause = " Where "
'Start the first part of the select statement.
sSQL = "select EmpName from tblSkills "
'Loop through each control on the form to get its value.
For Each ctl In Me.Controls
With ctl
'The only Control you are using is the check box.
'However, you can add as many types of controls as you want.
Select Case .ControlType
Case acCheckBox
.SetFocus
'This is the function that actually builds the clause.
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name,
dbBoolean, .Value)
Else
sWhereClause = sWhereClause & " And " &
BuildCriteria(.Name, dbBoolean, .Value)
End If
End Select
End With
Next ctl
'Set the listbox rowsource equal to the new select statement.
Me.txtSQL = sSQL & sWhereClause
Me.EmpList.RowSource = Me.txtSQL
Me.EmpList.Requery
Here's an example of the SQL that I have now:
What's happening is when I open my form and put a check in the checkbox
"Titanium" and "Aluminum and then run my SQL it works fine. But when I then
remove the check from the "Aluminum" box the SQL looks like this:
select EmpName from tblSkills Where Titanium=-1 And Aluminum=0
I need the above example to look like this when I remove the check from the
"Aluminum: checkbox:
select EmpName from tblSkills Where Titanium=-1
Any help would be greatly appreciated.
SS
are true. Here's what I have so far but the SQL is also being built with the
checkboxes that are false. How can I change the code to only have it build
the SQL with just the checkboxes that are true?
On Error Resume Next
Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String
Me.txtSQL = Null
'Initialize the Where Clause variable.
sWhereClause = " Where "
'Start the first part of the select statement.
sSQL = "select EmpName from tblSkills "
'Loop through each control on the form to get its value.
For Each ctl In Me.Controls
With ctl
'The only Control you are using is the check box.
'However, you can add as many types of controls as you want.
Select Case .ControlType
Case acCheckBox
.SetFocus
'This is the function that actually builds the clause.
If sWhereClause = " Where " Then
sWhereClause = sWhereClause & BuildCriteria(.Name,
dbBoolean, .Value)
Else
sWhereClause = sWhereClause & " And " &
BuildCriteria(.Name, dbBoolean, .Value)
End If
End Select
End With
Next ctl
'Set the listbox rowsource equal to the new select statement.
Me.txtSQL = sSQL & sWhereClause
Me.EmpList.RowSource = Me.txtSQL
Me.EmpList.Requery
Here's an example of the SQL that I have now:
What's happening is when I open my form and put a check in the checkbox
"Titanium" and "Aluminum and then run my SQL it works fine. But when I then
remove the check from the "Aluminum" box the SQL looks like this:
select EmpName from tblSkills Where Titanium=-1 And Aluminum=0
I need the above example to look like this when I remove the check from the
"Aluminum: checkbox:
select EmpName from tblSkills Where Titanium=-1
Any help would be greatly appreciated.
SS