Code shouldn't allow record to be saved, but it is??

[roystreet]

Hello,
The below code I have in a module and is called by a form at Before Update. It checks the integrity of the data entered (or lack thereof) in a form. If they do not follow the guidlines and enter in the data incorrectly, it should make a msgbox with a warning message and stop the record from being saved....What is happening currently is that the message box pops up, but as soon as you click OK, it accepts the record and goes on to another one. So somewhere in here, it gives the message, but it's not stopping you from saving the data.

Thanks,
---roystreet

Code In The Module:

Public Function ExitIntegrityCheck(frm As Form) As Integer

'Error handler included below requires specific integrity requirements be met
'String can not be 0, Null, Alpha, nor less than 4 characters
'Special Note: This code will insert leading zeros for user so as long as they type in one numeric string you will
'Always have 4 characters
'
'This code also concatenates the values eliminating the need to have it in the BeforeUpdate on the Forms code

'If string follows integrity requirements then end code - Else Error Message
'If string is 0 then below error - Else second error

Dim finalvalue As String
If IsNumeric(frm!Unique_Number) Then 'false if null or alpha
If frm!Unique_Number > 0 And frm!Unique_Number < 10000 Then
frm!Unique_Number = Right("0000" & Trim(frm!Unique_Number), 4) 'trim added to remove spaces
Else
MsgBox " The Regional Number Can NOT END With a 0 Value ", vbExclamation, ":::: Regional Number Not Complete :::: (Error: 150)"
Cancel = True
End If

'If string is alpha or does not have four numeric characters or is Null then below error
Else
MsgBox "The Regional Number MUST END With Four Numeric Characters and Can Not Be Null (Blank)", vbExclamation, ":::: Regional Number Not Complete :::: (Error: 125)"
Cancel = True
End If
'Error handler included below requiring an alpha-numeric string be inserted
'This will not allow the case to be updated (inserted as a new record in table) until all integrity rules are met

'If string is Null then error message - Else accept

Dim Msg As String, Stlye As Integer, Title As String

If IsNull(frm!Employee_Name) Then
Msg = "You MUST Enter In The Employees Name - " & DL & _
"This field is Required"
Style = vbExclamation + vbOKOnly
Title = ":::: Employee Name Not Complete :::: (Error: 175)"
MsgBox Msg, Style, Title
Cancel = True

End If

End Function

 

[ChrisO]

G’day roystreet

I think the most important thing here is Option Explicit and compile your code.

Option Explicit

Cancel = True
(Cancel not defined)

DL
(not defined)

Function ExitIntegrityCheck
(not assigned a value, compiler will not pick that up so I threw it in for good measure.)

Without testing…
Scrap Cancel = True and replace with ExitIntegrityCheck = True

Haven’t got a clue what DL is.

frm!Unique_Number could refer to a Field or Control on the Form and probably has a space in it.

There is no Error handler because there is no On Error Goto statement.

Try to use the Dim statement only at the top of the procedure not throughout the procedure.

Apart for that I can’t test it.

Hope those points (well OK criticisms) are taken as intended and also help in the future.

Regards,
Chris.

 

[ecniv]

In addition, this function should return the number back to the calling code which should set the cancel long value then. Possibly it isn't returning the code which cancels, thus your record is saved when it shouldn't be.

Which I think was pointed out in the previous post by Chris


Vince

 

[roystreet]

A little bit further....

Not sure what you mean here:

frm!Unique_Number could refer to a Field or Control on the Form and probably has a space in it.

Because I am seeing the code as it runs, it just doesn't stop the record from being saved. I made some changes to removing the DL and changing the cancel string. I have another Database where this code is in the actual form and is ran on Before Update of the field. The beginning of the code is for a unique_number of which ultimately becomes a regional number. Which it will stop you from going to another field and also will not save the record until you meet the criteria. The second portion of the code is for employee_name. They work fine in the form. Please note that the code states Me! instead of frm. when I had it in the form. It appears I am going to have to change a little bit more of it since I am wanting to run it from a module instead of in each specific form.
Part of my desire is to use modules more and this is a great learning experience. When you say criticisms, that's OK. Although, I do not know half as much as you probably do concerning this subject, I gladly will take any instruction. Hopefully, it will help me and then in turn I will be able to help others.

Here's the code I've place in there now:

[COLOR=RoyalBlue]Public Function ExitIntegrityCheck(frm As Form) As Integer

   'Error handler included below requires specific integrity requirements be met
'String can not be 0, Null, Alpha, nor less than 4 characters
'Special Note: This code will insert leading zeros for user so as long as they type in one numeric string you will
'Always have 4 characters
'
'This code also concatenates the values eliminating the need to have it in the BeforeUpdate on the Forms code

'If string follows integrity requirements then end code - Else Error Message
'If string is 0 then below error - Else second error

 Dim finalvalue As String
If IsNumeric(frm!Unique_Number) Then 'false if null or alpha
    If frm!Unique_Number > 0 And frm!Unique_Number < 10000 Then
         frm!Unique_Number = Right("0000" & Trim(frm!Unique_Number), 4) 'trim added to remove spaces
    Else
MsgBox "             The Regional Number Can NOT END With a 0 Value                        ", vbExclamation, ":::: Regional Number Not Complete :::: (Error: 150)"
    ExitIntegrityCheck = True
    End If

'If string is alpha or does not have four numeric characters or is Null then below error
Else
MsgBox "The Regional Number MUST END With Four Numeric Characters and Can Not Be Null (Blank)", vbExclamation, ":::: Regional Number Not Complete ::::  (Error: 125)"
     ExitIntegrityCheck = True
