Tab controls and recordsource

[google3luo359]

This is a tough one to nail down.

I have a tab control with four pages.
Each page currently has the same subform control.

I would simply like to change the recordset of the subform when each
page is clicked at the top.

Here's the code I have now that's not working:

Forms![fmAEP]!TabAEP.Pages(0).SetFocus
Forms![fmAEP].sfmTab09.Form.RecordSource = AEP09qry

the above code results in #Name? in each of the controls on the
subform.
This code kicks in when the user logs into Page(0).

If they click on Page(1) the following code is the OnClick event :
Me.sfmTab10.Form.RecordSource = AEP10qry
This brings up the record that should be brought up for Page (0) !!!!

Similarly if Page(2) or (3) is clicked the same record is brought up!!!

Any insight into this problem would be appreciated!
TIA Ric

 

[google3luo359]

A little more info.

Each subform on each of the four Pages has its own name but the control
source for each subform is the same, 'fmTab'.

I had the recordsource for fmTab set to AEP09qry until a few minutes
ago.
I removed it and now there is no recordsource.
I want the recordsource to be supplied when the tab page at the top is
clicked.

TIA Ric

 

[Marshall Barton]

I have a tab control with four pages.
Each page currently has the same subform control.

I would simply like to change the recordset of the subform when each
page is clicked at the top.

Here's the code I have now that's not working:

Forms![fmAEP]!TabAEP.Pages(0).SetFocus
Forms![fmAEP].sfmTab09.Form.RecordSource = AEP09qry

the above code results in #Name? in each of the controls on the
subform.
This code kicks in when the user logs into Page(0).

If they click on Page(1) the following code is the OnClick event :
Me.sfmTab10.Form.RecordSource = AEP10qry
This brings up the record that should be brought up for Page (0) !!!!

Similarly if Page(2) or (3) is clicked the same record is brought up!!!

I can't verify that your names are correct and I have no
idea why you would want to use SetFocus in this, but the
glaring problem is that the record source property has a
string type value. You need to use:

Me.sfmTab10.Form.RecordSource = "AEP10qry"

Are you aware that it is unnecessary to have four subform
controls for this? If you place one subform control on the
form instead of on a page of the tab control, then it will
show through all the tab pages.

 

[google3luo359]

You're a good man Marshall. I appreciate your patience.
Today has *not* been a good day for coding.

I can't verify that your names are correct and I have no
idea why you would want to use SetFocus in this,

When my frmPass opens fmAEP, how else can I select the proper Page
than
by using SetFocus?

but the
glaring problem is that the record source property has a
string type value. You need to use:

Me.sfmTab10.Form.RecordSource = "AEP10qry"

OK, thanks I'll try that.

Are you aware that it is unnecessary to have four subform
controls for this? If you place one subform control on the
form instead of on a page of the tab control, then it will
show through all the tab pages.

Here's where i've had no luck at all. Firstly I don't understand much
of the jargon.
I start out everything with a Form.
Do I then put a Tab control on the Form?
Or do I put a Subform on the Form?
If the latter, then do I put a Tab control onto the Subform?
That doesn't make sense to me.

TIA Ric

 

[Marshall Barton]

You're a good man Marshall. I appreciate your patience.
Today has *not* been a good day for coding.



When my frmPass opens fmAEP, how else can I select
the proper Page than by using SetFocus?

Using SetFocus in the Load event might make sense, but why
can't you just make the tab control the first item in the
section's tabbing order.

OK, thanks I'll try that.


Here's where i've had no luck at all. Firstly I don't
understand much of the jargon.
I start out everything with a Form.
Do I then put a Tab control on the Form?
Or do I put a Subform on the Form?
If the latter, then do I put a Tab control onto the Subform?
That doesn't make sense to me.

The tab control is definitely on the main form. If you put
the subform control on the form, it will show through the
tab control regardless of which one you created first. Note
that whether the tab control or the form contains the
subform only depends on which one you clicked on before
adding the subform control. Play around with this on a copy
of your form to get a feel for how it works.

From what I can see on this side of the newsgroup, you are
only using the tab control to select the subform's record
source. If that's really the case, there are other
mechanisms (e.g. combo box, list box, option group) that can
be used for that. Actually, I have to question what these
different queries are doing because, in most situations, all
that's different is a criteria to filter the data and there
are other ways of doing that.

 

[google3luo359]

Using SetFocus in the Load event might make sense, but why
can't you just make the tab control the first item in the
section's tabbing order.

Well depending on which student's record is selected, the appropriate
page must be displayed.

The tab control is definitely on the main form. If you put
the subform control on the form, it will show through the
tab control regardless of which one you created first. ... Play around with this on a copy of your form to get a feel for how it works.

OK, I see how this works now. The subform is showing on all pages now.

From what I can see on this side of the newsgroup, you are
only using the tab control to select the subform's record
source.

Currently each subform ( I now have four) has its own query for its
recordsource.

Actually, I have to question what these
different queries are doing because, in most situations, all
that's different is a criteria to filter the data and there
are other ways of doing that.

