One record per tab in a subform

[Terps]

I would like to create a Project management access program. A project can have multiple phases (ie Planning, Design, Construction). I would like to have each Phase record show up as a tab. Is there a way to do this?

[Rod]

Yes. (Oh, you expect a more detailed answer. )

The title to your post has it the wrong way round, there needs to be a subform on each tab. So the mechanics are that you place a tab control on the master form and modify it to have the required number of pages. Then place a subform control on each page of the tab control - these may all be the same subform is you wish. Now you have to bind and filter the subforms to achieve your requirement.

[Terps]

Thanks for the quick reply Rod. I think I am following you. I placed the subform on the tab and placed a query on the subform Record source to pick out the PhaseName. it worked. I would like to eventually make it more dynamic (say only 3 of the 7 phases are used then only those 3 tabs show up). Thanks again for the help.

[Rod]

Sounds as though you're going in the right direction. Do you need any more help? Get it working before you try to enhance it with hiding certain tabs.

[Terps]

I would really appreciate more help. I am new to Access and I think I am in over my head. When I went to insert the same subform on the second tab/page the query stayed the same. I don't know how binding works (or what it is) but I assume it will bind the query to that tab/page only and allow a different query on another tab/page.

I can't see the subform on the second tab/page. Is that normal?

[Rod]

Terps, it sounds as though you have something screwed up and rather than play guessing games why don't you publish your db (or a version stripped of sensitive data). To do so, save it as v2007 or earlier and then zip it. Use the 'Go Advanced' reply option of this forum and follow the instructions for attachments. I'm sure you're not too far adrift.

A word about binding. Controls on a form may be 'bound' to an attribute (field) in the underlying recordset for that form. (If there is no underlying recordset then the form is unbound and you cannot bind any controls. Access synchronises the values of the control with that of the recordset and, as far as possible, with the db if the type of recordset allows it. When I say 'as far as possible' I don't mean that some changes you make are not made to the db, it's just a matter of timing. The db is updated when the current record (row) of the recordset is saved and this may not occur until you move to the next record or close the form. The attribute (field) of the underlying recordset to which the control is bound is nominated in the 'Control Source' property.

In order to have an underlying recordset, forms must be 'bound' to a query (or table which if you think about it is just a simple query). The property which defines this query is called the 'Record Source.' This property may be a table name, a query name or a string that can be interpreted as a query. It is quite possible to have more than one form with the same record source but it is more difficult to have the same form (repeated) with different record sources. In the latter case you need to specify a different record souce for the second and subsequent repititions of the form as they are loaded. (You may also change a record source on the fly even after the form is loaded. After all this is exactly what applying a filter does.)

In summary you speak of tab pages being bound to a query. This is wrong thinking. A tab and its pages is simply a device for utilising the form's real estate. Yes a tab is a control but cannot be bound to an attribute or a query, it just doesn't make sense. A tab control does have properties and values, particularly which page is currently open. The pages themselves may be used as if they are extensions of the form itself.

Now a word about subforms - just for your edification! Many inexperienced Access users misunderstand this. There is a control called a 'subform' (also subreport). This control is a container for the subform and is not the subform itself. It doesn't help that MS makes the default name for the control the same as the name of the subform, no wonder people get confused. The distinction doesn't really matter until you need to refer to the subform from VBA or elsewhere. OK, this is just an aside.

You mention that you 'can't see' the second instance of your subform. Is this in design view? Can you see the outline/border but not the content? If yes to both then this is quite normal. Design view will present only one instance of the form available for editing, the other instances are presented as outlines only.

[Terps]

Rod,
Thanks for taking the time to help me. Attached is the db.

Terry

[Rod]

OK, I can read both attachments. Give me up to a couple of days.

[Rod]

OK, the feature that is causing you problems is the tab control at the bottom of the form. The control has seven pages: Planning; Charrette; BCP; Design; Construction; Study; FF&E. You wish to place the identical subform design on more than one of these pages but presumably each subform needs to show different data. (We’ll set aside the requirement to hide certain pages for the moment.)

The data that a subform shows is determined by two things:

1. The bound record source (table or query), and
2. The value(s) of the linked field(s) between the subform and the master form.

Obviously in your case the value(s) of the linked field(s) is(are) identical for all subforms since there is only one common master form. Thus it is the record source of each subform that has to be different. But how can the record source be different when there is only one common subform? Well, you need to change the record source (or filter) of each subform ‘on the fly’ at some strategic point (event) in the program flow. There are two appropriate and convenient events:

1. The ‘Load’ event of the master form, and
2. The ‘Change’ event of the tab control.

The ‘Load’ event is perhaps the simpler of the two since you do all the programming ‘up front’ and then forget about it. However it can be wasteful. In your case you need to wait while seven subforms are adjusted and processed when you may only view and manipulate two of them. In complex systems this may take a noticeable amount of time. Using the ‘Change’ event you are using a sort of ‘late binding’ and need only process the subform about to be viewed; you can even change to a different subform if required! (Don’t attempt this until you have mastered the basics. J)

I don’t know what each subform should show. I could make guesses but the danger if I guess wrong is that we start miscommunicating. I leave it to you to define the seven queries or filters needed. Design your subform using the query/filter that is most common.

So enough of all this waffle, how do we implement this? Let me try to guide you through the process.

1. Place a seven page tab control on the master form. (Add pages as necessary.)
2. Change the page captions to whatever you wish (‘Planning,’ etc.)
3. Change the page names (‘Other’ tab in the Property Sheet) to be:
   
   1. Either the same as the captions, or
   2. ‘Page0,’ ‘Page1,’ etc. (my choice)
   
   
4. Check the Page Indices (‘Format’ tab in Property Sheet) to be 0, 1, 2, etc.
5. Choose a sensible name for the tab control such as tabPhases.
6. Save the design.
7. Design your subform, test it and save it.
8. In design view drag the subform onto each page in turn. It will only show in full on one page and in outline on all the others.
9. Check the master-child field links are correct.
10. Change the subform control names to something meaningful such as:
11. sfrPlanning, sfrCharrette, etc., or
12. sfr1, sfr2, etc. (my choice)
13. Save the design.
14. Add the record source coding to the master form’s ‘Load’ event. To do this display the master form in design view; click in the box at the top left-hand corner of the form; double click the ‘OnLoad’ event in the Property Sheet; and then click on the elipses. You should now be taken to the VBA coding window with the event handler’s top and tail already written.
15. You now need to code all different record sources similar to the following.

Me.sfr1.Form.RecordSource = “… <insert the Planning record source> …”
Me.sfr2.Form.RecordSource = “ … <insert the Charrette record source> …”
Etc.

This is the master form 'Load' solution. I'll investigate more to see whether I can implement something in your db and return it to you as an example but as I have stated I am wary of guessing exactly what you want.

[Rod]

I've played around with your db and the tab control is working (after a fashion). Your existing data is somewhat corrupt and your variable naming is inconsistent. Anyway see what you think. The comments subform only shows when there is a phase record.

Project sketch6 2002-03.zip

[Terps]

Rod,
Thank you soooo much! I could not have done this without your help.

Terry

[Rod]

My mod to your db is fairly crude but has the benefit that it works. Glad to be of help.