calling global functions vs. writing repetition behind forms (1 Viewer)

[neuroman9999]

can any of you experts tell me if makes any sense whatsoever? I have a scenario where security paranoia is running rampant in a large American bank, who I am working for. I know what to do, obviously, but there are endless ways to tackle stuff like this. Here is what I am currently doing:

when a new main form record is entered, it has to be assigned a status, however, that status cannot be assigned and put into the binding until a sign off form is filled out and a UN and hashed PW is checked. so, here's a sample of code, that is not that efficient, behind the main form:

Private Sub btnSaveRec_Click()
If Me.NewRecord Then
    If Me.Dirty Then
        If validate_Controls_Private("status", "statusnotification") Then
            Me.btnApprove.Enabled = True
            Me.Status = "entered"
            Me.statusnotification = "Entered, Awaiting Approval"
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.OpenForm "frmSignOff"
        Else
            MsgBox "All fields must be filled in for new records.", vbCritical, "Error"
        End If
    Else
        MsgBox "No data has been entered for this record.", vbCritical, "Error"
    End If
Else 'record NOT NEW
    If Me.Dirty Then
        If Me.Status = "approved" Then 'record already locked
            MsgBox "This record cannot be modified or saved because " & vbCrLf & _
                   "it has already been approved and signed.  It is " & vbCrLf & _
                   "locked permanently.", vbCritical, "Error"
            Me.Undo
        Else
            DoCmd.RunCommand (acCmdSaveRecord)
            MsgBox "Record has been saved.", vbInformation, "Confirm"
        End If
    Else
        MsgBox "No changes have been made to the record." & vbCrLf & _
               "Record cannot be saved.", vbCritical, "Error"
    End If
End If
End Sub

and even though that code is a tad bit wrong, there are other parts of the app that make perfect sense. for instance, in another area, there is this code:

Private Sub btnClose_Click()
If Me.NewRecord Then 'IS new record.
    If Me.Dirty Then 'new record entry has been started.
        If validate_Controls_Private("status", "statusnotification") Then 'if all fields filled in.
            If MsgBox("You have not saved the current record." & vbCrLf & _
                      "Would you like to do so before closing the form?", vbYesNo + vbExclamation, "Confirm") = vbYes Then
                MsgBox "Saving new records requires you to sign off on the process." & vbCrLf & _
                       "You will now be redirected to the Sign Off form....", vbExclamation, "Alert"
                DoCmd.OpenForm "frmSignOff"
            Else
                Me.Undo
                DoCmd.Close acForm, Me.Name
            End If
        Else 'some fields are NOT filled in, ask if user wants to abondon efforts.
            If MsgBox("All fields must be filled in for new records." & vbCrLf & _
                      "Would you like to cancel this record entry and close the form?", vbYesNo + vbExclamation, "Confirm") = vbYes Then
                Me.Undo
                DoCmd.Close acForm, Me.Name
            End If
        End If
    Else
        DoCmd.Close acForm, Me.Name
    End If
Else 'NOT a new record.
    If Me.Dirty Then 'OLD records can ONLY be edited if ''status'' = ''entered''.  record HAS been edited.
        If MsgBox("You have made changes to the current record." & vbCrLf & _
                  "Would you like to save your changes before closing the form?", vbYesNo + vbExclamation, "Confirm") = vbYes Then
            DoCmd.RunCommand acCmdSaveRecord
            MsgBox "Record has been saved.", vbInformation, "Confirm"
            DoCmd.Close acForm, Me.Name
        Else
            Me.Undo
            DoCmd.Close acForm, Me.Name
        End If
    Else 'record NOT edited.
        DoCmd.Close acForm, Me.Name
    End If
End If
End Sub

and the called function in the form's module is:

Private Function validate_Controls_Private(exclude As String, exclude2 As String) As Boolean
For Each c In Me.controls
    If TypeOf c Is TextBox And (c.Name <> exclude And c.Name <> exclude2) Then
        If (c = "" Or IsNull(c)) Then
            validate_Controls_Private = False
            Exit Function
        End If
    End If
 Next c
    validate_Controls_Private = True
End Function

can anyone see how this can be reduced? it's not a big deal, but none-the-less, there is still more code written than need be, obviously. thanks.

 

[Pat Hartman]

Validation should always be triggered from the Form's BeforeUpdate event, NOT a button click. You want the validation code to run regardless of what prompted the record to be saved. Also, calling it only for new records means that once a record has been saved once, you code allows required fields to be reverted to null.

 

