Results 1 to 4 of 4

Thread: Compare records

  1. #1
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126

    Unanswered: Compare records

    When a user enters information into a database I don't him to enter SAME employeeName twice for one day. Primary key obviously doesn't obviously consider two fields. Is there a VB code I can use in the form? Thank you

  2. #2
    Join Date
    Apr 2003
    Posts
    42

    Hopefully this makes sense....

    Why don't you just test against the EmployeeId?

    I am guessing that you should have at least two tables set up like:

    EMPLOYEES TABLE
    -EmployeeId - (Primary Key)
    -FirstName
    -SecondName
    -etc....


    OTHERTABLE
    -DateId - (Primary key)
    -Date
    -EmployeeId
    -etc....

    If you have the tables set up like this then all you have to do is query the database before you perform the update to 'OTHERTABLE'.

    Eg.

    (This code would be behind a command button, you may need to edit it to suit your purposes and database)

    Private Sub cmdUpdate_Click()

    Dim rst As Recordset
    Dim db As Database
    Dim strSQL As String

    'check to see that a date has been entered
    If Not IsDate(txtDate.Value) Then
    MsgBox ("You must enter a valid date first!")
    txtDate.Value = Nothing

    Else
    'open database object
    Set db = CurrentDb()

    'query database for previous entry
    strSQL = "SELECT OTHERTABLE.Date, OTHERTABLE.EmpoyeeId FROM OTHERTABLE WHERE (((OTHERTABLE.Date)=#" & txtDate.Value & "#))"

    Set rst = db.OpenRecordset(strSQL)

    'test to see if a record exists or not
    If (rst.EOF) Then
    MsgBox ("Currently there are no details entered")

    'add a new record with details
    With rst
    .AddNew

    !Date = txtDate.Value
    !EmployeeId = lstEmployeeId.Value

    .Update

    MsgBox ("Data has been entered"] has been added!")

    End With

    Else
    MsgBox ("Currently there are already employee details entered for this date")
    End If


    'close recordset
    rst.Close
    Set rst = Nothing

    End If

    End Sub


    I hope this helps.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A primary key can include two or more fields, such as EmployeeName and LoginDate. You could truncate the Logindate to the integer portion of the datetime format, and this would prevent a user from creating a record twice on the same day.

    blindman

  4. #4
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126

    This is IT

    Thanks blindman, this is what I was looking for, I had no idea I could setup a multi-field primary key. Awesome, thanks

Posting Permissions

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