Using a listbox in a form to select multiple items for a record

[warren0127]

Hi,



I have a form that has several fields. One of them is a combobox that has a list of 10 choices. I have been given a requirement that the user must be able to select multiple items (more than ONE item); which means I had to change the combobox to a listbox.

My question is how do I get the listbox to be able to allow the user to select more than one item and save those items selected in the field to the record (ID). For example: User A has his "Filtered" form for his project.. and now needs to choose 3 items from the listbox field which will save to his ProjectID. In the table, it should be able to store those "3 items" in that listbox field.

I tried to modify the listbox properties to "Simple" and "extended" -- but I am getting the following error in the form when I try to do that:

"You tried to assign the Null value to a variable that is not a Variant data type" and then it wont allow me to save.

Is there a short snippet of VBA that I could add to do what I want? I tried googling the same problem but nothing I found had worked.

Any help would be appreciated!

[orange]

There are several youtube tutorials re MSAccess Multiselect listbox.
Have you watched any of these?
Good luck.

[June7]

Start with http://allenbrowne.com/ser-50.html

[warren0127]

Thanks all for your responses. I will take a look at the videos and see if that can help with my solution.

June7 - I noticed the link you had sent is for making a report. Could I leverage that example in a form, and then have those multiple choices be stored within a record?

Thanks
- Warren

[June7]

If you want to save multiple selections then options are:

1. multi-value field (I NEVER use them)

2. VBA code that loops through the listbox list and saves a record for each selected item. Allen Browne's code example demonstrates looping through listbox. In your case, instead of concatenating string, you would do an SQL INSERT action.

[warren0127]

June7,

I will give it a shot and see what I come up with. Thanks for the reference. I'm sure this has been done before with a form.. seems tricky.

[warren0127]

Hi June,

I found a video demonstrated by datapig technologies that describes exactly what I want to do with the two listboxes making them multi-value in the form:

http://www.datapigtechnologies.com/f...tboxtrick.html

Now, the problem is, how do I store those values in a record in a table, or get those values from the second listbox to save to a record with multiple values in a table. I'm still struggling with this.. I've looked at Allen Browne's example but it cannot seem to apply it to a form (his example is showing it to filter reports).

Appreciate any help you can provide.

- Warren

[June7]

The Allen Browne code that loops through listbox is what you need in order to save an individual record for each item selected. Inside the loop, instead of building a concatenated string, you would run SQL INSERT action to create record. Rest of the code related to filtering report can be discarded. Post your attempted code for analysis.

If you instead want to save multiple values to a single record - this is done by binding combobox or listbox to a multi-value field and no code is required.

[warren0127]

Thanks for the response. I will be posting my code for analysis shortly!

[warren0127]

So here is the code I came up with to Insert the unbound lstProducts listbox to a table I have called Fulfillments. It worked... sort of - not how I intend it to. It is not copying the "other fields" of the form with the insert of the ProductID, which is making the fulfillment table having empty fields.

Option Compare Database
Option Explicit


Private Sub cmdSaveSelected_Click()

Dim strInsertSQL As String
Dim varItem As Variant
Dim FulfillmentID As Integer


With Me.lstProducts
For Each varItem In .ItemsSelected
strInsertSQL = "INSERT INTO Fulfillment(ProductID) VALUES ('" & Me.lstProducts.ItemData(varItem) & "')"

DBEngine(0)(0).Execute strInsertSQL, dbFailOnError

Next varItem

End With


End Sub

Below are some screenshots of how my relationships are set up. As you can see, a Project has multiple Products, and a Fulfillment cant have multiple Products - Relationships. Because "Product" and "Project" have a relationship, the mainform/subforms are bound together through the ProjectID and ProductID. Basically, when you would open the main form, you select your project, it filters the project, then, it will know which Products you have under that specific Project. The problem is, the subform will work with a bound ProductID (this is why you see another product field before you get to the list box). If I try to make the listbox bound to ProductID - I get a "Null" error. As you can see below, the records are being produced with empty data - but only carries the productID - and a duplicate ProductID is being made as well because thats the "bound" ProductID to the form, which is a "Foreign" key in the table. The listbox is NOT bound to anything.



What I would like it to do is have the INSERT SQL function apply ALL fulfillment fields to each product selected in the Product List (as you can see in the Table, those fields were not populated - they are blank - all but the ProductID was inserted. How can I get it to work, to show all fields with the fulfillment data entered, for each ProductID selected in the list? Also, how do I get around having the "Extra" bound ProductID field if I want a multi-select list of Products, that have a relationship with a Project?

Thank you for your help.

[June7]

Include as many fields as you want in the SQL action. A generic example for text, date, number field types:

"INSERT INTO Tablename (field1, field2, field3) VALUES ('" & Me.controlname1 & "', #" & Me.controlname2 & "#, " & Me.controlname3 & ")"

However, if you are having to save the same data to multiple records, possibly data structure is not optimized for data normalization.

[warren0127]

So, I can include more fields in the SQL statement, even though it is inside the "With. Me.lstProducts"? Are you saying I can also populate the other fields such as Description, GapNum, GapStatement, etc -- all in that same line and it would work?

With Me.lstProducts
For Each varItem In .ItemsSelected
strInsertSQL = "INSERT INTO Fulfillment(ProductID) VALUES ('" & Me.lstProducts.ItemData(varItem) & "')"

DBEngine(0)(0).Execute strInsertSQL, dbFailOnError

Also, to help normalize, would you suggest an additional table?

[June7]

Yes to all.

[warren0127]

When I try adding in another field from my table as a test to the SQL line, I get a "Syntax Error" with the following:

Private Sub cmdSaveSelected_Click()

Dim strInsertSQL As String
Dim varItem As Variant




With Me.lstProducts
For Each varItem In .ItemsSelected
strInsertSQL = "INSERT INTO Fulfillment(ProductID, Fulfillment Description) VALUES ('" & Me.lstProducts.ItemData(varItem) & "', '" & Me.Fulfillment_Description & "')"

DBEngine(0)(0).Execute strInsertSQL, dbFailOnError

Next varItem

End With


End Sub

It highlights the "DBEngine(0)(0).Execute strInsertSQL, dbFailOnError" line in the code when I click Debug. Do you know where my mistake lies? I've tried several attempts to change things but still get the error.


EDIT: I caught the issue - it was my field naming convention. Having spaces in between field names is a big nono and that was my issue with Fulfillment Description. Now is called FulfillmentDescription and seems to append to the table.

[warren0127]

June - I have another question in regards to my original post.

In my case, the ProductID (or Product) is a part of a relationship with an overall Project. My listbox is not bound to anything. But, it would make sense if the Listbox was bound to the original source: ProductID. Is this possible, even with the multi-select SQL Insert? Right now, I have the "actual" ProductID field in the form as well.. because without it, none of this would work with the current setup I have.

I would like to remove the additional ProductID single field and just use the Product List I have setup with it being "bound" to my ProductID. When I tried to do this, I got a null error. Please advise. Thank you for your help.

- Warren