Classes and Events with a SplitForm or DataSheet (1 Viewer)

[AccessAllstars]

Kim,
I think you have confirmed that it is an issue associated with SplitForm and not your coding of class and events.
Perhaps time to have M$oft have a look.
Could be a bug/oversight or a purposeful design/implementation "feature"?

I agree - I will get this to them.

 

[AccessAllstars]

I agree - I will get this to them.

I did try one more thing - I showed the splitter bar and dragged down so the single form/row showed. If I changed the data there - the events in the class fire. If I do anything in the datasheet on the bottom, the events don't fire.

 

[isladogs]

Although in many ways very clever, split forms are somewhat of a kludge which means they have a number of important limitations.
For example, don't put anything in the footer section as it won't be shown. Don't try to use a split form in a subform etc, etc

Issues Modifying Split Forms

The Access split form can work well but has several important limitations and issues. This article discusses some of these issues and outlines alternative approaches to overcome them

isladogs.co.uk

As you are probably aware the split form is actually a special kind of single form. The datasheet doesn't actually 'exist' as a physical object so it isn't surprising that it doesn't respond to all events.

For much greater control and flexibility, the alternatives are far better if you want to do anything more than what the split form does 'out of the box'.
I'd make exactly the same comments about the built-in navigation form which is a neat idea but very limited in terms of making any changes to it

 

[AHeyne]

The split form is a wild animal.

You have to manage the events of the data sheet of a split form separately from those of the actual form.

To do this, the split form must be stored in a separate 'WithEvents' variable.

As far as I know, you can only reach the split forms datasheet via `Screen.ActiveDatasheet`. For this, the split form must be open and the active form.

Here is a small example of how to do it:

1. Create a SplitForm named "MySplitForm" (it needs to have a code module inside)

2. Create a class module named "MyFormClass":

Option Compare Database
Option Explicit

Private WithEvents mForm As Form
Private WithEvents mSplitFormsDatasheet As Form

Public Sub Initialize(ByVal formToHandle As Form)
    Set mForm = formToHandle
    '// Subscribe to the "BeforeUpdate" event:
    If LenB(Nz(mForm.BeforeUpdate)) = 0 Then mForm.BeforeUpdate = "[Event Procedure]"

    '// Check if the provided form is a split form:
    If mForm.DefaultView = AcDefView.acDefViewSplitForm Then
        '// The split form must be visible and active to be able to access its datasheet part.
        Set mSplitFormsDatasheet = Screen.ActiveDatasheet
    End If
End Sub

'// This event procedure is fired when you make changes in the forms part of the split form:
Private Sub mForm_BeforeUpdate(Cancel As Integer)
    MsgBox "mForm_BeforeUpdate"
End Sub

'// This event procedure is fired when you make changes in the datasheet part of the split form:
Private Sub mSplitFormsDatasheet_BeforeUpdate(Cancel As Integer)
    MsgBox "mSplitFormsDatasheet_BeforeUpdate()"
End Sub

3. Create a standard module with any name.

It will just contain a procedure to show how it could work.

Option Compare Database
Option Explicit

Private myFormClassInstance As MyFormClass

Public Sub TestSplitForm()
    DoCmd.OpenForm "MySplitForm"

    Set myFormClassInstance = New MyFormClass
    myFormClassInstance.Initialize Forms("MySplitForm")
End Sub

4. Run the procedure "TestSplitForm" and change control values in then form and datasheet part of the split form. You will see that different event procedures will fire.

 

[jdraw]

AtzeX,

Do you have a reference to documentation regarding this?

 

[AHeyne]

I use this approach since years in an application running on many clients.
As far as I can remember, that was an intense trial-and-error session to get it working.
I'm not sure, but I think thats not documented...

 

[isladogs]

@AtzeX
Faced with similar split form issues, I chose to look for alternatives rather than stick with the limitations of the standard split form.
It sounds like you worked hard to find ways of making the built-in version work for you but I believe you are still restricted by its design limitations.

 

[jdraw]

Axel,
I set up a sample of what you provided in #24.
If I modify a record:
--- in the form (top) area, I get:
mForm_BeforeUpdate

--- in the datasheet (bottom) area, I get:
mSplitFormsDatasheet_BeforeUpdate()


which confirms your comments. Thanks

 

[AccessAllstars]

The split form is a wild animal.

You have to manage the events of the data sheet of a split form separately from those of the actual form.

