Determine next control on a form

I have a unbound field on a form that I use to lookup a record in the database or to enter a new record.  I do not want the user to be able to leave it blank.  I can easily add a check in the "on exit" of that field.  The problem is that if they press the close button, I would like to skip the validation and just exit the program.

Is there anyway to determine the "next control"?  This would be the control that caused the program to exit the unbound field described above.  If I knew the next control, I could skip the validation if the next control = "btnClose".

This is basically the opposite of screen.previouscontrol.  I need something like screen.nextcontrol.

Currently, the only way a user can exit the form when the lookup field is blank is to press the red x in the upper right hand corner.  I would prefer to let them close via my close button.

In the on exit routine I would add the following logic to accomplish what i am after:
if NextControl = "btnClose" then
   Me.undo
   exit sub
else
   validate field
endif

Any help would be greatly appreciated.
compuzak1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
How about:

If Screen.ActiveControl.Name = "btnClose" Then  '  whatever

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:


If Screen.ActiveControl.Name = "btnClose" then
   Me.undo
   exit sub
else
   validate field
endif
0
compuzak1Author Commented:
I need to know the NEXT control while I am still in the exit of the lookup field called "unbLookup".

Private Sub unbLookup_Exit(Cancel As Integer)

    If screen.nextcontrol = "BtnClose"
         Me.undo
         exit sub
    else
          validate field
    end if
End Sub

The activeControl.Name at that point is still "unbLookup".
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, there is no such thing as Next Control.  The only way Next might apply would be the next control in the Tab order, but that's pretty vague.

"This would be the control that caused the program to exit the unbound field described above. '
Controls don't cause 'exit'.  Users or vba code does.  Further, how can you know what the user might do ... 'next' ?

mx

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
How about using the Form UnLoad event - which can be cancelled. This will trigger if user clicks X button or a docmd.Close acForm,me.name  occurs

Private Sub Form_UnLoad (Cancel As Integer)

       ' your code

End Sub

mx
0
compuzak1Author Commented:
I am not explaining this correctly.

If control is currently in field unbLookup and the user clicks the close button on the screen (btnClose), this triggers the unbLookup_Exit event.  In this subroutine I have logic to prevent the user from entering a blank value for field unbLookup. If btnClose is clicked, I want to skip this error check and let the program close.  On all other cases, I want to validate the field to ensure it is not blank.

Hope this makes sense, hard to explain (at least for me).
0
compuzak1Author Commented:
Form unload won't work because the system won't ever leave the unbLookup field since it is blank.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"and the user clicks the close button on the screen"

Guess I'm confused.  I you have an actual Command button, say with this code:

Private Sub btnClose_Click()
    Dim x
    x = Screen.ActiveControl.Name
    MsgBox x
    DoCmd.Close acForm, Me.Name
End Sub

 ... and the Focus is currently in that unbound Text box ... and you click the button ... the ActiveControl at that moment is the button.

mx
0
compuzak1Author Commented:
On my system (Access 2003 with os XP), the events trigger in the following order:

Control is currently in unbLookup_Exit
I click btnClose

unbLookup_Exit (triggers before close event)
btnClose_Click

Since the validation routine is in unbLookup_Exit, it never leaves the unbLookup_Exit (event is canceled since field is blank and I don't want to allow a blank value).

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, if you remove the code from the Exit ... then btnClose will be the ActiveControl ... I tested that.

mx
0
compuzak1Author Commented:
Yes, but then the form would allow the user to enter a blank value for the unbLookup.  I only want to allow a blank value if btnClose is pressed.

Thanks so much for all of your time.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Are there other controls on this form? Or just the text box and close button?

mx
0
compuzak1Author Commented:
There are about 50 controls on the form (45+ field, 5 butons)
0
compuzak1Author Commented:
Actually 36 fields and 5 buttons to be exact
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok.  So, what is the exact specific case you are trying to cover/prevent  .... relative to this one text box and data / no data ?

Have you considered using the Form BeforeUpdate event (assuming there a some bound controls) ?

mx
0
compuzak1Author Commented:
I can verify it during Before Update, but it is a key data field and they really should enter this field before entering any other data.  If I can't check it until the update, it will work ok, just not exactly per specifications.

I will either do that or never let it out the field unless it is non blank or the red x is clicked.  I will chose either method if there isn't someway to match my exact specifications.

I have a routine that creates a new record if the lookup key is not already in the table and if it is already in the table it recalls that record for modification.  It currently allows them to leave it blank, and if they go back to the field and enter in a new code it will try to lookup again or create a new record.  I can program around this of course, but it seems cumbersome.  Kind of surprising to me that Access does not have a way to look at screen.nextcontrol (they have current and previous).  This would be very helpful when clicking buttons to know what is next.  Just my 2 cents.  If this last rambling paragraph does not make sense just ignore, sorry.

If there is no other way I will just accept your last response at 3:12pm.  Let me know and thanks for all your help.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"but it is a key data field and they really should enter this field before entering any other data."
Well, that can be handled.  Do not Enable the other controls until this control has the required data.  I have a trick for doing the enabling/disabling of multiple controls if you need it.

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
compuzak1Author Commented:
Ya, I thought about that, but there are lot of fields.  I can iterate through fields with a loop, I think, but haven't done so before.  Also, how do I know when to turn on / turn off.  Yes, please post the trick on enable/disable.  My understanding is to keep the questions to one point, so I will post another question regarding enabling / disabling of fields.  I think that is the proper etiquette.
0
compuzak1Author Commented:
Thank you for all your help.  Amazing how fast you responded and kept with me even though I wasn't making sense to you.  Thanks!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ... you we making sense ... I just missed a couple of points in the original Q ...

" I can iterate through fields with a loop, I think, but haven't done so before."
Nah ... no need.  Here is a more elegant, simple an FAST approach:

Add a tab control to your form with the following properties:

One Page (tab)
Style = None
Back Style = Transparent

Put all of the controls on this tab.
You can now enable / disable the tab control - and subsequently all controls on it with one line of code

Then, based on some condition:
Me.YourTabControlName.Enabled = True  ' False

This is VERY fast also.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Note ... the Tab enable approach my require a layout modification regarding controls, since the tab control is rectangular ... but I've always found a way to do this, sometimes adding 2-3 tab controls to accommodate a special layout.

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.