Storing Multiple list Box Selections in different records

[jvet4]

Hello,

I have a form with a list box that stores data in a field is set as a required entry. I have a two part question:
1. When I change the list box propery MultiSelect from none to either Simple or Extended, I get an error that says the field I am saving to cannot contain a null value because required is set to true (i do not get this error when MultiSelect is set to none.) Is there a way to work around this without changing the required property to none?

2. Is there a way to have multiple items selected in a list box and save them to seperate records. There are four items on the form that I have going into the record (testID, DocumentID, requirementID, and TestStep). For each TestID, DocumentID, and TestStep, there can be multiple requirementID (i.e. Step 5 can have 3 requirementIDs mapped to it). I can accomplish this task by having the MultipleSelect property set to no and enter them one at a time, but I am trying to save time by allowing multiple requirementIDs to be selected for the same testID, DocumentID, and TestStep, but having them saved in seperate records.


Any information in these areas would be very helpful.

Thanks,

-Jason

 

[jebry]

Hi Jason!

First, to get rid of the error you are getting, go to the data tab in the property sheet for the list box and delete the control source. After setting the Multiselect property of the list box you will want to add a save button to the form if it doesn't already have one. In the Click event of the save button use the following code:

Dim rst As DAO.Recordset
Dim varRow As Variant

Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)

For Each varRow in Me!YourList.ItemsSelected
rst.AddNew
rst!TestID = Me!txtTestID
rst!DocumentID = Me!txtDocumentID
rst!TestStep = Me!txtTestStep
rst!RequirementID = Me!YourList.Column(0, varRow)
rst.Update
Next varRow

Set rst = Nothing

In the above code I have assumed that the RequirementID is in the first column of the list box, if it isn't then increment the 0 as needed.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[jvet4]

Jeff,

Thanks for the information, it works almost perfectly. If I select 3 Requirements from the list box, it stores those three in seperate rows (along with the TestID, DocumentID, and TestStep) however it also stores a 4th record with the TestID, DocumentID, TestStep, and leaves the RequirementID blank. Is there a way to prevent the blank record from being stored?

Thanks Again for your Help.

-Jason

 

[jebry]

Hi Jason!

Yes there is. What you have happening here is that the form is bound to the table and the three text boxes are bound to their respective fields. So, when you close the form or move to a new record, Access save a record with just the three bound fields. What you want to do is remove the control source from the text boxes and the record source from the form. You will need to build another form to view the records. There are ways of doing this in one form by how you open the form, but, with so few fields, creating another form is probably easiest.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[jvet4]

Jeff,

Thanks for all the help, it works perfectly!!!

-Jason