Correlated Subforms

[djf]

I am using Access 2003 and I need some help with setting up correlated
subforms.

My main form is called frmHaz and I need to place 2 subforms on it side by
side.

I have 3 tables
Hazard
Risk pk=riskid
PMC pk=pmcid
All 1 to M with enforce referential integrity set


I have placed my 1st subform on to the main form

Name: risknew
Source object: risk new
L/C/field: hazid
L/M/field: hazid

Now the bit I need to know , once I place the PMC subform next to the risk
subform how do I code it so it acts as if it was a nested subform eg placed
in the risk subform.
Also I do not understand the use of a link fieldon each subform.

Any help would be most welcome

 

[Marshall Barton]

I am using Access 2003 and I need some help with setting up correlated
subforms.

My main form is called frmHaz and I need to place 2 subforms on it side by
side.

I have 3 tables
Hazard
Risk pk=riskid
PMC pk=pmcid
All 1 to M with enforce referential integrity set


I have placed my 1st subform on to the main form

Name: risknew
Source object: risk new
L/C/field: hazid
L/M/field: hazid

Now the bit I need to know , once I place the PMC subform next to the risk
subform how do I code it so it acts as if it was a nested subform eg placed
in the risk subform.
Also I do not understand the use of a link fieldon each subform.

The Link Master property can be an unbound text box on the
main form.

Use that idea by adding an imvisible text box named Link to
the main form's Header section.

Then add a line of code to the risknew form's Current event:
Parent.Link = Me.riskid

Finally, set the pmc subform control's LinkMaster property
to Link and the LinkChild property to the foreign key field.

 

[djf]

Hi, Marsh
Thank you for you reply. If I could ask you for a bit more help.
Is there a limit to how many subforms can be linked like this as there are
with nesting?
If I need two more subform added to the main form do I put the code into the
current event of the next subform I place on the main form?
And my final question if I should need to have a command button on the main
form to trigger a pop-up form called comments linked to the risknew subform
how would I go about codeing

Thank you again for you help

 

[Marshall Barton]

Thank you for you reply. If I could ask you for a bit more help.
Is there a limit to how many subforms can be linked like this as there are
with nesting?
If I need two more subform added to the main form do I put the code into the
current event of the next subform I place on the main form?

And my final question if I should need to have a command button on the main
form to trigger a pop-up form called comments linked to the risknew subform
how would I go about codeing

You can have many subforms on a parent form, probably more
than you would ever want to deal with. The idea of using a
hidden text box on the parent form to coordinate two or more
subforms is a simple consequence of the Link Master/Child
properties and does not involve any trickery. Just put the
line of code in the first form's Current event and set the
second subform control's Link Master/Child properties. You
can even extend the idea to several subforms with each using
thier own Linkx text box to coordinate another form that has
a record source with multiple foreign keys.

I don't understand what you mean by "popup form 'linked' to
a subform". Is the comments data in a separate or the same
table as the risk form's data. THere are several ways to
"link" forms and their data. What are you trying to
accomplish with this?

 

[djf]

Hi, again Marsh
Sorry I do not understand what you are trying to tell me regarding linking
additional subforms.
I have used your idea from your ordinal reply and it looking good. In
hindsight I should have originally asked how I would go about linking not
only risk, PMC but a Task and a progress subforms as well on the Hazard
master form.

Hazard table
PK= hazardid

Risk table
PK = riskid
FK = Hazardid

PMC table
PK = pmcid
FK = riskid

Task table
PK = taskid
FK = pmcid

Task table
PK = progressid
FK = taskid


Just thought I would keep it simple and I would understand the issue better
then I do. Hopefully when I read your reply I will be kicking myself.
The idea of the pop-up form is just an idea at the moment, as I am thinking
that I may have a need to store comments for example related to information
being stored in the risk subform. I could have this field contained in my
risk subform but as it will only be needed as and when, I would like to
have this field displayed on a separate form triggered by a command button.
So I can keep the size of the overall form down. At this stage I would say
the comment field would be part of the risk table.

The other issue I had difficulties getting my head around is when one needs
to replace the functionality of the risk and PMC subforms with a popup form
triggered by a command buttons from the main form . Again should I need to
have more space on my mater form.

Thank you for the support on this
Dave

 

[Marshall Barton]

Sorry I do not understand what you are trying to tell me regarding linking
additional subforms.
I have used your idea from your ordinal reply and it looking good. In
hindsight I should have originally asked how I would go about linking not
only risk, PMC but a Task and a progress subforms as well on the Hazard
master form.

Hazard table
PK= hazardid

Risk table
PK = riskid
FK = Hazardid

PMC table
PK = pmcid
FK = riskid

Task table
PK = taskid
FK = pmcid

Task table
PK = progressid
FK = taskid

Just thought I would keep it simple and I would understand the issue better
then I do. Hopefully when I read your reply I will be kicking myself.

Ok, you have the Risk subform linked to the Hazard main form
by setting the risk subform control's:
LinkMaster hazardid (text box/field in Hazard form)
LinkChild Hazardid (field in Risk table)

Then, you coordinated the Risk and PMC subforms by adding a
text box named Link to the main form and added a line of
code to the Rixk form's Current event
Parent.Link = Me.riskid
and setting the PMC subform control's:
LinkMaster Link (text box on Hazard form)
LinkChild riskid (field in PMC table)

To coordinate the Task subform with the PMC subform, do the
same kind of thing. Add another text box (named LinkPMC) to
the main form and use a line of code in the PMC form's
Current event:
Parent.LinkPMC = Me.riskid
and setting the Task subform control's:
LinkMaster LinkPMC (text box on Hazard form)
LinkChild pmcid (field in Task table)

Similarly, the Progress subform can be coordinated with the
Task subform. Add another text box (named LinkTask) to the
main form and use a line of code in the Task form's Current
event:
Parent.LinkTask = Me.taskid
and setting the Progress subform control's:
LinkMaster LinkTask (text box on Hazard form)
LinkChild taskid (field in Progress table)

The idea of the pop-up form is just an idea at the moment, as I am thinking
that I may have a need to store comments for example related to information
being stored in the risk subform. I could have this field contained in my
risk subform but as it will only be needed as and when, I would like to
have this field displayed on a separate form triggered by a command button.
So I can keep the size of the overall form down. At this stage I would say
the comment field would be part of the risk table.

The other issue I had difficulties getting my head around is when one needs
to replace the functionality of the risk and PMC subforms with a popup form
triggered by a command buttons from the main form . Again should I need to
have more space on my mater form.

This is a different problem that I suggest should be put off
until it becomes necessary. The general idea is to open the
related forms using the OpenForm method's WhereCondition
argument. However if these other forms are already open,
then you would need to use their Filter property (has some
problems) or reconstruct their record source query to limit
them to the related records. With so many related forms,
this could get messy and, IMO, asking users to interact with
a lot of open forms can be confusing. I think I would
prefer to use a Tab control on the main form with the
subform's on different pages.

 

[djf]

Hi, Marsh
Thank you for the clarification, I am now up and running.
I take your point regarding users being better off if tab controls are
used.However I would like to use one command button to open a form so a user
can input comments regarding information in the risk sub form. I have design
the form as a modal . I placed a button within the risk form and via the
wizard came up with the following:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Risk_comments"

stLinkCriteria = "[RiskID]=" & Me![RiskID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

But Access displays the following: “You cannot add or change a record
because a related record is required in table hazard”

Could you put me straight again on how to achieve this function


Thank you yet again for your time and trouble

Dave

 

[Marshall Barton]

Thank you for the clarification, I am now up and running.
I take your point regarding users being better off if tab controls are
used.However I would like to use one command button to open a form so a user
can input comments regarding information in the risk sub form. I have design
the form as a modal . I placed a button within the risk form and via the
wizard came up with the following:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Risk_comments"

stLinkCriteria = "[RiskID]=" & Me![RiskID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

But Access displays the following: “You cannot add or change a record
because a related record is required in table hazard”

If you are adding a new (or editing an existing) record on
the risk form, you need to make sure the data for the record
is saved before another form can use it. Add this line of
code at the top of the button's click event procedure:
Me.Dirty = False

The related parent record in the hazard table should have
been saved automatically when the focus moved to the risk
subform (e.g. click on button).

 

[djf]

HI.
I tried you suggestion but I still get the error.
So just to clarify: I have a table called RiskComments
PK riskcommentsID
FK riskid

In the relationship view I have linked riskid in the risk table to the
riskid in the RiskComments table.

I have placed the command button on the risk form to open a form called
Risk_comments.


Also during testing of your Correlated Subforms coding idea I encounter an
issue (Error, You can't add or change a record because a…) when you enter
information without the previous subform being filled out. Which we all
know will happen at some point.

What is your view on how to resolve this issue?

I did a bit of hunting a round and found the following on the net from Bob
Bedell which sums up the issue well:-

Error 3201 arrises when you try to perform an operation that would have
violated referential integrity rules for related tables. In your case, this
error occurs if you try to change or insert a record in the "many" table in
a one-to-many relationship (say, tblOrders) and that record doesn't have a
related record in tblCustomer on the "one" side. The following code
instructs users that if they want to add or change the record on the "many"
side, they must first add a record to the "one" table that contains the same
value for the matching field. The code returns focus to the Customer ID
field of the parent form after canceling the attempted insert on the sub
form.


Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim strMsg As String

If DataErr = 3201 Then
strMsg = "You must select a Customer for this order " & vbCrLf
strMsg = strMsg & "before entering order detail info." & vbCrLf &
vbCrLf
strMsg = strMsg & "Please enter a Customer ID on the parent form."

MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
vbCritical + vbOKOnly, "No Customer ID entered."

RunCommand acCmdUndo
Me.Parent!CustomerID.SetFocus
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If


End Sub

However, there are other errors that could arise from leaving the Customer
ID field of the parent form blank that would not trigger 3201.
(For example, the Customer ID could already exist in the parent table, but
simply isn't entered on the parent form.) Basically, what you want to
accomplish is to ensure that the Customer ID on the parent form is filled
in. The following code is probably your best bet to cover all bases:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr > 0 Then
If IsNull(Me.Parent!CustomerID) Then
MsgBox "Select a Customer for this order before entering order
details info."
RunCommand acCmdUndo
Me.Parent!CustomerID.SetFocus
Response = acDataErrContinue
Else
Response = acDataErrDisplay
End If
End If

End Sub

 

[Marshall Barton]

I tried you suggestion but I still get the error.
So just to clarify: I have a table called RiskComments
PK riskcommentsID
FK riskid

In the relationship view I have linked riskid in the risk table to the
riskid in the RiskComments table.

I have placed the command button on the risk form to open a form called
Risk_comments.

I thought you said the comment was in the risk table. If it
is in a separate table, then the risk entry must exist
before you can comment on it (i.e. how can you expect to
comment on a nonexistent risk?)

I think the way to deal with this is to use the risk form's
Current event to disable the button when the current risk
entry is a blank new record:

Me.button.Enabled = Me.NewRecord And Not Me.Dirty

If the current record has some data entered in it, then, as
I said before, save the record before opening the comment
popup form:

Me.Dirty = False

Also, don't forget to set the comment record's FK value (in
the comments form's BeforeUpdate event:

Me.riskid = Forms!hazards.risksubform.riskid

Also during testing of your Correlated Subforms coding idea I encounter an
issue (Error, You can't add or change a record because a…) when you enter
information without the previous subform being filled out. Which we all
know will happen at some point.

What is your view on how to resolve this issue?

Sounds like the same issue as the comments. How can it make
sense to add data for a nonexistent item? I think I would
use each form's Current event to disable the correlated
subform when the "parent" subform has no data:

Parent.nextsubform.Enabled = Me.NewRecord And Not Me.Dirty

I don't have any idea what data would be required before a
correlated record would be valid, but if any data in any
field is sufficient, you can use the form's Dirty event to
enable the correlated subform:

Parent.somesubformcontrol.Enabled = True