related record is required in table

  • Thread starter Thread starter lettyg
  • Start date Start date
L

lettyg

I have a database in which I am tracking cases. Table 1 is Case Information
Table, that has the defendants name and other pertinent information. I have
a second table that includes the charges (count number, class, statue,
disposition, sentencing). 3rd table is a Victim Witness table that includes
information of how many times the victim was contacted per case and other
information essentially only one record from V/W table would be filled out
per case where as Charges can have 1 to 30 counts per case hence creating
seperate record for each charge. I created a form that has 2 subforms, the
case information form is displayed and then the charges subform is embedded,
the V/W subform is accessed by a control. The main form is the case
information form, subform for charges and a subform for the V/W table.
However when I am working from the main form and add my data and then click
on the control to open the V/W subform I get the error that the related
record is required in table Case information. I do not know what I am doing
wrong.

Can someone help.
 
lettyg said:
I have a database in which I am tracking cases. Table 1 is Case
Information
Table, that has the defendants name and other pertinent information. I
have
a second table that includes the charges (count number, class, statue,
disposition, sentencing). 3rd table is a Victim Witness table that
includes
information of how many times the victim was contacted per case and other
information essentially only one record from V/W table would be filled out
per case where as Charges can have 1 to 30 counts per case hence creating
seperate record for each charge. I created a form that has 2 subforms,
the
case information form is displayed and then the charges subform is
embedded,
the V/W subform is accessed by a control. The main form is the case
information form, subform for charges and a subform for the V/W table.
However when I am working from the main form and add my data and then
click
on the control to open the V/W subform I get the error that the related
record is required in table Case information. I do not know what I am
doing
wrong.
Click to expand...

From what you've written, the V/W "subform" is technically not a subform at
all; it's just a separate, related, main form. When you use a true
subform, embedded on the main form, Access automatically saves the main
form's record when you move the focus to the subform, so that the parent
record exists and referential integrity is satisfied. When you open a
related main form, though, this doesn't happen, so your own code (or macro)
has to force the parent record to be saved before opening the related form.

If you have a command button (or other control) with a click event that does
something like this:

DoCmd.OpenForm "YourRelatedForm"

.... you can force the current form's record to be saved first in a number of
ways. I like this one:

If Me.Dirty Then Me.Dirty = False

DoCmd.OpenForm "YourRelatedForm"
 
This is the information that appears on the Event Procedure:
Option Compare Database

Private Sub Form_Click()

End Sub

Private Sub V_W_Click()
On Error GoTo Err_V_W_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "tbl FV VW subform"
DoCmd.OpenForm stDocName, , , stLinkCriteria



Exit_V_W_Click:
Exit Sub

Err_V_W_Click:
MsgBox Err.Description
Resume Exit_V_W_Click

End Sub



i am not sure how to change the even to your recommendation.
 
lettyg said:
This is the information that appears on the Event Procedure:
Option Compare Database

Private Sub Form_Click()

End Sub

Private Sub V_W_Click()
On Error GoTo Err_V_W_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "tbl FV VW subform"
DoCmd.OpenForm stDocName, , , stLinkCriteria



Exit_V_W_Click:
Exit Sub

Err_V_W_Click:
MsgBox Err.Description
Resume Exit_V_W_Click

End Sub


i am not sure how to change the even to your recommendation.
Click to expand...


Change it to:

'------ start of revised code ------
Option Compare Database
Option Explicit

Private Sub Form_Click()

End Sub

Private Sub V_W_Click()
On Error GoTo Err_V_W_Click

Dim stDocName As String
Dim stLinkCriteria As String

' Force current record to be saved.
If Me.Dirty Then Me.Dirty = False

stDocName = "tbl FV VW subform"
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_V_W_Click:
Exit Sub

Err_V_W_Click:
MsgBox Err.Description
Resume Exit_V_W_Click

End Sub
'------ end of revised code ------
 
