Running nul check on main form and subform

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.

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?

which line is raising the error?

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

shows the first field on the main form..

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!

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

Pat let me process this for a bit....

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.

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.

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.

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 ...

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

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!

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:)

Well,.... all I can say is ... Ernest: I feel your pain! Pat: spot on again!  :)

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

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