Solved Form-specific codes in reusable class modules

[ysdai]

Using the technique mentioned in this thread: Creating Pseudo Custom Controls with Class Modules, I have successfully created my own set of form-navigation buttons, along with [Save] and [Undo] buttons.

However, there is a problem with the [Save] action. Each form has a different save criteria, like checking if all the required fields have data, trap duplicates in certain fields, or some forms have images in preview mode (in Temp folder) before save, and finally copying the images into destination folders when [Save] is committed.

It would not be a problem if I just put a universal DoCmd.RunCommand AcCmdSaveRecord in the [OnClick] event in the class module, and then attach the form-specific save-action code to the form's [OnClick] event of the save button, so that the form will commit those actions before getting to the Save code in the class module when the user clicks [Save].

But I have save actions not only in the click event of the [Save] button. When the user navigates the form out of the current record, say go to next record or close the form, I check if the form is dirty, and if so, pop a msgbox asking if the user wants to save before proceeding. This code will then have to be applied to every navigation button. And here comes the problem. How do I apply form-specific save actions to every single save event that's raised at class level?

This is really difficult to explain. I hope I made myself clear.

 

[CJ_London]

The thing about class objects is they do 'one thing'. In your case you want it to do multiple 'things' which will vary from form to form.

Not tried it by calling from a class module but you could investigate using the callbyname function in the class module and in your form code, pass the name of the (public) function you want to call. I use it in main forms (which is a class object after all) to modify events in its subforms so a) all my code is in one place, b) subforms are reuseable in other forms without worrying what the parent is or whether there is even a parent and c) subforms can often be saved without a module which has a small benefit on performance.

I use class modules along the lines of your link but tend to only use them for those events which have parameters such as mouse events.

CallByName function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[ysdai]

CallByName function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

I've never used the CallByName function, or even heard of it, but it looks like something I can try when I go back to work on Monday.

The latest approach I tried was to declare the btnSave_Click of the form, which contains form-specific save actions, as public, and then in the class, whenever a save is required, I'd Call NavForm.btnSave_Click. This didn't work (some object not defined error), then I put all the save action codes into a separate public sub, say SaveRecord(), and then from the class I Call NavForm.SaveRecord. Same result. (NavForm is the variable in the class used in place of the actual form that the nav buttons will be attached to).

My logic was that I might be able to achieve this if I put form-specific codes in the forms themselves under one universal sub name, declare the subs as public, and then call that sub from the class, so that no matter which form this class module is working with, calling that SaveRecord sub will invoke save actions specific to that form. But so far, that didn't work out.

 

[Pat Hartman]

Having a class module do multiple things depending on the form, is a misuse of a class module. Either it does EXACTLY the same thing in EVEY case or do not create a class module. PERIOD. There is nothing inherently good about class modules. The point is reuse. You are NOT reusing code so you should not be building class modules. Each form is already a class and the things that are common are already addressed by methods and properties. Every form is different as to the data it contains and the validation rules.

You can accomplish reuse by making standard modules and passing in a form reference. That allows you to have both standard and custom code for each form event.

One common action is to ensure all controls that are required are actually populated. To solve this problem with common code, use the tag property of the controls. Tag the required ones so the code can find only them. Create a function in a standard module that takes the form as an argument. The function returns true or false. True if all required fields are populated and false if they aren't. So you loop though. When you find an empty control, set focus to it. Display a generic error message. set the return to False and exit. The Form's beforeUpdate event, which is where you call this function, checks the return value and if false, cancels the update and exits. If true, it continues on with the rest of the validation.

This allows the Form's BeforeUpdate event to have both common and custom code.

If CheckForEmpty(Me) = False Then
    Cancel = True
    Exit Sub
End If

