Is there a way to select all values in a Multivalued Lookup Column

[Spencer_BLM]

I am playing with the new Multivalued Lookup Column feature in Access 2007.
I am wondering if there is a way to select all values in the lookup column
(click all the check boxes) without having to select them all individually?
I have been looking for some VBA code or a Macro that will accomplish this,
but I've been unsuccessful thus far. If anyone knows a good way to select
all possible values in a Multivalued Lookup Column I would appreciate any
help you can give. Thanks.

 

[Allen Browne]

From your question, it seems you are not afraid to use some code, so I would
encourage you to consider whether multi-valued fields (MVFs) are appropriate
for you.

It is possible to assign a Recordset variable to the MVF, and AddNew or
Delete or Edit. But my personal experience is that they end up being too
frustrating for any serious use. Access does not maintain the OldValue
properly for a control bound to an MVF field. The structure is super-hidden,
so you can't get to the actual data. It imposes limits on what you can do
with your tables (e.g. a query statement that contains "FROM Table1 IN
'c:\data\somefile.accdb'")

By all means, experiment. Let us know what you decide.

 

[Albert D. Kallal]

I am playing with the new Multivalued Lookup Column feature in Access 2007.
I am wondering if there is a way to select all values in the lookup column
(click all the check boxes) without having to select them all
individually?
I have been looking for some VBA code or a Macro that will accomplish
this,
but I've been unsuccessful thus far. If anyone knows a good way to select
all possible values in a Multivalued Lookup Column I would appreciate any
help you can give. Thanks.

The following code would do the trick....

' select all values...

Dim v As Variant
Dim vValueList As Variant
Dim rstAdd As DAO.Recordset

vValueList = Split(Me.FavColors.RowSource, ";")

' delete any possbile selected records...
Set rstAdd = Me.Recordset!FavColors.Value
Do While rstAdd.EOF = False
rstAdd.Delete
rstAdd.MoveNext
Loop

' now add all
Me.Recordset.Edit
For Each v In vValueList
rstAdd.AddNew
rstAdd(0) = Split(v, Chr(34))(1)
rstAdd.Update
Next
Me.Refresh

 

[Spencer_BLM]

Allen and Albert,

Thanks for your responses. I especially appreciate the code you attached
Albert. Unfortunately, I am VERY new to programming in Visual Basic and
Access. I have done some programming in other languages a long time ago, so
I have a basic understanding of how programming works.

I have plugged the code you gave me into my database and played with it to
try to make it work but I've been unsuccessfull thus far. Which parts of
your code must be updated to the specific database form I'm working on?

What I'm trying to set up is a button in a form that a user will click to
select all values in the Multivalue Lookup Field. I have copied your code
into the OnClick code builder but I can't get it to work. The name of the
Multivalue Lookup Field in the form is "AllotmentName". Do I need to plug
this into the code somewhere?

I appologize for my lack of VBA knowledge. Any help you could give me would
be most appreciated. Thanks for all the help,

 

[Albert D. Kallal]

What I'm trying to set up is a button in a form that a user will click to
select all values in the Multivalue Lookup Field. I have copied your code
into the OnClick code builder but I can't get it to work. The name of the
Multivalue Lookup Field in the form is "AllotmentName". Do I need to plug
this into the code somewhere?

Yes....you have to replace the names I used with yours.
In my example, I assumed a "list" of favorite colors. So, you would
replace FavColors with AllotmentName.

(you don't mention if AllotmentName is a "value list" you typed in, or in
fact from another table?

Assuming it is a "list" you typed in...then just replace FavColors in the
code.

eg:


Dim v As Variant
Dim vValueList As Variant
Dim rstAdd As DAO.Recordset

vValueList = Split(Me.AllotmentName.RowSource, ";")
' ^^^^ line was changed...

' delete any possbile selected records...
Set rstAdd = Me.Recordset!AllotmentName.Value
' ^^^^^ above line was change
Do While rstAdd.EOF = False
rstAdd.Delete
rstAdd.MoveNext
Loop

' now add all
Me.Recordset.Edit
For Each v In vValueList
rstAdd.AddNew
rstAdd(0) = Split(v, Chr(34))(1)
rstAdd.Update
Next
Me.Refresh

 

[Spencer_BLM]

AllotmentName is pulled from another table. Will that change anything in the
code? Thanks again for the help.