List Box Problems

[cbd]

I have three tables, "List of Materials", "Risk Details" and "Risk Phrases". Risk Phrases table contains risk numbers with the associated risk phrase. List of materials store general information about a material and Risk Details joins each material with its asscoicated risk. When a user is entering a new record, they select different risks associated with the material from a list box with multi select - Simple. When the they close the form or move on to enter another record, I want the risks selected to automatically be entered into the table called "Risk Details" and the material details into the "List of Materials" table. I am not very good at coding but I think i am on the right track. Unfortunately my code doesn't work. Here it is.

Dim db As Database, rs As Recordset
Dim varItm As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("Risk Details", dbOpenDynaset)
On Error Resume Next
rs.AddNew
For Each varItm In Me!List58.ItemsSelected
rs![Cas Number] = Me![Cast Number]
rs![Risk Phrase Number] = Me!List58.ItemData(varItm)
rs.Update
Next varItm


The above code i also called in the OnCurrent procedure of my form, I'm not sure if that is appropriate.

 

[LittleSmudge]

One obvious problem stands out. You need the AddNew inside the loop because you want to add a new record EACH time you go around the loop. Else all you get is the last item added and the rest are ignored. ( Is that what's been happening ? )


For Each varItm In Me!List58.ItemsSelected
rs.AddNew
rs![Cas Number] = Me![Cast Number]
rs![Risk Phrase Number] = Me!List58.ItemData(varItm)
rs.Update
Next varItm


Also, see the FAQ at

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=2190&CFID=78740754&CFTOKEN=15215991

a about avoiding the use of b1@5^£d space characters in Access object names !



'ope-that-'elps.

G LS

 

[LittleSmudge]

Correction to the link

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=2190&CFID=78740754&CFTOKEN=15215991