List Box Update

[JC3]

Hi

I am creating an input form to assign an engineer to a project for x days.

The form consists of two Text Boxes for the Engineer Code and Project Name. I also have a Multi Select List Box which is linked to a table. The Table contains two fields ID-Autonumber and Date-ShortDate. This table contains all dates for the next three years.

I ideally want the user to select the Engineer Code and Project and the desired dates the engineer is to be assigned and add these to another table.

I have tried the following code but while the two Text Boxes update fine and the number of selected dates are added the dates themselves are not.

Can anyone see where I am going wrong and guide me in the right direction

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim db As ADODB.Connection
Dim r As ADODB.Recordset
Set db = CurrentProject.Connection
Set r = New ADODB.Recordset

Dim var As Variant

r.Open "tblIM", db, adOpenStatic, adLockPessimistic

For Each var In Me.List0.ItemsSelected
    r.MoveLast
    r.AddNew
    r.Fields("Date") = Me.List0.Value
    r.Fields("EngineerCode") = Me.EngineerCode.Value
    r.Fields("Project") = Me.Project.Value
    r.Update
Next var

Exit_Command4_Click:
    Exit Sub

Err_Command4_Click:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Command4_Click
    
End Sub

Thanks

JC

 

[RuralGuy]

Is the date column the bound column of your ListBox?

 

[RuralGuy]

Highlight ItemsSelected in your code and hit F1. I do not believe you are using it correctly to retrieve the date value.

 

[JC3]

You were right I had to use the column option to bring back the correct data. Works a charm now.

Me.List0.Column(1, var)

Thanks for your help.

JC

 

[RuralGuy]

Glad to assist and thanks for posting back with your success.