Solved

Running nul check on main form and subform

Posted on 2015-01-08
20
178 Views
Last Modified: 2015-01-10
hello,

I was asking to see how I can run a null check on a main form and the subform at on time.

currently I have the button to save the record and to run a check on the subform but I want to run a check on the main form as well.

Dim strCtlName As String, NullCtl As String, Msg As String, ctl As Control
 strCtlName = ""
 For Each ctl In Me.Controls
     If ctl.Tag = "REQ" Then
         If Len(Me(ctl.Name) & "") = 0 Then
             strCtlName = strCtlName & ctl.Name & ";"
         End If
     End If
 Next
 If Len(strCtlName) > 0 Then
     NullCtl = Mid(strCtlName, 1, InStr(strCtlName, ";") - 1)
     Msg = "Please fill out the required fields!" & vbCr & vbCr
     Msg = Msg & Left(strCtlName, Len(strCtlName) - 1)
     MsgBox Msg, vbCritical, "Required Fields"
     Cancel = True
     Me(NullCtl).SetFocus
     
 Else
   If Me.Dirty Then
   If Me.BadgeIssued = "3055" Then
        Me.TimeOut = Me.TimeIn
    Else
        If DCount("BadgeIssued", "qryLicenseSearch3") > 0 Then
   
            MsgBox "Please Enter a Different Badge as that badge is already assigned to a customer...and You must sign out the badge first", vbOKOnly
            Cancel = True
            Me.BadgeIssued.SetFocus
            Exit Sub
        End If
    End If
End If
Me.Dirty = False
On Error GoTo 0 'reset
DoCmd.OpenReport "rpt3055_2", acViewPreview, , , acDialog
End If
0
Comment
Question by:Ernest Grogg
  • 8
  • 4
  • 4
  • +2
20 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 334 total points
Comment Utility
the codes looks familiar :-)

if your button is on the subform, to check the main form for null values
create another routine with

 For Each ctl In Me.PARENT.Controls
     If ctl.Tag = "REQ" Then
         If Len(Me(ctl.Name) & "") = 0 Then
             strCtlName = strCtlName & ctl.Name & ";"
         End If
     End If
 Next

etc....
0
 
LVL 84
Comment Utility
Be aware, however, that when you move to the subform, Access automatically saves the data in the Main form - so by the time you're running that code in the subform, the mainform record has already been saved.
0
 

Author Comment

by:Ernest Grogg
Comment Utility
Scott,

I see that and when the form opens I have it focused on the subform, so no data really is needed but, since the parameters have changed (the customer needs to change some data) on the main form, there are now empty or null fields on the main form that for data purposes as the customer goes through the process of data entry, these need to be caught.

so, either I can have it start on the main form then tab through to the subform or I can leave it.

I would rather leave it because the data may already be there and I just want to capture the fields if they are null.

Rey...very familiar, ey!  thanks!

Well, I seem to be getting the error that it can't seem to find the field "my field" referred to in the expression.

I placed this after the fact.  Or at the end of the code.

the button is infact on the subform.

What do you think?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
which line is raising the error?
0
 

Author Comment

by:Ernest Grogg
Comment Utility
If Len(Me(ctl.Name) & "") = 0 Then

shows the first field on the main form..
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 334 total points
Comment Utility
change this

If Len(Me(ctl.Name) & "") = 0 Then
with

If Len(Me.PARENT(ctl.Name) & "") = 0 Then
0
 

Expert Comment

by:JamesDF
Comment Utility
In light of Scott's note above you probably need to place the code to check the main form field values in your On BeforeUpdate event of the main form. In essence, don't allow entry in to the subform until the main form is correct by cancelling the update and using setfocus (as you have done above) to any offending field(s). It is a bit of a juggling act because of the way the events fire so be sure to test your solution well before moving it to production!
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 166 total points
Comment Utility
All null checks should be done in the BeforeUpdate event of the form.  Doing the null check anywhere else will result in invalid data being saved.  Your button's click event should save the record.  That will force the BeforeUpdate event to run.  ALL final edits should be done there NOT in the click event.  The point being that the BeforeUpdate event runs whenever focus changes to a new record, a new object, or the form closes as long as the current record is dirty.  Putting validation code in the click event of a button means you have to put traps in lots of other events that would be completely unnecessary if you simply put the validation code where it belongs!!!

