Referencing a control on a subform (Acc 97 sr2 on 95b)

Topic

New Reply

I have a subform on a tabbed contol that shows a continuous form. I want to have another tab set the reference for another subform from a selected control on the first subform.

mainform shows customers
subform1 shows jobs by customer
subform2 to show delivery dockes by jobs

these are the exixting relationships.

Ideas anyone??

Reply | Quote

Replies

Tab controls have no impact on the way you refer to controls; there is no difference between the way you refer to a control that is on a tabbed page and a control that isn’t.

You *do* have to include subform names if you want to refer to controls on them, See this page on Dev Ashish’s Access Web for a comprehensive overview of controls on subforms.

Note: thanks to Support4John for pointing out this useful link

Reply | Quote

Sorry, but you are missing my question

I have a tab control with subform1 on page 1 and subform2 on page 2

If subform1 is set to show continous form, and I select the “txtJob” box on a particular record on the subform, if I then reference me!subform1.form!txtJob from the mainform, will it return the value from the selected record.

I want to use this value to set a filter and requery subform2 when the tab that shows subform2 is clicked.

Reply | Quote

If you want to do that kind of thing automatically, the second subform could be nested within the first subform – we do that pretty regularly. Othewise, you have to make the second subform unbound and then set filters for it on the fly. If you try to use the regular subform links, things tend to get screwed up when you move around on the form – though it can be done with great care if you create dynamic SQL that changes as records are selected in subform one. Hope this sheds some light on a couple of approaches.

Reply | Quote

Wendell,

Through sheer luck and blind blundering, I have gone down the path of an unbound subform2. I am now stuck trying to define the filters for the subform, so pointers would be appreciated.

My main form is set to customers
on tab 1 i have subform1 that shows all the jobs for that customer link is acc to acc and shows jobno
on tab 2 I have subform2 that I want to show just the times of the job chosen in subform1. flter by jobno picked from above

I have worked out how to get the number of the selected job, but don’t know how to set the filter using this value so that subform2 shows me the correct record.

I am trying to bring seperate existing forms from different “modules” (workshop, accounting, sales) together for ease of viewing by using a tab control and hopefully filtering on the fly when the tabs are changed

Reply | Quote

Allan,

Have a look at the attached zipped demo database. It contains a form and two subforms on tabbed pages. The first subform is linked to the main form. The second subform is linked to the first subform by means of an invisible text box on the main form. There is no code at all. The idea is from the Solutions database on the Office 97 Pro CD.

HTH,
Hans

Reply | Quote

I noticed this tip when trying to resolve a problem synching records on a tab controlled subform to a record in the main form. The tabcontrol contains 7 subforms, one per tab, for each day of the week. the subform is linked to the main form with a ID number and I need to display the info across a week consistently. I put in a button to add a new week to the subforms; this button goes to each subform and adds a new record then goes back to the Monday subform on the tab control. I have some code that populates the rest of the week dates based on the monday date and was not able to get the new records on the other day subforms to show up consistently after adding the new record.

I inserted the hidden textbox on the main form to reference to the MondayDate on the subform and now the new record information shows up. (previously the new record for Monday shows but the other dates don’t necessarily display the new record even tho it is there) First question: how does this even work? You’re not setting up any filtering or queries or anything, just displaying the subform based on a hidden field on the main form.

I am also trying to accomplish the following: navigate to a given record in the Monday subform. Then click on the tab for some other date and have the appropriate record show up. For example, a single client has three weeks of data in the subform. When you go to the second week in the Monday subform, the Tuesday subform doesn’t follow along. I added the navigation controls to at least make it easy to find the right date but I want to do this automatically. I tried to add a hidden field to the Tuesday subform referencing the WeekIDCase of the Monday subform but that didn’t do anything – displays #Name?

The overall structure is:

ClientID –> ID for main form info
WeekID –> ID for any weekly information for a ClientID
WeekIDCase –> ID for a specific Week in the ClientID-WeekID selection

Which looks fine to me; the problem is this Tab Control and getting the subforms to respond to changes in the WeekIDCase when changed in the first Subform. I have built each subform on top of a single query so the source is exactly the same; each subform simply displays a subset of the available info in the source query.

Any suggestions? TIA!

Reply | Quote

Steve,

About your first question: subforms are linked to the main form by setting master and child link fields (in the Data tab of the Properties window).

You can’t link a subform directly to another subform on the main form. But by putting a (hidden) text box on the main form whose control source refers to a control on the first subform, and by using this text box as master link field for the second subform, you are effectively linking the second subform to the first one, or synchronizing it to the first one.

Reply | Quote

thanks for the explanation. still a little fuzzy on what it does as the hidden field on the main form isn’t named anything pertinent to the master/child links. also the secondary subforms use the same Master/Child relationship as the primary subform, which is appropriate as a given client can have the same info (dates and such) on their subforms so the subforms need to be tied to the client ID. I guess, abstractly, the SQL for the secondary subform displays would be

SELECT * from qryWeeklyActivities WeekIDCase = [MondayDate subform].WeekIDCase

with the ClientID/WeekID link already set, this is the needed refinement.

In this case you see I need to reference the MondayDate subform directly on the other day subforms but haven’t had much luck so far. well, must keep banging away…I am concerned with constructing a GUI that gives the end user navigation based on a week chosen and not confuse them with the details.

Reply | Quote

In the demo database, the hidden text box is named JobID, and this is used as Master link field for the second subform sbfDates. JobID is not a field in the record source of the main form.

Reply | Quote

I think Hans has you on the right track – at least it’s the approach I would take. The filter approach gets very unwieldy, and the SQL strings get very long as you have to reference the control on subform1 with a full “path” approach. The nested subform approach also means you don’t have click on the second tab to see the related records. The problem is that it may take a fair bit of real estate I suppose, but if you limit subform to displaying a single record (don’t make it a continuous form – make it a regular display form) then you can display several records in subform2 before you exceed a typical monitor configuration.

Reply | Quote

Thanks for the example and all that goes with providing it!

Reply | Quote