Database Record Exist

[abelingaw]

I'm having problem trying to find out what is wrong with a code.
Here's what it is supposed to do.

1. The messagebox should only appear if a record already exist in the database
2. If there is no record of the same information, then it would save the new record

Here's the code that creates the error (Logic ?):

DBConnection "Database.mdb"
    
    With RecSet
    

        .Open "Select * from TimeLog where EM_Name = '" & _
         LblEmployeeName.Caption & "'", DBLink, adOpenDynamic, adLockOptimistic
    
       
        If .EOF = False Then
            MsgBox "Record already exist.", vbExclamation, "Prompt"

            DBLink.Close
            Exit Sub
        End If
        
        .Close
    End With

And the rest:

With RecSet
    
              
        .Open "Select * From TimeLog where ID = " & FrmMainDTR.txtEIDN.Text & " and Date_LOG = '" & Format(Date, "YYYY-MM-DD") & "'", DBLink, adOpenDynamic, adLockOptimistic
               
        If .EOF = True Then
            .AddNew
            
            .Fields(10) = FrmMainDTR.txtEIDN.Text
            .Fields(1) = LblEmployeeName.Caption
            .Fields(2) = Format(Date, "YYYY-MM-DD")
            
        End If
        
        
   'THE FOLLOWING CODE IS IGNORED AND A USER CAN LOG IN/OUT MULTIPLE TIMES
   
   '(Turned it to Text) and it now works well
        
        If LblLogType.Caption = "In [AM]" And IsNull(.Fields(3)) = True Then
             .Fields(3) = sts2.Panels(2).Text
            MsgBox "The fingerprint was verified and user was logged in at " & sts2.Panels(2).Text & ".", vbInformation, "FingerPrint Verification Successful"
        ElseIf LblLogType.Caption = "In [AM]" And IsNull(.Fields(3)) = False Then
            MsgBox "The user has already Logged In for the Morning"
        End If
        
        If LblLogType.Caption = "Out [AM]" And IsNull(.Fields(4)) = True Then
            .Fields(4) = sts2.Panels(2).Text
            MsgBox "The fingerprint was verified and user was logged out at " & sts2.Panels(2).Text & ".", vbInformation, "FingerPrint Verification Successful"
        ElseIf LblLogType.Caption = "Out [AM]" And IsNull(.Fields(4)) = False Then
            MsgBox "The user has already Logged Out for the Morning"
        End If
        
        If LblLogType.Caption = "In [PM]" And IsNull(.Fields(6)) = True Then
            .Fields(6) = sts2.Panels(2).Text
            MsgBox "The fingerprint was verified and user was logged in at " & sts2.Panels(2).Text & ".", vbInformation, "FingerPrint Verification Successful"
        ElseIf LblLogType.Caption = "In [PM]" And IsNull(.Fields(6)) = False Then
            MsgBox "The user has already Logged In for the Afternoon"
        End If
        
        If LblLogType.Caption = "Out [PM]" And IsNull(.Fields(7)) = True Then
            .Fields(7) = sts2.Panels(2).Text
            MsgBox "The fingerprint was verified and user was logged out at " & sts2.Panels(2).Text & ".", vbInformation, "FingerPrint Verification Successful"
        ElseIf LblLogType.Caption = "Out [PM]" And IsNull(.Fields(7)) = False Then
            MsgBox "The user has already Logged Out for the Afternoon"
        End If
        
        .Update
        .Close
    End With
    
    DBLink.Close

This gets me stucked in my project.

Thanks in advance

[Cruize_Invades]

what's the error?
or it just doesn't get what you want to produce?

[abelingaw]

Error is:

1. The messagebox appears even if the database is empty and I'm trying to save a
new record in it.

Which should not supposed to be cuz the database is EMPTY.

[debasisdas]

try this

Select count(*) from TimeLog where EM_Name = '.................

If it returns 1 display the message else proceed further and save the data.

You need to ensure that EM_Name is the primary key of the table

[abelingaw]

Got this one solved.

The problem was in my SQL statement.

Query = "Select * from TimeLog " & _
              "where EM_Name = '" & LblEmployeeName.Caption & "' " & _
              "AND IN_AM <> null and OUT_AM <> null " & _
              "AND IN_PM <> null and OUT_PM  <> null " & _
              "AND Date_LOG = '" & Format(Date, "YYYY-MM-DD") & "'"

Thanks..