Save Listbox Items to Field/s in database 1

[VBeric]

My Database Application in ADODB/VB6/Access has Listbox1 populated with a list of 20 Customer Requirements from an Access Table of all possible requirements. As not all requirements are required then the user selects which are required.
When user double clicks the selections he requires for this record in the database they are then listed in Listbox2.
This works fine but I now need to know how to save ALL the items/contents of Listbox2 to the database record field or mutiple fields and how to display the selections when record is recalled/reviewed
I would appreciate it if someone could show the coding required as I have tried SQL Update/Insert without success.
Thanks in advance.

 

[tedsmith]

As a general rule you never store the data of one table field more than once - it is memory wasteful.
If you did you could just store the listbox2 values in a memo field.
Easier to use a label.caption with wordwrap=true to show listed requirements instead of a listbox and store the contents in a new memo field like any data change.
Put a VbCr after each requirement when selecting for a separate line.

MyRecordset!Edit
MyRecorset!AllRequirements=label1.caption
MyRecordset!Update

Do the reverse when reading it later

Use a text box if you want to add something extra (use VbCrLf for a new line after each requirement)

Or

You could have 20 separate Yes/No fields in your main database,(use fields 1 to 20) and set them to Yes for each requirement selected on the Requirements table based on the ListIndex number. (Eg Select Requirement Item 5 makes Field(5)=Yes

To see the previously chosen requirements later on, have a recordset to find the selected customer and assemble the actual requirement text in a simple label when you select each customer later

'something like
Sub Form_Load()
Dim Requirements(20) as string
'Put all the 20 requirements into an array 
End Sub

Sub ShowReq()
Dim a as long
lblRequirements.caption=""
For a=1 to 20
  If MyRecordet.Fields(a).value=Yes then 
lblRequirements.caption=lblRequirements.caption & Requirement(a) & vbcr
  End if
Next
End sub

The contents of the label become the list of specific requirements for that customer and you can print this if necessary (or even store it in a new memo field in the customer's record instead but this is duplicating data)

If you didn't want to use an array you can make a joined query but you'd read a record for every requirement that you'd have to assemble into one box.