End If
'Error handler included below requiring an alpha-numeric string be inserted
'This will not allow the case to be updated (inserted as a new record in table) until all integrity rules are met

'If string is Null then error message - Else accept

   Dim Msg As String, Stlye As Integer, Title As String
   
   If IsNull(frm!Employee_Name) Then
      Msg = "You MUST Enter In The Employees Name  - This field is Required"
      Style = vbExclamation + vbOKOnly
      Title = ":::: Employee Name Not Complete ::::  (Error: 175)"
      MsgBox Msg, Style, Title
      ExitIntegrityCheck = True
    
   End If
 
End Function
[/COLOR]

Now, what is happening is that if I don't enter in a number, but I enter in a name - It will produce the popup I want and will not save the record. But if I enter in a number, but not a name and/or enter in the number incorrectly, ie using alpha or zero - It will give me the popup, but will still save the record. I'm still working, thanks for the help so far.

---roystreet

 

[ChrisO]

G’day roystreet

frm!Unique_Number could be a Field in the Table or a Control on the Form.
It makes a difference sometimes and is best to apply a naming convention to the Control.

I prefer to use the ! (bang) for Fields and . (dot) for Controls so: -
frm!Unique_Number for the Field and frm.txtUnique_Number for the Control that contains the Field.

Unique_Number may have a space…

If a Field in a Table has a name of Unique Number then the Access wizard will, bless its little heart, create a Control by the same name including the space. When an event handler is created for the Control Access will place an underscore in place of the space in the event handler’s name.

This confuses my (singular) brain cell so I always remove spaces from names.
Simply not worth the effort so please don’t use spaces if you did and it is hard to say from this end.

Always use Option Explicit in Modules.
Dim Msg As String, Stlye As Integer, Title As String
Stlye is misspelled.

Dim finalvalue As String is not used, this causes no problem but is untidy.

Anyhow, there’s a little A97 demo attached that seems to do the job.

If not can you modify it for your requirements and post it back in A97 please.

Regards,
Chris.

 

[Pat Hartman]

Your Cancel = True is not cancelling the update - but you already know that. Change your function so that it returns one value if an error is found and a different value if no errors were found. Then use that returned value to determine whether or not to cancel the form's update event.

In the form's BeforeUpdate event:

If YourFunc = "error" Then
    Cancel = True
End If

 

[ChrisO]

Pat.

In the demo I attached, which at time of writing does not appear to have been downloaded, this is the code for the Form: -

Option Explicit
Option Compare Text


Private Sub Form_BeforeUpdate(Cancel As Integer)

    Cancel = ExitIntegrityCheck(Me)

End Sub

The Public Function is: -

Option Explicit
Option Compare Text


Public Function ExitIntegrityCheck(frm As Form) As Boolean
    Dim Msg   As String
    Dim Style As Integer
    Dim Title As String
    
    [color=green]'   Error handler included below requires specific integrity requirements be met
    '   String can not be 0, Null, Alpha, nor less than 4 characters
    '   Special Note: This code will insert leading zeros for user so as long as they type in one
    '   numeric string you will Always have 4 characters
    
    '   This code also concatenates the values eliminating the need to have it in the BeforeUpdate on the Forms code
    
    '   If string follows integrity requirements then end code - Else Error Message
    '   If string is 0 then below error - Else second error[/color]
    If IsNumeric(frm.txtUniqueNumber) Then 'false if null or alpha
        If Val(frm.txtUniqueNumber) > 0 And Val(frm.txtUniqueNumber) < 10000 Then
            frm.txtUniqueNumber = Format(frm.txtUniqueNumber, "0000")
        Else
            MsgBox "             The Regional Number MUST BE between 1 and 9999                      ", _
                   vbExclamation, ":::: Regional Number Not Complete :::: (Error: 150)"
            ExitIntegrityCheck = True
        End If
    [color=green]'   If string is alpha or is Null then below error[/color]
    Else
        MsgBox "The Regional Number MUST ONLY contain Numeric Characters and Can Not Be Null (Blank)", _
               vbExclamation, ":::: Regional Number Not Complete ::::  (Error: 125)"
        ExitIntegrityCheck = True
    End If
    
    [color=green]'   Error handler included below requiring an alpha-numeric string be inserted
    '   This will not allow the case to be updated (inserted as a new record in table) until all integrity rules are met
    '   If string is Null then error message - Else accept[/color]
    If Nz(frm.txtEmployeeName, "") = "" Then
        Msg = "You MUST Enter In The Employees Name  - This field is Required"
        Style = vbExclamation + vbOKOnly
        Title = ":::: Employee Name Not Complete ::::  (Error: 175)"
        MsgBox Msg, Style, Title
        ExitIntegrityCheck = True
    End If
 
End Function

There may be problems at this stage but I can’t spot one.

I’ll wait and see if the OP finds one, unless you can spot one in the meantime, in which case I will fix it and be most appreciative.

Regards,
Chris.

 

[Pat Hartman]

That should be fine. As you noticed I didn't download the db. I don't have time to download every db and examine it and since I'm not using A97, I need to convert them in order to do anything with them so I simply don't bother.

 

[ChrisO]

Thanks Pat for the educated eye.

Conversion is a problem and that’s why I stick to A97 and try to remember to include that in the download name.

Can cause problems, going back to A97, but not as much as going up from A97.

Thanks again…

Regards,
Chris.