Listbox, selecting multiples, and subsequent display of selections 1

[cbalser]

I have 2 tables: parent table= tblListProv and child = tblCriteria. tblCriteria needs to store multi-selections linked by Prov_ID to tblListProv from a listbox (lboCriteria) that I placed on frmForm1. I can select more than one item from the listbox (using simple property) but how do I save the selections to tblCriteria table (linking on Prov_ID in both tables). I would appreciate the complete code (not bits and pieces--way too confusing) and any control or form properties that might need to be changed or entered? Should it activate upon lost_focus or when record/form is saved? I think I need 2 forms: for input and for viewing but am open to better ideas.

Also, I am confounded in how to display those selections later when pulling up the particular record. I would like to use another listbox (to view) instead of a subform, as they are so bulky looking. Max items available is 5. Any better ideas are appreciated!

Thanks much for detailed assistance!

 

[PHV]

Thanks much for detailed assistance
what have YOU tried so far and where in YOUR code are you stuck ?
Tip: in the VBA help take a look at the ItemsSelected collection.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LonnieJohnson]

In order to get detailed assistance you must give detailed specifications. Based on what you gave, this is what I can give you.

1. The process of saving to your table tblCriteria.

I created a button on your frmForm1 called cmdAddItems. In the click event I iterate through the list of items and take the selected ones and place them into your table. You did not say where you list comes from so I made my list a Value List and typed the values in.

Private Sub cmdAddItems_Click()

    Dim var As Variant
    
    For Each var In Me.lboCriteria.ItemsSelected
        CurrentDb.Execute "INSERT INTO tblCriteria " _
        & "(Prov_ID,Criteria) " _
        & "Values ('" & Me.Prov_ID & "','" & Me.lboCriteria.ItemData(var) & "')"
    Next
    
    MsgBox "Updates Saved", vbInformation
    Me.lboSelectedCriteria.Requery
    
End Sub

2) Showing the values selected. At the end of the code you see the line "Me.lboSelectedCriteria.Requery". This is a second list box I put on your form. It's RowSource is the following SQL.

SELECT tblCriteria.Criteria FROM tblCriteria WHERE (((tblCriteria.Prov_ID)=Forms!frmForm1!Prov_ID));

tblCriteria.Criteria is the field name I gave a field in your tblCriteria table to hold the values.

You should be able to take this and build upon it. If you do not have vba knowledge, you may want to do some reading up on it or hire a consultant to assist you in building the database if you want too much detial done for you.

I hope this helps.

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[cbalser]

Thanks both of you for the help! With Lonnie's code it saves the selections to the child db. There is a glitch however when opening the form. A box appears asking for user to enter parameter value: Forms!frmForm1!Prov_ID.

If I enter a number so it will open the form (which I shouldn't have to do) and move to other recs with the record selector, the data in the second listbox (lboSelectedCriteria) does not change.

Couldn't I just use the one listbox for selecting and viewing? I have spent hours and days reading books and researching this but still can't put the whole thing together. Does anyone have a sample app that does this simply? So sorry for being so dense with this control!

Thanks again!!

 

[LonnieJohnson]

No. You should not use the same box to view and select from.

The selection box is usually a set list of items. For intance a list of options for a car.

AIR CONDITIONING
AM/FM RADIO
SUN ROOF
CUSTOM WHEELS

These choices are available to everyone. However, everyone does not choose the same options. I may want the first two and you want the second two. So you have to be able to show what was chosen and be able to show what is available. Unless I missed the whole point.

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[cbalser]

Yes, I see the point of using 2 boxes; I thought maybe when bringing the record up later, the ones NOT previously selected could be grayed out. But 2 boxes is fine.

Can you tell why when opening the form, a box appears asking for user to "Enter parameter value: Forms!frmForm1!Prov_ID"? What am I missing somewhere? The Prov_ID from tblProvList is on the form as Prov_ID. The box also appears after running the cmdAddItems code that I got from you.

Shouldn't the Control Source for the 2nd listbox be: tblCriteria.Criteria ? Nothing appears in the 2nd box at all when running it or scrolling through the records.

 

[LonnieJohnson]

The second box is filtering the choices in the child table by the ID on the main form. Is the form name correct? I just used what you had which was frmForm1. Did you change the form name?

ProDev, Builders of Affordable Software Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[cbalser]

No, the form name is still Form1.