Sub-form tab navigation 2

[jgoodman00]

Hello there,
I have created a database within microsoft access, & want to use subforms as part of the data-entry method. However, it would appear that subforms want to work when related to data that is related by a one-to-many relationship. The relationships i have created are one-to-one & must stay this way. Whilst this itself does not cause any significant problems, entering data does. As data is entered in each field & the user tabs to the next field this falls over at the subform point. It tabs into the form, & the user can continue to tab UNTIL the end of the subform. At this point, instead of returning to the main-form, it simply cycles through the subform infinetely. Does anyone have any idea how i can make it tab back into the main-form upon completion of data entry in the subform.

Thanks

 

[Rohdem]

On the after update event of the last tab stop, you could set the focus to a field on the main form:

forms!mainformname!textboxname.setfocus
Mike Rohde
rohdem@marshallengines.com

 

[jagilman]

Use the exit event, you will get results even if the user didnot actually enter any data in the last field.

John A. Gilman
gms@uslink.net

 

[RickSpr]

You really don't need to, and shouldn't, be using subforms for this purpose. Subforms really are intended for the "many" table in a one-to-many. I have the impression you decided on the subform approach in the belief that you can only use one table per form. But that's not true.

What you ought to do is create a query that includes both (or even three or more) tables and their relationships, then use the query as the record source for your form. The fields in the query will all be updatable in most circumstances, especially when the relationships are one-to-one.

There's only one thing to be careful about, and that's which table provides the copy of the join fields. To preserve updatability of the query, you want these to come from the "parent" table in the relationship. Rick Sprague

 

[rafe]

Rick,

I really like the multi-table query for a source on a form idea & I'm definitely using it. However, I have been using a 1 to 1 relationship for some of my subforms. In some (definitely not all) cases I do think it's a good idea. Let me explain...

I happen to use a few subforms as objects (which it is anyway) with a standard interface to both the user & programmer. This standardized subform is used throughout the DB. It provides the object oriented advantages of encapsulation, standardization, & ease of maintenance. Change it once & it changes everywhere etc.

Now if you're only doing a one time subform I couldn't agree with your solution more but these have standard subforms have really helped me maintain my DB.

rafe

 

[jgoodman00]

Almost identical to myself then. The reason I wanted to use a subform is because we have over 20 forms, & they all have some fields in common. Therefore it was logical to incorporate the common fields into a subform, because when the forms are updated, the common fields would only have to be updated once......


P.S. I have solved this problem now as well...

 

[RickSpr]

Rafe, that sounds like a pretty clever idea, actually. So is the subform the only control on the main form, typically? I was thinking you could leave the subform borderless and make it look indistinguishable from a custom-built main form. But there are certain differences that would still show in the user interface, like having to use Ctrl-Tab to get out of a subform, and the way an Undo on the subform wouldn't back out changes on the main form. Do these cause you problems? Rick Sprague

 

[rafe]

Rick,

Typically no, I have other controls in the header. And I leave the border etc. as a visual cue to grouping of function &c. I actually get around the tabbing issue by avoiding it.

Let me explain...

The college has some of its paper documents perform multiple functions depending on codes placed in certain fields of the document. Some of these fields change infrequently but enough to have them available for changes. Aside from one or two fields including these few codes, the header prompts are pretty much the same, the real difference is in the line item data.

The controls for the header information of the document that pertain to the function of the document I place on the main Access form. The infrequently changed header information common to all variations of a document I place on the subform. I “blue-out” these sub-form controls… that is I have set the background color to light blue as a sign to the user that they can change it but it’s not a tab stop -- avoidance but because they are infrequently changed it works here.

I setup, validate, read/write data, or whatever, for the controls/data on the subform via Public function (or sub) calls using the object-oriented dot notation. The undo issue isn’t a problem because I don’t attach the controls to data directly I only do it thru dot calls; so a simple reset will work fine.

This design has really helped speed up changes to the DB interface… the only annoying part is making sure each Access main form has room in the header section for the rather blocky subform(s).

I hope this is clear.

rafe

BTW: I’ve just used the multi-table query form source idea. I like! & Why didn’t I think of that?!

 

[jgoodman00]

For what I am working on, it is necessary not to be able to distinguish between the mainform & the subform....As for the tab order, you can simply use GotoControl to tab in & out of subforms....

 

[jgoodman00]

I have discovered a slight problem with using the subforms though. Onscreen, the form looks perfect, but a gap seems to be introduced after the subform across all columns. (Please see

http://www.angelfire.com/sports/f1pictures/index.htm

for two pictures of what I am on about)....Does anyone have any idea what causes this, & more importantly how I can resolve this.

 

[rafe]

because i don't print the forms, i feed them to reports this hasn't happened to me. i'll see if i can replicate this.

 

[jgoodman00]

Ok, to solve the problem I previously documented is very simple. Open the subform in design view, & select the section detail. Change 'Can Grow' to No, & this will solve the problem. This function automatically adjusts the subform size at print-time, but not at normal view-time, so it only comes out when you print the form.....