How do I link a subform to another subform in Access 2000?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am constructing a database for a school, to keep control of lesson plans
for instructors, and eventually for use across a web. My main form consists
of four combo boxes for a user to make choices to determine what lesson(s) he
is looking for. There are several subforms, but the most important contains
details of, and hyperlinks to, the actual PowerPoint lesson or lessons
pertaining to the choices made on the main form. More than one lesson is
possible. Another subform contains reference material pertaining to the
individual lessons. Each lesson could use different references, so it would
be better to link them to the lesson subform than to the main form. Is there
a way to link a subform to another subform? My experience with SQL is fairly
scant, and with VBA even less, so if a solution involves these I'll need
fairly detailed and simple advise.
 
Chief Collator said:
I am constructing a database for a school, to keep control of lesson plans
for instructors, and eventually for use across a web. My main form consists
of four combo boxes for a user to make choices to determine what lesson(s) he
is looking for. There are several subforms, but the most important contains
details of, and hyperlinks to, the actual PowerPoint lesson or lessons
pertaining to the choices made on the main form. More than one lesson is
possible. Another subform contains reference material pertaining to the
individual lessons. Each lesson could use different references, so it would
be better to link them to the lesson subform than to the main form. Is there
a way to link a subform to another subform? My experience with SQL is fairly
scant, and with VBA even less, so if a solution involves these I'll need
fairly detailed and simple advise.
Click to expand...

Add a hidden text box to the main form (Visible property = No). Let's call
it txtLink.

Supposing the primary key for a lesson is a field called lesson_PK, add the
following statement to the Current event for form 1:

Me.Parent.txtLink = [lesson_PK]

Link form 2 to txtLink i.e. set Link Child Fields to lesson_PK, and set Link
Master Fields to txtLink.

n.b. it is not possible to publish an Access user interface on the web, the
technologies are entirely incompatible.
 
Thanks very much, I'll try that.

As for the user interface on the web, the intent is for users to 'read
only'. Is that possible on the web?

Brian said:
Chief Collator said:
I am constructing a database for a school, to keep control of lesson plans
for instructors, and eventually for use across a web. My main form consists
of four combo boxes for a user to make choices to determine what lesson(s) he
is looking for. There are several subforms, but the most important contains
details of, and hyperlinks to, the actual PowerPoint lesson or lessons
pertaining to the choices made on the main form. More than one lesson is
possible. Another subform contains reference material pertaining to the
individual lessons. Each lesson could use different references, so it would
be better to link them to the lesson subform than to the main form. Is there
a way to link a subform to another subform? My experience with SQL is fairly
scant, and with VBA even less, so if a solution involves these I'll need
fairly detailed and simple advise.
Click to expand...

Add a hidden text box to the main form (Visible property = No). Let's call
it txtLink.

Supposing the primary key for a lesson is a field called lesson_PK, add the
following statement to the Current event for form 1:

Me.Parent.txtLink = [lesson_PK]

Link form 2 to txtLink i.e. set Link Child Fields to lesson_PK, and set Link
Master Fields to txtLink.

n.b. it is not possible to publish an Access user interface on the web, the
technologies are entirely incompatible.
Click to expand...
 
Chief Collator said:
Thanks very much, I'll try that.

As for the user interface on the web, the intent is for users to 'read
only'. Is that possible on the web?

:
Click to expand...

No, it isn't possible.

You can develop Data Access Pages in Access, but they are quite different to
regular Access forms. If you have a limited and private audience, you can
also present an Access application using Terminal Services over a Virtual
Private Network. However, neither of these technologies is remotely
suitable for a public website.

To present data from a database on a public website, you would have to use a
web-specific technology such as ASP or PHP.

I have seen gizmos which claim to publish your Access website on the web,
but all they actually do is to publish the contents of your tables, not your
forms and reports. Not clever.
 
Well, I guess I'll have to figure out Data Access Pages after I get this
working. Our first priority is for in-house staff, so I'll keep on this
version for now. I've got it 3/4 working, but I'm still doing something
wrong.

I added a text box (RefLink) to the main form, on the same tab page where
I'm putting the 2nd subform. I left it visible, for now.

I renamed the primary key field for the 1st subform LessonPK.

It took a couple of tries to get the code in the right spot before the error
messages stopped, but now they have. For Form_sbfPresentations (1st subform)
I added the following to Form/Current:
Private Sub Form_Current()
Me.Parent.RefLink = [LessonPK]
End Sub

The RefLink field wasn't displaying anything, so after consulting 'Help' I
made the Control Source read
'=[Forms]![frmLessons]![sbfPresentations]![LessonPK]'. Now it displays the
data as desired.

I added the 2nd subform, made its equivalent-to-LessonPK field the Child,
and made RefLink the Master. But it isn't displaying any data. 'Help' is no
help. You've been an enormous help so far. Any more suggestions?

Brian said:
Chief Collator said:
I am constructing a database for a school, to keep control of lesson plans
for instructors, and eventually for use across a web. My main form consists
of four combo boxes for a user to make choices to determine what lesson(s) he
is looking for. There are several subforms, but the most important contains
details of, and hyperlinks to, the actual PowerPoint lesson or lessons
pertaining to the choices made on the main form. More than one lesson is
possible. Another subform contains reference material pertaining to the
individual lessons. Each lesson could use different references, so it would
be better to link them to the lesson subform than to the main form. Is there
a way to link a subform to another subform? My experience with SQL is fairly
scant, and with VBA even less, so if a solution involves these I'll need
fairly detailed and simple advise.
Click to expand...

Add a hidden text box to the main form (Visible property = No). Let's call
it txtLink.

Supposing the primary key for a lesson is a field called lesson_PK, add the
following statement to the Current event for form 1:

Me.Parent.txtLink = [lesson_PK]

Link form 2 to txtLink i.e. set Link Child Fields to lesson_PK, and set Link
Master Fields to txtLink.

n.b. it is not possible to publish an Access user interface on the web, the
technologies are entirely incompatible.
Click to expand...
 
Chief Collator said:
Well, I guess I'll have to figure out Data Access Pages after I get this
working. Our first priority is for in-house staff, so I'll keep on this
version for now. I've got it 3/4 working, but I'm still doing something
wrong.

I added a text box (RefLink) to the main form, on the same tab page where
I'm putting the 2nd subform. I left it visible, for now.

I renamed the primary key field for the 1st subform LessonPK.

It took a couple of tries to get the code in the right spot before the error
messages stopped, but now they have. For Form_sbfPresentations (1st subform)
I added the following to Form/Current:
Private Sub Form_Current()
Me.Parent.RefLink = [LessonPK]
End Sub

The RefLink field wasn't displaying anything, so after consulting 'Help' I
made the Control Source read
'=[Forms]![frmLessons]![sbfPresentations]![LessonPK]'. Now it displays the
data as desired.

I added the 2nd subform, made its equivalent-to-LessonPK field the Child,
and made RefLink the Master. But it isn't displaying any data. 'Help' is no
help. You've been an enormous help so far. Any more suggestions?
Click to expand...


Hahaha "help is no help", I like that!

Anyway, I would suggest the following:

1. Abandon your ControlSource setting and return RefLink to being an
unbound text box (sorry, I realise you must have been real pleased to have
got that far!)

2. Reinstate your code (which looks fine)

3. In the properties window for sbfPresentations, on the Events tab, make
sure that it reads "[Event Procedure]" against the On Current event. This
can easily go haywire if you been copying code around (which it sounds like
you might have been doing), and if it isn't correct, then the code will
never run!

HTH
 
Thanks enormously, it's working! The rest should be simple now (although
every time I think I've solved all the dilemmas for this thing another new
wrench appears). It's definately been a brain-stretcher.

Brian said:
Chief Collator said:
Well, I guess I'll have to figure out Data Access Pages after I get this
working. Our first priority is for in-house staff, so I'll keep on this
version for now. I've got it 3/4 working, but I'm still doing something
wrong.

I added a text box (RefLink) to the main form, on the same tab page where
I'm putting the 2nd subform. I left it visible, for now.

I renamed the primary key field for the 1st subform LessonPK.

It took a couple of tries to get the code in the right spot before the error
messages stopped, but now they have. For Form_sbfPresentations (1st subform)
I added the following to Form/Current:
Private Sub Form_Current()
Me.Parent.RefLink = [LessonPK]
End Sub

The RefLink field wasn't displaying anything, so after consulting 'Help' I
made the Control Source read
'=[Forms]![frmLessons]![sbfPresentations]![LessonPK]'. Now it displays the
data as desired.

I added the 2nd subform, made its equivalent-to-LessonPK field the Child,
and made RefLink the Master. But it isn't displaying any data. 'Help' is no
help. You've been an enormous help so far. Any more suggestions?
Click to expand...


Hahaha "help is no help", I like that!

Anyway, I would suggest the following:

1. Abandon your ControlSource setting and return RefLink to being an
unbound text box (sorry, I realise you must have been real pleased to have
got that far!)

2. Reinstate your code (which looks fine)

3. In the properties window for sbfPresentations, on the Events tab, make
sure that it reads "[Event Procedure]" against the On Current event. This
can easily go haywire if you been copying code around (which it sounds like
you might have been doing), and if it isn't correct, then the code will
never run!

HTH
Click to expand...
 
Check out www.leadinged.com There is a program called Teacher's Partner that
links ministry objectives to lesson plans. Links lesson plans to worksheets
links worksheets to a markbook and links marks to a report card. You can
import lesson plans from one teacher to another. You can export plans to a
central location on the web and import from there.
 
Back
Top