?
Solved

do not allow subform entry until fiels in parent are filled

Posted on 2012-12-28
7
Medium Priority
?
726 Views
Last Modified: 2013-01-09
Hi.  I am trying to make controls in my subform or the subform as a whole, inactive until specific controls on my main form are filled. I've tried a number of things and am having no luck.  I figured the easiest would be to use the gotfocus property of a control on my subform and if parent control is null then setfocus back to the parent control and isue a message to the user.  The logic works but as soon as I open my parent form the message I'm sending my user pops up and it doesn't matter which subform control I wrap this code around either.  I'm confused as to why my subform controls all seem active when I open the parent form.  Is there a way to keep the subform from being active until the user manually activates it?  What's the best way to keep users from entering data into a subform until they have properly filled the parent form? I am including code from my subform.
Private Sub ExpDate_GotFocus()
If IsNull(Me.Parent!TripFinish) Then
    MsgBox "Fill in the Trip Detail above before entering Expense data.", , "ERROR"
    Me.Parent!TripPurpose.SetFocus
End If
End Sub

Open in new window


parent form = frmTripDetail
parent form control = TripFinish
Subform = subfrmExpDetail
subform control = ExpDate
Key field = TripID

thanks
0
Comment
Question by:valmatic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38728296
To spare you the never ending debate, ...

You can validate the fields in the Before Update event of the main form.

Trying to do this "On-the-fly", while in the field is always problematic.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If isnull(me.YourField) then
        msgbox "You must fill in YourField."
        me.YourField.stefocus
    End if
    Cancel=True
End Sub

But lets see what other experts may post...

;-)


jeff
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 38728300
Slight code corrections:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.YourField) Then
        MsgBox "You must fill in YourField."
        Me.YourField.SetFocus
        Cancel = True
    End If
End Sub
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 500 total points
ID: 38728628
"To spare you the never ending debate, ..."
Agree with boag2000!

Which field will trigger end of entry?
Which fields allow nulls?

One idea, to check for newrecord, and disable the subform.
At the point you are satisfied with main form entry, enable subform.

Private Sub Form_Current()
    If NewRecord Then
        subForm.Enabled = False
    Else
        subForm.Enabled = True
   End If

In a button enable subform in data entry.

    subForm.Enabled = True

This can be set as boag2000 suggested in before update event of the main form.
And as boag2000 mentioned this can go on for a while analysing the problem.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38728695
hnasr,

As always, thank you for that great additional info...
;-)

Jeff
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 38728955
Another idea is to develop a routine that checks each of the mainform's controls and enables/disables the subform based on the results of that. You would then just call that routine in one of the control Events (AfterUpdate, Exit, LostFocus, etc). Something like:

Function CheckRecord as Boolean

Dim bOK As Boolean
bOK = True '/ assume everything it okay

bOK= Nz(Me.Textbox1,"") = "" 
'/ If bOK is already false, we don't reset it - we only
'/ continue the checks if the previous controls return True
If bOK Then
  bOK = Nz(Me.Textbox2, "") = ""
End If

If bOK Then
  bOK = Nz(Me.Combo1.Column(0), "") = ""
End If

Me.YourSubformControl.Enabled = bOK

End Function

Now just call CheckRecord for each control that is used to determine if the mainform record is valid. Again, use the AfterUpdate, Exit, LostFocus, etc etc to determine that.
0
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 500 total points
ID: 38728992
I agree with LSM.  Whenever I use a subform this way, I almost always disable the subform in the main forms Current event if Me.NewRecord = True.

I then have a Save button on the main form, which runs through my required fields and makes sure they are all filled in.  If not, it sets the focus to the missing control(s) sequentially and after all controls are filled in, the Save button will save the record and enable the subform.
0
 
LVL 7

Author Closing Comment

by:valmatic
ID: 38759225
Thanks for all of the responses on this one guys.  I tried variations of each of your posts and could not get it to work exactly like I wanted it so I'm changing tactics.  I'm going to make the controls on my subform non-maintainable so data is preview only.  User can call an entry form through a button on the main form, which will ultimately populate my subform.  User can add as many entries as he wants without actually touching my subform and hopefully minimize mistakes and bogus entries.  I think that's the theory LSM and Fyed wer trying to get across..  NO idea how to split this one so am just doing an even split for everyone.  As always, I appreciate the great input.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question