Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21

    Post Unanswered: FindFirst Multiple Criteria

    I have a table named tbl_EmployeeDay. Each time someone opens a data entry form, I want to log in this table, only once, their Employee Number, Date, and Start Time. The problem is every time the form is opened a new record is added:

    tbl_EmployeeDay
    ID ENum Date STime TotalWorkHours
    12 0097323 11-07-2006 3:37:24 PM 0
    26 0097323 11-21-2006 11:18:38 AM 0
    27 0097323 11-21-2006 11:18:46 AM 0
    28 0097323 11-21-2006 11:25:10 AM 0

    In the above table, I only want record ID 26 not 27 and 28. I want to find first the person’s log on id and today’s date in this table. If both of those match a record in this table, do not enter another record. If their ID and today’s date are not in a record, then enter a new record with this information. Here is my code:

    Dim EmpNum As String, Date1 As Date, strStringSearch As String
    Dim db As Database, RS1 As Recordset

    Set db = CurrentDb()
    Set RS1 = db.OpenRecordset("tbl_EmployeeDay", dbOpenDynaset)

    'fOSUserName holds the user's NT log in ID.
    txtUserID.Value = fOSUserName

    Date1 = Date
    EmpNum = DLookup("EmployeeID", "tbl_Employees", "UserName = txtUserID.Value")


    RS1.FindFirst "Enum " & "='" & EmpNum & "' AND " & _
    "Date =" & Date1 & ""

    If RS1.NoMatch = True Then
    RS1.AddNew
    RS1("ENum").Value = EmpNum
    RS1("Date").Value = Date
    RS1("STime").Value = Time
    RS1.Update
    RS1.Close
    Else
    GoTo Exit_Form_Load:
    End If

  2. #2
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21

    Post FindFirst Multiple Criteria

    I have a table named tbl_EmployeeDay. Each time someone opens a data entry form, I want to log in this table, only once, their Employee Number, Date, and Start Time. The problem is every time the form is opened a new record is added:

    tbl_EmployeeDay
    ID ENum Date STime TotalWorkHours
    12 0097323 11-07-2006 3:37:24 PM 0
    26 0097323 11-21-2006 11:18:38 AM 0
    27 0097323 11-21-2006 11:18:46 AM 0
    28 0097323 11-21-2006 11:25:10 AM 0

    In the above table, I only want record ID 26 not 27 and 28. I want to find first the person’s log on id and today’s date in this table. If both of those match a record in this table, do not enter another record. If their ID and today’s date are not in a record, then enter a new record with this information. Here is my code:

    Dim EmpNum As String, Date1 As Date, strStringSearch As String
    Dim db As Database, RS1 As Recordset

    Set db = CurrentDb()
    Set RS1 = db.OpenRecordset("tbl_EmployeeDay", dbOpenDynaset)

    'fOSUserName holds the user's NT log in ID.
    txtUserID.Value = fOSUserName

    Date1 = Date
    EmpNum = DLookup("EmployeeID", "tbl_Employees", "UserName = txtUserID.Value")


    RS1.FindFirst "Enum " & "='" & EmpNum & "' AND " & _
    "Date =" & Date1 & ""

    If RS1.NoMatch = True Then
    RS1.AddNew
    RS1("ENum").Value = EmpNum
    RS1("Date").Value = Date
    RS1("STime").Value = Time
    RS1.Update
    RS1.Close
    Else
    GoTo Exit_Form_Load:
    End If

  3. #3
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21

    Post FindFirst Multiple Criteria

    I have a table named tbl_EmployeeDay. Each time someone opens a data entry form, I want to log in this table, only once, their Employee Number, Date, and Start Time. The problem is every time the form is opened a new record is added:

    tbl_EmployeeDay
    ID ENum Date STime TotalWorkHours
    12 0097323 11-07-2006 3:37:24 PM 0
    26 0097323 11-21-2006 11:18:38 AM 0
    27 0097323 11-21-2006 11:18:46 AM 0
    28 0097323 11-21-2006 11:25:10 AM 0

    In the above table, I only want record ID 26 not 27 and 28. I want to find first the person’s log on id and today’s date in this table. If both of those match a record in this table, do not enter another record. If their ID and today’s date are not in a record, then enter a new record with this information. Here is my code:

    Dim EmpNum As String, Date1 As Date, strStringSearch As String
    Dim db As Database, RS1 As Recordset

    Set db = CurrentDb()
    Set RS1 = db.OpenRecordset("tbl_EmployeeDay", dbOpenDynaset)

    'fOSUserName holds the user's NT log in ID.
    txtUserID.Value = fOSUserName

    Date1 = Date
    EmpNum = DLookup("EmployeeID", "tbl_Employees", "UserName = txtUserID.Value")


    RS1.FindFirst "Enum " & "='" & EmpNum & "' AND " & _
    "Date =" & Date1 & ""

    If RS1.NoMatch = True Then
    RS1.AddNew
    RS1("ENum").Value = EmpNum
    RS1("Date").Value = Date
    RS1("STime").Value = Time
    RS1.Update
    RS1.Close
    Else
    GoTo Exit_Form_Load:
    End If

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Try changing

    RS1.FindFirst "Enum " & "='" & EmpNum & "' AND " & _
    "Date =" & Date1 & ""

    to

    RS1.FindFirst "Enum " & "='" & EmpNum & "' AND " & _
    "Date = #" & Date1 & "#"

    Sam

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a dirty solution to your problem:
    make the employee ID & date (but make sure you only use the date not a date time value) as the primary key....

    put the code into the forms on load event as a standalone recordset, with an error trap to allow the form to continue opening if there is already a record for this day AND user.

    failing that you need to do a bit more work


    dlookup is fine, personally Id do a "select <columnlist> from <table> where UserID = blah and logondate=blah, using an updateabel recordset (dynamic)

    if its an empty record set then add a new record, otherwise .....

    bear in mind you proabbly need to encase the date wit the hash symbol #
    eg where <mydatecolumn> = #<mydatevalue>#
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21
    I'm not sure how I created 3 copies when creating this post - sorry about that.

    Sam,

    Adding the pound signs worked. Thank you for your help and quick response.

    Eric

  7. #7
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21
    healdem,

    Putting the hash marks around the date value corrected the issue. Sam Landy also suggested that in a duplicate post of this thread (not sure how I did that and that was not my intent). Thank you for your help.

    Eric

Posting Permissions

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