Here is a sample proc (untested) that will check for nulls or ZLS in ANY form.  When you call it, you pass in the form object so the exact same code procedure will run from your main form and from your subform and from any other form where you need the ability to prevent nulls.  The only problem with the code is that it displays the column name of the control.  This name is not usually fit for human consumption so you might want to put captions on the "REQ" fields and display the caption property rather than the name property.

when you call the function, you pass in the current form object.  That allows the function to address the controls.  The function returns True or False.  If False, at least one field is empty.  The loop stops when the first error is found and sets focus to the empty control.  If the function returns True, the BeforeUpdate event continues with the rest of your process.

''  In the BeforeUpdate event of ANY form:

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


'' this code goes in a STANDARD module.  It will not be reusable if you put it in the CLASS module of a form
Public FunctionCheckRequired(frm As Form) as Boolean
    Dim ctl As Control
    For Each ctl In frm.Controls
        If ctl.Tag = "REQ" Then
            If ctl.Value & "" = "" Then
                MsgBox ctl.name & " is required.  Please enter a valid value.", vbOKOnly
                ctl.SetFocus
                CheckRequired = False
                Exit Function
            End If
        End If
    Next ctl
    
    Set ctl = Nothing
    CheckRequired = True
End Function

Open in new window


Adding data in a subform before entering data in a main form does not make logical sense.  To prevent this from happening, add some code to the BeforeInsert event of the subform.  This event runs as soon as the user starts typing in an empty record.
If Me.Parent!SomeFieldThatAlwaysHasAValue & "" = "" Then
    Msgbox "Please ender data in the main form first.",vbokOnly
    Cancel = True
    Me.Undo
    Me.Parent!SomeField.SetFocus
    Exit Sub
End If

Open in new window

0
 

Author Comment

by:Ernest Grogg
Comment Utility
James,

Yes, while I do agree that I should put this perhaps in the BeforeUpdate event on the main form, I don't see the coding running because I am running the save record on the subform.  I want to check and update the record from the subform.  I am not saying that is the best way, but for currently what they are asking, I can't see really moving it all around and recreating this whole form and the queries they have driven off of it.  

I have used Rey's coding and I like the way it works for what we have here.


Rey,  I did see that I had to change the below:

Me.Parent(NullCtl).SetFocus

so far, it is working and my day is about up, so I just need to run the report to make sure it runs properly.  Just don't want to close this prematurely.

Will finish later on tonight.

Ernest
0
 

Author Comment

by:Ernest Grogg
Comment Utility
Pat let me process this for a bit....
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I don't see the coding running because I am running the save record on the subform
At least one person besides myself has already pointed out that by the time your code in the subform runs, the main form has already been saved with the bad data.

The ONLY event that works 100% of the time for this type of validation is the BeforeUpdate event of the form.  So, if you put the code in the BeforeUpdate event of the main form, you won't even get to the subform if data is missing and that is as it should be.

You may like Rey's code but it doesn't stop the bad data from being saved since it is already saved when the code runs.  You can get error messages but you are only fooling yourself.  I'm sure Rey will agree with me on this.  What you have is a placebo, not real data validation.  think of it as shutting the barn door after the horse has escaped.
0
 

Author Comment

by:Ernest Grogg
Comment Utility
Pat,

You are right, don't get me wrong...but I meant that the code was running in the BeforeUpdate event and I had the button to save the record..and I got more errors and at times it seemed to miss null fields, so I saw (not to normal practice) that if I ran it "on demand" that it seems to catch the data at the time.

Sure, saving record should trigger the BeforeUpdate event, but I didn't see that running each and every time, because I would test running test data and leaving null values on purpose, thus the reason for triggering the code during the OnClick event.

But, I will try once again to move it...just give me some time.

