Reset the active control value of a subform when switching to another subform

[Nangasaur]

Access 07
Windows 7

I've trapped the SHIFT+TAB and TAB keystrokes within the keydown event of a subform to behave appropriately (switch back and forth between subform1 and subform2). However, my current problem is this:

Subform1 and Subform2 have 3 textbox controls each. If I am on control2 of subform2, and mouseclick to subform1, then tab to control1, then control2, then control3 and tab again to appropriately switch to subform2, the focus is always set to the previous active control of subform2 that I clicked out of. This only happens if I mousclick out of subform2. Tabbing works just fine.

I would like that focus to ALWAYS go back to control1 of subform2 when I click out of subform2 to subform1 and tab back to subform2. Confusing enough? I hope not.

I'm fairly proud of myself for figuring out a way to trap these keystrokes to move between the two subforms properly, rather than using the OnExit event, so please no floggings for doing this the complicated way. I have specific reasons for why I need this to behave accordingly. Mostly being that I don't want people to be able to go to control2 of subform2 if control1 of subform1 has nothing in it.

If any advice is available to get the focus to reset to control1 after mousclicking out of subform2 and tabbing back to subform2, I would be very grateful.

Here is my keystroke trap code from the OnKeyDown event of Subform1:

Private Sub Form_KeyDown(Keycode As Integer, shift As Integer)
If (Keycode = vbKeyTab) And ((shift And acShiftMask) > 0) Then
 
    If Screen.ActiveControl.Name = Me.Vest_New_PIDtxtbox.Name Then
        Me!Vest_New_Date_Completedtxtbox.SetFocus
    ElseIf Screen.ActiveControl.Name = Me.Vest_New_Due_Datetxtbox.Name Then
        Me!Vest_New_PIDtxtbox.SetFocus
 
    End If
 
End If
If (Keycode = vbKeyTab) And ((shift And acShiftMask) = 0) Then
    If Screen.ActiveControl.Name = Me.Vest_New_Due_Datetxtbox.Name Then
 
        Keycode = 0
 
        Parent!Helmet_Dates_Quick_Updater.SetFocus
 
 
    End If
 
End If
End Sub

 

[vbaInet]

I don't get why you want to trap keystrokes for this thing to work. It seems a bit convoluted.

It might be a matter of changing the order of tabs? Check the order to ensure that control1 is set as the first control.

 

[Nangasaur]

Was my first thought to check the tab order, and it was all correct.

Let me try and describe better what is happening:

My MainForm is "Quick Updater"
Subform1 is "Vest_Dates_Quick_Updater"
Subform2 is "Helmet_Dates_Quick_Updater"

A record is selected via the MainForm. Subform1 and Subform2 control data from two different queries, and their recordset is defined by the record I select from MainForm.

Subform1 has three textbox controls: Completed, PID, and DueDate.

Subform2 also has three textbox controls: Completed, PID, and DueDate.

Subform1 controls the records from my Vests table.

Subform2 controls the records from my Helmets table.

There's other underlying code in the form that doesn't allow people to exit subform1 unless ALL THREE controls are filled out properly, OR, ALL THREE controls are empty, no exceptions. Completed has to be filled before PID can be filled. PID has to be filled before DueDate can be filled. This is to ensure a record is not created without incomplete data (incomplete being only 1 of 3 or only 2 of 3 fields are filled, instead of 3 or none at all).

If Subform1 is filled out properly, or no data at all, the user can then tab to Subform1, and begin filling out the three controls there, following the same rules as Subform1: All three fields out properly, or none at all.

I'm trapping those keystrokes as a preventative measure to keep people from moving focus away from those subforms while they are filled out improperly.

HOWEVER, if someone is in Subform2, and notice that they made a mistake in Subform1 (as in, entered in the wrong date or made a spelling error in PID), I need them to be able to go back to Subform1 by clicking into it and correcting their typing error, then tab back to the first control on Subform2 again.

The current behavior is as follows:

Subform1.control1 is good. Tab to Subform1.control2. Subform1.control2 is good. Tab to subform1.control3. Subform1.control3 is good. Tab to Subform2.Control1.

That is exactly the behavior I want and am expecting to occur. The behavior I am trying to fix is as follows:

Subform2.Control1 is good. Tab to Subform2.Control2. User notices they typed "PA1234" instead of "PS1234" in Subform1.Control2, so they mouse-click back to Subform1.Control2. User corrects data in Subform1.Control2. Tab to Subform1.Control3. Tab to Subform2.Control2.

The bolded part SHOULD do this: Tab to Subform2.Control1.

So, what is happening is Subform2 is remembering that it's most recent focus was on Control2, and when I tab back to Subform2 again, it goes to Control2 instead of Control1.

I really, really don't want it to do that.

 

[vbaInet]

I get the picture now. I think you should forego this keystroke capturing business and consider using the On ENTER (not Exit) event of subform 2. You would also use Me.Dirty to check whether a record was changed AND check whether Control1 has a valid value.

You will waste too much time on debugging/testing this keystroke method because I don't think you will always get stable results. Maybe that will come in handy for other purposes in the future but certainly not for this.

 

[Nangasaur]

I get the picture now. I think you should forego this keystroke capturing business and consider using the On ENTER (not Exit) event of subform 2. You would also use Me.Dirty to check whether a record was changed AND check whether Control1 has a valid value.

You will waste too much time on debugging/testing this keystroke method because I don't think you will always get stable results. Maybe that will come in handy for other purposes in the future but certainly not for this.

I ended up fixing it just a moment ago due to a syntax error that strangely wasn't giving any error message. A little help from the mvps.org Forms reference card got me the correct syntax I needed in switching between several subforms on a mainform. I also had to add

Parent!Helmet_Dates_Quick_Updater!Helmet_New_Date_Completedtxtbox.SetFocus

...directly under the line to set the focus to the form itself in the KeyDown event, and it's setting the focus properly now.

I had played with the OnEnter event of the form before I switched to the KeyDown event because it was giving me other problems that seemed like more of a headache to fix than to change my method. And OnExit...well...that wasn't working right at all, of course.

Even though you didn't give me the direct answer, you still helped in making my brain switch from thinking I didn't have the right method to thinking more about my syntax in changing between the subforms. As always, thanks for the needed push in the right direction, AWF!

 

[vbaInet]

Just for the fun of it let me show you what I would have done:

On Enter event:

If Me.Dirty and len$(Control1.value & "") <> 0 then
     Control1.setfocus
end if

Something along those lines. It will need some tweaking but it's a base.

Nevertheless, glad you got it working

 

[Nangasaur]

Thanks I'm learning, slowly but surely. I'm not a young kid anymore, so it takes me a bit to just dive head first into this and pick stuff up. If I can't read it and see the exact logic in what the code is doing, I'm totally lost in the sauce. So things like "len$" makes no sense to me at all...yet

 

[vbaInet]

... totally lost in the sauce.

Just like stock cubes that get lost in the sauce hehe!!

There's Len and LenB and Len$. I wouldn't go into much detail but Len is function that returns the length of a string and Len would take variants whilst Len$ is for strings. If you use Len$ that takes a Null value as parameter, it wouldn't like it. I was sure my argument was a string because I concatenated it with the empty string (i.e. notice the double quotes) that was why I used Len$. Len$ performs faster computations than variant equivalent (Len).

Let's get you up to scartch with some of these functions. Here's a good resource:

http://www.techonthenet.com/access/functions/index.php

Remember, if there's something you're not sure of:

1. Go to the VBE editor in Access
2. Press F1 (for the Help Files)
3. Type what you're looking for and hit Enter