Requery 7 subforms based on 1 table

[Guest]

Hi, I've posted a few times on this and thought I finally had it figured
out. I have a main table (tbl_PNG_Main) and a subtable (tbl_Date_Detail).
Each main record has to go through any number of actions that all have
different action dates and due dates. There are 7 possible actions and each
record will have at least 2 but could have up to 7.
tbl_Date_Detail consists of:
Date_ID (PK)
PNG_ID (FK to main table)
Date_Type (the 7 possible actions)
Action_Date
Due_Date

I built a Main Form that consists of tabs and then built 7 subforms (one for
each action). Each subform has a default value in the Date_Type of the
appropriate action and this field is not visible so that it cannot be edited.
The user can enter the Action &/or Due Dates and then there are other
calculated dates on the form (ie. send reminder). I have this all set up and
when you fill out a record it stores the correct information back to the
detail table. However, the data does not stay in the form, when you go back
to the record it has the first due date entered stored in all the due dates.

I have tried putting in code on the On Current and the After Update event to
requery each subform (Me.Subfrm_Abandon.Form.Requery etc.) however, it
doesn't seem to work. Any idea what I'm doing wrong? Thanks a lot!

 

[Graham Mandeno]

First, I would do this with one subform, not seven.

I don't know what other controls (besides the subforms) you have on your tab
pages, so this idea might need some refining, but you could try it as a
starting point.

Put one copy of the subform on your main form with no filters and no default
values.

Now add an option group "opgDateType" to your main form, containing seven
toggle buttons (or option buttons or checkboxes if you prefer), one for each
possible Date_Type. Set the option value for each button to the
corresponding date type value.

Next, in the property sheet of your subform control, set the following
properties:
LinkMasterFields: PNG_ID; opgDateType
LinkChildFields: PNG_ID; Date_Type

Now it should all be automatic. Selecting a button from the option group
will cause the subform to be filtered to show only those date detail records
which match the current PNG_ID *and* the selected Date_Type. Not only that,
but any new records added to the subform will automatically inherit the
PNG_ID and the Date_Type from the parent form.

 

[Guest]

Thanks Graham, I'm not sure if this will work for me but maybe I'm
misunderstanding. What I actually have in the Main table is also some other
information that relates to the different actions (ie. staff person,
comments, results). The reason I built the subtable for the dates is that I
need to report on the most recent due date and all other dates and fields
related to the current due date.

The user needs to be able to see all the info on the screen (some of it is
split out into tabs based on the type of action) - I've turned off the
borders, scroll bars etc. so that it looks like part of the main form.
Whether on the Main form or a tab, there is some information from the main
table and some date fields from the detail table.

Another issue I'm finding is that I only want the user to be able to enter
one set of information for each action so, when they tab, I would like them
to go to the next subform, not a new record of the current subform.

Maybe I am going about the whole thing the wrong way? I have the format the
way it needs to look but maybe my tables are still designed incorrectly?

First, I would do this with one subform, not seven.

I don't know what other controls (besides the subforms) you have on your tab
pages, so this idea might need some refining, but you could try it as a
starting point.

Put one copy of the subform on your main form with no filters and no default
values.

Now add an option group "opgDateType" to your main form, containing seven
toggle buttons (or option buttons or checkboxes if you prefer), one for each
possible Date_Type. Set the option value for each button to the
corresponding date type value.

Next, in the property sheet of your subform control, set the following
properties:
LinkMasterFields: PNG_ID; opgDateType
LinkChildFields: PNG_ID; Date_Type

Now it should all be automatic. Selecting a button from the option group
will cause the subform to be filtered to show only those date detail records
which match the current PNG_ID *and* the selected Date_Type. Not only that,
but any new records added to the subform will automatically inherit the
PNG_ID and the Date_Type from the parent form.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, I've posted a few times on this and thought I finally had it figured
out. I have a main table (tbl_PNG_Main) and a subtable (tbl_Date_Detail).
Each main record has to go through any number of actions that all have
different action dates and due dates. There are 7 possible actions and
each
record will have at least 2 but could have up to 7.
tbl_Date_Detail consists of:
Date_ID (PK)
PNG_ID (FK to main table)
Date_Type (the 7 possible actions)
Action_Date
Due_Date

I built a Main Form that consists of tabs and then built 7 subforms (one
for
each action). Each subform has a default value in the Date_Type of the
appropriate action and this field is not visible so that it cannot be
edited.
The user can enter the Action &/or Due Dates and then there are other
calculated dates on the form (ie. send reminder). I have this all set up
and
when you fill out a record it stores the correct information back to the
detail table. However, the data does not stay in the form, when you go
back
to the record it has the first due date entered stored in all the due
dates.

I have tried putting in code on the On Current and the After Update event
to
requery each subform (Me.Subfrm_Abandon.Form.Requery etc.) however, it
doesn't seem to work. Any idea what I'm doing wrong? Thanks a lot!

 

[Graham Mandeno]

To prevent multiple records for each action, you can create a unique index
on your Date detail table comprising the two fields, PNG_ID and Date_Type.
Also, you can toggle a form's AllowAdditions property depending on the
number of records in its recordset by testing RecordSet.RecordCount.

If you want all the details to be visible at once, have you considered one
continuous subform listing all the date types for the corresponding PNG_ID?
All you would have in each row is Date_Type (this could be a combo box),
Action_Date and Due_Date (and also the calculated reminder date).


--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham, I'm not sure if this will work for me but maybe I'm
misunderstanding. What I actually have in the Main table is also some
other
information that relates to the different actions (ie. staff person,
comments, results). The reason I built the subtable for the dates is that
I
need to report on the most recent due date and all other dates and fields
related to the current due date.

The user needs to be able to see all the info on the screen (some of it is
split out into tabs based on the type of action) - I've turned off the
borders, scroll bars etc. so that it looks like part of the main form.
Whether on the Main form or a tab, there is some information from the main
table and some date fields from the detail table.

Another issue I'm finding is that I only want the user to be able to enter
one set of information for each action so, when they tab, I would like
them
to go to the next subform, not a new record of the current subform.

Maybe I am going about the whole thing the wrong way? I have the format
the
way it needs to look but maybe my tables are still designed incorrectly?

First, I would do this with one subform, not seven.

I don't know what other controls (besides the subforms) you have on your
tab
pages, so this idea might need some refining, but you could try it as a
starting point.

Put one copy of the subform on your main form with no filters and no
default
values.

Now add an option group "opgDateType" to your main form, containing seven
toggle buttons (or option buttons or checkboxes if you prefer), one for
each
possible Date_Type. Set the option value for each button to the
corresponding date type value.

Next, in the property sheet of your subform control, set the following
properties:
LinkMasterFields: PNG_ID; opgDateType
LinkChildFields: PNG_ID; Date_Type

Now it should all be automatic. Selecting a button from the option group
will cause the subform to be filtered to show only those date detail
records
which match the current PNG_ID *and* the selected Date_Type. Not only
that,
but any new records added to the subform will automatically inherit the
PNG_ID and the Date_Type from the parent form.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, I've posted a few times on this and thought I finally had it
figured
out. I have a main table (tbl_PNG_Main) and a subtable
(tbl_Date_Detail).
Each main record has to go through any number of actions that all have
different action dates and due dates. There are 7 possible actions and
each
record will have at least 2 but could have up to 7.
tbl_Date_Detail consists of:
Date_ID (PK)
PNG_ID (FK to main table)
Date_Type (the 7 possible actions)
Action_Date
Due_Date

I built a Main Form that consists of tabs and then built 7 subforms
(one
for
each action). Each subform has a default value in the Date_Type of the
appropriate action and this field is not visible so that it cannot be
edited.
The user can enter the Action &/or Due Dates and then there are other
calculated dates on the form (ie. send reminder). I have this all set
up
and
when you fill out a record it stores the correct information back to
the
detail table. However, the data does not stay in the form, when you go
back
to the record it has the first due date entered stored in all the due
dates.

I have tried putting in code on the On Current and the After Update
event
to
requery each subform (Me.Subfrm_Abandon.Form.Requery etc.) however, it
doesn't seem to work. Any idea what I'm doing wrong? Thanks a lot!

 

[Guest]

Thanks Graham - I will try the AllowAdditions and hopefully that will help
with the multiple records. I did think of using a continuous subform (that's
what I would normally do) but this has more to do with the functionality for
the user and the other information that is on each tab. I didn't put the
other data in my detail table because it varies for each action type.

I'm kind of stuck with either leaving it the way it is because the tables
are working in which case, is there any way to get the correct information to
stay on the form? Requery?

Or, would it be better to have a subtable for each action? Then I think
I'll still have a problem reporting based on the most current due date?

Thanks again for all your suggestions.

To prevent multiple records for each action, you can create a unique index
on your Date detail table comprising the two fields, PNG_ID and Date_Type.
Also, you can toggle a form's AllowAdditions property depending on the
number of records in its recordset by testing RecordSet.RecordCount.

If you want all the details to be visible at once, have you considered one
continuous subform listing all the date types for the corresponding PNG_ID?
All you would have in each row is Date_Type (this could be a combo box),
Action_Date and Due_Date (and also the calculated reminder date).


--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham, I'm not sure if this will work for me but maybe I'm
misunderstanding. What I actually have in the Main table is also some
other
information that relates to the different actions (ie. staff person,
comments, results). The reason I built the subtable for the dates is that
I
need to report on the most recent due date and all other dates and fields
related to the current due date.

The user needs to be able to see all the info on the screen (some of it is
split out into tabs based on the type of action) - I've turned off the
borders, scroll bars etc. so that it looks like part of the main form.
Whether on the Main form or a tab, there is some information from the main
table and some date fields from the detail table.

Another issue I'm finding is that I only want the user to be able to enter
one set of information for each action so, when they tab, I would like
them
to go to the next subform, not a new record of the current subform.

Maybe I am going about the whole thing the wrong way? I have the format
the
way it needs to look but maybe my tables are still designed incorrectly?

First, I would do this with one subform, not seven.

I don't know what other controls (besides the subforms) you have on your
tab
pages, so this idea might need some refining, but you could try it as a
starting point.

Put one copy of the subform on your main form with no filters and no
default
values.

Now add an option group "opgDateType" to your main form, containing seven
toggle buttons (or option buttons or checkboxes if you prefer), one for
each
possible Date_Type. Set the option value for each button to the
corresponding date type value.

Next, in the property sheet of your subform control, set the following
properties:
LinkMasterFields: PNG_ID; opgDateType
LinkChildFields: PNG_ID; Date_Type

Now it should all be automatic. Selecting a button from the option group
will cause the subform to be filtered to show only those date detail
records
which match the current PNG_ID *and* the selected Date_Type. Not only
that,
but any new records added to the subform will automatically inherit the
PNG_ID and the Date_Type from the parent form.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, I've posted a few times on this and thought I finally had it
figured
out. I have a main table (tbl_PNG_Main) and a subtable
(tbl_Date_Detail).
Each main record has to go through any number of actions that all have
different action dates and due dates. There are 7 possible actions and
each
record will have at least 2 but could have up to 7.
tbl_Date_Detail consists of:
Date_ID (PK)
PNG_ID (FK to main table)
Date_Type (the 7 possible actions)
Action_Date
Due_Date

I built a Main Form that consists of tabs and then built 7 subforms
(one
for
each action). Each subform has a default value in the Date_Type of the
appropriate action and this field is not visible so that it cannot be
edited.
The user can enter the Action &/or Due Dates and then there are other
calculated dates on the form (ie. send reminder). I have this all set
up
and
when you fill out a record it stores the correct information back to
the
detail table. However, the data does not stay in the form, when you go
back
to the record it has the first due date entered stored in all the due
dates.

I have tried putting in code on the On Current and the After Update
event
to
requery each subform (Me.Subfrm_Abandon.Form.Requery etc.) however, it
doesn't seem to work. Any idea what I'm doing wrong? Thanks a lot!

 

[Graham Mandeno]

If you want to stay with the seven subform design, then add an invisible
textbox to each of the tab pages, one for each subform.

Set each textbox's ControlSource to a constant expression matching the
required Date_Type for the matching subform. For example, for the subform
where Date_Type is 1, set these properties for the textbox:

Name: txtDateType1
ControlSource: =1

Then, for the corresponding subform control, set these properties:
LinkMasterFields: PNG_ID; txtDateType1
LinkChildFields: PNG_ID; Date_Type

In the Form_Current even procedure of the subform, put this code:
Me.AllowAdditions = Me.RecordsetClone.RecordCount = 0

You need only one subform for the seven different subform controls. Each
subform control will contain a copy of the same actual form, the only
difference being the link field properties.

The requerying and allowing/disallowing new records should then look after
itself
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham - I will try the AllowAdditions and hopefully that will help
with the multiple records. I did think of using a continuous subform
(that's
what I would normally do) but this has more to do with the functionality
for
the user and the other information that is on each tab. I didn't put the
other data in my detail table because it varies for each action type.

I'm kind of stuck with either leaving it the way it is because the tables
are working in which case, is there any way to get the correct information
to
stay on the form? Requery?

Or, would it be better to have a subtable for each action? Then I think
I'll still have a problem reporting based on the most current due date?

Thanks again for all your suggestions.

To prevent multiple records for each action, you can create a unique
index
on your Date detail table comprising the two fields, PNG_ID and
Date_Type.
Also, you can toggle a form's AllowAdditions property depending on the
number of records in its recordset by testing RecordSet.RecordCount.

If you want all the details to be visible at once, have you considered
one
continuous subform listing all the date types for the corresponding
PNG_ID?
All you would have in each row is Date_Type (this could be a combo box),
Action_Date and Due_Date (and also the calculated reminder date).


--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham, I'm not sure if this will work for me but maybe I'm
misunderstanding. What I actually have in the Main table is also some
other
information that relates to the different actions (ie. staff person,
comments, results). The reason I built the subtable for the dates is
that
I
need to report on the most recent due date and all other dates and
fields
related to the current due date.

The user needs to be able to see all the info on the screen (some of it
is
split out into tabs based on the type of action) - I've turned off the
borders, scroll bars etc. so that it looks like part of the main form.
Whether on the Main form or a tab, there is some information from the
main
table and some date fields from the detail table.

Another issue I'm finding is that I only want the user to be able to
enter
one set of information for each action so, when they tab, I would like
them
to go to the next subform, not a new record of the current subform.

Maybe I am going about the whole thing the wrong way? I have the
format
the
way it needs to look but maybe my tables are still designed
incorrectly?


:

First, I would do this with one subform, not seven.

I don't know what other controls (besides the subforms) you have on
your
tab
pages, so this idea might need some refining, but you could try it as
a
starting point.

Put one copy of the subform on your main form with no filters and no
default
values.

Now add an option group "opgDateType" to your main form, containing
seven
toggle buttons (or option buttons or checkboxes if you prefer), one
for
each
possible Date_Type. Set the option value for each button to the
corresponding date type value.

Next, in the property sheet of your subform control, set the following
properties:
LinkMasterFields: PNG_ID; opgDateType
LinkChildFields: PNG_ID; Date_Type

Now it should all be automatic. Selecting a button from the option
group
will cause the subform to be filtered to show only those date detail
records
which match the current PNG_ID *and* the selected Date_Type. Not only
that,
but any new records added to the subform will automatically inherit
the
PNG_ID and the Date_Type from the parent form.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, I've posted a few times on this and thought I finally had it
figured
out. I have a main table (tbl_PNG_Main) and a subtable
(tbl_Date_Detail).
Each main record has to go through any number of actions that all
have
different action dates and due dates. There are 7 possible actions
and
each
record will have at least 2 but could have up to 7.
tbl_Date_Detail consists of:
Date_ID (PK)
PNG_ID (FK to main table)
Date_Type (the 7 possible actions)
Action_Date
Due_Date

I built a Main Form that consists of tabs and then built 7 subforms
(one
for
each action). Each subform has a default value in the Date_Type of
the
appropriate action and this field is not visible so that it cannot
be
edited.
The user can enter the Action &/or Due Dates and then there are
other
calculated dates on the form (ie. send reminder). I have this all
set
up
and
when you fill out a record it stores the correct information back to
the
detail table. However, the data does not stay in the form, when you
go
back
to the record it has the first due date entered stored in all the
due
dates.

I have tried putting in code on the On Current and the After Update
event
to
requery each subform (Me.Subfrm_Abandon.Form.Requery etc.) however,
it
doesn't seem to work. Any idea what I'm doing wrong? Thanks a lot!

 

[Guest]

Graham - I'm sending you lots of smiles today - thanks for all your help. It
is all working perfectly now (what a relief).

If you want to stay with the seven subform design, then add an invisible
textbox to each of the tab pages, one for each subform.

Set each textbox's ControlSource to a constant expression matching the
required Date_Type for the matching subform. For example, for the subform
where Date_Type is 1, set these properties for the textbox:

Name: txtDateType1
ControlSource: =1

Then, for the corresponding subform control, set these properties:
LinkMasterFields: PNG_ID; txtDateType1
LinkChildFields: PNG_ID; Date_Type

In the Form_Current even procedure of the subform, put this code:
Me.AllowAdditions = Me.RecordsetClone.RecordCount = 0

You need only one subform for the seven different subform controls. Each
subform control will contain a copy of the same actual form, the only
difference being the link field properties.

The requerying and allowing/disallowing new records should then look after
itself
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham - I will try the AllowAdditions and hopefully that will help
with the multiple records. I did think of using a continuous subform
(that's
what I would normally do) but this has more to do with the functionality
for
the user and the other information that is on each tab. I didn't put the
other data in my detail table because it varies for each action type.

I'm kind of stuck with either leaving it the way it is because the tables
are working in which case, is there any way to get the correct information
to
stay on the form? Requery?

Or, would it be better to have a subtable for each action? Then I think
I'll still have a problem reporting based on the most current due date?

Thanks again for all your suggestions.

To prevent multiple records for each action, you can create a unique
index
on your Date detail table comprising the two fields, PNG_ID and
Date_Type.
Also, you can toggle a form's AllowAdditions property depending on the
number of records in its recordset by testing RecordSet.RecordCount.

If you want all the details to be visible at once, have you considered
one
continuous subform listing all the date types for the corresponding
PNG_ID?
All you would have in each row is Date_Type (this could be a combo box),
Action_Date and Due_Date (and also the calculated reminder date).


--

Graham Mandeno [Access MVP]
Auckland, New Zealand



Thanks Graham, I'm not sure if this will work for me but maybe I'm
misunderstanding. What I actually have in the Main table is also some
other
information that relates to the different actions (ie. staff person,
comments, results). The reason I built the subtable for the dates is
that
I
need to report on the most recent due date and all other dates and
fields
related to the current due date.

The user needs to be able to see all the info on the screen (some of it
is
split out into tabs based on the type of action) - I've turned off the
borders, scroll bars etc. so that it looks like part of the main form.
Whether on the Main form or a tab, there is some information from the
main
table and some date fields from the detail table.

Another issue I'm finding is that I only want the user to be able to
enter
one set of information for each action so, when they tab, I would like
them
to go to the next subform, not a new record of the current subform.

Maybe I am going about the whole thing the wrong way? I have the
format
the
way it needs to look but maybe my tables are still designed
incorrectly?


:

First, I would do this with one subform, not seven.

I don't know what other controls (besides the subforms) you have on
your
tab
pages, so this idea might need some refining, but you could try it as
a
starting point.

Put one copy of the subform on your main form with no filters and no
default
values.

Now add an option group "opgDateType" to your main form, containing
seven
toggle buttons (or option buttons or checkboxes if you prefer), one
for
each
possible Date_Type. Set the option value for each button to the
corresponding date type value.

Next, in the property sheet of your subform control, set the following
properties:
LinkMasterFields: PNG_ID; opgDateType
LinkChildFields: PNG_ID; Date_Type

Now it should all be automatic. Selecting a button from the option
group
will cause the subform to be filtered to show only those date detail
records
which match the current PNG_ID *and* the selected Date_Type. Not only
that,
but any new records added to the subform will automatically inherit
the
PNG_ID and the Date_Type from the parent form.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, I've posted a few times on this and thought I finally had it
figured
out. I have a main table (tbl_PNG_Main) and a subtable
(tbl_Date_Detail).
Each main record has to go through any number of actions that all
have
different action dates and due dates. There are 7 possible actions
and
each
record will have at least 2 but could have up to 7.
tbl_Date_Detail consists of:
Date_ID (PK)
PNG_ID (FK to main table)
Date_Type (the 7 possible actions)
Action_Date
Due_Date

I built a Main Form that consists of tabs and then built 7 subforms
(one
for
each action). Each subform has a default value in the Date_Type of
the
appropriate action and this field is not visible so that it cannot
be
edited.
The user can enter the Action &/or Due Dates and then there are
other
calculated dates on the form (ie. send reminder). I have this all
set
up
and
when you fill out a record it stores the correct information back to
the
detail table. However, the data does not stay in the form, when you
go
back
to the record it has the first due date entered stored in all the
due
dates.

I have tried putting in code on the On Current and the After Update
event
to
requery each subform (Me.Subfrm_Abandon.Form.Requery etc.) however,
it
doesn't seem to work. Any idea what I'm doing wrong? Thanks a lot!

 

[Graham Mandeno]

That's great! Glad to be of help

--
Good Luck!

Graham

Graham - I'm sending you lots of smiles today - thanks for all your help.
It
is all working perfectly now (what a relief).

If you want to stay with the seven subform design, then add an invisible
textbox to each of the tab pages, one for each subform.

Set each textbox's ControlSource to a constant expression matching the
required Date_Type for the matching subform. For example, for the
subform
where Date_Type is 1, set these properties for the textbox:

Name: txtDateType1
ControlSource: =1

Then, for the corresponding subform control, set these properties:
LinkMasterFields: PNG_ID; txtDateType1
LinkChildFields: PNG_ID; Date_Type

In the Form_Current even procedure of the subform, put this code:
Me.AllowAdditions = Me.RecordsetClone.RecordCount = 0

You need only one subform for the seven different subform controls. Each
subform control will contain a copy of the same actual form, the only
difference being the link field properties.

The requerying and allowing/disallowing new records should then look
after
itself
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham - I will try the AllowAdditions and hopefully that will
help
with the multiple records. I did think of using a continuous subform
(that's
what I would normally do) but this has more to do with the
functionality
for
the user and the other information that is on each tab. I didn't put
the
other data in my detail table because it varies for each action type.

I'm kind of stuck with either leaving it the way it is because the
tables
are working in which case, is there any way to get the correct
information
to
stay on the form? Requery?

Or, would it be better to have a subtable for each action? Then I
think
I'll still have a problem reporting based on the most current due date?

Thanks again for all your suggestions.

:

To prevent multiple records for each action, you can create a unique
index
on your Date detail table comprising the two fields, PNG_ID and
Date_Type.
Also, you can toggle a form's AllowAdditions property depending on the
number of records in its recordset by testing RecordSet.RecordCount.

If you want all the details to be visible at once, have you considered
one
continuous subform listing all the date types for the corresponding
PNG_ID?
All you would have in each row is Date_Type (this could be a combo
box),
Action_Date and Due_Date (and also the calculated reminder date).


--

Graham Mandeno [Access MVP]
Auckland, New Zealand



Thanks Graham, I'm not sure if this will work for me but maybe I'm
misunderstanding. What I actually have in the Main table is also
some
other
information that relates to the different actions (ie. staff person,
comments, results). The reason I built the subtable for the dates
is
that
I
need to report on the most recent due date and all other dates and
fields
related to the current due date.

The user needs to be able to see all the info on the screen (some of
it
is
split out into tabs based on the type of action) - I've turned off
the
borders, scroll bars etc. so that it looks like part of the main
form.
Whether on the Main form or a tab, there is some information from
the
main
table and some date fields from the detail table.

Another issue I'm finding is that I only want the user to be able to
enter
one set of information for each action so, when they tab, I would
like
them
to go to the next subform, not a new record of the current subform.

Maybe I am going about the whole thing the wrong way? I have the
format
the
way it needs to look but maybe my tables are still designed
incorrectly?


:

First, I would do this with one subform, not seven.

I don't know what other controls (besides the subforms) you have on
your
tab
pages, so this idea might need some refining, but you could try it
as
a
starting point.

Put one copy of the subform on your main form with no filters and
no
default
values.

Now add an option group "opgDateType" to your main form, containing
seven
toggle buttons (or option buttons or checkboxes if you prefer), one
for
each
possible Date_Type. Set the option value for each button to the
corresponding date type value.

Next, in the property sheet of your subform control, set the
following
properties:
LinkMasterFields: PNG_ID; opgDateType
LinkChildFields: PNG_ID; Date_Type

Now it should all be automatic. Selecting a button from the option
group
will cause the subform to be filtered to show only those date
detail
records
which match the current PNG_ID *and* the selected Date_Type. Not
only
that,
but any new records added to the subform will automatically inherit
the
PNG_ID and the Date_Type from the parent form.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi, I've posted a few times on this and thought I finally had it
figured
out. I have a main table (tbl_PNG_Main) and a subtable
(tbl_Date_Detail).
Each main record has to go through any number of actions that all
have
different action dates and due dates. There are 7 possible
actions
and
each
record will have at least 2 but could have up to 7.
tbl_Date_Detail consists of:
Date_ID (PK)
PNG_ID (FK to main table)
Date_Type (the 7 possible actions)
Action_Date
Due_Date

I built a Main Form that consists of tabs and then built 7
subforms
(one
for
each action). Each subform has a default value in the Date_Type
of
the
appropriate action and this field is not visible so that it
cannot
be
edited.
The user can enter the Action &/or Due Dates and then there are
other
calculated dates on the form (ie. send reminder). I have this
all
set
up
and
when you fill out a record it stores the correct information back
to
the
detail table. However, the data does not stay in the form, when
you
go
back
to the record it has the first due date entered stored in all the
due
dates.

I have tried putting in code on the On Current and the After
Update
event
to
requery each subform (Me.Subfrm_Abandon.Form.Requery etc.)
however,
it
doesn't seem to work. Any idea what I'm doing wrong? Thanks a
lot!