Multiple Page form but same code? 2

[freddydog]

I have a form with multiple pages. The page forms are identical .. the only difference is the source (query ... all the data comes from one table but varies by a status code).
Is there an easy way to use one set of routines/code for all the pages ... varying only by a parameter which identifies the page of origin/status code. The code has many routines e.g. deleting rows, updating row ... so it's not like invoking a function.
Thanks for any help.

 

[Remou]

I do not quite see the problem. Are you not using the same form for all the recordsets?

Any standard code can be stored in an ordinary module and called from the form module.

 

[missinglinq]

Are you saying that you have a form with multiple tabbed pages and subforms on each page, with the source for each subform coming from different queries on the same table?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[MajP]

You may be able to harness the power of multiple form instances. (My assumption like Remou and Ace Man, is that you have a tabbed page control with subform controls on each page)
1) I would build only one subform, not individual subforms for each page
2) Put your procedure events on the subform
3) Put a subform control on each page with the same subform in each control
4) When the main form opens set the recordsource for each subform

By placing the same subform multiple times within different subform controls, you have created multiple instances of the same form. So any events that fire from a subform are unique to that instance and so are runtime properrties. In this way you may be able to run the same code without passing any parameters.

For example say you have a click event on a subform that changes the color of a textbox. If you fire that event the textbox changes color only for that instance of the subform. Although the other subform controls use the same form, they are unaffected.

 

[freddydog]

Thanks everyone for your interest. As Majp reiterated, I am trying to create a tabbed page control with subform controls on each page (since I'm fairly new at this, my problem description and terminology are awkward).

Majp,I will try your idea. There is no reason why it shouldn't work. I will add a control which will tell me which page is being processed. I need that information for the specific query, etc. This will save me a lot of repetition and work down the road if changes are needed.
Thanks again.

 

[MajP]

In the subform instance, I would think something like
me.parent.yourtabControlname.value
would return the page being processed
(where 0 is the first page, 1 the second page, ...)

 

[freddydog]

MajP, I'm still struggling with setting up the various tabs with same subform. In your response to me (item 4) you say:

"When the main form opens set the recordsource for each subform"

How do I do that? what is the syntax? I tried the following

Forms!TYWorkPagefrm(0)!RecordSource = "TYWorkPageqry"

and variations thereof but get the message can't find form.
TYWorkPagefrm is my subform which is on many tabs.
Thanks for your help.

 

[ZOR]

I might have misread your problem, but:

If you are trying to address a subform, then you must include the parent forms name, and the subforms name in your Forms! code.

Have a look at this:

http://www.mvps.org/access/forms/frm0031.htm

Regards

 

[MajP]

Lets say you have three subform controls each with the same form in them, but you want to give each a seperate recordsource. If the subform controls are "subFrmOne", "subFrmTwo", and "subFrmThree", On the forms load event

me.subFrmOne.form.recordsource = "qryOne"
me.subFrmTwo.form.recordsource = "qryTwo"
me.subFrmThree.form.recordsource = "qryThree"

Here is another trick I use if each subform control has the same form in it. I add a public variable at the top of the form's code.

public strSubCntrlName as string

Then I add to the above code for the main form's on load event.

me.subFrmOne.form.recordsource = "qryOne"
me.subFrmOne.form.strSubCntrlName = "subFrmOne"
me.subFrmTwo.form.recordsource = "qryTwo"
me.subFrmTwo.form.strSubCntrlName = "subFrmTwo"
me.subFrmThree.form.recordsource = "qryThree"
me.subFrmThree.form.strSubCntrlName = "subFrmThree"


What this allows you to do is determine what control the subForm is in, and do something different depending on which one it is in. For example you could have a cmd button on the subform to do some of your processing, but you need to know which subform control you are in.

select case me.strSubCntrlName
case "subFrmOne"
do something ..
case "subFrmTwo"
do something
case "subFrmThree"
do something
case else
do something
end select

By designing your form this one you only have one subform, that appears multiple times on the form, each instance has its own recordsource, and each instance can execute the same code differently.

You could of course just build three subforms, and conceptually that would be easier. This technique is tidy and efficient because you only have one subform to build and maintain.

One more thing a subform on a tab control is referenced no differently then a subform on the main form. The tab control has no affect. Something like
me.subFrmControlName.form

 

[MajP]

Another tip on this. If you do not embed your code in the subform, but in the main form, then value of the tab control tells you which page you are on. Often people try to use the tab control click event to fire when you click on a different page, this will not work. Use the tab controls on change event. In the change event do something like

select case me.tabCtrl.value
case 0
do something if you clicked on the first page
case 1
do something if you clicked on the second page
case x
do something if you clicked on the (X-1) page
end select

the pages are indexed (0,1,2,...)

 

[freddydog]

Zor, thanks for the link to that useful website.

MajP .. thanks for the detailed instructions. Success at last! ... I will definitely use your valuable tips!