detect 'all' from combobox

[bobh]

Hi All,

I've added an 'All' item to my combobox(below) and it does display as
the first item

If Me.cboLOB.Column(0) = "auto" Then
strSQL = "SELECT DISTINCT tblAUTO_MASTER.Section" & _
" FROM tblAUTO_MASTER" & _
" Union" & _
" SELECT '(ALL)' As Bogus FROM tblAUTO_MASTER" & _
" ORDER BY 1"

but when I select "(ALL)" in the displayed combobox and click the 'go'
button which runs this code(below) the "(ALL)" does not get detected.
WHY? What I'm I missing??

If ctl.ItemsSelected.Count = 0 Then
MsgBox "You did not select any Phases, please try again.",
vbExclamation, "Ooooooops!"
Exit Sub
ElseIf ctl.ItemsSelected.Count = 1 Then
If ctl.ItemData(varItem) = "(ALL)" Then
strIN = " WHERE tblAUTO_MASTER.Section Like '*'"
Else
strIN = " WHERE tblAUTO_MASTER.Section='"
For Each varItem In ctl.ItemsSelected
strIN = strIN & ctl.ItemData(varItem) & "'"
Next varItem
End If

thanks
bobh.

[salad]

bobh wrote:

Hi All,

I've added an 'All' item to my combobox(below) and it does display as
the first item

If Me.cboLOB.Column(0) = "auto" Then
strSQL = "SELECT DISTINCT tblAUTO_MASTER.Section" & _
" FROM tblAUTO_MASTER" & _
" Union" & _
" SELECT '(ALL)' As Bogus FROM tblAUTO_MASTER" & _
" ORDER BY 1"

but when I select "(ALL)" in the displayed combobox and click the 'go'
button which runs this code(below) the "(ALL)" does not get detected.
WHY? What I'm I missing??

If ctl.ItemsSelected.Count = 0 Then
MsgBox "You did not select any Phases, please try again.",
vbExclamation, "Ooooooops!"
Exit Sub
ElseIf ctl.ItemsSelected.Count = 1 Then
If ctl.ItemData(varItem) = "(ALL)" Then
strIN = " WHERE tblAUTO_MASTER.Section Like '*'"
Else
strIN = " WHERE tblAUTO_MASTER.Section='"
For Each varItem In ctl.ItemsSelected
strIN = strIN & ctl.ItemData(varItem) & "'"
Next varItem
End If

thanks
bobh.

What's the value of varItem? Null?

Maybe
ElseIf ctl.ItemsSelected.Count = 1 Then
If Me.List0.ListIndex = 0 Then
strIN = " WHERE tblAUTO_MASTER.Section Like '*'"
Else
strIN = " WHERE tblAUTO_MASTER.Section='"
strIN = strIN & ctl.ItemData(ctl.ListIndex) & "'"
...or whatever code you want
endif
Endif

You can use Msgbox to see the values.

[ManningFan]

On May 21, 3:04 pm, bobh <[e-mail]:

Hi All,

I've added an 'All' item to my combobox(below) and it does display as
the first item

If Me.cboLOB.Column(0) = "auto" Then
strSQL = "SELECT DISTINCT tblAUTO_MASTER.Section" & _
" FROM tblAUTO_MASTER" & _
" Union" & _
" SELECT '(ALL)' As Bogus FROM tblAUTO_MASTER" & _
" ORDER BY 1"

but when I select "(ALL)" in the displayed combobox and click the 'go'
button which runs this code(below) the "(ALL)" does not get detected.
WHY? What I'm I missing??

If ctl.ItemsSelected.Count = 0 Then
MsgBox "You did not select any Phases, please try again.",
vbExclamation, "Ooooooops!"
Exit Sub
ElseIf ctl.ItemsSelected.Count = 1 Then
If ctl.ItemData(varItem) = "(ALL)" Then
strIN = " WHERE tblAUTO_MASTER.Section Like '*'"
Else
strIN = " WHERE tblAUTO_MASTER.Section='"
For Each varItem In ctl.ItemsSelected
strIN = strIN & ctl.ItemData(varItem) & "'"
Next varItem
End If

thanks
bobh.

Put a break in the code and find out what it thinks
ctl.ItemData(varItem) is when you select "(ALL)". That should tell
you what you need to trap for.

[bobh]

On May 21, 9:29 pm, salad <[e-mail]:

bobhwrote:

Hi All,

I've added an 'All' item to my combobox(below) and it does display as
the first item

If Me.cboLOB.Column(0) = "auto" Then
strSQL = "SELECT DISTINCT tblAUTO_MASTER.Section" & _
" FROM tblAUTO_MASTER" & _
" Union" & _
" SELECT '(ALL)' As Bogus FROM tblAUTO_MASTER" & _
" ORDER BY 1"

but when I select "(ALL)" in the displayed combobox and click the 'go'
button which runs this code(below) the "(ALL)" does not get detected.
WHY? What I'm I missing??

If ctl.ItemsSelected.Count = 0 Then
MsgBox "You did not select any Phases, please try again.",
vbExclamation, "Ooooooops!"
Exit Sub
ElseIf ctl.ItemsSelected.Count = 1 Then
If ctl.ItemData(varItem) = "(ALL)" Then
strIN = " WHERE tblAUTO_MASTER.Section Like '*'"
Else
strIN = " WHERE tblAUTO_MASTER.Section='"
For Each varItem In ctl.ItemsSelected
strIN = strIN & ctl.ItemData(varItem) & "'"
Next varItem
End If

thanks
bobh.

What's the value of varItem? Null?

Maybe
ElseIf ctl.ItemsSelected.Count = 1 Then
If Me.List0.ListIndex = 0 Then
strIN = " WHERE tblAUTO_MASTER.Section Like '*'"
Else
strIN = " WHERE tblAUTO_MASTER.Section='"
strIN = strIN & ctl.ItemData(ctl.ListIndex) & "'"
...or whatever code you want
endif
Endif

You can use Msgbox to see the values. - Hide quoted text -

- Show quoted text -

In debug mode the value being returned was 'null' so I changed the
code to this which fixed the problem

ElseIf ctl.ItemsSelected.Count = 1 Then
If Me.cboPhase.Column(0) = "(ALL)" Then
strIN = " WHERE tblAUTO_MASTER.Section Like '*'"
Else
strIN = " WHERE tblAUTO_MASTER.Section='"
For Each varItem In ctl.ItemsSelected
strIN = strIN & ctl.ItemData(varItem) & "'"
Next varItem
End If
Else 'user must have selected multiple values

bobh.