Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2013
    Posts
    5

    Unanswered: need to generate mssg when incomplete information is entered in form

    I am using Access 97. I have a form that is used to enter data in a sort of recipe that calculates the amounts of ingredients needed to produce a metallurgical product.

    I have 3 sets of 2 fields that are dependent on each other. If data is entered in the first field of the set but the second field is omitted it creates a problem in the final calculation. I need to generate an error message if this occurs. Here are examples of the fields.

    Field 1 Field2
    [rework] [rework %Co]
    [reclaim] [reclaim %Co]
    [wet rework] [wet rework %Co]

    If the user enters data in field 1 and they neglect to enter data in field 2 then I need an error message that says something like "you entered data for rework but you forgot to enter data for rework %Co. You need to complete this field"

    Any help on this problem is appreciated.

    Thanks in advance.

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    If IsNull([FieldOne]) Then
    MsgBox "Please Fill in Required Data"
    [FieldOne].SetFocus
    ElseIf IsNull([FieldTwo]) Then
    MsgBox "Please Fill in Required Data"
    [FieldTwo].SetFocus
    End If


    HTH

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in the forms before update event place somed code that does your validations
    if its a single control you want to validate then the controls before update is the right place, but for composite / multi control validations then use the forms event.


    if the validation fails set the cancel variable = true.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    To expand on the Jaded One's comment a little bit:

    As a rule, Validation for data that has actually been entered in a given Control, such as a Textbox, is done in the Control's BeforeUpdate event. This would include such things as making sure Numeric data was entered, if appropriate, rather than Text, or that the data had to contain a certain number of characters, and so forth. If the data fails the Validation, you simply set Cancel = True, which makes the Focus stay on the errant Control until appropriate data is entered.

    Validation that involves multiple Controls, such as is the case, here, has to go in the Form_BeforeUpdate event. This includes things such as insuring that a EndDate is later than a StartDate, or verifying that if ControlA is populated, ControlB must also have data; sound familiar?

    Validation that one or more Controls actually contain data also has to be done in the Form_BeforeUpdate event. Why? Because Validating that a Control actually has data, using one of that Control's events, is useless; all the user has to do is skip the Control entirely, and none of its events will fire!

    To cancel an update, if the Validation fails:

    1. Pop a Messagebox to inform the user of the problem
    2. Use healdem's suggestion of Cancel = True, to actually halt the update
    3. Send Focus back to the errant Control
    4. And if you're doing more than one Validation, follow this up with Exit Sub.
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Sep 2013
    Posts
    5
    Thank you for your replies. If I am understanding this correctly then I should use burrinas code in the Form_BeforeUpdate code section.

    I have 3 of these dependent pair controls that have to be considered. I think I need to write code for each pair.

    I will try try the code and see what happens.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Carboman View Post

    ...If I am understanding this correctly then I should use burrinas code in the Form_BeforeUpdate code section...
    The code has to be in the Form_BeforeUpdate event, that much is true, but Burrina's code is not complete! As I said before, you need to run your Validation check, which is not simply

    If Isnull(ControlY) Then

    but rather

    If Not Isnull(ControlX) And Isnull(ControlY) Then

    Then, if the Validation fails
    1. Pop a Messagebox to inform the user of the problem
    2. Use Cancel = True, to actually stop the update
    3. Send Focus back to the errant Control
    4. And since you're doing more than one Validation, use the command Exit Sub.

    Quote Originally Posted by Carboman View Post

    ...I have 3 of these dependent pair controls

    ...I think I need to write code for each pair...
    That is correct, you need a separate Validation for each of your pairs.

    Get this up, and running correctly, and then someone will explain why there are better Validation tests, for 'empty' Controls, than using the IsNull Function.

    Give it a try, using these guidelines, and get back to us if you have problems.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Sep 2013
    Posts
    5

    Syntax Error

    Below is the code I have entered. I am trying it for one of the pairs. If it works I can transpose the code tho fit the other pairs.

    I am getting a "compile erroryntax Error".

    When the error occurs the line "Elself IsNull([Rework %Co]) Then" is highlited in red.

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Not IsNull([Rework]) And IsNull([Rework %Co]) Then
    MsgBox "You need to enter a Co% for the Rework"
    Cancel = True
    [Rework].SetFocus
    Elself IsNull([Rework %Co]) Then
    MsgBox "You need to enter a Co% for the Rework"
    [Rework %Co].SetFocus
    Exit Sub
    End Sub

    Thanks

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    There are better ways to check for empty Controls than IsNull, but that aside, for the moment, and assuming that your names are spelled correctly:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If Not IsNull(Me.Rework) And IsNull(Me.[Rework %Co]) Then
     MsgBox "You need to enter a Co% for the Rework"
     Cancel = True
     [Rework %Co].SetFocus
     Exit Sub
    End Sub


    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    At first glance the problem is probably not having an endif to terminate the if statement.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2013
    Posts
    5
    I have put the following code and the message does not appear when the Rework% is not entered.

    I have the default values for both of these controls as 0, not IsNull. Could that be causing a problem?

    Would I just be able to change the IsNull to 0? I tried this and I got a syntax error. I also tried =0 and got a syntax error.

    If Not IsNull(Me.[Rework]) And IsNull(Me.[Rework %Co]) Then
    MsgBox "You need to enter a Co% for the Rework"
    Cancel = True
    [Rework %Co].SetFocus
    End If
    Exit 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
  •