Procedure behaves on Main Form, but not on the SubForm (1 Viewer)

[quest4]

I have a form with a subform and on the form and also on the subform, I have three comboboxes or txtboxes and in the OnExit event I have a little procedure which makes sure that each have been filled in or it asks if you want to cancell and if yes, it setfocus on the cmdCancell and then I can canells the records, and the procedure looks like this:
Me!QCTypes_Label.ForeColor = 0 'Change Label Color to Black
Dim Response As VbMsgBoxResult
If Len(Nz(Me.QCTypes, "")) = 0 Then 'If QC Type is Blank
Response = MsgBox("No QC Type Was Entered," & vbCrLf & "Do You Want to Cancel QCA.", vbYesNo + vbDefaultButton2, "No?")
If Response = vbYes Then
Me.cmdCancell.SetFocus 'SetFocus on cmdCancell
Exit Sub 'Exit Immediately
Else
Me!QCTypes_Label.ForeColor = 33023 'Change Label Color to Orange
Cancel = True 'Re-Enter QC Type, same field
End If
End If
Now the main form works great, but the subform acts strange, when I select YES, instead of cancelling and closing like the main form, this one does each procedure for each of the other two procedures. Why does it work right on the main and not on the subform? Thank you in advance for any assistnace rendered.

 

[Pat Hartman]

Any code that checks to ensure that fields are not empty MUST go in the FORM's BeforeUpdate event. Using the onExit event as you are doing presumes that the user actually tabbed into the field. If he did not, no field level event would fire.

PS I don't use "Response" as a variable name because some procedures use that name for one of their arguments.

The code would then be:

Me.QCTypes_Label.ForeColor = 0 'Change Label Color to Black
Dim MyResponse As VbMsgBoxResult
If Len(Nz(Me.QCTypes, "")) = 0 Then 'If QC Type is Blank
    MyResponse = MsgBox("No QC Type Was Entered," & vbCrLf & "Do You Want to Cancel QCA.", vbYesNo + vbDefaultButton2, "No?")
    If MyResponse = vbYes Then
        Cancel = True
    Else
        Me.QCTypes_Label.ForeColor = 33023 'Change Label Color to Orange
    End If
End If

 

[quest4]

Thank you for the response. I have already tried it in the Form's BeforeUpDate event and nothing. This works great on the main form, it is only the subform that is giving me problems. It is the tabstops on the subform, I think. Why it works on the main form and not the subformis what has me confused.

 

[Pat Hartman]

Are we supposed to guess what the trouble is? There is no way that the onExit event is the solution. It is simply the wrong event for this code.

 

[quest4]

Thank you again for the response, Pat. I have tried it in the Form' BeforeUpDate with and without full reference names. I have tried it everywhere and every way imaginable. On the main form, using the OnExit, as above, it works great. But in the subform it does not, that is the strange part. Instead of Cancell the recordit keeps poping up the next OnExit event for the next control item in the subform, it is like following the tabstops, til they end. Strange and the main form controls just cancel the records like they should. I hope I have made this a little clearer. Because of a procedure further into the subform there is certain controls which must have data in them or that procedure will crash. My users are notorious for trying to leave blanks al over the place, that is why I am trying to do this to certain controls. Thanks again for the response, I do appreciate it.

 

[Pat Hartman]

Did you read what I said about the onExit event being the wrong event?

The code to control the mainform MUST go in the mainform's BeforeUpdate event.
The code to control the subform MUST go in the subform's BeforeUpdate event.

To test what I am telling you, just leave the field empty on the mainform but NEVER tab into the field. Your code will not even execute so how can it be working???

When you use "cancel = true" in the exit event, you are cancelling the exit from the form, you havn't prevented the saving of the bad record. It is ALREADY saved. When you set cancel = true in the BeforeUpdate event, you cancel the record save so the bad data does not get saved.

 

[quest4]

