Setting list box selection with VBA code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with numerous textboxes that fall under various "Groups." By
doubleclicking one of these textboxes, the user launches a popup form with a
multi-select listbox that contains all the group names. What I want is for
the "group of origin" (the one associated with the textbox he doubleclicked)
to be already selected, with the user having the option to select additional
groups.

Unfortunately, I can't seem to find the right VBA to set that initial
selection. I'm sure it's no more than one or two lines of code, but it's
eluding me. Help, please.
 
LarryP said:
I have a form with numerous textboxes that fall under various "Groups." By
doubleclicking one of these textboxes, the user launches a popup form with
a
multi-select listbox that contains all the group names. What I want is
for
the "group of origin" (the one associated with the textbox he
doubleclicked)
to be already selected, with the user having the option to select
additional
groups.

Unfortunately, I can't seem to find the right VBA to set that initial
selection. I'm sure it's no more than one or two lines of code, but it's
eluding me. Help, please.
Click to expand...

Pass the "group of origin" value to the popup form in the OpenArgs parameter
of the OpenForm call:

DoCmd.OpenForm "popup",,,,,"group value"

Then, in the popup form's Load event, set the value of the listbox to the
passed value:

Me.lstGroups = Me.OpenArgs

Carl Rapson
 
Hi Larry

As Carl says, use the OpenArgs mechanism to pass the "group of origin" to
the popup form.

However, as this is a multi-select listbox, setting its Value will have no
effect on the selection.

You will need to loop through all the items in the list and select the
appropriate one, at the same time unselecting the others:

Dim i As Integer
It Not IsNull(Me.OpenArgs) Then
With YourListBoxName
For i = 0 To .ListCount - 1
.Selected(i) = .ItemData(i) = Me.OpenArgs
Next
End With
End With
 
Larry,

The problem with this method is that if they don't use the control button to
select another another item in the list, then the selection of the "group of
record" will go away.

I think I would modify the query that I use to populate the listbox so that
the "group of record" doesn't even show. Then, when you write the other
selections to your database, I would write that record automatically.

HTH
Dale
 
I think I like your approach best, Dale. Since the objective of all this is
to build a dynamic SQL criterion string -- [Group] = "Group A" OR [Group] =
"Group F" OR [Group] = "Group J"... -- for use in a query, storing the
initial group selection as the first piece of that string right at the outset
(and leaving it out of the listbox altogether) is a good solution. Looping
through all the other groups in the listbox and tacking the selected ones
onto the string should give me exactly what I'm after. Thanks, and thanks to
all others who contributed their ideas. Love this site!
 
Back
Top