Insert Listbox values

[smti]

Hello:

I need some help inserting values from a listbox in to a table. I have a table with a field named: PrefReports. This field will hold data from values in a list box that the user selects.

Table: Profiles
Field Name: PrefReports Type: Memo

The user utilizes a form to input the values necessary for the query. I then have the query tied to a command button. When the command button is clicked, the query is executed.

The query is as follows:

INSERT INTO TblProfiles ( [Employee ID], FName, LName, Barcode, PrefReports )
VALUES ([forms]![FrmCreateNewProfile]![txtEmployeeID], [forms]![FrmCreateNewProfile]![txtEmployeeFName], [forms]![FrmCreateNewProfile]![txtEmployeeLName], [forms]![FrmCreateNewProfile]![txtBarcode], [forms]![FrmCreateNewProfile]!lstReports.Value);

Problem
How can I take what the user selects in the list box, and insert into my TblProfiles.PrefReports field? When I run the query, all other information makes it to the table except the list box values.

Any help would be greatly appreciated!

 

[MarkK]

Are there multiple values in the listbox? If so, you can't accurately represent that in a single record without violating normalization rules. If you have many items related to a single item you need to store the "many" things in a separate related table, one item per row.

 

[smti]

Yes, the list box has multiple values. So, you are saying I would need to insert the values in to a secondary table and tie it together using the primary key? Even still, how would I go about getting what the user selected from the listbox on the form?

 

[MarkK]

You need to write a loop that enumerates the selected items in the listbox and takes action on that basis. Check out the ItemsSelected property of the Access.ListBox object, which is an array of row numbers of the selected rows in the list. There is a good article in VBA help for the ListBox.ItemsSelected property that shows a couple code examples of loops that do this job.