How to loop through records and then save them in the table

[rbasram]

Hi,

I have a list box which has a list of products in it.

What I am trying to do is when I add a new product I choose 3 or more products from the list box and then move them to the other list box.

In the form I fill all the other fields required. After this I want to save the record but I want to each product in differnt rows. somehow I just want to loop through the listbox and then save the other fields the the same recod.

In the end I will have the 3 differnt products selected appended to the table with all the other fields equal.

I am stuck on this problem any suggestions...

 

[Scoty]

rsoor,
I just finished with a similar problem. only I had to go from table to table. What a mess! Here is what I would suggest. You will need to store the information from the list box to another area (maybe when the user clicks a product the item can "jump over" to another list box) as I understand it the value of the list box is the last Item Selected and you can not get more that one selected index # out of code

Sub SaveThis_Click()
Dim lrsMyRecordSet as RecordSet
Dim lsMySQLStatement as String
Dim liLoop as Interger
lsMySQLStatement = "SELECT * FROM tblMyTable"
Set lrsMyRecordSet = CurrentDB.OpenRecordset(lsMySQLStatement)
For liLoop = 0 to lbDestenation.ListCount
     lbDestenation.Selected(liLoop)
     DoCmd.GoToControl "SomeOtherControl"'I know this looks
                                         'sloppy but this
                                         'is the only way I
                                         'can get the value
                                         'to change
     lrsMyRecordSet.AddNew
     lrsMyRecordSet!Name = txtRepName
     lrsMyRecordSet!Date = txtDate
     lrsMyRecordSet!Item = lbDestenation.Value
     lrsMyRecorSet.Update
Next liLoop
Set lrsMyRecordSet = Nothing
End Sub

You will have to change the variable and control name settings to suit your needs but I don't believe I left anything out. Anyway I hope this helps

Scoty : "Learn from others' mistakes. You could not live long enough to make them all yourself."
-- Hyman George Rickover (1900-86),