Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validating Input (Access 97 /SR2)

    I have a form which has a number of fields, all of which need to be filled in.
    I have set the required option on these fields to yes - but this just returns the standard Access error description. I need to write validation into the form but do not know where to put it. I tried writing it in the BEFORE UPDATE section and including a save record routine in the function but that didn't work (so if the fields were okay it'd save, but if they weren't it'd exit the sub routine).

    I also need to write a routine which writes a combination of data into a field to make an identifier, and i'm not sure of the best place to write this either - AFTER INSERT seems to work fine, would there be any likely problems using this method?

    Any help on either of these problems would be much appreciated - specially the save one as I am completely stuck.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating Input (Access 97 /SR2)

    The before update of the form is the right place to do your test.
    Then If the test fail, open a message box with what's going wrong and set the variable Cancel = True
    This will return the user to the unsaved form.
    You can also set the focus to the wrong field before the Cancel = True
    Somethung like this:
    <pre>Private Sub Form_BeforeUpdate(Cancel as Integer)
    If IsNull(Me!textBox1) Then
    MsgBox "Enter something in textBox1"
    Me!textbox1.SetFocus
    Cancel = True
    End If
    End Sub</pre>

    For the Identifier, if the field is in the recordset of the form, put it on the form (maybe Visible Property = NO), and update the field in the Before Update event. If you have to open another recordset for it, do it in the After Insert event.
    Francois

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Validating Input (Access 97 /SR2)

    AFTER INSERT is probably the wrong place to create that identifier. This takes place AFTER a new record has been written to the table, so you are writing the record without the identifier then going back and updating the record with the identifier (not very efficient).

    Put your code in the form's BeforeUpdate event.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Lounger
    Join Date
    Apr 2002
    Location
    Bloomsburg, Pennsylvania
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating Input (Access 97 /SR2)

    I found this post which was exactly what I needed. But...

    After my message comes up about the field being required, and I try to send them back to the form, I get this window that say "The DoMenuItem action was canceled." Is there any way to avoid getting this window? I'd just like them to click ok. on my message window and take them back to the required field on the form. I tried putting in a docmd.setwarnings (messageoff) in the vba code, turning it back on after the cancel, but that didn't work.

    I've attached a copy of the message.

    Thanks.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validating Input (Access 97 /SR2)

    Can you post the code you are using?

  6. #6
    Lounger
    Join Date
    Apr 2002
    Location
    Bloomsburg, Pennsylvania
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating Input (Access 97 /SR2)

    Yes, thank you.

    If IsNull(Me!SR_SECTION_NUM) Or Me!SR_SECTION_NUM = 0 Then
    Call MsgBox("Section Number is required", vbInformation, "Master Schedule")
    Me!SR_SECTION_NUM.SetFocus
    Cancel = True
    DoCmd.SetWarnings (warningson)
    Exit Sub
    End If

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validating Input (Access 97 /SR2)

    Please post the entire procedure, from Sub ... to End Sub.

  8. #8
    Lounger
    Join Date
    Apr 2002
    Location
    Bloomsburg, Pennsylvania
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating Input (Access 97 /SR2)

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me!Checkchange = True
    Me!txtuserid = Forms!frmdept!Text11
    Me!txtuseriddate = Now()
    If Me!holdadd = True And Me!Check83 = False Then
    Me!Check83 = True
    End If
    DoCmd.SetWarnings (warningsoff)
    If IsNull(Me!Combo88) Or Me!Combo88 = " " Then
    Call MsgBox("Course Number is required", vbInformation, "Master Schedule")
    Me!Combo88.SetFocus
    Cancel = True
    DoCmd.SetWarnings (warningson)
    Exit Sub
    End If
    If IsNull(Me!SR_SECTION_NUM) Or Me!SR_SECTION_NUM = 0 Then
    Call MsgBox("Section Number is required", vbInformation, "Master Schedule")
    Me!SR_SECTION_NUM.SetFocus
    Cancel = True
    DoCmd.SetWarnings (warningson)
    Exit Sub
    End If

    Me!SEQ = Nz(DMax("[seq]", "extractdata", "[sr_course_code] = '" & Me.Combo88 & "' and sr_section_num = '" & Me!SR_SECTION_NUM & "'"), 0)
    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validating Input (Access 97 /SR2)

    Try moving the two sections with If ... End If up to the beginning of the procedure. There's no sense in setting things if you're going to cancel anyway.

    Unless you have defined constants or variables named warningsoff and warningson, the DoCmd.SetWarnings instructions will not have the effect you intend. To turn off warnings, use

    DoCmd.SetWarnings False

    and to turn them on again, use

    DoCmd.SetWarnings True

    You should not need these lines here.

  10. #10
    Lounger
    Join Date
    Apr 2002
    Location
    Bloomsburg, Pennsylvania
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating Input (Access 97 /SR2)

    I moved it around and even tried setting the warnings off inside the if, but I still got the "domenuitem" message after my message box. I started wondering if it had something to do with the docmd in the sub for the save button on the form. When I looked at that code the error handling had a msg box. When I commented that out it worked fine.

    Private Sub Command80_Click()
    On Error GoTo Err_Command80_Click

    DoCmd.SetWarnings False
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.SetWarnings True

    Exit_Command80_Click:
    Exit Sub

    Err_Command80_Click:
    'MsgBox Err.Description
    Resume Exit_Command80_Click

    End Sub

    Thanks for your help!! I'm the only one here using Access and without this bulletin board I would have no resources to go to. Thanks for responding so fast.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validating Input (Access 97 /SR2)

    Try this:

    Private Sub Command80_Click()
    On Error GoTo Err_Command80_Click
    RunCommand acCmdSaveRecord
    Exit Sub

    Err_Command80_Click:
    If Err = 2501 Then
    ' canceled - ignore
    Else
    MsgBox Err.Description
    Else If
    End Sub

    If an action is canceled, it usually causes error number 2501. I just made the error handler ignore that error.

  12. #12
    Lounger
    Join Date
    Apr 2002
    Location
    Bloomsburg, Pennsylvania
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating Input (Access 97 /SR2)

    I changed the code in this sub as you suggested and it works great. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •