Force Entry Into Subform Before Leaving Main Form (1 Viewer)

JJFernandez

Registered User.
Local time
Today, 07:21
Joined
Oct 29, 2018
Messages
18
Hi all,

I am trying, and failing, to think of a good way of ensuring that an entry is made into a subform before the user is allowed to move to another record, create a new record, or save the existing record.

I've tried simply adding some code to the on click event of a Save/Refresh Control Button on the Main Form but keep getting an 'object required' error on the If/Then Line

Private Sub Command63_Click()
If Me.SubformName.Form.TextBox.Value Is Null Then
MsgBox "Subform Value Must Be Entered"
Else
Me.Form.Refresh
End If
End Sub

I've also read that I might want to use a Dcount to test if there is a value in the subform tied to the current main form entry but I just don't know how I would write it.

Thanks for any assistance.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:21
Joined
Oct 29, 2018
Messages
21,358
Hi,

Since there is no “exit record” event, I think the closest you can do to prevent saving a parent record without a child record is to use unbound forms. However, since child records need the parent’s ID, you might run into a Catch-22 situation.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:21
Joined
Feb 19, 2013
Messages
16,555
not tried it but perhaps something like this in the parent form

Code:
Option Compare Database
Option Explicit
 
Public noSubEntry as Boolean

 
Private Sub Form_BeforeUpdate(Cancel As Integer)
 
    if not me.newrecord then
        if noSubEntry
             msgbox "No entries made in subform, record not saved", vbokOnly
             Cancel = noSubEntry
        end if
    end if

End Sub
 
 
Private Sub Form_Current()
 
        noSubEntry = Me.subformname.Form.RecordCount = 0 'true if there are no records
    
End Sub
and in the subform form_ afterupdate event

Code:
Private Sub Form_AfterUpdate()
    
    parent.noSubEntry=False
 
End Sub

Don't think this is quite right - but I'll leave it there as a possible basis to be developed.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:21
Joined
Jan 14, 2017
Messages
18,186
I saw this & thought it could be useful for me, so I tested CJL's solution.
It was almost perfect first time!

Just 2 very minor changes needed (in RED):

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
 
    if not me.newrecord then
        if noSubEntry [COLOR="red"]Then[/COLOR]
             msgbox "No entries made in subform, record not saved", vbokOnly
             Cancel = noSubEntry
        end if
    end if

End Sub
 
 
Private Sub Form_Current()
 
        noSubEntry = Me.subformname.Form[COLOR="red"].Recordset.[/COLOR]RecordCount = 0 'true if there are no records
    
End Sub

Thanks CJ
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:21
Joined
Feb 19, 2013
Messages
16,555
It was air code so thanks for correcting
 

isladogs

MVP / VIP
Local time
Today, 12:21
Joined
Jan 14, 2017
Messages
18,186
It was neat. I wouldn't have thought of using that method.
 

Users who are viewing this thread

Top Bottom