Results 1 to 9 of 9
  1. #1
    RachelBedi is offline Advanced Beginner
    Windows Vista Access 2003
    Join Date
    Jul 2012
    Posts
    67

    Suppressed run time error 2501

    I have a form that is set to display a message box for each field that contains a null value when left blank. On my save button I added code to suppress the ever annoying 2501 run time error....unfortunately even if it runs into an error is still displays my "You saved me!" message - which obviously it shouldn't as it won't let you save it until you enter additional information. Any suggestions on how to get the message box of "You saved me!" to only display if there is NO error? here is my code;



    Private Sub Command37_Click()


    On Error GoTo ErrHand


    DoCmd.RunCommand acCmdSaveRecord
    MsgBox "You Saved Me!"
    EndIt:
    Exit Sub


    ErrHand:
    Select Case Err.Number
    Case 2501
    Resume Next
    Case Else
    MsgBox Err.Number
    Resume EndIt
    End Select
    End Sub

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    When you issue the RESUME NEXT command your error trapping loop throws your code back to the line directly after the line that failed you thus you're getting the 'you saved me' message.

    The error number is not wiped out when you are sent back to your loop though so you could do something like:

    Code:
    err.number = 0
    DoCmd.RunCommand acCmdSaveRecord
    if err.number <> 2501 then
         MsgBox "You Saved Me!"
    endif
    EDIT: I don't know the entirety of your code so you may have to reset the error number to 0 right before the command you're running I'm not sure if the syntax I have is right but you get the idea.

  3. #3
    RachelBedi is offline Advanced Beginner
    Windows Vista Access 2003
    Join Date
    Jul 2012
    Posts
    67
    hmm that didn't seem to help :-( here's the code I have on the form as well; any other suggestions?



    Private Sub Form_BeforeUpdate(Cancel As Integer)


    If IsNull([Company Name]) Then
    MsgBox "Company name cannot be left blank!", vbexlamation
    Cancel = True


    End If


    If IsNull([Date of enrollment]) Then


    MsgBox "Date of Enrollment cannot be left blank!", vbexlamation
    Cancel = True




    End If


    If IsNull(Banker) Then
    MsgBox "Banker cannot be left blank!", vbexlamation
    Cancel = True


    End If


    If IsNull([bank modified date]) Then
    MsgBox "Bank Modified Date cannot be left blank!", vbexlamation
    Cancel = True


    End If


    If IsNull(Assigned) Then
    MsgBox "Assigned cannot be left blank!", vbexlamation
    Cancel = True


    End If


    If IsNull(Status) Then


    MsgBox "Status cannot be left blank!", vbexlamation
    Cancel = True


    End If


    If IsNull([Date Modified]) Then


    MsgBox "Date Modified cannot be left blank!", vbexlamation
    Cancel = True


    End If


    If (Status) = "submitted" And IsNull(Issue) Then


    MsgBox "Issue cannot be left blank!", vbexlamation
    Cancel = True


    End If




    End Sub

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Are you using a bound form? or an unbound form? A bound form is a form that is tied directly to a table or query (which I suspect is what you're using), an unbound form isn't.

    If you're using a bound form you don't need to issue the save command. Any record that passes through your BEFORE UPDATE event will already be saved. Issuing the save command is redundant.

  5. #5
    RachelBedi is offline Advanced Beginner
    Windows Vista Access 2003
    Join Date
    Jul 2012
    Posts
    67
    yeah I understand that - unfortunately it gives folks a warm and fuzzy feeling that their information has been saved - do you have another suggestion that would provide a message that the information has been saved?

    I'm a newbie to all of this!

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    on your save button have this code:

    Code:
    If IsNull([company name]) Or IsNull([date of enrollment]) Or IsNull([Banker]) Or IsNull([bank modified date]) Or IsNull([Assigned]) Or IsNull([Status]) Or IsNull([date modified]) Or ([Status] = "submitted" And IsNull([Issue])) Then
        MsgBox "Record Incomplete"
    Else
        MsgBox "Record Saved"
    End If
    it's strictly for show so you don't actually have to do anything other than generate a message

  7. #7
    RachelBedi is offline Advanced Beginner
    Windows Vista Access 2003
    Join Date
    Jul 2012
    Posts
    67
    Worked like a charm! THANK YOU! :-D

  8. #8
    RachelBedi is offline Advanced Beginner
    Windows Vista Access 2003
    Join Date
    Jul 2012
    Posts
    67
    So this issue just got more complicated - there will be certain instances where particular fields can be left blank, unfortunately also once you alter any data in a record it's not saving, don't know why but I guess I would prefer to add in the "saverecord" command.

    The fields for the form are;

    Paymode ID, Company Name, Date of Enrollment, Bank Name, Banker, Bank Mod Date, Bank Status, Paymode X Product, Bank Mismatch, Assigned, Status, Date Modified, Number of Contacts, Contact, Issue, and Comments

    If the Paymode X Product is "Risk" then Banker, Bank Mod Date and Bank Status cannot be left blank

    If the Paymode X Product is "UW" then Assigned, Status and Date Modified cannot be left blank

    If the Paymode X Product isnull then Banker, Bank Mod Date, Bank Status, Assigned, Status, Date Modified cannot be left blank.

    I'm having a really hard time getting this code to do what I want it to do, even if I mark an account as Risk and fill in all the info I should be filling in I'm getting the message box for the UW if/then statement. Here is my code;


    Private Sub Command37_Click()
    If IsNull([Paymode X Product]) And IsNull([company name]) Or IsNull([date of enrollment]) Or IsNull([Banker]) Or IsNull([Bank Modified Date]) Or IsNull([Assigned]) Or IsNull([Status]) Or IsNull([Date Modified]) Or ([Status] = "submitted" And IsNull([Issue])) Then
    MsgBox "Record Incomplete, please ensure to complete ALL required fields, required fields are marked with*. Please note Issue is only required when status of submitted is chosen", vbCritical
    DoCmd.RunCommand acCmdSaveRecord
    ElseIf ([Paymode X Product]) = "UW" And IsNull([Assigned]) Or IsNull([Status]) Or IsNull([Issue]) Or IsNull([Date Modified]) Then
    MsgBox "Record Incomplete, please ensure you have filled in Assigned, Issue and Date Modified"
    ElseIf ([Paymode X Product]) = "Risk" And IsNull([Banker]) Or IsNull([Bank Modified Date]) Or IsNull([Bank Status]) Then
    MsgBox "Record Incomplete, please ensure you have filled in Banker, Bank Modified Date and Bank Status before saving"
    DoCmd.RunCommand acCmdSaveRecord
    MsgBox "Record Saved", vbInformation
    End If
    End Sub

    I'm sure it's something either silly or I'm going about it totally the wrong way. Thank you in advance for your help!

  9. #9
    RachelBedi is offline Advanced Beginner
    Windows Vista Access 2003
    Join Date
    Jul 2012
    Posts
    67
    I think I figured it out - of course as soon as I hit the button it clicks in my head - stay tuned

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

Similar Threads

  1. Replies: 15
    Last Post: 07-26-2012, 02:06 PM
  2. Replies: 7
    Last Post: 07-18-2012, 07:53 AM
  3. Replies: 3
    Last Post: 05-06-2012, 06:29 PM
  4. Error 2501 displays with Error handling
    By agent- in forum Programming
    Replies: 13
    Last Post: 08-05-2011, 02:20 PM
  5. Replies: 9
    Last Post: 04-04-2011, 11:13 AM

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