Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012
    Posts
    8

    Unanswered: Two DAO.Recordset.. is it possible? please look at this if this is possible

    Please help me with this. I have two tables Login and TimeSheet.
    under Login table i have [PK]EmpID, vzid, LastName, FirstName
    under TimeSheet table I have [PK]EmployeeID, Empvzid, LName, TimeIn, TimeOut

    What I'm trying to do is, I want a user to enter his ID (alphanumeric) to textbox then click on the Clock In or Clock Out button.

    What the Clock In button will do is search for the ID under vzid under Login if the ID is in the record then it will put a time stamp of the date and time in ClockIn under TimeSheet and will copy the record from LastName to LName and vzid to Empvzid. Lastly it will prompt a message box that clock in is successful with the FirstName and LastName.

    Same goes with ClockOut

    Here is my weird script:

    Private Sub ClockIn_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs1 As DAO.Recordset2

    Dim strCriteria As String
    Dim Ndate As Date

    Ndate = Now()
    strCriteria = "vzid = '" & Me.InputT.Value & "'"
    Set db = CurrentDb
    Set rs = db.OpenRecordset("LoginT", dbOpenSnapshot)

    rs.FindFirst strCriteria
    If rs.NoMatch = False Then

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("TimeSheet", dbOpenSnapshot)

    rs.AddNew
    rs!Empvzid = rs1!VZID
    rs!LName = rs1!LastName
    rs!TimeIn = Ndate
    rs.Update
    rs.Close

    Else
    rs.FindNext strCriteria

    End If


    Me.InputT.Value = ""
    DoCmd.Save
    Set rs = Nothing

    End Sub

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You really don't need recordsets for this. You can use DLookup to check for the user's ID, and then compile a simple SQL statement to execute that will add a new record to TimeSheet based on the current date/time and the user's details:
    Code:
    Dim strUser As String
    Dim datNow as Date
    Dim strSQL as String
    
    strUser = Nz(DBLookup("[LName]", "Login", "[vzid] = '" & Me.InputT & "'"), "")
    If strUser = "" Then
    MsgBox "The user ID you entered was not found!  Please check and try again.", vbCritical, "Unknown user ID"
    Exit Sub
    End If
    
    datNow = Now()
    strSQL = "INSERT INTO TimeSheet(Empvzid, LName, TimeIn) " _
    & "VALUES ('" & Me.InputT & "', '" & strUser & "', #" & datNow  & "#);"
    
    CurrentDb.Execute strSQL
    The above is air code, but will hopefully point you in the right direction.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •