Problem returning selected items in list box in Access
I have a form with a list box control called ppe_req. The user selects one or more items from the list and then clicks a button that opens a word template and populates the document with information from the form. Everything works except the data coming from the list boxes. Access ignores the selected items and returns an empty string when the loop is executed below. If I use the immediate window and type in ?me.ppe_req.itemsselected.
i = 0
Do Until IsNull(Me.ppe_req.ItemData(i))
If Me.ppe_req.Selected(i) Then
strPpe_req = strPpe_req & Me.ppe_req.ItemData(i) & vbCrLf
End If
i = i + 1
Loop
>and type in ?me.ppe_req.itemsselected.
ASKER
capricorn,
VBA highlights the word ".Selected" in "With Me.ppe_req.Selected" and warns: "Argument not optional"
GRayL,
I use Me in the immediate window all the time. It only works while I am stepping through code, but it does work. If I type ?Me.level I get "Level 1a Housing" which is correct.
VBA highlights the word ".Selected" in "With Me.ppe_req.Selected" and warns: "Argument not optional"
GRayL,
I use Me in the immediate window all the time. It only works while I am stepping through code, but it does work. If I type ?Me.level I get "Level 1a Housing" which is correct.
sorry typo ( remove .Selected )
dim j
with Me.ppe_req
for each j in .itemsselected
strPpe_req = strPpe_req & .ItemData(j) & vbCrLf
next
end with
dim j
with Me.ppe_req
for each j in .itemsselected
strPpe_req = strPpe_req & .ItemData(j) & vbCrLf
next
end with
ASKER
Cap,
For some reason, access doesn't see any items as selected. It passes over the "For Each j In .ItemsSelected" and never loops. There most definitely are 5 items selected. But this goes back to the Me.ppe_req.itemsselected.c
I'm heading into a meeting for the next hour...
For some reason, access doesn't see any items as selected. It passes over the "For Each j In .ItemsSelected" and never loops. There most definitely are 5 items selected. But this goes back to the Me.ppe_req.itemsselected.c
I'm heading into a meeting for the next hour...
Access 2003?
You must be running A2007 or A2010
instead of loop try this
Dim varItem As Variant
Dim ctlList As ListBox
Set ctlList = Me.ppe_req
With ctlList
For Each varItem In .ItemsSelected
strPpe_req = strPpe_req & varItem & vbCrLf
Next varItem
End With
Dim varItem As Variant
Dim ctlList As ListBox
Set ctlList = Me.ppe_req
With ctlList
For Each varItem In .ItemsSelected
strPpe_req = strPpe_req & varItem & vbCrLf
Next varItem
End With
bczingo,
pls read posted comment before posting already posted codes.
Nash
do a compact and repair of your db.
from VBA window do a Debug > compile
correct any errors raised
test
pls read posted comment before posting already posted codes.
Nash
do a compact and repair of your db.
from VBA window do a Debug > compile
correct any errors raised
test
Rey: I know you have A2007. Does Me! work in the Immediate Pane?
ASKER
I had already tried the compact and repair utility but it did not help. I've attached a scaled down version of my database. Open the only form and click the TEST PPE button. A messagebox will display the value of the strPPE_Req variable (which is always empty)
I am using Access 2010. The file will definitely not open in 2003 or earlier but it will open in 2007 (I tested on a different machine).
ListBoxIssue.accdb
I am using Access 2010. The file will definitely not open in 2003 or earlier but it will open in 2007 (I tested on a different machine).
ListBoxIssue.accdb
for one, you are using a combo box, not a listbox.
sorry capricorn1 - didn't see it
ASKER
Capricorn,
Thanks for pointing out that I was using a multi-select combo box rather than a list box. I don't usually use multi select combo boxes and thought they would behave in code the same way list boxes do. For example, intellisense autocompletes me.ppe_req."itemsselected"
Thanks for pointing out that I was using a multi-select combo box rather than a list box. I don't usually use multi select combo boxes and thought they would behave in code the same way list boxes do. For example, intellisense autocompletes me.ppe_req."itemsselected"
ASKER
Anyone?
read thru this link
http://www.theregister.co.uk/2006/07/18/multivalued_datatypes_access/
and then decide if you still want to use MVF
http://www.theregister.co.uk/2006/07/18/multivalued_datatypes_access/
and then decide if you still want to use MVF
ASKER
I'm certainly not opposed to using three tables (levels, ppe, and lvl_ppe for example), but if I do this, how do I present a field on a form where the user can multi-select the ppe required for the current level? I can't get my head around this. I can imagine having a subform that displays all the records on the lvl_ppe table that are related to the current record's level, but I need to show all possible ppe options and allow the user to select the appropriate ones.
you can do this
Add a boolean field to the table and use a continuous form (showing the boolean field) as a subform, instead of using a listbox.
Add a boolean field to the table and use a continuous form (showing the boolean field) as a subform, instead of using a listbox.
ASKER
So if I understand you, I would have to have a record for every combination of ppe/level with a boolean field that is either yes/no? This sounds like unnecessary overhead. It is not too much overhead in this particular case, but as a design approach wouldn't that be a problem?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cap,
I am implementing a slightly different approach. I now have an unbound listbox that has as its Row Source the new "ppe" table (this has 5 records, one for each personal protective equipment option). I use the form's OnCurrent event to get a recordset from the lvl_ppe junction table where the level ID matches the current record's level ID (IOW, all ppe requirements for the current room's level) and then it selects the matching items in the list box. I use the list box's LostFocus event to compare the selected items to the items on the lvl_ppe table. If they no longer match, I update the lvl_ppe table to be in sync with the selected items in the list box.
I believe this is the best of both worlds because I can still give the user the multi-select list box control (which I think is more intuitive and cleaner than a continuous subform) and it conforms to the relational database best practices. What do you think?
I am implementing a slightly different approach. I now have an unbound listbox that has as its Row Source the new "ppe" table (this has 5 records, one for each personal protective equipment option). I use the form's OnCurrent event to get a recordset from the lvl_ppe junction table where the level ID matches the current record's level ID (IOW, all ppe requirements for the current room's level) and then it selects the matching items in the list box. I use the list box's LostFocus event to compare the selected items to the items on the lvl_ppe table. If they no longer match, I update the lvl_ppe table to be in sync with the selected items in the list box.
I believe this is the best of both worlds because I can still give the user the multi-select list box control (which I think is more intuitive and cleaner than a continuous subform) and it conforms to the relational database best practices. What do you think?
dim j
with Me.ppe_req.Selected
for each j in .itemsselected
strPpe_req = strPpe_req & .ItemData(j) & vbCrLf
next
end with