Thank you so much for your help. However I still get the same error message
as before "You can not add or change a record because a related record is
required in table 'tbl FV Case Info'

tbl FV Case Info Table : (field name=tbl Id No "is primary key";data
type:Auto Number;Long Integer;Increment;Yes no duplicates)

tbl FV Charges : (field name=tbl Id No "no primary keys assigned";data
type:Number;Long Integer;Decimal Places: Auto;default
value:0;required:No:Indexed: Yes duplicates ok)

tbl FV VW: (field name=tbl Id No "no primary keys assigned";data
type:Number;Long Integer;Decimal Places: Auto;default
value:0;required:No:Indexed: Yes duplicates ok)

The following are my relationship settings:

tbl FV Case Info tbl FV Charges
tbl FV Case Info tbl FV Charges
tbl Id No 1 ï‚¥ tbl ID No
Attributes: Enforced
RelationshipType: One-To-Many

tbl FV Case Infotbl FV VW
tbl FV Case Info tbl FV VW
tbl Id No 1 ï‚¥ tbl Id No
Attributes: Enforced
RelationshipType: One-To-Many
 
lettyg said:
Thank you so much for your help. However I still get the same error
message
as before "You can not add or change a record because a related record is
required in table 'tbl FV Case Info'

tbl FV Case Info Table : (field name=tbl Id No "is primary key";data
type:Auto Number;Long Integer;Increment;Yes no duplicates)

tbl FV Charges : (field name=tbl Id No "no primary keys assigned";data
type:Number;Long Integer;Decimal Places: Auto;default
value:0;required:No:Indexed: Yes duplicates ok)

tbl FV VW: (field name=tbl Id No "no primary keys assigned";data
type:Number;Long Integer;Decimal Places: Auto;default
value:0;required:No:Indexed: Yes duplicates ok)

The following are my relationship settings:

tbl FV Case Info tbl FV Charges
tbl FV Case Info tbl FV Charges
tbl Id No 1 ï‚¥ tbl ID No
Attributes: Enforced
RelationshipType: One-To-Many

tbl FV Case Infotbl FV VW
tbl FV Case Info tbl FV VW
tbl Id No 1 ï‚¥ tbl Id No
Attributes: Enforced
RelationshipType: One-To-Many
Click to expand...


Here are some side comments before I address the main issue:

1. You would do well not to have spaces or other punctuation in the names of
your tables, fields, or other objects. They make it more cumbersome to
refer to them, as the names must always be enclosed in square brackets ([]).

2. You should remove the default value 0 from the foreign key fields in your
related tables, [tbl FV Charges] and [tbl FV VW]. These fields must always
be set to refer to a valid record in [tbl FV Case Info], so having a default
value of 0 only confuses the issue. Also, I imagine that the foreign-key
fields should have their Required property set to Yes, unless you're going
to have records in these tables that have no assigned case record.

3. Although Access allows a table to have no primary key, it's a good idea
to give every table a primary key. Apparently your child tables don't. If
there is no combination of fields that make a good primary key, go ahead and
use an autonumber.

Now the main point. I had assumed that you had code in your form "tbl FV VW
subform" that assigned the value of the current Case Info record's [tbl Id
No] field to the foreign key field of any new record you created.
Apparently you don't, so when a new record is created the foreign key is not
set. You're going to need code in the form to make that happen. You may
also need to filter that form by the current case's ID value.

One way to do this is to have the code that opens the form (from your main
form) filter the form to show only the records with the same [tbl Id No],
and also set the DefaultValue property of the [tbl Id No] control on that
form to the current ID number. Try this revision of your button's Click
event procedure:

'------ start of code ------
Private Sub V_W_Click()
On Error GoTo Err_V_W_Click

Dim stDocName As String
Dim stLinkCriteria As String

' Force current record to be saved.
If Me.Dirty Then Me.Dirty = False

stLinkCriteria = "[tbl Id No] = " & Me![tbl Id No]
stDocName = "tbl FV VW subform"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Forms(stDocName)![tbl Id No].DefaultValue = Me![tbl Id No]

Exit_V_W_Click:
Exit Sub

Err_V_W_Click:
MsgBox Err.Description
Resume Exit_V_W_Click

End Sub
'------ end of revised code ------

I can't guarantee that this will work, as there could be a couple of
variations in the way you might have set things up that would interfere with
it. Try it and let me know how it goes.
 
It appears to be working. I appreciate the help and the time you took to
assist me with this issue.

Thank you!

Dirk Goldgar said:
lettyg said:
Thank you so much for your help. However I still get the same error
message
as before "You can not add or change a record because a related record is
required in table 'tbl FV Case Info'

tbl FV Case Info Table : (field name=tbl Id No "is primary key";data
type:Auto Number;Long Integer;Increment;Yes no duplicates)

tbl FV Charges : (field name=tbl Id No "no primary keys assigned";data
type:Number;Long Integer;Decimal Places: Auto;default
value:0;required:No:Indexed: Yes duplicates ok)

tbl FV VW: (field name=tbl Id No "no primary keys assigned";data
type:Number;Long Integer;Decimal Places: Auto;default
value:0;required:No:Indexed: Yes duplicates ok)

The following are my relationship settings:

tbl FV Case Info tbl FV Charges
tbl FV Case Info tbl FV Charges
tbl Id No 1 ï‚¥ tbl ID No
Attributes: Enforced
RelationshipType: One-To-Many

tbl FV Case Infotbl FV VW
tbl FV Case Info tbl FV VW
tbl Id No 1 ï‚¥ tbl Id No
Attributes: Enforced
RelationshipType: One-To-Many
Click to expand...


Here are some side comments before I address the main issue:

1. You would do well not to have spaces or other punctuation in the names of
your tables, fields, or other objects. They make it more cumbersome to
refer to them, as the names must always be enclosed in square brackets ([]).

2. You should remove the default value 0 from the foreign key fields in your
related tables, [tbl FV Charges] and [tbl FV VW]. These fields must always
be set to refer to a valid record in [tbl FV Case Info], so having a default
value of 0 only confuses the issue. Also, I imagine that the foreign-key
fields should have their Required property set to Yes, unless you're going
to have records in these tables that have no assigned case record.

3. Although Access allows a table to have no primary key, it's a good idea
to give every table a primary key. Apparently your child tables don't. If
there is no combination of fields that make a good primary key, go ahead and
use an autonumber.

Now the main point. I had assumed that you had code in your form "tbl FV VW
subform" that assigned the value of the current Case Info record's [tbl Id
No] field to the foreign key field of any new record you created.
Apparently you don't, so when a new record is created the foreign key is not
set. You're going to need code in the form to make that happen. You may
also need to filter that form by the current case's ID value.

One way to do this is to have the code that opens the form (from your main
form) filter the form to show only the records with the same [tbl Id No],
and also set the DefaultValue property of the [tbl Id No] control on that
form to the current ID number. Try this revision of your button's Click
event procedure:

'------ start of code ------
Private Sub V_W_Click()
On Error GoTo Err_V_W_Click

Dim stDocName As String
Dim stLinkCriteria As String

' Force current record to be saved.
If Me.Dirty Then Me.Dirty = False

stLinkCriteria = "[tbl Id No] = " & Me![tbl Id No]
stDocName = "tbl FV VW subform"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Forms(stDocName)![tbl Id No].DefaultValue = Me![tbl Id No]

Exit_V_W_Click:
Exit Sub

Err_V_W_Click:
MsgBox Err.Description
Resume Exit_V_W_Click

End Sub
'------ end of revised code ------

I can't guarantee that this will work, as there could be a couple of
variations in the way you might have set things up that would interfere with
it. Try it and let me know how it goes.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
Back
Top