save listbox columns to table fields

[rikklaney1]

What is the correct way to save the values in a listbox column to the fields of a table? I can use
rec("field1") = Me.list16.ItemData(Varitem)
and that records the bound column to field 1 but how can I save the unbound columns to other fields in the table?

 

[CJ_London]

all you should need is this

rec("field1") = Me.list16

to refer to other columns

rec("field2") = Me.list16.column(x)

where x is the column you want to save (first column is 0)

however I don't understand why you would want to do this since it means storing the same data twice. All you need is a query which links the value in field1 to the query you have used as your listbox rowsource

 

[rikklaney1]

Actually saving it to another table with some added info. Thanks.

 

[rikklaney1]

Nope. That didn't seem to work. Here is the entirety of the code

Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("select * from [Daily Run] Where field2 = '" & Me.list12 & "'")
If (rec.EOF) Then
rec.AddNew
rec("field1") = Me.text11
rec("field2") = Me.list12
rec("field3") = Me.list12.Column(1)
rec("field4") = Me.list12.Column(2)
rec("field5") = Me.list12.Column(3)
rec("Run Date") = Me.Daterun
rec("Shift") = Me.Shift
Else
rec.Edit
rec("field1") = Me.text11
rec("field2") = Me.list12
rec("field3") = Me.list12.Column(1)
rec("field4") = Me.list12.Column(2)
rec("field5") = Me.list12.Column(3)
rec("Run Date") = Me.Daterun
rec("Shift") = Me.Shift
End If
rec.Update
Set rec = Nothing
Set db = Nothing


The only field that actually gets a value entered is field2 and it seems to get the first column value no matter which column is the bound column. I'm sure it's something simple taht I am just overlooking but it's eluding me.

 

[rikklaney1]

Solved it Here is what I did

Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("select * from [Daily Run] Where field2 = '" & Me.list12.column(0, field2) & "'")
If (rec.EOF) Then
rec.AddNew
rec("field1") = Me.text11
rec("field2") = Me.list12.column(0, field2)
rec("field3") = Me.list12.Column(1, Field3)
rec("field4") = Me.list12.Column(2, field4)
rec("field5") = Me.list12.Column(3, field5)
rec("Run Date") = Me.Daterun
rec("Shift") = Me.Shift
Else
rec.Edit
rec("field1") = Me.text11
rec("field2") = Me.list12.column(0, field2)
rec("field3") = Me.list12.Column(1, field3)
rec("field4") = Me.list12.Column(2, field4)
rec("field5") = Me.list12.Column(3, field5)
rec("Run Date") = Me.Daterun
rec("Shift") = Me.Shift
End If
rec.Update
Set rec = Nothing
Set db = Nothing



Adding the ,FieldX did the trick. No idea why but it's working.