Multi List Box

  • Thread starter Thread starter NEWER USER
  • Start date Start date
N

NEWER USER

I have a Multiple List Box with the following values that I pull from a query.
Asian
Asian/Domestic
Asian/European
European
European/Domestic

When I select Asian, I would like the two other Asian values selected and
highlighted automatically (same for European). Can this auto select be
handled through coding? Any help appreciated.
 
NEWER USER said:
I have a Multiple List Box with the following values that I pull from a
query.
Asian
Asian/Domestic
Asian/European
European
European/Domestic

When I select Asian, I would like the two other Asian values selected and
highlighted automatically (same for European). Can this auto select be
handled through coding? Any help appreciated.
Click to expand...

Paste this function into a standard module:

Public Function SetSelections(List As Access.ListBox, _
Column As Long, ParamArray Items())
For i& = LBound(Items) To UBound(Items)
p& = ListIdx(List, Items(i&), Column)
If p& > 0 Then List.Selected(p&) = True
Next
End Function

Then call it in the listbox's OnClick event like:

If Forms!MyForm!MyListbox.Value = "Asian" Then
SetSelections Forms!MyForm!MyListbox, 0, _
"Asian/Domestic", "Asian/European"
End If

If you don't want to hard-code the selections, you'll need to work out some
kind of system for this, but you can use the function to do the 'grunt
work'.
 
I get a compile error on Debug

i& - variable not defined; What additional coding is needed and where to
place in defined Function?
Thank you
 
NEWER USER said:
I get a compile error on Debug

i& - variable not defined; What additional coding is needed and where to
place in defined Function?
Thank you
Click to expand...

Ah. I forgot I often work without the 'Option Explicit' safety net when I'm
developing util functions. Here's what it ought to look like on here:

Public Function SetSelections(List As Access.ListBox, _
Column As Long, ParamArray Items())
Dim i&, p&
For i = LBound(Items) To UBound(Items)
p = ListIdx(List, Items(i), Column)
If p > 0 Then List.Selected(p) = True
Next
End Function
 
I believe I am almost there; I got another compile error:

ListIdx - Sub or Function not defined; Thanks for your help.
 
NEWER USER said:
I believe I am almost there; I got another compile error:

ListIdx - Sub or Function not defined; Thanks for your help.
Click to expand...

Oh dear. Not doing too well tonight. Here's the missing function:

Public Function ListIdx(List As Access.ListBox, Find As Variant, _
Optional Column As Long = 0) As Long
Dim t$, i&
t = Replace(Find, "'", "")
t = Replace(t, Chr(34), "")
ListIdx = -1
For i = 0 To List.ListCount - 1
If List.Column(Column, i) = t Then
ListIdx = i
Exit For
End If
Next
End Function

Sorry. It's a bit late here (UK).
 
Still getting hung; this is what I have - getting no action when clicking on
"Asian" in my list box.

In a module, I pasted your code

Public Function SetSelections(List As Access.ListBox, _
Column As Long, ParamArray Items())
Dim i&, p&
For i = LBound(Items) To UBound(Items)
p = ListIdx(List, Items(i), Column)
If p > 0 Then List.Selected(p) = True
Next
End Function


Public Function ListIdx(List As Access.ListBox, Find As Variant, _
Optional Column As Long = 0) As Long
Dim t$, i&
t = Replace(Find, "'", "")
t = Replace(t, Chr(34), "")
ListIdx = -1
For i = 0 To List.ListCount - 1
If List.Column(Column, i) = t Then
ListIdx = i
Exit For
End If
Next
End Function

In the form, On Click Event Property, I have

Private Sub lstType_Click()
On Error GoTo lstType_Click_Err
'Select corresponding rows in List Box
If Forms!frmSelectCriteria!lstType.Value = "Asian" Then
SetSelections Forms!frmSelectCriteria!lstType, 0, _
"Asian/Domestic", "Asian/European", "Asian/Domestic/European"
End If

If Forms!frmSelectCriteria!lstType.Value = "European" Then
SetSelections Forms!frmSelectCriteria!lstType, 0, _
"European/Domestic", "Asian/European", "Asian/Domestic/European"
End If

If Forms!frmSelectCriteria!lstType.Value = "Domestic" Then
SetSelections Forms!frmSelectCriteria!lstType, 0, _
"European/Domestic", "Asian/Domestic", "Asian/Domestic/European"
End If

lstType_Click_Exit:
Exit Sub

lstType_Click_Err:
MsgBox Err.Description
Resume lstType_Click_Exit

End Sub

Any more ideas where error might be or why no action taking place?

Thanks,
Stuart Wentzel
 
NEWER USER said:
Still getting hung; this is what I have - getting no action when clicking
on
"Asian" in my list box.

In a module, I pasted your code

Public Function SetSelections(List As Access.ListBox, _
Column As Long, ParamArray Items())
Dim i&, p&
For i = LBound(Items) To UBound(Items)
p = ListIdx(List, Items(i), Column)
If p > 0 Then List.Selected(p) = True
Next
End Function


Public Function ListIdx(List As Access.ListBox, Find As Variant, _
Optional Column As Long = 0) As Long
Dim t$, i&
t = Replace(Find, "'", "")
t = Replace(t, Chr(34), "")
ListIdx = -1
For i = 0 To List.ListCount - 1
If List.Column(Column, i) = t Then
ListIdx = i
Exit For
End If
Next
End Function

