Focus shift issues: Subform detail to second subform

[EdaxFlamma]

Good afternoon all,

I've been digging around trying to figure out how to adjust my tab order through my various subforms within a given form and have been having some troubles. Below is a rough skeleton of my form:


We have:
frmShiYield as the main form
subfrmFirstBreak - first column from left
subfrmFirstBreakDetail - detail for first column from left (datasheet)
subfrmSecondBreak - second column from left
subfrmSecondBreakDetail - detail for first column from left (datasheet)
subfrmThirdBreak - third column on left


subfrmThirdBreakDetail - detail for first column from left (datasheet)

My form flows fine up until I reach the last value in my first subform detail (red). I am unable to shift the cursor focus to the field "SecondBreakSoakDate" (orange). I expect once I can solve this, green to blue will require a similar fix. I'd ideally like to be able to transfer to a new record on the main form once I have filled in the necessary values (near purple) but I'm not going to be greedy. Ideally I'd like all of this to be done without the use of a mouse.

From what I understood, my research suggested that I would need to bind the focus switch to a particular key press in order to achieve this. While F5 isn't the most efficient key from a data entry perspective I used it so as to not change too many variables while trying to understand the code.

I will preface this by saying that my VBA knowledge is quite lacking and, that I shamelessly stole someones code and played Frankenstein with it. During my research it also seemed that I needed to reference the parent forms in order to change focus between subforms.

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
        Case vbKeyF5
            Me.Parent.SetFocus
Me.Parent![SubfrmFirstBreak].SetFocus
Me.Parent![Copy_of_frmShiYield].SetFocus
Me.Parent![SubfrmSecondBreak].Form![SecondBreakSoakDate].SetFocus
    End Select
End Sub

While the code appears to run, all I end up with is the F5 key moving my cursor to the first tab within the same subform. Have I misunderstood how this process should work? Answer: I have, this is what F5 appears to do anyway...

Unfortunately I feel as though my understanding of the actual steps that are occurring is preventing me from troubleshooting this effectively. While I won't say no to quick fixes, does anyone have any recommended resources that might help me become a bit more self-sufficient?

Thank you for your patience,
-J.P.

[June7]

Is this actually a form/subform/subsubform arrangement?

If you want to provide db for analysis, follow instructions at bottom of my post.

Date is a reserved word. Should not use reserved words as names.

[EdaxFlamma]

It is a Form, subform, subsubform as best I understand.

I have attached a DB fragment below and have removed the reserved word.

Thanks,
-J.P.

[June7]

I am not sure a separate table for each break is optimal. Could be one table with another field for the break sequence. Then one table for break details.

I expect at some point you will want to compare the 3 sets of break details with each other and/or aggregate (average, etc) their data. The aggregation calcs might require use of UNION query to get the records into one dataset.

If the table structure were redesigned, I think could still have the 3 subforms arrangement by setting filter criteria for the subforms to parameter on BreakSeq field. Set DefaultValue property of textbox bound to that field and set the textbox as Locked Yes and TabStop No so users can't change value.

The subsubform could be set for continuous view and controls arranged to look like datasheet. This will allow placement of a command button in header section. Normally, a command button can be activated with shortcut key defined by & character in button caption. For instance, if caption on button is: &Go To Break 2, the G will show with an underscore and shortcut key combination of Alt+G will trigger the Click event.

This code behind button works but only if the button is mouse clicked. Apparently the Alt+ shortcut doesn't work from subforms - odd thing is it does trigger the code but focus does not change.

Private Sub Command7_Click()
Form_Copy_of_frmShiYield.SubfrmSecondBreak.SetFocu s
End Sub

Note that the code only sets focus to the subform container control and the TabOrder property causes focus to land on the date textbox.

[EdaxFlamma]

I am not sure a separate table for each break is optimal. Could be one table with another field for the break sequence. Then one table for break details.

Very good point. I should have caught that.

I expect at some point you will want to compare the 3 sets of break details with each other and/or aggregate (average, etc) their data. The aggregation calcs might require use of UNION query to get the records into one dataset.

If the table structure were redesigned, I think could still have the 3 subforms arrangement by setting filter criteria for the subforms to parameter on BreakSeq field. Set DefaultValue property of textbox bound to that field and set the textbox as Locked Yes and TabStop No so users can't change value.

I'll try to implement that and see how it goes. You are correct, I will be working with the break data in those ways so I will have to brush up on my union queries prior to reporting. I should be able to handle that.

The subsubform could be set for continuous view and controls arranged to look like datasheet. This will allow placement of a command button in header section. Normally, a command button can be activated with shortcut key defined by & character in button caption. For instance, if caption on button is: &Go To Break 2, the G will show with an underscore and shortcut key combination of Alt+G will trigger the Click event.

This code behind button works but only if the button is mouse clicked. Apparently the Alt+ shortcut doesn't work from subforms - odd thing is it does trigger the code but focus does not change.

Private Sub Command7_Click()
Form_Copy_of_frmShiYield.SubfrmSecondBreak.SetFocu s
End Sub

Note that the code only sets focus to the subform container control and the TabOrder property causes focus to land on the date textbox.

This part is all brand new to me and is where my vision goes fuzzy. I think I will need to try it on my own before I can comment more. If I understand correctly, this can achieve what I was looking to accomplish mechanistically but the reason that it does so is based on the fact that this selects the subform itself and then defaults to tab order to land in the correct box. However, the command itself cannot be run from a keyboard alone; it relies on a mouse click. While ideally I would prefer the user to be able to enter data and navigate using only one kind of peripheral I may need to explore other options.

Thank you for taking the time to investigate this for me. It seems I was going about it the wrong way. I will work on properly normalizing this section, apply the suggestions and report back.

[June7]

I went back and tried the KeyDown event. The event is not triggered. I even tried event in the subsubform. F5 just moves focus to first record of subsubform.

If you restructure data, should eliminate need for UNION query.

[EdaxFlamma]

http://stackoverflow.com/questions/1...m-to-main-form

This is where I began to steal code fragments. When I find my other site that talked about having to shift focus back to the parent first I'll add it here.

[June7]

This is an issue I have also been faced with and gave up. I tried a Tab control with shortcut Alt+ on the page captions - doesn't really work either. When focus is on main form, Alt+ will go to a tab page but doesn't work when focus is on a control sitting on the page. And can't get back to page 1 except by clicking on it. Very frustrating.

[EdaxFlamma]

While I'm curious if there has been a work around, given your difficulty with it I won't pull my hair out over it.

That having been said, you did help me condense my tables and that works perfectly! Nine or so tables is down to three. It's one of those things I feel like I should have seen but didn't - that and the "date" issue.

[June7]

You might want code that prevents moving to second and third break forms until a record in first is committed. A common approach is to set controls as invisible or disabled until some condition is met. So maybe code in first subform AfterUpdate event would enable second subform and code in second subform would enable third subform.

[EdaxFlamma]

My only concern there is that there can be times when there are no first or second breaks as breaks are based on a date range after soaking. (very weird process...)

[June7]

Just curious, what are you breaking?

[EdaxFlamma]

It's an agricultural product. We refer to our crop cycles as breaks.