HELP: "Cannot add or change record"

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

Guest

The tables are as follows:

tble_query -.> 1:n -> tble_CCode <- n:1 <- lkup_CCodeS <- n:1 <- lkup_CCodeT

I have a form that holds "query" information and a subform that holds
"CCode" information. Within this subform I have a cascading combo that uses
"CCodeT" and "CCodeS" as the source information.

I have all the information I need in the look-up tables and the combo's
work fine. If I choose from the list in the first combo, I can delete the
record, but if I choose from the second combo I get the above error and it
also says "a related record is required in the table "lkup_CCodeS"

The master and child link properties are fine so I don't know how to correct
the problem.

Cheers!
 
Near as I can follow, it is saying that the record you just deleted
was required to create a related record. With relationships enforced
you can't create child records without a parent.
 
Presumably the combo box based on lkup_CCodeS is bound to a field in
tble_CCode. To what field is the combo box based on lkup_CCodeT bound?
This does seem to be a problem with the link properties.
 
tble_Query
QueryID (PK)
CustomerName
Address1

tble_CCode
QueryID (FK)
Type
Description (FK)
CCode

lkup_CcodeS
CCodeT_ID (FK)
CCodeID
CCode
Description (PK)

lkup_CCodeT
CCodeT_ID (PK)
Description

In the subform,

the first combo box is called "type", the source is
[lkup_CCodeT]![description] and is bound to [tble_CCode]![type]

the second combo box is called "description", the source is
[lkup_CcodeS]![description] and is bound to [tble_CCode]![description]

I have deleted the subform and re-established the connection.

In the old version, the main form had two individual subforms (not one
inside the other). One was for branch address and the second for query
information.

I know have a main form and one subform but I can't see that it would affect
the problem.

thanks
 
scubadiver said:
tble_Query
QueryID (PK)
CustomerName
Address1

tble_CCode
QueryID (FK)
Type
Description (FK)
CCode

lkup_CcodeS
CCodeT_ID (FK)
CCodeID
CCode
Description (PK)

lkup_CCodeT
CCodeT_ID (PK)
Description

In the subform,

the first combo box is called "type", the source is
[lkup_CCodeT]![description] and is bound to [tble_CCode]![type]

the second combo box is called "description", the source is
[lkup_CcodeS]![description] and is bound to [tble_CCode]![description]
Click to expand...

If the two combo boxes are bound to field in tble_CCode, their row source
tables should both be related to tble_CCode, rather than one of the row
source tables being bound to the other.

tble_CCode
QueryID (FK)
Type (FK)
Description (FK)
CCode

lkup_CCodeS
Description (PK)

lkup_CCodeT
Type (PK)

I have suggested a name change for the field in CCodeT, since it is bound to
Type in CCode, and I excluded fields that from what I can tell do not enter
into the relationship, but if they are helpful by all means keep them in
place. I can't tell from the field descriptions just what they do. Also,
if you are storing the literal text of Description and Type you could use
those as the PKs, but if you change Type or Description in the lookup
tables, the old stored values in CCode will no longer create the needed link
between records unless you use Cascade Update. Depending on the
circumstances, you may want something like this instead:

tble_CCode
QueryID (FK)
CCodeS_ID (FK)
CCodeT_ID (FK)
CCode

lkup_CCodeS
CCodeS_ID (PK)
Description

lkup_CCodeT
CCodeT_ID (PK)
Type
I have deleted the subform and re-established the connection.

In the old version, the main form had two individual subforms (not one
inside the other). One was for branch address and the second for query
information.
Click to expand...

I think understand the idea of "branch address", but "query information"
could be anything from my vantage point. You can still use cascading combo
boxes in which the row source of one depends on the selection made in the
other. If selecting a Type limits the list of Descriptions to just those
associated with the selected Type (one type => many descriptions), there may
be a relationship between the two lookup tables, but it would be that
CCodeT_ID is added to lkup_CCodeS as a FK. The After Update event of the
Type combo box would set the row source of the Description combo box so that
it includes only records related to the selected Type. If a Description may
be associated with several Types it becomes more complex in that a junction
table may be involved in the relationship between the lookup tables.
 
Thanks for the reply.

The relationship is one way (a description cannot refer to more than one
type).