[neuroman9999]

Also, calling it only for new records means that once a record has been saved once, you code allows required fields to be reverted to null.

that has been covered elsewhere in the code. it's not perfect code Pat, and that's because I'm not an access pro. but none-the-less, it does the job.

I will use your wisdom next time. the code is too long to go back now. and I was unaware of the ""official"" way to validate anything.

 

[Pat Hartman]

It doesn't look covered to me.

Access forms have event procedures that are intended to handle certain types of processing. The Form's BeforeUpdate event is like a flap on a funnel. If the flap is open, the record gets saved. If the flap is closed, it doesn't. It's that simple. You use the Cancel argument of the event to close the funnel.

If you don't use the event intended for a particular purpose, your code won't necessarily fail but there will be gaps. You'll need to put additional code in various other form events to make sure a form cannot close for example unless you allow it to. You might be able to get enough code in enough events but if you had put the code in the correct event, it would all be handled in a single event and nothing would get past it because if the record is dirty, it ALWAYS passes through the BeforeUpdate event REGARDLESS of what prompted the save and this event can NEVER be bypassed. It is the last event to run before the record is saved. You can certainly put the validation in one or more procedures but they should be invoked from the Form's BeforeUpdate event to be effective.

If you think that form and control events are random, then ignore them but believe me when I tell you that the developers of Access had a specific intention for what type of code would go into each and every event. There is a little overlap because control's have events as well as the Form. In some cases, you might elect to put validation code into control events. For example, if you had a complex pattern that you could not make an input mask to satisfy, you might use the Change event which runs once for each character that is typed into a control. If you want to stop data entry early if a particular field is entered, you might use the BeforeUpdate event for that control but in most cases, you would still need to duplicate this code in the Form's BeforeUpdate event because if focus never enters a control and the user doesn't type anything, it isn't dirtied and so its specific BeforeUpdate event will not run. Another mistake is to put validation code in the on Lost Focus or Exit events. That's fine but you will be validating data that wasn't necessarily changed. You'll probably get away with it if you notice that the Lost Focus event doesn't have a Cancel argument so you will need other measures to keep focus in the control if that is your intent and that still won't stop the record from being saved. Cancel in the Exit event of a control doesn't stop the value from being saved. It just stops the cursor from leaving the control.

The most important thing about becoming proficient in Access is an understanding of how to correctly use various Form and Control level events to control the validation of data and ensuring that your forms only allow valid data to be saved..

So, yes, there really is an "official" way to do things in Access.

 

[neuroman9999]

Holy cow bad that's a lot to take in period let me read it later and get back to you if I got any questions about it thanks

 

[neuroman9999]

The most important thing about becoming proficient in Access is an understanding of how to correctly use various Form and Control level events to control the validation of data and ensuring that your forms only allow valid data to be saved..

So, yes, there really is an "official" way to do things in Access.

but business rarely goes that way, because so many people are literally stupid and understand nothing. and I do not have the time to spend learning everything about this program because I write code in a lot of languages and offer guidance to many others. and everyone uses different tools.

by the way, speaking of different tools and using Satan against himself, did you know that right now you can take out literally and endless of amount of credit cards and get cash out of each one of them if they have the visa/mastercard/any bank name logo on them? I have done this, and have gotten 4 cards in the last 3 weeks strictly for the purpose of purchasing products at various places around my area of the world and paying off each card to 0 at the end of each month. I am doing it for 2 reasons:

=> 1) to trick corporate software into believing that I'm rich and can manage money on many different lines.
=> 2) giving myself a huge amount of financial backup reserves just in case the idiots that run the world can't get a handle on themselves, the virus carries on forever and my business takes a hit (although I have plenty of connections with DHS to get food for free anyway), or a civil war starts because people can't seem to let go of the George Floyd incident.

 

[Pat Hartman]

and I do not have the time to spend learning everything about this program because I write code in a lot of languages and offer guidance to many others. and everyone uses different tools.

I am not going to say what I think about this statement. We'll just keep cleaning up the messes you make with your bad advice.

 

[neuroman9999]

I am not going to say what I think about this statement. We'll just keep cleaning up the messes you make with your bad advice.

likewise. i won't even consider you either. I do a lot of good, and you will never see it. if you don't like it, quit engaging. it's that simple. DUH