Add Records to table from multiselect listbox (1 Viewer)

[spacepro]

Hi All,

I currently have a multiselect listbox which shows all Employees and there relevant department from tblEmployees.

For Each Employee that is selected I want to add a record to log the time in and out to tblTime.

But I want to look up the table tbltime to ensure they haven't already logged in today. Currently I am using the following code, but this does not include the validation.

Private Sub CmdLogin_Click()
    Dim myFrm As Form, myCtl As Control
    Dim item As Variant
    Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblTime", dbOpenDynaset)

    
    Set myFrm = Me
    Set myCtl = Me.Lbxemployee
    
        With rst
        For Each item In myCtl.ItemsSelected
        .AddNew
        .Fields("Employee") = Me.Lbxemployee.Column(1)
        .Fields("Department") = Me.Lbxemployee.Column(2)
        .Fields("DateIn") = Date
        .Fields("TimeIn") = Time
        .Fields("EnteredBy") = Me.Text6
        .Update
        MsgBox "Logged In", vbInformation, "OK.."
        Next item
        End With
    Set rst = Nothing
    
   
End Sub

The problem is that even the code selects the selected items it will only select the one that was clicked on.

Thanks

Andy

 

[Mile-O]

Me.Lbxemployee.Column(1, item)
Me.Lbxemployee.Column(2, item)

 

[Rich]

Why not just restrict the list box to those who haven't logged in?

 

[spacepro]

Thanks Mile,

So simple when you know. Just need to work on my validation now.


Thanks

Andy

 

[spacepro]

Why not just restrict the list box to those who haven't logged in?

Thanks Rich, never thought of that..

Cheers

Andy

 

[spacepro]

Mile/Anyone

Can you help?

I am now trying to do the same for logging people out and I am using the following:

NameofEmployee = rst.fields("Employee")

With rst
    For Each item In myCtl.ItemsSelected
        If Me.Lbxemployee.Column(1, item) = Nameofemployee Then
    
        .Edit
        .Fields("TimeOut") = Time
        .Update
        End If
    Next item
        .MoveNext
End With
Set rst = Nothing
    MsgBox "All Selected Employees Are Now Logged Out", vbInformation, "OK.."

It will only select from the top down has this is the way it's reading the recordset, how would I loop the recordset until the if statement = true

Tired now....

Many Thanks

Andy

 

[Rich]

I use this

strSQL = "INSERT INTO tblMail ( fldLtrPK, AddressID ) SELECT tblLetters.fldLtrPK, [Properties owned].AddressID FROM tblLetters, [PropertiesOwned]"
For I = 0 To List0.ListCount - 1
If List0.Selected(I) Then
If List0.Column(0, I) = "All" Then
flgAll = True
End If
strIN = strIN & List0.Column(0, I) & ","
End If
Next I

'create the WHERE string, stripping off the last comma of the IN string
strWhere = "WHERE [AddressID] In (" & left((strIN), Len(strIN) - 1) & " ) And ([tblLetters].[fldLtrPK] In(" & Me![Text7] & "))"

'if "All" was selected, don't add the WHERE condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If

DBEngine(0)(0).Execute strSQL, dbFailOnError

any help?

 

[spacepro]

Hi Rich,

Thanks for the suggestion, the code looks complicated, That learning curve is still a bit steep.

I haven't tried it yet but what I am trying to do with my code is to match the name in the listbox (column 1) with the entry in the table where the Timeout field is null and the name matches the employee field.

I just wondered how I would loop the recordset continually until the if statement matches then moves onto the next name in the listbox that is selected.

I will try your suggestion, but just wondered if my code could be adapted to do the above.

Thanks

Andy

 

[spacepro]

Rich,

Sorry to be a pest but any chance of explaining what the code does, just can't get my head around it at the moment.

Cheers

Andy