Updating Table Field from a Form w/List Boxes

[Josh Hill]

Happy Halloween everyone!

This is my first post, but I've been lurking for sometime. I'm grateful for all the great advice given here; despite my efforts, I can't find anything directly related to what I'm doing, though.

I have a form that is populated from a query. The query has some calculated fields and some direct selection fields from a couple of tables. One of the direcly selected fields is one that I'm trying to populate from the items in a list box.

On this form, there are two list boxes, List1 and List2. The user makes selections in List1 and clicks a command button, which runs code so that the second list box is populated with the items from List1. This was shown here:

MS Article

I actually just used this code and made changes accordingly so that this feature is working perfectly. However, the items in List2 need to be updated into a table's field, and this is where I'm having the problem.

I've got List2 bound to the proper field in the query, and I can manually run that query and make changes in that field fine. What I can't figure out is why I can't get the ItemsData property of the List2 control in there. For ease of code, I've added a line that copies the List2 rowsource variable to another variable so that the values can be used elsewhere. I can't seem to get the field to receive the variable in VBA, and I can't figure out how to get the values back into the query so that the query's source table is updated.

Any clues? Or is this unclear? I'm happy to give any further information. I've been working on this for more than a week, trying different things, and I'm at wits' end.

Thank you all again for any responses. I appreciate the help.

Josh Hill

 

[supercharge]

why I can't get the ItemsData property of the List2 control in there.

Correct me if I'm wrong but as I remember that in order to use ItemData, you will have to use ItemsSelected together with ItemData. If not, your ItemData would be Null because none of the items in your listbox is selected.

You can make all the items in your listbox auto-selected when loaded. Post it if you need help on that.

 

[Josh Hill]

Thanks for the response, Supercharge.

I have a small update to make; I added a text box to the form, and populated the text box with the variable I used to store the recordsource of the second list box. That works fine; now everything in the second list box is going into the appropriate table field.

Now what I need to do is get the second list box to update with the record. That is, when I move from one record to the next, the value(s) in the second list box remains whatever it was for the last record. I tried setting the second list box's RecordSource property to Nothing in the OnCurrent event of the form, but that didn't work. I will also try clearing the list box's ItemsData property there and see if that works.

Any insights on this secondary issue are greatly appreciated, and I'll check back as I'm able. Thank you again for responding!

Josh

 

[Josh Hill]

Thanks for all the help, I figured out how to get this done. I appreciate the assistance.

-Josh