By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,799 Members | 1,352 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,799 IT Pros & Developers. It's quick & easy.

detect 'all' from combobox

P: n/a
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.

May 21 '07 #1
Share this Question
Share on Google+
3 Replies

P: n/a
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.
May 22 '07 #2

P: n/a
On May 21, 3:04 pm, bobh <vulca...@isp.comwrote:
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.

May 22 '07 #3

P: n/a
On May 21, 9:29 pm, salad <o...@vinegar.comwrote:
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.

May 23 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.