save listbox columns to table fields

rikklaney1

Registered User.
Local time
Today, 12:08
Joined
Nov 20, 2014
Messages
157
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?
 
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
 
Actually saving it to another table with some added info. Thanks.
 
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.
 
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.
 

Users who are viewing this thread

Back
Top Bottom