Subform best practice (1 Viewer)

alex

Registered User.
Local time
Today, 13:59
Joined
Apr 14, 2009
Messages
13
Hi,

I have a form that contains many tabs, one of which is a subform, which itself contains 3 more subforms. As you may expect, an error occurs if the user creates a new record and goes directly in the subform (or one of the 3 sub-sub-form) to enter data, since the parent record is not created yet.

What's the "best practice" to avoid that problem? Save the record (form+subform) as soon as the user hits new record? That would work in my case, but since filling the subform is optional and not required for every record, i feel that I would be wasting not-so precious disk space, but you get the idea...

Thanks for your suggestions! :)
 

CraigDolphin

GrumpyOldMan in Training
Local time
Today, 10:59
Joined
Dec 21, 2005
Messages
1,583
I typically avoid this by using the on_enter event of the subform control (the container for the subform, not the subform itself) to check to see whether the pk on the parent form has a value or not (if it does, there's a parent record....if not, then I change the focus to the first user-editable control on the parent form)

Whether this consititutes best practise, I couldn't say.
 

alex

Registered User.
Local time
Today, 13:59
Joined
Apr 14, 2009
Messages
13
I like your suggestion...but instead of bringing the cursor back on a field on the main form, I would like to create the record associated with the parent form once the on_enter event is triggered.

I've tried this without success:

Code:
Private Sub MySubFormContainer_Enter()
    If IsNull(FormPK) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
End Sub

Thanks
 

boblarson

Smeghead
Local time
Today, 10:59
Joined
Jan 12, 2001
Messages
32,059
I've used several methods, depending on what it was that the database was for and these are my two most used:

1. Not display the subform until a record has been created in the main form.

2. Not Enable the subform until a record has been created in the main form.
 

alex

Registered User.
Local time
Today, 13:59
Joined
Apr 14, 2009
Messages
13
Thank you both for your help...after playing with vba a little, I found a way to automatically create a record if the user goes directly in the subform...here's the code:

Code:
Private Sub SubForm_Enter()
    If IsNull(Form_PK) Then
        ' Set focus back to the parent form to create the record
        Me.AnyTextBox.SetFocus
        ' Set any value in the parent form
        Me.FormTypeTxt = Me.FormType
        ' The record is now dirty; we can save it
        DoCmd.RunCommand acCmdSaveRecord
        ' Set focus back to where the user was
        Me.SousFormulaireSpecifique.SetFocus
        ' Talam! No errors!
    End If
End Sub
 

CraigDolphin

GrumpyOldMan in Training
Local time
Today, 10:59
Joined
Dec 21, 2005
Messages
1,583
I'm glad you found a solution that works for you. Thanks for posting your final solution for others who might come across this thread in the future. :)

One thing I would say is that I personally would probably not take this approach. In order for this work, you are automating the data entry of information into a field. Now, that's fine if the information is correct, or the information is meaningless, but I have three things to say

If there is any chance the automated information could be wrong, then you risk compromising the integrity of the data itself: users might neglect to notice the incorrect value and not change it to suit reality.

If you can guarantee that the automated value is always correct because it's, essentially, a simple calculation based on other information then you probably should not be storing that calculated result at all.

And if the information is meaningless then you're using up file space for no good reason.

About the only field that could, arguably, be used in this way IMO might be audit trail fields like 'LastEditedDate' or somesuch which records information about the record itself, not information about the entity which the table represents.

But, perhaps that's just me :p

In any case, good luck with the rest of your project.
 

evanscamman

Registered User.
Local time
Today, 10:59
Joined
Feb 25, 2007
Messages
274
If your user clicks on the subform and then exits the form altogether, you will have a blank record, which could cause all kinds of problems down the line.

I would take Bob's approach and disable the subforms or the tab control until data has been entered in the main form - perhaps the OnDirty event.

Evan
 

Users who are viewing this thread

Top Bottom