To do this, the split form must be stored in a separate 'WithEvents' variable.

As far as I know, you can only reach the split forms datasheet via `Screen.ActiveDatasheet`. For this, the split form must be open and the active form.

Here is a small example of how to do it:

1. Create a SplitForm named "MySplitForm" (it needs to have a code module inside)

2. Create a class module named "MyFormClass":

Option Compare Database
Option Explicit

Private WithEvents mForm As Form
Private WithEvents mSplitFormsDatasheet As Form

Public Sub Initialize(ByVal formToHandle As Form)
    Set mForm = formToHandle
    '// Subscribe to the "OnCurrent" event:
    If LenB(Nz(mForm.OnCurrent)) = 0 Then mForm.BeforeUpdate = "[Event Procedure]"

    '// Check if the provided form is a split form:
    If mForm.DefaultView = AcDefView.acDefViewSplitForm Then
        '// The split form must be visible and active to be able to access its datasheet part.
        Set mSplitFormsDatasheet = Screen.ActiveDatasheet
    End If
End Sub

'// This event procedure is fired when you make changes in the forms part of the split form:
Private Sub mForm_BeforeUpdate(Cancel As Integer)
    MsgBox "mForm_BeforeUpdate"
End Sub

'// This event procedure is fired when you make changes in the datasheet part of the split form:
Private Sub mSplitFormsDatasheet_BeforeUpdate(Cancel As Integer)
    MsgBox "mSplitFormsDatasheet_BeforeUpdate()"
End Sub

3. Create a standard module with any name.

It will just contain a procedure to show how it could work.

Option Compare Database
Option Explicit

Private myFormClassInstance As MyFormClass

Public Sub TestSplitForm()
    DoCmd.OpenForm "MySplitForm"

    Set myFormClassInstance = New MyFormClass
    myFormClassInstance.Initialize Forms("MySplitForm")
End Sub

4. Run the procedure "TestSplitForm" and change control values in then form and datasheet part of the split form. You will see that different event procedures will fire.

Thank you for sharing this! I will check it out and get back to you!!

 

[AHeyne]

@AtzeX
Faced with similar split form issues, I chose to look for alternatives rather than stick with the limitations of the standard split form.
It sounds like you worked hard to find ways of making the built-in version work for you but I believe you are still restricted by its design limitations.

Hi Colin,
I read your article you linked above regarding the disadvantages of split forms now and can say that split forms seemed to met my needs over time because:
- I don't use overlapping windows in my applications.
- I don't use a split form in a subform or tab control.
- I don't use a footer in my split forms.

Regarding the deletion-action:
This was new to me, but it is grayed out in my standard Access popup menus. Maybe Microsoft fixed this meanwhile?

I'm using Microsoft Access 2016 64-bit Version 2306 (Build 16529.20182 C2R) for development.

 

[isladogs]

Hi Axel
As I stated at the start of that article, split forms generally work well if you use tabbed documents display and don't try to do anything out of the box with them.
I reported all the issues to Microsoft before writing my article and it is likely that some of them will get fixed at some point.
However, I've just tested and the issues regarding Hide Fields and Delete actions are still present as is the code context error

 

[AccessAllstars]

Thank you for sharing this! I will check it out and get back to you!!

That works! Thank you for sharing - at least we now know what is going on even if we don't use the SplitForm approach.

 

[AHeyne]

My pleasure.

 

[Gasman]

@AHeyne
Could you please explain to me what this is actually doing?

    '// Subscribe to the "OnCurrent" event:
    If LenB(Nz(mForm.OnCurrent)) = 0 Then mForm.BeforeUpdate = "[Event Procedure]"

Why are we checking the OnCurrent event, but setting the BeforeUpdate event?

 

[AHeyne]

@AHeyne
Could you please explain to me what this is actually doing?

    '// Subscribe to the "OnCurrent" event:
    If LenB(Nz(mForm.OnCurrent)) = 0 Then mForm.BeforeUpdate = "[Event Procedure]"

Why are we checking the OnCurrent event, but setting the BeforeUpdate event?

Upps, thats a typo or better a copy/paste error from preparing this small demonstration code, thanks for info. Both should be "BeforeUpdate". I corrected that in my sample.

 

[Gasman]

Upps, thats a typo or better a copy/paste error from preparing this small demonstration code, thanks for info. Both should be "BeforeUpdate". I corrected that in my sample.

OK, thank you, that makes more sense.