Thank you for responding, again. yes I did read it and I retried it and in the form's Before UPDate event it does not work, with or without full references. The OnExit is was talking about is the control's OnExit event. Right now I have it so the main form works with the OnExit procedure and I have a module working with the stuff in the subform. It ain't exactly what would would like to do but it is working. At least I am forcing the users to fill in the manditory data that needs to be filled in for the automated procedure, which fills in lots of controls. I think I am going to try a function for this, at leat it will be easy to move. Thanks again.

 

[RuralGuy]

You of course can come up with maybe a dozen ways to accomplish a coding task. It may even appear to function correctly under most conditions. Pat's suggestions are the correct method to use for the Access event model. Good luck with your project.

 

[quest4]

Thank you for the response. I am running these procedures before the form is completed and the four or so controls MUST have data in them. There lies the problem, before the form is finished or the record is complete.

 

[RuralGuy]

I am running these procedures before the form is completed and the four or so controls MUST have data in them.

That is exactly what the Form_BeforeUpdate event is for!

I do not quite understand this:

...I retried it and in the form's Before UPDate event it does not work, with or without full references.

What didn't work? What did it do? What code did you use?

Again, you may do it anyway you choose. I am certain many of us here can get this code to function properly in the BeforeUpdate event if you wish to persue this approach further.

 

[quest4]

I tried a dozen different times and ways with the form's BeforeUpDate event, I tried with Me and with Forms.FormName.ControlName. I tried from a function also and always got the same results, I would tabe into one of the manditory controls and tab right out of it and on into the automatic section, Invoices. Ouch. The OnExit sub above does work, it is when I hit the cmdCancell that is reqally causing the trouble, it is not closing the formor exiting the sub it is just going on and on, but only in the subform controls. How is that for bizare? I experimented with some stops and isolated the problem a little better. Can not figure out why it is not read the Exit Sub. Oh well, I will keep trying to figure this out. thanks again.

 

[RuralGuy]

Do you care to strip out any sensitive data and post the zipped up db?

 

[quest4]

I don't mind giving you a copy of the whole thing, there is nothing sensitive in it it is a QC dbase. The problem is it is large, from code, about 5.12 Kb compressed, and it has ODBC connections to our sever and our business system. It acts flacky when I delete them. Please advise if you still want me to send it. It is quiting time here and I will check in the morning.

 

[RuralGuy]

That is pretty big for this forum and may be rejected. Go ahead and give it a try. You can send it to me directly if you want. My email is Rural Guy at Wild Blue dot Net (without any spaces). I have a high speed link.

 

[Pat Hartman]

What about my explaination of when the onExit fires did you not understand? How can you think the code works when it doesn't even run if you never tab into the field?

If what you are trying to accomplish is to get four fields entered in sequence so you can perform some calculation before moving on, then you'll need to gain a lot more control over the form. For example, you need to lock all fields except the current field, then when the user tabs out of it, you can unlock the next field, set focus to it and lock the previous field so that the user doesn't go back and remove the entry already edited.

This is really a lot of trouble when you should probably be using the nz() function to handle nulls or better still, doing the calculation in the query itself.

 

[RuralGuy]

Pat...you might consider decaf.

We'll get there with patience and your help!

 

[quest4]

I think I got it working now. Changed where it ran in the subform to GotFocus of the next control. Appears to work fairly well there. I would like to know why it works great on the main form in the OnExit, but not in the subform? That is nuts, I would assume that it would work the same in both forms. Maybe that is the problem, I assumed and you know what that means, when you "ass_u_me" I mean. Thanks again for the help.

 

[Pat Hartman]

Clearly I'm not getting through to you. You need to do some reading regarding the Access event model so you understand when events fire and why specific events are appropropriate for specific functions. The event to use for a given purpose is NOT random!

There is simply NO control level event that can reliably check for an empty control. Your code depends on the user actually tabbing into a specific field. If he doesn't do that your code DOES NOT work.

Perhaps someone else will be able to help you since I can't.

 

[quest4]

Don't get me wrong Pat, I appreciate the help and I think I understand what you are saying. I do have to running now and almost exactly what I was trying to do, but doing it in two different events for two different forms. I was only tring to understand the why for my own education. I kind of always thought what worked for one should work for the other. Guess not. Thanks again, and have a good one.