Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009

    Unanswered: Prevent going to next record on failed data validation

    MSA 2003

    I now know why I never became a VB programmer. Perl, Java, but anything
    but VB I can do..

    I have spent last 4 hours and searching online but no resolution. I assume the answer has been answered a billion times but I cannot get the right keyword combination. I have tried err., error, return codes, everything..

    -- I have one form and it has two fields.
    Both fields come from the same tables.

    Field1 is a Combo box with values A, B, C
    Field2 is a text box that should either be null or have a number.

    -- What I want to do is the following......

    If a user select 'C' from the combo box they also must enter a number in the text box. If they choose A or B the box should always be null.

    -- My problem is this.

    I have the code which I have tried entering in a million event types, but finally decided to put it into the FORMS "Before Update" event. The event triggers and my MsgBox pops up stating to fill in the text box.

    However I cannot prevent the form from advancing to the next record. I need the user to stay on the same record and fill out the box.

    What do I have to do?? Help..


  2. #2
    Join Date
    Dec 2004
    Madison, WI
    You can also just put code in the combobox and textbox themselves.

    In the AfterUpdate event of the combobox, you could add code like this:

    if me!MycomoboboxName = "C" then
    if not isnull(me!MyTextField) then msgbox "Text field cannot be blank!"
    me!MyTextField = null
    end if

    You may also want to put the same code in the Afterupdate Event of the textbox.

    If you wanted to prevent going to a new record, what I sometimes do is put a Checkbox field on the form (unbound to any field) with a Default = True (I'll call it something like: NoNewRec). (or you can do it the opposite by setting the Default value to False and call it RecOk.)

    Then I'll set the checkbox to true or false depending on what value(s) may/may not be entered. When the user clicks the "Add New Record" button I created, I'll then test to see if this checkbox value is true or not to allow them to add a new record.

    Or I'll just add in all my field testing logic into the "Add New Record" button to allow them to add a new record or not.

    You can also add in this logic (if you're using the navigation bar to go to a new record) but I can't recall offhand how I did this.
    Last edited by pkstormy; 04-01-09 at 18:48.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2009
    Hi Pkstormy;

    So i think you are saying I have two choices.

    (1) Remove the Forms "navigation bars" under properties and build my own
    navigation using buttons.

    (2) figure out how to call events or manage control of the built in forms "navigation bars".

    Is there a third choice?? For instance, If my event calls a subroutine; Can I
    just return an error code that does not complete the event? In other words how to return to MSA that the subroutine failed and it should have the user try again? Of course having the debugger open is not favorable.

    Attached Thumbnails Attached Thumbnails untitled.bmp  

  4. #4
    Join Date
    Mar 2009
    I think I found the solution. This seems to do what I want.

    The crucial piece is the "Cancel = True".

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    On Error GoTo Err_Form_BeforeUpdate

    If (Combo282 = "IG") Then
    If IsNull(Text290) Then
    MsgBox "Please enter the primary database key of the file that supports this file."
    Cancel = True
    End If
    End If

    Exit Sub

    MsgBox Err.Description
    Resume Exit_Form_BeforeUpdate

    End Sub

Posting Permissions

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