How do I Navigate to Next Tab Automatically

[Rachel Turner]

Hi,

I am trying to create a data entry form for a large database. There is a parent form with 6 forms linked with a one-to-one relationship (due to a large number of fields) and several tables linked with a one-to-many. All have auto update/delete checked. The date entry form uses tab control (across 11 tabs) and subforms for some of the tables. I have two problems...

Firstly, ** Edit Form Doesn't Display Existing Data **

Secondly, when using the tab key to move through the fields in the table, when I get to the end of a tab control, instead of moving to the next tab control for the same record, it moves to a new record on the same tab.

I am fairly new to access and not familiar with all the code etc behind it, so I would be grateful for any advice!

Thanks

[NeoPa]

I would suggest you take this a number of steps back first. What you are planning is not a usual approach and is quite flawed in concept. Due to the unusual nature of your approach, and the relative lack of detail in your question, it's hard for someone to guess at what you want with enough detailed accuracy to allow appropriate alternatives to be suggested. What you describe is the normal behaviour of subforms.

An alternative to consider would be a single form with tabs, but having bound fields on each tab, rather than separate sub-forms.

That said, I expect the best advice I could give would be to revisit your overall design, starting with the data structure (I can think of no situation where one-to-one relationships would be desirable. There may be possibilities here, but very few I expect).

Following closely behind that would be to try out your concepts with a structure which is considerably simpler until you are sure they can handle what you need. Only then build the extra complication into your design. Otherwise you should sensibly expect all sorts of related (to the complexity rather than the concept) problems.

[Rachel Turner]

Hi, thanks for your reply. I'm sure the design is not ideal as I am a beginner in Access! The database is for data from a very long household survey, and there are >255 fields in the main table so I had to split it into at least two tables with one-to-one (perhaps it would have been better to stick to 2?). I am not sure how I can get around the problem of one-to-one tables with so many unique fields?

I have a single form with tabs, and many of the fields are in bound fields on the tabs. However I could not add all of them in this way (I assume this was again becuase of the large number of fields). All the one-to-many are in subforms. I should have said that the tab key issue is not with the subforms but with the bound fields which are the last fields on each tab (I appreciate that that is the normal behaviour in subforms).

Grateful for any advice!

[NeoPa]

First of all, my original advice still applies. You will still find the whole process more reliable if you remove many of the complexities involved with such a complicated setup. However, I'll leave that with you for now and consider the distinctly non-newbie problem's you're faced with.

I would first consider using a BeforeUpdate event procedure for the form which cancels any update except those that are triggered in the way you choose. The obvious solution here would be to provide a Command Button control that the user must click on to save any record.

Next you'd need to determine what happens, IE. which events are triggered, when you trigger a save (that is cancelled) by moving past the last control on the tab. When you can reliably handle this situation you can place code in the relevent event procedure which passes control to the next page instead.

Does this sound like it makes sense?

[Rachel Turner]

Thanks - this makes sense in theory but I have no idea how to do it... will go away and do some reading! I did have a similar but simpler database which had just one main form and several subforms, and the tab controls all worked fine. I also had a previous version of this database with one form plus subforms, which I had created in the same way but had the problem with the tab key - I have been through all the properties and built it up from scratch again but I cant figure out what's different. Thanks for your help...

[Mihail]

In my opinion is almost impossible to have an object with more than 255 properties.
I think that this is also the reason of Access designers to limit the number of fields (at a very large range: 255).
So, every time I hear about, I suspect a (very) poor design of database.

Take a look here:

http://bytes.com/topic/access/insights/585228-database-normalization-table-structures

and see if you don't change your mind about store more than 255 fields (properties for a SINGLE object, in fact) in a single table.

[sierra7]

Rachel,
Obviously I agree that you have a highly complex situation and endorse the previous advice to simplify if possible.....

But to address the mechanics of where you are stuck, Move between Tabs, there are some variations which may seem complicated due to your complex situation. But there is logic so stay with me..

First you understand that you have Parent form. It seems that some of the fields from the Parent recordset may be grouped on individual tabs because of shortage of space.

This is the most simple case, where the next field you want to move to belongs to the same recordset i.e. not in a new sub-form. You just put some code in the Lost Focus event of the last field on your current tab to set focus to the next field you want to visit. Even if it is on a different tab-page, Access will find it and put the focus there (so long as the field is Enabled).

If you are moving from a field in the Parent recordset to a field in a Sub_Form on another tab, there are three options;

1. Move to the sub-form control
2. Move to a control within the sub-form
3. Move to the Tab Page

The first option is the simplest, just setfocus to the sub-form. The preset Tab-Order within the sub-form will then take over.

If you want to move to a specific field within the subform then you need the second line in the example below. This assumes that PhonesCustomers is a sub-form in a tab-page listing the one-to-many contacts within a customer's organisation. First set focus to the sub-form then move the focus inside it to the control you want.

Private Sub ExchangeRate_LostFocus()
Me!PhonesCustomers.SetFocus
Me!PhonesCustomers.Form!PhoneNumber.SetFocus
End Sub

The third option does much the same as the first. The tab-control has a name of its own (I have one called TabCtl72) and each tab has a Page number starting from 0 (zero). You might need to use this option in a Read-Only situation, where none of the controls in the sub-form will accept focus.

Private Sub ExchangeRate_LostFocus()
Me.TabCtl72.Pages(2).SetFocus
End Sub

The next problem is when you are in one sub-form and want to move the focus to another field in another sub-form. Here you have to issue three seperate commands, first to move up to the Parent form from within the first sub-form. Then to move focus to the next sub-form; and finally to a specific field.

Private Sub PhoneNumber_LostFocus()
Me.Parent.SetFocus
Me.Parent!NextSubform.SetFocus
Me.Parent!NextSubform.Form!NextField.SetFocus
End Sub

Note that all three commands are directed via the Parent form.

S7

[TheSmileyCoder]

Could you please try to describe what the general purpose of your application is, what it is you need to achieve with it? What kind of information are you gathering, and for what purpose.

[Mihail]

Hi S7 !
Forget me, but I can't understand how is fired the _LostFocus event for the sub_form.

[NeoPa]

Mihail:
I can't understand how is fired the _LostFocus event for the sub_form.

That was never mentioned in S7's post. Only the last control on the form, that is within the sub-form, needs it's LostFocus event handled.

[Mihail]

My bad. Thank you, NeoPa !

[Rachel Turner]

sierra7 - thanks so much for your help! problem resolved!

[NeoPa]

At this stage it would make sense to select the post that most helped you as Best Answer for the thread. As your thanks were reserved for S7 I assume it will be one of theirs, but only you can do that.