In the form, On Click Event Property, I have

Private Sub lstType_Click()
On Error GoTo lstType_Click_Err
'Select corresponding rows in List Box
If Forms!frmSelectCriteria!lstType.Value = "Asian" Then
SetSelections Forms!frmSelectCriteria!lstType, 0, _
"Asian/Domestic", "Asian/European", "Asian/Domestic/European"
End If

If Forms!frmSelectCriteria!lstType.Value = "European" Then
SetSelections Forms!frmSelectCriteria!lstType, 0, _
"European/Domestic", "Asian/European", "Asian/Domestic/European"
End If

If Forms!frmSelectCriteria!lstType.Value = "Domestic" Then
SetSelections Forms!frmSelectCriteria!lstType, 0, _
"European/Domestic", "Asian/Domestic", "Asian/Domestic/European"
End If

lstType_Click_Exit:
Exit Sub

lstType_Click_Err:
MsgBox Err.Description
Resume lstType_Click_Exit

End Sub

Any more ideas where error might be or why no action taking place?

Thanks,
Stuart Wentzel
Click to expand...

Well so long as:

Forms!frmSelectCriteria!lstType.Value

correctly refers to your listbox, and the listbox has one column, that code
should definitely work..
 
Stuart,

I've never seen the $ and & used in variable names. I assume this is just
your shorthand for identifying the type of variable. What other symbols do
you use, and what are their meanings?

Dale
 
I would put a breakpoint in your code at the beginning of the click event and
step through the code. My first guess is that "Asian" is not in the bound
column of your listbox, but is in the second field. If so you will have to
refer to the column that it resides in.

'Select corresponding rows in List Box
If me.lstType.Value = "Asian" Then
SetSelections me.lstType, 0, "Asian/Domestic", _
"Asian/European", "Asian/Domestic/European"
End If

'If not the bound column then it would look something like:
If me.lstType.column(1) = "Asian" Then
SetSelections me.lstType, 0, "Asian/Domestic", _
"Asian/European", "Asian/Domestic/European"
End If

Also, since I assume the click event is on the same form as the listbox, you
can shorten your references to that control using the Me object. This works
as long as the code is in the forms code module. If the code is in a
standard code module, you will have to use the method you are using or
somthing similar (I usually declare a variable as a Form, then set that
variable) like:

Dim frm as Form
Set frm = Forms!frmSelectCriteria

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Dale Fye said:
I would put a breakpoint in your code at the beginning of the click event
and
step through the code. My first guess is that "Asian" is not in the bound
column of your listbox, but is in the second field. If so you will have
to
refer to the column that it resides in.

'Select corresponding rows in List Box
If me.lstType.Value = "Asian" Then
SetSelections me.lstType, 0, "Asian/Domestic", _
"Asian/European", "Asian/Domestic/European"
End If

'If not the bound column then it would look something like:
If me.lstType.column(1) = "Asian" Then
SetSelections me.lstType, 0, "Asian/Domestic", _
"Asian/European", "Asian/Domestic/European"
End If
Click to expand...

Actually the code in that case would need to pass 1 to the SetSelections
routine, too:

SetSelections me.lstType, 1, "Asian/Domestic", _
"Asian/European", "Asian/Domestic/European"
Also, since I assume the click event is on the same form as the listbox,
you
Click to expand...

The click event I mentioned was the onclick of the _listbox_, not the form
itself.
 
Dale Fye said:
Stuart,

I've never seen the $ and & used in variable names. I assume this is just
your shorthand for identifying the type of variable. What other symbols
do
you use, and what are their meanings?
Click to expand...
<SNIP>

You need to get out more <grin>. Well actually you need to be old like me
and have used 'type declaration characters' as they're known, in Basic for
years before VB and VBA came along. They go like this:

String$
Integer%
Long&
Single!
Double#
Currency@

So if you declare:

Dim s$, i%

what the compiler interprets is:

Dim s As Long
Dim i As Integer

Perfect for poor typists (read: lazy b**tards :) like me.

Although I normally convert them before posting on here - yesterday was a
long day...
 
Enjoy learning something new.

I didn't do much coding before 1992, then in 92 was introduced to dBase III
and Clipper, then moved to Access in about '96. Don't ever remember using
"type declaration characters", but I'm lazy too, so I might consider it.

Thanks

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Stuart,

I so sorry to not have mentioned that I had two columns in my list box.
Changed O to 1 and LIFE IS GOOD. Thanks again for all your help. Truly
apppreciated.

Stu Wentzel
 
Thank you for your help; truly appreciated.

Dale Fye said:
Enjoy learning something new.

I didn't do much coding before 1992, then in 92 was introduced to dBase III
and Clipper, then moved to Access in about '96. Don't ever remember using
"type declaration characters", but I'm lazy too, so I might consider it.

Thanks

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
Click to expand...
 
NEWER USER said:
Stuart,

I so sorry to not have mentioned that I had two columns in my list box.
Changed O to 1 and LIFE IS GOOD. Thanks again for all your help. Truly
apppreciated.

Stu Wentzel
Click to expand...

No problem. Serves me right for assuming one column. I should know better
than that.

Good luck with the rest of the project.
 
Back
Top