I am storing the literal text so I can see a potential problem with changing
information in the lookup tables. I hadn't considered it because what I am
storing is extremely unlikely to change (due to the nature of the
information).

I would like to add that the tables and their relationships have not changed
from a previous version. It worked perfectly ok so I can't see why it should
be going wrong now.

thanks

BruceM said:
scubadiver said:
tble_Query
QueryID (PK)
CustomerName
Address1

tble_CCode
QueryID (FK)
Type
Description (FK)
CCode

lkup_CcodeS
CCodeT_ID (FK)
CCodeID
CCode
Description (PK)

lkup_CCodeT
CCodeT_ID (PK)
Description

In the subform,

the first combo box is called "type", the source is
[lkup_CCodeT]![description] and is bound to [tble_CCode]![type]

the second combo box is called "description", the source is
[lkup_CcodeS]![description] and is bound to [tble_CCode]![description]
Click to expand...

If the two combo boxes are bound to field in tble_CCode, their row source
tables should both be related to tble_CCode, rather than one of the row
source tables being bound to the other.

tble_CCode
QueryID (FK)
Type (FK)
Description (FK)
CCode

lkup_CCodeS
Description (PK)

lkup_CCodeT
Type (PK)

I have suggested a name change for the field in CCodeT, since it is bound to
Type in CCode, and I excluded fields that from what I can tell do not enter
into the relationship, but if they are helpful by all means keep them in
place. I can't tell from the field descriptions just what they do. Also,
if you are storing the literal text of Description and Type you could use
those as the PKs, but if you change Type or Description in the lookup
tables, the old stored values in CCode will no longer create the needed link
between records unless you use Cascade Update. Depending on the
circumstances, you may want something like this instead:

tble_CCode
QueryID (FK)
CCodeS_ID (FK)
CCodeT_ID (FK)
CCode

lkup_CCodeS
CCodeS_ID (PK)
Description

lkup_CCodeT
CCodeT_ID (PK)
Type
I have deleted the subform and re-established the connection.

In the old version, the main form had two individual subforms (not one
inside the other). One was for branch address and the second for query
information.
Click to expand...

I think understand the idea of "branch address", but "query information"
could be anything from my vantage point. You can still use cascading combo
boxes in which the row source of one depends on the selection made in the
other. If selecting a Type limits the list of Descriptions to just those
associated with the selected Type (one type => many descriptions), there may
be a relationship between the two lookup tables, but it would be that
CCodeT_ID is added to lkup_CCodeS as a FK. The After Update event of the
Type combo box would set the row source of the Description combo box so that
it includes only records related to the selected Type. If a Description may
be associated with several Types it becomes more complex in that a junction
table may be involved in the relationship between the lookup tables.
I know have a main form and one subform but I can't see that it would
affect
the problem.

thanks
Click to expand...
Click to expand...
 
I've solved it but the solution is so simple it was something I hadn't even
remotely considered.

BruceM said:
scubadiver said:
tble_Query
QueryID (PK)
CustomerName
Address1

tble_CCode
QueryID (FK)
Type
Description (FK)
CCode

lkup_CcodeS
CCodeT_ID (FK)
CCodeID
CCode
Description (PK)

lkup_CCodeT
CCodeT_ID (PK)
Description

In the subform,

the first combo box is called "type", the source is
[lkup_CCodeT]![description] and is bound to [tble_CCode]![type]

the second combo box is called "description", the source is
[lkup_CcodeS]![description] and is bound to [tble_CCode]![description]
Click to expand...

If the two combo boxes are bound to field in tble_CCode, their row source
tables should both be related to tble_CCode, rather than one of the row
source tables being bound to the other.

tble_CCode
QueryID (FK)
Type (FK)
Description (FK)
CCode

lkup_CCodeS
Description (PK)

lkup_CCodeT
Type (PK)

I have suggested a name change for the field in CCodeT, since it is bound to
Type in CCode, and I excluded fields that from what I can tell do not enter
into the relationship, but if they are helpful by all means keep them in
place. I can't tell from the field descriptions just what they do. Also,
if you are storing the literal text of Description and Type you could use
those as the PKs, but if you change Type or Description in the lookup
tables, the old stored values in CCode will no longer create the needed link
between records unless you use Cascade Update. Depending on the
circumstances, you may want something like this instead:

tble_CCode
QueryID (FK)
CCodeS_ID (FK)
CCodeT_ID (FK)
CCode

lkup_CCodeS
CCodeS_ID (PK)
Description

lkup_CCodeT
CCodeT_ID (PK)
Type
I have deleted the subform and re-established the connection.

In the old version, the main form had two individual subforms (not one
inside the other). One was for branch address and the second for query
information.
Click to expand...

I think understand the idea of "branch address", but "query information"
could be anything from my vantage point. You can still use cascading combo
boxes in which the row source of one depends on the selection made in the
other. If selecting a Type limits the list of Descriptions to just those
associated with the selected Type (one type => many descriptions), there may
be a relationship between the two lookup tables, but it would be that
CCodeT_ID is added to lkup_CCodeS as a FK. The After Update event of the
Type combo box would set the row source of the Description combo box so that
it includes only records related to the selected Type. If a Description may
be associated with several Types it becomes more complex in that a junction
table may be involved in the relationship between the lookup tables.
I know have a main form and one subform but I can't see that it would
affect
the problem.

thanks
Click to expand...
Click to expand...
 
What was it?

scubadiver said:
I've solved it but the solution is so simple it was something I hadn't
even
remotely considered.

BruceM said:
scubadiver said:
tble_Query
QueryID (PK)
CustomerName
Address1

tble_CCode
QueryID (FK)
Type
Description (FK)
CCode

lkup_CcodeS
CCodeT_ID (FK)
CCodeID
CCode
Description (PK)

lkup_CCodeT
CCodeT_ID (PK)
Description

In the subform,

the first combo box is called "type", the source is
[lkup_CCodeT]![description] and is bound to [tble_CCode]![type]

the second combo box is called "description", the source is
[lkup_CcodeS]![description] and is bound to [tble_CCode]![description]
Click to expand...

If the two combo boxes are bound to field in tble_CCode, their row source
tables should both be related to tble_CCode, rather than one of the row
source tables being bound to the other.

tble_CCode
QueryID (FK)
Type (FK)
Description (FK)
CCode

lkup_CCodeS
Description (PK)

lkup_CCodeT
Type (PK)

I have suggested a name change for the field in CCodeT, since it is bound
to
Type in CCode, and I excluded fields that from what I can tell do not
enter
into the relationship, but if they are helpful by all means keep them in
place. I can't tell from the field descriptions just what they do.
Also,
if you are storing the literal text of Description and Type you could use
those as the PKs, but if you change Type or Description in the lookup
tables, the old stored values in CCode will no longer create the needed
link
between records unless you use Cascade Update. Depending on the
circumstances, you may want something like this instead:

tble_CCode
QueryID (FK)
CCodeS_ID (FK)
CCodeT_ID (FK)
CCode

lkup_CCodeS
CCodeS_ID (PK)
Description

lkup_CCodeT
CCodeT_ID (PK)
Type
I have deleted the subform and re-established the connection.

In the old version, the main form had two individual subforms (not one
inside the other). One was for branch address and the second for query
information.
Click to expand...

I think understand the idea of "branch address", but "query information"
could be anything from my vantage point. You can still use cascading
combo
boxes in which the row source of one depends on the selection made in the
other. If selecting a Type limits the list of Descriptions to just those
associated with the selected Type (one type => many descriptions), there
may
be a relationship between the two lookup tables, but it would be that
CCodeT_ID is added to lkup_CCodeS as a FK. The After Update event of the
Type combo box would set the row source of the Description combo box so
that
it includes only records related to the selected Type. If a Description
may
be associated with several Types it becomes more complex in that a
junction
table may be involved in the relationship between the lookup tables.
I know have a main form and one subform but I can't see that it would
affect
the problem.

thanks

:

Presumably the combo box based on lkup_CCodeS is bound to a field in
tble_CCode. To what field is the combo box based on lkup_CCodeT
bound?
This does seem to be a problem with the link properties.

The tables are as follows:

tble_query -.> 1:n -> tble_CCode <- n:1 <- lkup_CCodeS <- n:1 <-
lkup_CCodeT

I have a form that holds "query" information and a subform that
holds
"CCode" information. Within this subform I have a cascading combo
that
uses
"CCodeT" and "CCodeS" as the source information.

I have all the information I need in the look-up tables and the
combo's
work fine. If I choose from the list in the first combo, I can
delete
the
record, but if I choose from the second combo I get the above error
and
it
also says "a related record is required in the table "lkup_CCodeS"

The master and child link properties are fine so I don't know how to
correct
the problem.

Cheers!
Click to expand...
Click to expand...
Click to expand...
 
*ahem*

referencing the wrong "bound column"


BruceM said:
What was it?

scubadiver said:
I've solved it but the solution is so simple it was something I hadn't
even
remotely considered.

BruceM said:
tble_Query
QueryID (PK)
CustomerName
Address1

tble_CCode
QueryID (FK)
Type
Description (FK)
CCode

lkup_CcodeS
CCodeT_ID (FK)
CCodeID
CCode
Description (PK)

lkup_CCodeT
CCodeT_ID (PK)
Description

In the subform,

the first combo box is called "type", the source is
[lkup_CCodeT]![description] and is bound to [tble_CCode]![type]

the second combo box is called "description", the source is
[lkup_CcodeS]![description] and is bound to [tble_CCode]![description]

If the two combo boxes are bound to field in tble_CCode, their row source
tables should both be related to tble_CCode, rather than one of the row
source tables being bound to the other.

tble_CCode
QueryID (FK)
Type (FK)
Description (FK)
CCode

lkup_CCodeS
Description (PK)

lkup_CCodeT
Type (PK)

I have suggested a name change for the field in CCodeT, since it is bound
to
Type in CCode, and I excluded fields that from what I can tell do not
enter
into the relationship, but if they are helpful by all means keep them in
place. I can't tell from the field descriptions just what they do.
Also,
if you are storing the literal text of Description and Type you could use
those as the PKs, but if you change Type or Description in the lookup
tables, the old stored values in CCode will no longer create the needed
link
between records unless you use Cascade Update. Depending on the
circumstances, you may want something like this instead:

tble_CCode
QueryID (FK)
CCodeS_ID (FK)
CCodeT_ID (FK)
CCode

lkup_CCodeS
CCodeS_ID (PK)
Description

lkup_CCodeT
CCodeT_ID (PK)
Type


I have deleted the subform and re-established the connection.

In the old version, the main form had two individual subforms (not one
inside the other). One was for branch address and the second for query
information.

I think understand the idea of "branch address", but "query information"
could be anything from my vantage point. You can still use cascading
combo
boxes in which the row source of one depends on the selection made in the
other. If selecting a Type limits the list of Descriptions to just those
associated with the selected Type (one type => many descriptions), there
may
be a relationship between the two lookup tables, but it would be that
CCodeT_ID is added to lkup_CCodeS as a FK. The After Update event of the
Type combo box would set the row source of the Description combo box so
that
it includes only records related to the selected Type. If a Description
may
be associated with several Types it becomes more complex in that a
junction
table may be involved in the relationship between the lookup tables.


I know have a main form and one subform but I can't see that it would
affect
the problem.

thanks

:

Presumably the combo box based on lkup_CCodeS is bound to a field in
tble_CCode. To what field is the combo box based on lkup_CCodeT
bound?
This does seem to be a problem with the link properties.

The tables are as follows:

tble_query -.> 1:n -> tble_CCode <- n:1 <- lkup_CCodeS <- n:1 <-
lkup_CCodeT

I have a form that holds "query" information and a subform that
holds
"CCode" information. Within this subform I have a cascading combo
that
uses
"CCodeT" and "CCodeS" as the source information.

I have all the information I need in the look-up tables and the
combo's
work fine. If I choose from the list in the first combo, I can
delete
the
record, but if I choose from the second combo I get the above error
and
it
also says "a related record is required in the table "lkup_CCodeS"

The master and child link properties are fine so I don't know how to
correct
the problem.

Cheers!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HELP: Error message "You cannot add or change a record" 1
"You cannot add or change a record": HELP please 1
A quick query on calculating percentages 1
Checkbox to change forms record source 1
Cascading ComboBox on SubForm 6
Problem designing a form envolving many-to-many relationships 3
Combo Box on Main Form and limits Combo Box selection on the Subfo 1
Sorry to repost, but I cant get Agent to include the gif file. - relationships.gif (0/1) 2

Back
Top