Trust me, I really like the detailed explanation.  That is great!  You don't see that much anymore.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Ernest,
I'll say it again.  The BeforeUpdate event of the form ALWAYS runs.  It is the single event you can use to trap ALL errors of this type.  Occasionally, for certain types of validation, you can put the code in the BeforeUpdate event of a control but the bulk of the code should go in the BeforeUpdate event of the FORM.  If "null" controls were being missed, it was because the test was in the wrong event or you were doing it incorrectly.  There is a difference between Null and a ZLS and if your table definition permits ZLS, you have to deal with that also.  Using the Len(yourfield) > 0 works and so does yourfield & "" <> ""
Sure, saving record should trigger the BeforeUpdate event, but I didn't see that running each and every time
You may not have seen it but I assure you it DOES run EACH and EVERY time a record is saved whether you force the save with the DoCmd.RunCommand acCmdSaveRecord or Me.Dirty = False or some other trick like Me.Requery or Me.Refresh.  It also runs whenever Access determines it needs to save the record.  THAT is why code in the click event is ineffective.  If Access decides it needs to save the record it will NOT run your click event but it WILL run the FORM's BeforeUpdate event.

In addition to using the Form's BeforeUpdate event where you can display meaningful messages, you should also define the fields as required in the table and leave the default as null in the table.  That way if data is added outside the form, RI will enforce the requirement anyway.  The only difference is that the error message isn't so user friendly.

I am finished arguing about this.  Do what you want.  I am telling you how to do validation that works 100% and even took the trouble to explain why my way (the Access way) works and your way does not but apparently you know better.
0
 

Expert Comment

by:JamesDF
Comment Utility
Hey fellas, lighten up! Perhaps I am wrong but I don't think there is any point or reason to argue here ;)
Ernest, Pat is absolutely correct and his generously offered code should be easily adaptable to your situation. I cautioned you earlier to test *thoroughly* to be sure you handle all situations a user will probably encounter. The most annoying case (at least for me) is the one in which the user gets part way through then decides to bag it an Cancel (you know they will at some point!).
Here is a simple thing you can do to convince yourself you understand how the events are firing: add a debug.print statement as the first line of code in each event you want to track. e.g.,
debug.print <whateveryouwant> & " fired at: " & now()

where <whateveryouwant> would be a string that contains the form name and the event name.

Run your app through its paces and then look at what you find in the Immediate window. That will tell you exactly what is happening and in what order.

You could make it a bit more general (and easier to "turn off"!) by declaring a global variable gDebugOn as boolean and using a call to a sub such as:

Sub EventTrace(aMsg as string)
if gDebugOn = True then
   debug.print aMsg & " - " & now()
end if
End Sub

call it in each of the events you are tracking by making the first line of code in the event
EventTrace(<whateveryouwant>)

just be sure to set gDebugOn = True at some point because it will be False by default. You could do this in the Immediate Window so you only turn it on when you need to.

Just a thought ...
0
 

Expert Comment

by:JamesDF
Comment Utility
My bad: if you make eventTrace a Sub then call it via:
EventTrace <whateveryouwant>
(no parentheses)
;(
0
 

Author Comment

by:Ernest Grogg
Comment Utility
Thanks everyone for their helpful comments.

I didn't mean to argue.  Please accept my sincere apologies.  I wanted to simply try to explain why I was not doing what should be in the beforeupdate event.

I realize this and agree with Pat.

But in the tables there is data that is absolutely required and some data that isn't.  But the data that isn't, isn't because of certain circumstances that are dealt with at the form and query level.

I have 4 areas that data is being entered into the db and each area is different a per the requirements for the area.

Now I setup the tables to require the data (all fields) but like was said the message box was unfriendly.  So the supervisor, wanted us to get some more "user friendly" information.

Now, next was that since there was data already LIVE, I had to find some way around the issue because they came back to add fields, modify fields....you know the whole deal.

So, I sat in total amazement since I already spent a considerable amount of time to get them this and move it to a live situation.

Records were there and I couldn't just play around with it.  So, to create new queries, ect would be to me a lot of work to try to keep track of so I found ways around it.  

After adding the fields to the forms this created null fields naturally.  So, I had to have the user catch these fields in live action so the best way if they just didn't fill it in, since the forms were a form and subform.  It can catch it if the active form is being worked on but the subform may not be, so to catch those and force the user to add the data, I saw that if the main form or subform was not edited it would not catch it in BeforeUpdate since there was no change in the recordset.

What could I do?  This to me at the time is the best solution.

I am sorry if I created this storm of sorts.

I am working diligently to actively incorporate everyone's suggestions and ideas.  This site has been a great help to me...

Thanks so much!
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Sorry Ernest, I didn't mean to come down so hard on you.

With the data in an inconsistent state it is difficult to play catch up.  My suggestion on using the BeforeUpdate event doesn't change any query or code in the main form.  Simply move the validation code for the main form from where ever you have it now to the FORM's BeforeUpdate event and add the code I posted to check for empty fields.  Since your code is now moving to an event that is cancellable, you should add code to cancel the event if an error is discovered.  If the user changes anything in the mainform, the BeforeUpdate event will fire and pick up the missing field.

The bigger problem is if the user does not modify the data on the main form.  In that case, the BeforeUpdate event will not fire.  So the kludge is to call the mainform BeforeUpdate event from the subform AfterUpdate event.   You don't want validation errors on already saved data to interfere with the validation of the current record so don't run it until after the current record is saved.  The bad data is already there and it isn't going anywhere.  The code as I wrote it is generic and as long as you place it in a standard code module, you can call it from any form.  You just have to pass in the correct form reference.  So, when calling it for "yourself", what ever form you are in, you use (Me).  When calling it from the subform to validate the main form, you use (Me.Parent).  If this doesn't work, we'll help you figure out what the correct syntax is.

@JamesDE,
The point of my tirade was that if you use the correct event for validation, you don't have to worry about what the user will do and in what order he does it.  It is impossible to predict the stupid stuff they do and it never ceases to amaze me.  For self protection and to maintain your own sanity, use the correct event to do your validation and solve the problem in ONE and ONLY ONE place.  When you don't use the FORM's BeforeUpdate event, you will have code hither, thither, and yon and you will still miss sequences and end up with bad data.  As you can see, I am passionate about managing the validation process.  We have a fiduciary responsibility to our clients/employers to protect their information.  If we allow bad data in a table, we could actually ruin a company that makes a bad decision on what our data tells them.  NOTHING we do is more important then ensuring data validity.

Dismounting from platform:)
0
 

Expert Comment

by:JamesDF
Comment Utility
Well,.... all I can say is ... Ernest: I feel your pain! Pat: spot on again!  :)
0
 

Author Comment

by:Ernest Grogg
Comment Utility
Pat,

Thanks so much for working with me.  I can understand how frustrating it can be to work with new guys like me...a noob!

Well,

here is where I am at:

I used the coding above and here is where I am at:

I get to the Form AfterUpdate and I get stuck.  Runtime 2465-Application-defined or object-defined error

I thought that either one of those in the afterupdate should have called the beforeupdate on the mainform.  eitherway I did it doesn't go further.



Private Sub Form_AfterUpdate()
Call Me.Parent.Form_BeforeUpdate
'Call Form_frm_Main.Form_BeforeUpdate
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
''  In the BeforeUpdate event of ANY form:

''....
    If CheckRequired(Me) = False Then
        Cancel = True
        Exit Sub
    End If
   
   If Me.BadgeIssued = "3055" Then
        Me.TimeOut = Me.TimeIn
    Else
        If DCount("BadgeIssued", "qryLicenseSearch3") > 0 Then
   
            MsgBox "Please Enter a Different Badge as that badge is already assigned to a customer...and You must sign out the badge first", vbOKOnly
            Cancel = True
            Me.BadgeIssued.SetFocus
            Exit Sub
        End If
    End If

End Sub
0
 

Author Comment

by:Ernest Grogg
Comment Utility
Well,

Since I couldn't figure out this I did part of what Pat and part of what Rey gave.

I used the BeforeUpdate event to trigger the coding for Null Values from Rey but I put this as Pat suggested in a Module...

I called this module from the BeforeUpdate event on the SubForm since I know they will enter data there.  Since the MainForm was my concern,  I used the On Enter event of the subform control in the main form to trigger the code from Rey for Null Values (using Pat's Module and Rey's coding) and also used the BeforeUpdate on the MainForm.  That may not be the best answer, but it does catch all senerios and I even tried to break it and well can't seem to do that.



It really seems to catch
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now