Form before update workaround

[Garindan]

Hi all, I currently have the code below on a form. The before update code is there to prevent alarms being entered at the same times and works perfectly.

Private Sub btnReschedule_Click()
    Call GetDate([Form]![txtCommentAlarm], 0)
End Sub

Private Sub btnResetComputerName_Click()
    
    If MsgBox("This will reset the computer name which will show the alarm to this Computer. Are you sure?", vbOKCancel, "Warning") = vbOK Then
            
        Me![txtComputerName] = Environ("Computername")
    
    End If
    
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If DCount("*", "tblCustomerComments", "[CommentAlarm]= #" & Format(Me![txtCommentAlarm], "mm\/dd\/yyyy hh:nn") & "#") > 0 Then ' It is a duplicate
        Cancel = True
        MsgBox "Sorry an alarm has already been set at this Date/Time, please enter a different Date/Time"
        Me.Undo  '<----Remove this if you don't want to erase form input
    End If
End Sub

However, the 'ResetComputerName' button is just to change the computer name associated with the alarm. It works as intended, and it doesn't change the alarm time so I don't need to check for duplicates etc, but the beforeupdate code prevents it from saving as it recognises the alarm time as being one that is already set.

How could I change/workaround the code to allow changes to be made the txtComputerName?

Many thanks in advance!

 

[Beetle]

Presumably you only need to check for duplicates if the user is entering a new record, so you can trap for that.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        If DCount("*", "tblCustomerComments", "[CommentAlarm]= #" _
                 & Format(Me![txtCommentAlarm], "mm\/dd\/yyyy hh:nn") & "#") > 0 Then ' It is a duplicate
            Cancel = True
            MsgBox "Sorry an alarm has already been set at this Date/Time," _
                 & " please enter a different Date/Time"
            Me.Undo  '<----Remove this if you don't want to erase form input
        End If
    End If
End Sub

 

[Garindan]

Hi Sean, sorry for the slow reply! Unfortunately I can't use Me.NewRecord because this form is for the manager/admin to view and change alarms, but the alarms have already been set earlier so the record already exists, and using Me.NewRecord would allow two alarms to be set at the same time if the manager reschedules the alarm

Any other ideas?

Thanks for your help

 

[Beetle]

Then add the PK field of the table to your where condition in the DCount using an inequality operator so you can check if the value already exists for a different record.

 

[boblarson]

Here's an alternative way:

Option Compare Database
[B][COLOR=red]Private blnFlag As Boolean[/COLOR][/B]
[B][COLOR=#ff0000][/COLOR][/B] 
Private Sub btnReschedule_Click()
    Call GetDate([Form]![txtCommentAlarm], 0)
End Sub
Private Sub btnResetComputerName_Click()
    
    If MsgBox("This will reset the computer name which will show the alarm to this Computer. Are you sure?", vbOKCancel, "Warning") = vbOK Then
            
[B][COLOR=red]        blnFlag = True[/COLOR][/B]
      Me![txtComputerName] = Environ("Computername")
[B][COLOR=red]      If Me.Dirty Then[/COLOR][/B]
[B][COLOR=red]         Me.Dirty = False[/COLOR][/B]
[B][COLOR=red]     End If[/COLOR][/B]
[B][COLOR=red]       blnFlag = False[/COLOR][/B]
    End If
    
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
    [B][COLOR=red]If blnFlag = False Then
[/COLOR][/B]       If DCount("*", "tblCustomerComments", "[CommentAlarm]= #" & Format(Me![txtCommentAlarm], "mm\/dd\/yyyy hh:nn") & "#") > 0 Then ' It is a duplicate
           Cancel = True
           MsgBox "Sorry an alarm has already been set at this Date/Time, please enter a different Date/Time"
           Me.Undo  '<----Remove this if you don't want to erase form input
       End If
    [COLOR=red][B]End If
[/B][/COLOR]End Sub

 

[Garindan]

Brilliant! Works perfectly Bob! Thankyou both for your help!