I have used Forum Code but still problem with LIST BOX

[John Jnr]

I want to poulate a table from a list box. The list box shows 3 columns with headings. I have the same headings in a different table. This is my code:
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

Dim MyDb As Database
Dim Myset As Recordset

Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set Myset = MyDb.OpenRecordset("Table1")

MsgBox ("Part Number= " & Me!List26)
Myset.AddNew
Myset![PartNumber] = DLookup("[PartNumber]", "PartsTable", "[PartNumber]=PartNumber")
Myset![PartNumber] = Me!List26
Myset![PartDescription] = DLookup("[PartDescription]", "PartsTable", "[PartNumber]=PartNumber")
Myset![PartDescription] = Me!List26
Myset![PartCost] = DLookup("[PartCost]", "PartsTable", "[PartNumber]=PartNumber")
Myset![PartCost] = Me!List26
Myset![JobID] = Me!Text13
Myset.Update
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Myset.Close

It works OK but only copies the first column of the list box and copies it into all three fields of my target table. What I actually want is it to transfer all three columns of my selection into all three fields of my target table. I hope that is clear enough?
Where am I wrong?


Cheers

J

 

[John Jnr]

It's OK I did it. I am a bit slow today and I lost my job (I wonder why!)

Oh well here is the code:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

Dim MyDb As Database
Dim Myset As Recordset

Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set Myset = MyDb.OpenRecordset("Table1")

MsgBox ("Part Number= " & Me!List26)
Myset.AddNew
Myset![PartNumber] = DLookup("[PartNumber]", "PartsTable", "[PartNumber]=PartNumber")
Myset![PartDescription] = DLookup("[PartDescription]", "PartsTable", "[PartNumber]=PartNumber")
Myset![PartCost] = DLookup("[PartCost]", "PartsTable", "[PartNumber]=PartNumber")
Myset![PartNumber] = Me!List26.Column(0)
Myset![PartDescription] = Me!List26.Column(1)
Myset![PartCost] = Me!List26.Column(2)
Myset![JobID] = Me!Text13
Myset.Update
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Myset.Close

Cheers

J