'''' continue with custom validation

 

[MajP]

I would have to see how you designed this. Are you using the subform idea or just individual buttons? But you can have your custom class raise a custom event.
So if you built a Navigation control class, you can have it raise the custom Save event. You may or may not have any code. Then the Main form listens for that event and fires its save event.

 

[Pat Hartman]

When you are using bound forms, Access will attempt to save a record in a number of situations. The code to catch this needs to go in the Form's BeforeUpdate event. If you put code in other events, you will have lots of code in lots of events and still potentially be saving bad data. Think of the form's BeforeUpdate event as the flap at the bottom of a funnel. If the flap is open, the record gets saved. If it is closed, the record is NOT saved. Easy if you understand Form events.

 

[CJ_London]

I'd Call NavForm.btnSave_Click. This didn't work

call is not the same as callbyname

I would expect something like

callbyname "NavForm","btnSave_Click",vbMethod

although personally I would create a separate function called say btnSave and call that instead rather than using the click event.

other things to consider - if your Navform is a subform on another form you may need to trace down from the mainform name, I'm working from a different starting point so not an issue for me.

 

[ysdai]

Funny I never thought about the BeforeUpdate event, with forms or controls. In fact, I don't think I've ever used it. With controls I always use AfterUpdate for validation, and with forms the dirty bit. I will try this approach. And also CallByName, this one also looks promising.

MajP:
I would have posted my code in the opening post had I been with my work computer. From your thread about pseudo custom controls, I downloaded both yours and Arnel's versions, but then got mixed up and ended up with the latter as my master template, then modified it to suit my needs. And yes, I have also incorporated the subform requery routine into the class and it worked pretty well. I will post the code when I get back to work.

I know I'm kind of abusing my class module here. They are meant do the same thing in every case, but I'm new to class modules, and I wanted to see how far I can go with this. I didn't want to write separate codes for my forms if they basically perform the same functions but with a little twist. I still want to use these buttons as a class template if I can.

 

[ysdai]

although personally I would create a separate function called say btnSave and call that instead rather than using the click event.

I did that. I put all the save codes into separate public function called SaveRecord, so that hopefully I can not only call it from the Click event of the button but also from the class. Didn't work. This SaveRecord wasn't recognized as the form's member from class level. Hopefully CallByName will be my ticket out.

 

[sonic8]

I did that. I put all the save codes into separate public function called SaveRecord, so that hopefully I can not only call it from the Click event of the button but also from the class. Didn't work. This SaveRecord wasn't recognized as the form's member from class level. Hopefully CallByName will be my ticket out.

I would also have recommended this is approach.
What exactly seems to be the problem? Your SaveRecord function will not be offered by intellisense, but it should still work if you just type it.
CallByName should work, but it is one abstraction too far, and you should be fine without it.

A more OO design would be to design an Interface including the SaveRecord function, make all forms that are handled by your class implement that interface, and then pass the interface type to your class instead of the Form type.

 

[ysdai]

Okay, so here's my code.
First, variable declarations. I added the Undo and Save buttons.

Dim WithEvents mNavForm As Form
Dim WithEvents mBtnFirst As CommandButton
...
Dim WithEvents mBtnUndo As CommandButton
Dim WithEvents mBtnSave As CommandButton
Public SaveCancel As Boolean
Public formToUpdate As String

And then there's the standard Init routine, we can skip that. Now with the click events that will navigate out of the current record, I added a ConFirmSave procedure.

Private Sub mBtnFirst_click()
    If mNavForm.Dirty Then ConfirmSave
    mNavForm.Recordset.MoveFirst
End Sub
...
Private Sub ConfirmSave()
    If SaveOrNot = vbYes Then DoCmd.RunCommand acCmdSaveRecord Else mNavForm.Undo
    'If SaveOrNot = vbYes Then Call mNavForm.SaveRec Else mNavForm.Undo
End Sub

In the above code, the line that's commented out is one that didn't work. SaveOrNot is just a wrapper function for a MsgBox that asks if the user wants to save. SaveRec is a public sub that I put in the form to do form-specific save actions. Every single save event in the form will call this sub. My plan was that, if this works, I will put a SaveRec sub into every future form so that if called from the class module, it would perform form-specific save tasks, and that would have solved the problem. But it could not be called from class level (some object not defined error), not with the mNavForm reference.

Finally, the code for the Save button:

Private Sub mBtnSave_Click()
    On Error GoTo EH
    Dim ctl As Control
    'SaveCancel is assigned from the form's code. If set to True, the save will be skipped
    If SaveCancel = True Then
        SaveCancel = False
    Else
        'Call mNavForm.SaveRec
        DoCmd.RunCommand acCmdSaveRecord
        If Not IsBlank(formToUpdate) Then   'There is a form to be updated. Check to see if it's open and requery its subform
            If IsFormLoaded(formToUpdate) Then
                For Each ctl In Forms(formToUpdate).Controls
                    If TypeOf ctl Is SubForm Then ctl.Form.Requery
                Next ctl
            End If
        End If
    End If
    Exit Sub
EH:
    ShowError "mBtnSave_Click"
End Sub

Again, I commented out the line that didn't work. The DoCmd line could only perform a generic save, nothing more. For now I have to hit the Save button on the form to do a proper save, but if the save was triggered from anywhere else, it's just a generic save. FormToUpdate is assigned in the form's code. It tells the class module which form to update when a save happens.

I'm not sure if CallByName will work if I use the class module's mNavForm variable instead of the actual form's name. I'll give it a shot as soon as I'm done with all my other chores.

 

[MajP]

Here is a demo how I would probably do this concept. Taking my original example I added another button to the class for SAVE. However, I made this Optional, just to demo an optional property. This will allow you to choose to add a Save button or not for your navigation controls.

Public Sub Initialize(FirstButton As Access.CommandButton, _
                      PreviousButton As Access.CommandButton, _
                      NextButton As Access.CommandButton, _
                      LastButton As Access.CommandButton, _
                      TextRecordCounter As Access.TextBox, _
                      Optional SaveButton As Access.CommandButton = Nothing)

   Set m_FirstButton = FirstButton
   Set m_PreviousButton = PreviousButton
   Set m_NextButton = NextButton
   Set m_LastButton = LastButton
   Set m_TextRecordCounter = TextRecordCounter
   Set m_ParentForm = m_FirstButton.Parent
   If Not SaveButton Is Nothing Then
     Set m_SaveButton = SaveButton
   End If
   'Ensure that events are raised
   ConfigureEvents

End Sub

I added a custom event to the class called SaveRecord

Private WithEvents m_FirstButton As Access.CommandButton
Private WithEvents m_PreviousButton As Access.CommandButton
Private WithEvents m_TextRecordCounter As Access.TextBox
Private WithEvents m_NextButton As Access.CommandButton
Private WithEvents m_LastButton As Access.CommandButton
Private WithEvents m_ParentForm As Access.Form
Private WithEvents m_SaveButton As Access.CommandButton
Public Event SaveRecord()

I raise the Event when the button is clicked (if one is used)

Private Sub m_SaveButton_Click()
  RaiseEvent SaveRecord
End Sub

I did not add any code if this button is clicked. But theoretically you can trap this event in the Class and run code. Then the form can trap the custom event and run form specific code.

In the Parent form, I now declare the navigation control WITHEVENTS since it now raises events.

Private WithEvents CustNavCont As CustomNavigationControls

Private Sub Form_Load()
  Set CustNavCont = New CustomNavigationControls
  CustNavCont.Initialize Me.cmdF, Me.cmdP, Me.cmdN, Me.cmdL, Me.txtCount, Me.cmdSave
End Sub

Private Sub CustNavCont_SaveRecord()
  'Trapped event from nav control
  MsgBox "Do something in the form if save button clicked"
End Sub

So now Every parent form can do something unique if the Save button is pressed, but it is completely decoupled from everything. The navigation class and the parent form do not need to call eithers procedures. The parent can choose do have or not have a save record functionality.



DISCLAIMER: Again this is for demonstration only for explaining concepts of building custom classes. So please do not tell me how you have a better navigation control, or if this has been done differently in the past, or how you should or should not validate the data, or if you should not build class controls. I really do not care. This navigation control is not something I use and only built it for demonstration.

 

[ysdai]

Here is a demo how I would probably do this concept.

Wow, an event declared and raised in the class module, but with the actual code in the parent form. This is completely new to me, but I believe this is the solution I'm looking for. I will implement this and rewrite my code, but it could take quite a while, because my hands are kind of full right now. Thanks a lot. This would open quite a few doors if I get it working. I'll update my progress as soon as I get my hands on it.

 

[Pat Hartman]

With controls I always use AfterUpdate

@ysdai
Please notice that the AfterUpdate event does not have a Cancel argument. Therefore, you are displaying error messages but not preventing the bad data from being saved. The BeforeUpdate does have a cancel event and that is what it is for!

Except for one condition, I put all validation code into the Form's BeforeUpdate event because I prefer to see it all together. The "one" condition is if the user is entering a value that cannot be duplicated and I want to stop him before he goes any further. it is annoying to the user if I let him fill out all the fields on a form and then won't let him save if the SSN is a duplicate so I do that check in the BeforeUpdate event of the SSN control so I can stop the save immediately. If you prefer to have your code spread hither, thither, and yon then put some in the control's BeforeUpdate events and the rest in the Form's BeforeUpdate events.

Since it is possible to never set focus to a control, it is NOT possible to rely on code in control level events to ensure that the control contains a value. So, if you put validation code in the BeforeUpdate event of the control, you STILL need validation code in the Form's BeforeUpdate event to at least require the value to be present.

I hate to burst your bubble but you are not using the form's CLASS module correctly so before you start writing your own class because you think you can do it better, you really need to get a grip on what the properties and methods of the form's existing CLASS module do for you. I guarantee there is bad data in your tables if you are using the controls' AfterUpdate event for validation.

You may never get your class to work correctly because it is the FORM's BeforeUpdate event that is in control over whether or not a record gets saved and there are lots of ways to trigger saving a record that you would have to intercept or you cannot stop saving bad data short of going to an unbound form. And once you go to unbound forms, you might as well give up Access entirely.

MajP is very clever and an excellent programmer so he might figure out some way to beat Access at its own game but WHY?

I once removed nearly 5,000 lines of code from an application because the programmer also didn't use the BeforeUpdate events to do his validation. He duplicated his code in a half dozen events on every form, he even went so far as validating every preceding (in the tab order) field in each of those multiple events which is how we got to such a huge number of lines of code. So, if a form had 50 controls. the first control validated the first field. the second validated the first and the second. The third validated the first, second, and third. etc. And replaced it with about 500 lines in the CORRECT events. And once I cleaned up the code, then I had to clean up years worth of bad data.

You are working with a class module. The form itself is a class and you need to understand how it actually works!

 

[ysdai]

Pat:
Thanks for taking the time to give me this advice. I really appreciate it. You're absolutely right, I need to get a better grip on existing facts before trying to achieve something new. I jumped into this with just bare-bones knowledge of the long-forgotten BASIC language, not even QuickBasic or VB. I didn't get any proper training, but needed to produce results, so I learned by examples, adapting codes here and there, went through lots of trials and errors, not to mention many of the "solutions" that I found were either shortcuts or workarounds, and not the "correct" textbook methods. I do know my foundations are shaky at best, but still need to be reminded from time to time.

It is people like you who, instead of just throwing out a solution, but are willing to offer some real advice, that allow me to progress. It's always a good thing to burst a few bubbles before they grow too big.

That said, I still need to get this save issue resolved. I will adopt MajP's solution because I believe that will solve my problem in this thread. And in the process, I'll also be rewriting all my previous codes to use BeforeUpdate (but not after I've read enough on how to use it). Kind of amazing how I managed to miss this BeforeUpdate throughout all this time, since it sounded so obvious that saves should be trapped before an update happens. But well. I guess I should just slow down.

Thanks again.

 

[ysdai]

I have modified my code to raise a SaveRecord event for saves and then use the form's BeforeUpdate to trap all the saves. There are quite a few issues.

First the codes. This is in the form's code for the SaveRecord event.

Public Sub clsNavigate_SaveRecord()
    'Form-specific save actions here
    DoCmd.RunCommand acCmdSaveRecord
    UpdateForm "frmPartsList"
End Sub

Then I removed all the save-trapping codes and put them in one place, the form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error Resume Next
    If SaveOrNot = vbYes Then
        Call clsNavigate_SaveRecord
    Else
        'Cancel = True
        Me.Undo
    End If
End Sub

The reason I commented out the Cancel=True line is that, for example, if I edit a record and then do something to get out of current record, a save confirmation box pops up (Save, Yes or No). Now if I click No, a run-time error shows up saying action was cancelled by associated object. Clicking Debug revealed the highlight on the line that defines the class click event action of the button I just clicked (like NextRecord). I hit F5 and the form continues to run as usual, with the cancel properly executed. But still, that's a nasty break.

On the other hand, if I click Yes to save, then Access complains of the record being changed by another user and offered to copy to clipboard or drop changes. I drop changes and then Access crashed me out to the desktop. Opening Access again will show that the record had actually been properly saved though.

I don't know why this is happening but for now I don't have time to explore any further. Ideas?

 

[GK in the UK]

This (the save error, not the class module) might be something that an Access less-than-expert can answer. I too have ancient non-event driven coding experience. You've coded some stuff that is still beyond my skill set but you are experiencing the same relatively simple issues that I did, so simple, it was (I think) assumed I would ‘just know’.

I couldn't get to grips with 'Errors'. To me, errors were faults, something that had to be coded out. But Access absolutely has to try to execute the code you write, and the events that transpire as a result, in the right order. You have asked Access to save the record with DoCmd.RunCommand acCmdSaveRecord. That is what it will attempt to do. But, as Pat repeatedly (and helpfully) tells us, form BeforeUpdate event is our last chance to Cancel the save, if data validation fails.

Execution goes like this:

DoCmd.RunCommand acCmdSaveRecord
So you are telling Access to save the record but BEFORE it can do that it MUST execute BeforeUpdate, and that WILL happen before you get to the next line of your SaveRecord sub. So, in BeforeUpdate you do the validation tests / user confirmation and if they fail, you set Cancel = true. (Post #4) The record will not be saved.

Execution returns to your SaveRecord sub. It seemed weird to me, a hobby procedural coder from the dinosaur era, that even though I told Access to NOT save the record, the calling sub doesn't somehow 'know' this and complains that the record can't be saved and gives you an 'error' message. I KNOW the record couldn't be saved, I ORDERED it not to. But, Access has to report that fact as an 'error', sorry, couldn't do what you told me to. So that is where you have to 'trap' the error message, basically you tell Access to ignore the error codes that result from a Cancelled update. Something like this:

Public Sub clsNavigate_SaveRecord()
On Error GoTo Err_Handler   


DoCmd.RunCommand acCmdSaveRecord
' BeforeUpdate will fire here and now
UpdateForm "frmPartsList"


Exit_Handler:
     Exit Sub
Err_Handler:
     Select Case Err.Number
         Case 2001, 2101, 2115, 2501, 2169
             ' expect this if validation fail caused BeforeUpdate to be cancelled, do nothing
         Case Else
               ' whatever you want to happen in case of a real, unexpected error
     End Select
     Resume Exit_Handler
end sub

Don't just use my error codes, put in the ones that you're seeing in 'normal' usage. You might want to code for the case that the record wasn't saved before you call UpdateForm. Also, you should not be calling SaveRecord from within BeforeUpdate. BeforeUpdate fires when a save is about to occur, it’s executing BECAUSE you called SaveRecord.

Sorry, can’t help with the class module stuff.

 

[MajP]

@GK in the UK explanation is good. This has nothing to do with any use of custom classes. If you tried doing the exact same thing without the class then you would get the same error.

DISCLAIMER: I have never ever coded a save button because that is the default action. I find no utility to have a button to do something that is actually pretty hard to stop from occurring automatically. Maybe a cancel button, but never a save. But 99% of the time I simply let execution flow and validate data in the beforeupdate and cancel if necessary.

As GK stated when you force a save action that will then trigger the before update and if you then cancel the BU the code returns back to the "saving code" since it did not terminate yet, and will cause an error.

If I was going to do something like this then I would use GK's code in the Save because if you cancel in the BU you will throw a 2101 when code execution returns to the save code.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If SaveOrNot = vbNO then
       Cancel = True
        Me.Undo
    End If
End Sub

No reason to ever but "save" code in the before update as you had it.

 

[ysdai]

So you are telling Access to save the record but BEFORE it can do that it MUST execute BeforeUpdate, and that WILL happen before you get to the next line of your SaveRecord sub.

You nailed it right there. It WAS an issue that I overlooked because I only had that much time today to test things out. Since I'd always used AfterUpdate instead of BeforeUpdate in the past (a tragic mistake), it didn't come to me that all the save events, even the proper ones like when you click the Save button, will have to pass through the gates of BeforeUpdate before committing. And so it went into a vicious loop. But I think for now I have it taken care of.

You've coded some stuff that is still beyond my skill set

That's an illusion. I may be able to come up with some flashy codes but inside the core, I'm just a beginner. I don't have a solid foundation. All my skills are learned through the pressures of problem-solving and results-producing. Thanks a lot for your input.

 

[ysdai]

Consider this problem solved. I have achieved my original purpose of writing form-specific save codes in a common class module. Should there be any other issues that arise out of this, they are not within the scope of this thread and I should try to deal with them myself.

Thanks everybody!