Link to home
Start Free TrialLog in
Avatar of CountryGirlMD
CountryGirlMDFlag for United States of America

asked on

Access listbox with multi select not saving values

I’m trying to resolve an issue using list box with the multi select option
I have form with a tab control and a sub form
I have a multi select listbox on the parent form / 1st tab called ActionHistory
I have a combo boxes on both the parent and sub form on 2nd tab that have the same values as the ActionHistory listbox called ActionTaken & ActionTaken2

What I want to do is when an item is selected in either of the combo boxes I also want it selected in the list box.    I know the first question everyone will have is why?  Answer - Because the customer wants it.

The combo box on the sub form works – except that it shifts the focus to the list box on the 1st tab and ignores the set focus back to the subform.  I can create multiple child records on the subform and all of the selected ActionTaken entries are saved in the ActionHistory on the parent.

The combo box on the parent form DOES NOT WORK – it checks the box on the list but doesn’t actually save the selection – if you move to the next record and come back the item is no longer selected

I put the same code under a command button on the parent form – if you click it after selecting the item from the drop down the selection is saved

If I call the command button from the AfterUpdate of the drop it does not work

So if anybody can tell me what’s wrong or give me a better way to do this I'm open to all ideas

Here’s the relevant code

Sub form code that works – except for the setfocus comand
Private Sub ActionTaken2_AfterUpdate()
    Dim i As Variant
    For i = 0 To Me.Parent.ActionHistory.ListCount - 1
        If Me.Parent.ActionHistory.ItemData(i) = Me.ActionTaken2 Then
      Me.Parent.ActionHistory.Selected(i) = True
        End if
    Next
    Me.Remarks.SetFocus
End Sub

Parent form code – checks the box on the list but does not save value
Private Sub ActionTaken_AfterUpdate()
    Dim i As Variant
    For i = 0 To Me.ActionHistory.ListCount - 1
       If Me.ActionHistory.ItemData(i) = Me.ActionTaken Then
            Me.ActionHistory.Selected(i) = True
        End If
    Next
End Sub

Command button code that works when you click the button
Private Sub Command67_Click()
    Dim i As Variant
    For i = 0 To Me.ActionHistory.ListCount - 1
        If Me.ActionHistory.ItemData(i) = Me.ActionTaken Then Me.ActionHistory.Selected(i) = True
    Next
End Sub

Calling the command button from the AfterUpdate of the combo box – DOES NOT WORK
Private Sub ActionTaken_AfterUpdate()
        DoCmd.RunCommand acCmdSaveRecord
        Call Command67_Click
End Sub
Avatar of Eric Sherman
Eric Sherman
Flag of United States of America image

Can you load up a sample db ... would make it easier to understand and see exactly what needs to be done???

ET
Avatar of CountryGirlMD

ASKER

Here's a sample of what I'm trying do
ListboxTest.accdb
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<no points please>

I concur completely with mbizup - get rid of MVFs.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
mbizup

I gave you the points for the effort toward alternate solutions
the B grade because I would have liked to know why the code didn't work
The original 'question' in your post as I understood it was:

"So if anybody can tell me what’s wrong or give me a better way to do this I'm open to all ideas"

So what I posted was an attempt at giving you a better, more workable structure... with a user interface like the customer wanted.

<<  but nobody actually answered the question. >>

I realize I didn't post anything near a complete solution, but was hoping for some feedback and clarification.

Fwiw, replying a week after we suggest solutions and immediately closing the question does not help us improve the suggestions posted.

Most of the regulars here do stay subscribed to our participated questions and will work with you towards better solutions - but it works best if you give us timely feedback (ideally within the same day) and a chance to follow up.
mbizup

I gave you the points for the effort toward alternate solutions
the B grade because I would have liked to know why the code didn't work