Because I've redesigned the table structure of the db on
recommendation, a few things are still out of whack.

What I'm trying to do is really simple. I just don't know how to set it
up properly.
I would like one main form. On this form I'd like two subforms (one big
and one small one). These subforms are to be placed on a tab control
that has four pages.
All I'd like is that when a student logs in (with their ID) they are
taken to the correct page of the tab control, and their record is
pulled up on this page.

All student records are now housed in one table.

TIA Ric

 

[Marshall Barton]

Well depending on which student's record is selected, the appropriate
page must be displayed.



OK, I see how this works now. The subform is showing on all pages now.



Currently each subform ( I now have four) has its own query for its
recordsource.


Because I've redesigned the table structure of the db on
recommendation, a few things are still out of whack.

What I'm trying to do is really simple. I just don't know how to set it
up properly.
I would like one main form. On this form I'd like two subforms (one big
and one small one). These subforms are to be placed on a tab control
that has four pages.
All I'd like is that when a student logs in (with their ID) they are
taken to the correct page of the tab control, and their record is
pulled up on this page.

All student records are now housed in one table.

You have yet to explain WHY you think you need a tab control
instead of just two subforms and a group of option buttons.
You said that the tab control page depends "on which
student's record is selected". Does this mean you only have
four students??

I still think there may very well be no need to change the
subform's record source, especially if it's only to select a
different student and/or grade level. This is the kind of
thing that the Link Master/Child properties are intended to
deal with.

What I'm trying to do here is back up before your How To
question (which seems overly complex) to the form's overall
objective. Maybe then we can figure out what arrangement
will meet those needs.

 

[google3luo359]

You have yet to explain WHY you think you need a tab control
instead of just two subforms and a group of option buttons.
You said that the tab control page depends "on which
student's record is selected". Does this mean you only have
four students??

You are quite correct that I really don't need a tab control.
I just thought from the start that the best way to display a student's
four records (that's their max.)
would be by using a tab with four pages. Click on Gd. 9, up comes their
Gd. 9 Plan, etc.

I still think there may very well be no need to change the
subform's record source, especially if it's only to select a
different student and/or grade level. This is the kind of
thing that the Link Master/Child properties are intended to
deal with.

Maybe my explanation below will help decide this.

What I'm trying to do here is back up before your How To
question (which seems overly complex) to the form's overall
objective. Maybe then we can figure out what arrangement
will meet those needs.

I'll try to be as specific yet as brief as possible.
There are approx. 650 students enrolled each year at the school.
Each of their four years at the school they will be completing an
Annual Plan (AEP).
I designed a form with basically memo fields to house their plan
components.

At first I housed their plans in four tables AEP09, AEP10, AEP11,
AEP12.
But on recommendation, I have now consolidated them into one AEP table.

These are the current tables: Students, StudentsTemp, AEP, and
DatePass.

Students contains: StudNum, LastName, FirstName, StudGrade
StudentsTemp is used when importing the students list each Sept.: new
students are queried into Students table.
AEP contains: AEPID, StudNum, Grade (not linked to StudGrade), 3 memo
fields, LastEditDate
DatePass contains: StudNum, LogDate, Password

That's basically it. So the main form's objective (in the student's
version) is to display four year's worth of plans, but allow editing
only in the current year for that student. I hope this makes more sense
now.

TIA Ric

 

[Marshall Barton]

You are quite correct that I really don't need a tab control.
I just thought from the start that the best way to display a student's
four records (that's their max.)
would be by using a tab with four pages. Click on Gd. 9, up comes their
Gd. 9 Plan, etc.


Maybe my explanation below will help decide this.


I'll try to be as specific yet as brief as possible.
There are approx. 650 students enrolled each year at the school.
Each of their four years at the school they will be completing an
Annual Plan (AEP).
I designed a form with basically memo fields to house their plan
components.

At first I housed their plans in four tables AEP09, AEP10, AEP11,
AEP12.
But on recommendation, I have now consolidated them into one AEP table.

These are the current tables: Students, StudentsTemp, AEP, and
DatePass.

Students contains: StudNum, LastName, FirstName, StudGrade
StudentsTemp is used when importing the students list each Sept.: new
students are queried into Students table.
AEP contains: AEPID, StudNum, Grade (not linked to StudGrade), 3 memo
fields, LastEditDate
DatePass contains: StudNum, LogDate, Password

That's basically it. So the main form's objective (in the student's
version) is to display four year's worth of plans, but allow editing
only in the current year for that student. I hope this makes more sense
now.

I still think this is more easily handled by using an option
group (named optGrade) to select the grade. Set each
option's OptionValue to 9, 10, 11 or 12 so you can use the
option group as one of the Link Master fields. The
LinkMasterFields would then be
StudNum;optGrade
and the LinkChildFields property:
StudNum;Grade

You can use main form's Current event to set the option
group's value to the StudGrade field so it starts out with
the current grade.

If I am finally grasping your needs, then I think that's all
it takes to deal with the issues of the plans subform.