Results 1 to 13 of 13
  1. #1
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170

    ErrorHandler

    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!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    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)...

    Code:
        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

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Code:
    "Sorry, " & Me![RecordNumber] & " was already requested by " & Me![Operator] & " on " & Me![TimeAdded] & ", would you like to Request anyway?"

  5. #5
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    Quote Originally Posted by ItsMe View Post
    Code:
    "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?

  6. #6
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    Nevermind.

    Code:
    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.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    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.

  8. #8
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    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.

    Code:
    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

  9. #9
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    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

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    Quote Originally Posted by June7 View Post
    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"

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

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    athyeh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jun 2013
    Posts
    170
    I bow to you. Thread Solved.

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums