VBA Code Help

[Secret Squirrel]

I'm trying to run this code below but I'm not sure how to add "acComboBox" to
my code. Right now it's checking all my checkboxes via the "acCheckBox" but I
also need it to look at comboboxes as well. Is it as simple as adding "Or
acComboBox" on the Case line?

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 qryARSkills "

'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
'This is the function that actually builds the clause.
If (.Value) Then
If sWhereClause = " Where " Then
sWhereClause = sWhereClause &
BuildCriteria(.Name, dbBoolean, .Value)
Else
sWhereClause = sWhereClause & " And " &
BuildCriteria(.Name, dbBoolean, .Value)
End If
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

 

[Tom van Stiphout]

On Sat, 5 Sep 2009 19:07:01 -0700, Secret Squirrel

The help file is not very clear on this point, but:
case acCheckBox, acComboBox
will work.

Since checkboxes and comboboxes have such different object model, you
can also use:
case acCheckBox
'do this
case acComboBox
'do that

-Tom.
Microsoft Access MVP

 

[Arvin Meyer [MVP]]

Right here:

End If

Case acComboBix
'If ... Then
'Else
'End Id

End Select
End With
Next ctl

 

[Secret Squirrel]

I agree about the help file. I couldn't understand it totally. I ended up
doing what you said by creating 2 Cases, 1 for the checkbox and 1 for the
combobox and it works fine.

Thanks for your help!
SS

 

[David W. Fenton]

Since checkboxes and comboboxes have such different object model

Strictly speaking, they have no object model at all. It's the
events/methods/members/properties that are different.

 

[David W. Fenton]

'End Id

You can't have an End Id without an If Ego.

 

[Arvin Meyer [MVP]]

You can't have an End Id without an If Ego.

LOL. Good catch.

That's why I *usually* use the phrase (air code) when I type directly into a
posting.