ErrorHandler

[athyeh]

Hi Folks,

Trying to have a custom error message that pops up when a duplicate record is found.

Relevant Fields:
[TimeAdded]
[RecordNumber]
[Operator]

Desired Results:


When user triers to enter a duplicate record number:
"Sorry, [RecordNumber] was already requested by [Operator] on [TimeAdded], would you like to Request anyway?"
If Yes, Save Record
If No, Do Not Save Record -> Clear Form

Thanks!

[June7]

Can use DLookup to search table, something like:

If Not IsNull(DLookup("fieldname", "tablename", "RecordNumber=" & Me.textboxname) Then
If MsgBox("RecordNumber already used. Do you want to request anyway?", vbYesNo) = vbYes Then
'save record
Else
'do not save record
End If
End If

[athyeh]

Thank you!

Is there anyway to incorporate the original data of the [TimeAdded], [RecordNumber], [Operator] fields into the Message?

I.E.

"Sorry, [RecordNumber] was already requested by [Operator] on [TimeAdded], would you like to Request anyway?"

I have this much: but it is returning the values on the form not the values from the original record (which is desired result)...

    If Not IsNull(DLookup("Note_ID", "tblMainDB", "Note_ID=" & Me.Note_ID)) Then
        If MsgBox("" & Note_ID & " has already been requested by " & Requestor & " on, " & Time_Req & ",", vbYesNo) = vbYes Then
            RunCommand acCmdSaveRecord
            Me.Requery

[ItsMe]

"Sorry, " & Me![RecordNumber] & " was already requested by " & Me![Operator] & " on " & Me![TimeAdded] & ", would you like to Request anyway?"

[athyeh]

"Sorry, " & Me![RecordNumber] & " was already requested by " & Me![Operator] & " on " & Me![TimeAdded] & ", would you like to Request anyway?"

Thanks but unless I'm wrong this is similar to what I posted, which returns values that are from the form, not from the original record. Would it be a DLookUp Again?

[athyeh]

Nevermind.

Private Sub Command34_Click()
    If Not IsNull(DLookup("Note_ID", "tblMainDB", "Note_ID=" & Me.Note_ID)) Then
        If MsgBox("" & Note_ID & " has already been requested by " & Requestor & " on, " & Time_Req & ", would you still like to request note?", vbYesNo) = vbYes Then
            RunCommand acCmdSaveRecord
            Me.Requery
        Else
    End If
    End If
End Sub

This totally worked.

Thank you both for your assists.

[ItsMe]

I missed that. Yes, DLookup is a trip to the data each time. Might be less expensive to use DAO. Then you will have the entire record in memory. Hold record in memory, validate data, create msg for user.

[athyeh]

Forgot to account for the "happy path" (i.e. no duplicate to start with) the code below produces desired results:
Thank you for your replies.

Private Sub Command34_Click()
        If IsNull(DLookup("Note_ID", "tblMainDB", "Note_ID=" & Me.Note_ID)) Then
        DoCmd.RunCommand acCmdSaveRecord
        Me.Requery
        Else
        If MsgBox("" & Note_ID & " has already been requested by " & Requestor & " on, " & Time_Req & ", request anyway?", vbYesNo) = vbYes Then
                RunCommand acCmdSaveRecord
                Me.Requery
            Else
        End If
        End If
End Sub

[athyeh]

How would I go about incorporating this same logic into a query?

Desired Results:

If Not IsNull(DLookup("Note_ID", "tblMainDB", "Note_ID=" & Me.Note_ID)) Then
UPDATE tblMainDB SET tblMainDB.RecByExc = Yes, tblMainDB.RecByExc_Time = Now(), tblMainDB.RecByExcOp = [Forms]![frmExc]![ExcOpRec]
WHERE (((tblMainDB.Note_ID)=[Forms]![frmExc]![CheckIn]));
Else
If MsgBox("" & Note_ID & " was never requested.", vbOk)
End If

[June7]

Try:

CurrentDb.Execute "UPDATE tblMainDB SET RecByExc = Yes, RecByExc_Time = Now(), RecByExcOp = " & [Forms]![frmExc]![ExcOpRec] & " WHERE Note_ID=" & [Forms]![frmExc]![CheckIn]

If RecByExcOp or Note_ID are text fields, will need apostrophe delimiters.

CurrentDb.Execute "UPDATE tblMainDB SET RecByExc = Yes, RecByExc_Time = Now(), RecByExcOp = '" & [Forms]![frmExc]![ExcOpRec] & "' WHERE Note_ID='" & [Forms]![frmExc]![CheckIn] & "'"

If the date value was user entry to a textbox, would need # delimiters.

[athyeh]

Try:

CurrentDb.Execute "UPDATE tblMainDB SET RecByExc = Yes, RecByExc_Time = Now(), RecByExcOp = " & [Forms]![frmExc]![ExcOpRec] & " WHERE Note_ID=" & [Forms]![frmExc]![CheckIn]

If RecByExcOp or Note_ID are text fields, will need apostrophe delimiters.

CurrentDb.Execute "UPDATE tblMainDB SET RecByExc = Yes, RecByExc_Time = Now(), RecByExcOp = '" & [Forms]![frmExc]![ExcOpRec] & "' WHERE Note_ID='" & [Forms]![frmExc]![CheckIn] & "'"

If the date value was user entry to a textbox, would need # delimiters.

Sorry what I meant to say was, how would I about incorporating Dlookup and Msgbox logic into the query to operate:

If the Dlookup cannot find the Note_ID in a previously saved record (i.e. it was never scanned in) then it returns a msg "Note was never scanned"

UPDATE tblMainDB SET tblMainDB.RecByExc = Yes, tblMainDB.RecByExc_Time = Now(), tblMainDB.RecByExcOp = [Forms]![frmExc]![ExcOpRec]
WHERE (((tblMainDB.Note_ID)=[Forms]![frmExc]![CheckIn]));

[June7]

DLookup and MsgBox are not incorporated into the query. They are used in VBA If Then Else structure, like you already have. If ID is not found then run the CurrentDb.Execute Else MsgBox.

[athyeh]

I bow to you. Thread Solved.