Subform Problem "The value you entered isn't valid for this field" error

[Ecohouse]

I have a main form with two subforms. The first subform has the child
link to the main form identity key.

subform1 - Master Field: SK
Child Field: TrainingMasterSK

The second subform has a master-child link to the first subform.

subform2 - Master Field: subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK

All of the links are working and I can see the correct data. I'm able
to enter new records on the first subform with no problem.

But when I go to enter a record in the second subform I get the
following error message: "The value you entered isn't valid forthis
field". This is happening in the BeforeInsert event.

Once I move past the message box then the data in that controls shows
up. When I check the table that this subform is using I can see that
when a new record is created the TrainingMasterSK field is populated
but the TrainingModuleTopicSK field isn't.

The TrainingMasterSK field comes from the main form and the
TrainingModuleTopicSK field comes from the first subform. And because
the TrainingModuleTopicSK isn't being populated once the record is
saved there is no link to the first subform.

I'm under the assumption that the second subform should automatically
fill in the TrainingModuleTopicSK field when a new record is entered.

So at this point I have no idea what to do or how to get around this
problem. Any help would be appreciated.

[Allen Browne]

Hmm. There's a few possible things here.

You might try adding a text box to the main form, with properties:
Control Source =[subTrainingModule].[Form]![TrainingModuleTopicSK]
Name txtTrainingModuleTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterFields property of the 2nd
subform.

What that does is to set the 2nd subform to be dependent on a text box on
the main form, rather than a control in another subform. You might find that
this dependency (the normal one Access expects for subforms) solves the
timing problem.

If that doesn't work, is subform2 based on a query that contains more than
one table? If so, there are a couple of other potential issues.
a) Double-check that the key field in the query is from the table you
actually want to write the record to. If it's from the other table, the
attempt to assign the value will fail and the value of the real key field
won't be set.

b) Open the other table in design view (i.e. the one you are not appending
the record to.) Are there any fields with a Default Value set? If so, delete
the Default Value. Access can misinterpret this and try (unsuccessfully) to
assign the value, even though you are not trying to add a record to that
table. Any controls on your subform that come from the other table(s) may
also need their Default Value cleared.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@a3g2000cwd.googlegro ups.com...

>I have a main form with two subforms. The first subform has the child
link to the main form identity key.

subform1 - Master Field: SK
Child Field: TrainingMasterSK

The second subform has a master-child link to the first subform.

subform2 - Master Field: subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK

All of the links are working and I can see the correct data. I'm able
to enter new records on the first subform with no problem.

But when I go to enter a record in the second subform I get the
following error message: "The value you entered isn't valid forthis
field". This is happening in the BeforeInsert event.

Once I move past the message box then the data in that controls shows
up. When I check the table that this subform is using I can see that
when a new record is created the TrainingMasterSK field is populated
but the TrainingModuleTopicSK field isn't.

The TrainingMasterSK field comes from the main form and the
TrainingModuleTopicSK field comes from the first subform. And because
the TrainingModuleTopicSK isn't being populated once the record is
saved there is no link to the first subform.

I'm under the assumption that the second subform should automatically
fill in the TrainingModuleTopicSK field when a new record is entered.

So at this point I have no idea what to do or how to get around this
problem. Any help would be appreciated.

[Ecohouse]

Thanks for the help Allen. The subform isn't based on a query, only
one table. If I add the control on the main form I assume that I have
to requery it form subform1 whenever I move to a new record?

Once the new control receives the new value will it automatically
update subform2?
Allen Browne wrote:

Hmm. There's a few possible things here.

You might try adding a text box to the main form, with properties:
Control Source =[subTrainingModule].[Form]![TrainingModuleTopicSK]
Name txtTrainingModuleTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterFields property of the 2nd
subform.

What that does is to set the 2nd subform to be dependent on a text box on
the main form, rather than a control in another subform. You might find that
this dependency (the normal one Access expects for subforms) solves the
timing problem.

If that doesn't work, is subform2 based on a query that contains more than
one table? If so, there are a couple of other potential issues.
a) Double-check that the key field in the query is from the table you
actually want to write the record to. If it's from the other table, the
attempt to assign the value will fail and the value of the real key field
won't be set.

b) Open the other table in design view (i.e. the one you are not appending
the record to.) Are there any fields with a Default Value set? If so, delete
the Default Value. Access can misinterpret this and try (unsuccessfully) to
assign the value, even though you are not trying to add a record to that
table. Any controls on your subform that come from the other table(s) may
also need their Default Value cleared.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@a3g2000cwd.googlegro ups.com...

I have a main form with two subforms. The first subform has the child
link to the main form identity key.

subform1 - Master Field: SK
Child Field: TrainingMasterSK

The second subform has a master-child link to the first subform.

subform2 - Master Field: subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK

All of the links are working and I can see the correct data. I'm able
to enter new records on the first subform with no problem.

But when I go to enter a record in the second subform I get the
following error message: "The value you entered isn't valid forthis
field". This is happening in the BeforeInsert event.

Once I move past the message box then the data in that controls shows
up. When I check the table that this subform is using I can see that
when a new record is created the TrainingMasterSK field is populated
but the TrainingModuleTopicSK field isn't.

The TrainingMasterSK field comes from the main form and the
TrainingModuleTopicSK field comes from the first subform. And because
the TrainingModuleTopicSK isn't being populated once the record is
saved there is no link to the first subform.

I'm under the assumption that the second subform should automatically
fill in the TrainingModuleTopicSK field when a new record is entered.

So at this point I have no idea what to do or how to get around this
problem. Any help would be appreciated.

[Allen Browne]

The text box will requery itself, and when it does the other subform will
requery.

If the timing is not adequate, you can force the update after the first
subform changes if you Recalc the main form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@79g2000cws.googlegro ups.com...

Thanks for the help Allen. The subform isn't based on a query, only
one table. If I add the control on the main form I assume that I have
to requery it form subform1 whenever I move to a new record?

Once the new control receives the new value will it automatically
update subform2?
Allen Browne wrote:

>Hmm. There's a few possible things here.

You might try adding a text box to the main form, with properties:
Control Source =[subTrainingModule].[Form]![TrainingModuleTopicSK]
Name txtTrainingModuleTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterFields property of the 2nd
subform.

What that does is to set the 2nd subform to be dependent on a text box on
the main form, rather than a control in another subform. You might find
that
this dependency (the normal one Access expects for subforms) solves the
timing problem.

If that doesn't work, is subform2 based on a query that contains more
than
one table? If so, there are a couple of other potential issues.
a) Double-check that the key field in the query is from the table you
actually want to write the record to. If it's from the other table, the
attempt to assign the value will fail and the value of the real key field
won't be set.

b) Open the other table in design view (i.e. the one you are not
appending
the record to.) Are there any fields with a Default Value set? If so,
delete
the Default Value. Access can misinterpret this and try (unsuccessfully)
to
assign the value, even though you are not trying to add a record to that
table. Any controls on your subform that come from the other table(s) may
also need their Default Value cleared.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@a3g2000cwd.googlegr oups.com...

>I have a main form with two subforms. The first subform has the child
link to the main form identity key.

subform1 - Master Field: SK
Child Field: TrainingMasterSK

The second subform has a master-child link to the first subform.

subform2 - Master Field: subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK

All of the links are working and I can see the correct data. I'm able
to enter new records on the first subform with no problem.

But when I go to enter a record in the second subform I get the
following error message: "The value you entered isn't valid forthis
field". This is happening in the BeforeInsert event.

Once I move past the message box then the data in that controls shows
up. When I check the table that this subform is using I can see that
when a new record is created the TrainingMasterSK field is populated
but the TrainingModuleTopicSK field isn't.

The TrainingMasterSK field comes from the main form and the
TrainingModuleTopicSK field comes from the first subform. And because
the TrainingModuleTopicSK isn't being populated once the record is
saved there is no link to the first subform.

I'm under the assumption that the second subform should automatically
fill in the TrainingModuleTopicSK field when a new record is entered.

So at this point I have no idea what to do or how to get around this
problem. Any help would be appreciated.

[Ecohouse]

Allen,

I tried your method. And the subforms are working in sync properly.
But when I go to add a record to subform2 I'm getting the following
error - The link MasterFields property setting has produced this
error: "The object doesn't contain the Automation object
txtTrainingModuleTopicSK."

I checked the Masterfield in subform2 and it is still set to
txtTrainingModuleTopicSK. Any ideas? It doesn't matter what field I
try to enter data in on subform2, it returns the same error message.

Thanks again for the help.
Ecohouse wrote:

Thanks for the help Allen. The subform isn't based on a query, only
one table. If I add the control on the main form I assume that I have
to requery it form subform1 whenever I move to a new record?

Once the new control receives the new value will it automatically
update subform2?
Allen Browne wrote:

Hmm. There's a few possible things here.

You might try adding a text box to the main form, with properties:
Control Source =[subTrainingModule].[Form]![TrainingModuleTopicSK]
Name txtTrainingModuleTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterFields property of the 2nd
subform.

What that does is to set the 2nd subform to be dependent on a text box on
the main form, rather than a control in another subform. You might find that
this dependency (the normal one Access expects for subforms) solves the
timing problem.

If that doesn't work, is subform2 based on a query that contains more than
one table? If so, there are a couple of other potential issues.
a) Double-check that the key field in the query is from the table you
actually want to write the record to. If it's from the other table, the
attempt to assign the value will fail and the value of the real key field
won't be set.

b) Open the other table in design view (i.e. the one you are not appending
the record to.) Are there any fields with a Default Value set? If so, delete
the Default Value. Access can misinterpret this and try (unsuccessfully) to
assign the value, even though you are not trying to add a record to that
table. Any controls on your subform that come from the other table(s) may
also need their Default Value cleared.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@a3g2000cwd.googlegro ups.com...

>I have a main form with two subforms. The first subform has the child
link to the main form identity key.
>
subform1 - Master Field: SK
Child Field: TrainingMasterSK
>
The second subform has a master-child link to the first subform.
>
subform2 - Master Field: subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK
>
All of the links are working and I can see the correct data. I'm able
to enter new records on the first subform with no problem.
>
But when I go to enter a record in the second subform I get the
following error message: "The value you entered isn't valid forthis
field". This is happening in the BeforeInsert event.
>
Once I move past the message box then the data in that controls shows
up. When I check the table that this subform is using I can see that
when a new record is created the TrainingMasterSK field is populated
but the TrainingModuleTopicSK field isn't.
>
The TrainingMasterSK field comes from the main form and the
TrainingModuleTopicSK field comes from the first subform. And because
the TrainingModuleTopicSK isn't being populated once the record is
saved there is no link to the first subform.
>
I'm under the assumption that the second subform should automatically
fill in the TrainingModuleTopicSK field when a new record is entered.
>
So at this point I have no idea what to do or how to get around this
problem. Any help would be appreciated.

[Allen Browne]

Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

Then compact the database:
Tools | Database Utilities | Compact/Repair

Double-check the name of the text box on your main form matches the name in
the LinkMasterFields property of the problem subform.

Check that the LinkChildFields property refers to a text box in the subform
(not merely a field in the subform's RecordSource.)

If that all checks out okay, close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the command prompt
while Access is not running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
Then compact again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

Allen,

I tried your method. And the subforms are working in sync properly.
But when I go to add a record to subform2 I'm getting the following
error - The link MasterFields property setting has produced this
error: "The object doesn't contain the Automation object
txtTrainingModuleTopicSK."

I checked the Masterfield in subform2 and it is still set to
txtTrainingModuleTopicSK. Any ideas? It doesn't matter what field I
try to enter data in on subform2, it returns the same error message.

Thanks again for the help.
Ecohouse wrote:

>Thanks for the help Allen. The subform isn't based on a query, only
one table. If I add the control on the main form I assume that I have
to requery it form subform1 whenever I move to a new record?

Once the new control receives the new value will it automatically
update subform2?
Allen Browne wrote:

Hmm. There's a few possible things here.

You might try adding a text box to the main form, with properties:
Control Source
=[subTrainingModule].[Form]![TrainingModuleTopicSK]
Name txtTrainingModuleTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterFields property of the 2nd
subform.

What that does is to set the 2nd subform to be dependent on a text box
on
the main form, rather than a control in another subform. You might find
that
this dependency (the normal one Access expects for subforms) solves the
timing problem.

If that doesn't work, is subform2 based on a query that contains more
than
one table? If so, there are a couple of other potential issues.
a) Double-check that the key field in the query is from the table you
actually want to write the record to. If it's from the other table, the
attempt to assign the value will fail and the value of the real key
field
won't be set.

b) Open the other table in design view (i.e. the one you are not
appending
the record to.) Are there any fields with a Default Value set? If so,
delete
the Default Value. Access can misinterpret this and try
(unsuccessfully) to
assign the value, even though you are not trying to add a record to
that
table. Any controls on your subform that come from the other table(s)
may
also need their Default Value cleared.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@a3g2000cwd.googlegro ups.com...
I have a main form with two subforms. The first subform has the child
link to the main form identity key.

subform1 - Master Field: SK
Child Field: TrainingMasterSK

The second subform has a master-child link to the first subform.

subform2 - Master Field: subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK

All of the links are working and I can see the correct data. I'm
able
to enter new records on the first subform with no problem.

But when I go to enter a record in the second subform I get the
following error message: "The value you entered isn't valid forthis
field". This is happening in the BeforeInsert event.

Once I move past the message box then the data in that controls shows
up. When I check the table that this subform is using I can see that
when a new record is created the TrainingMasterSK field is populated
but the TrainingModuleTopicSK field isn't.

The TrainingMasterSK field comes from the main form and the
TrainingModuleTopicSK field comes from the first subform. And
because
the TrainingModuleTopicSK isn't being populated once the record is
saved there is no link to the first subform.

I'm under the assumption that the second subform should automatically
fill in the TrainingModuleTopicSK field when a new record is entered.

So at this point I have no idea what to do or how to get around this
problem. Any help would be appreciated.

[Ecohouse]

Allen,

I tried everything that you suggested and I'm still having the same
problem as previously stated.

The other thing I didn't mention was that when you first click on the
combobox in subform2 when you move to select a choice it blanks out. I
don't know if that gives a clue as to what's going on or not.

But at this point I'm still stuck.

Allen Browne wrote:

Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

Then compact the database:
Tools | Database Utilities | Compact/Repair

Double-check the name of the text box on your main form matches the name in
the LinkMasterFields property of the problem subform.

Check that the LinkChildFields property refers to a text box in the subform
(not merely a field in the subform's RecordSource.)

If that all checks out okay, close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the command prompt
while Access is not running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
Then compact again.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...

Allen,

I tried your method. And the subforms are working in sync properly.
But when I go to add a record to subform2 I'm getting the following
error - The link MasterFields property setting has produced this
error: "The object doesn't contain the Automation object
txtTrainingModuleTopicSK."

I checked the Masterfield in subform2 and it is still set to
txtTrainingModuleTopicSK. Any ideas? It doesn't matter what field I
try to enter data in on subform2, it returns the same error message.

Thanks again for the help.
Ecohouse wrote:

Thanks for the help Allen. The subform isn't based on a query, only
one table. If I add the control on the main form I assume that I have
to requery it form subform1 whenever I move to a new record?

Once the new control receives the new value will it automatically
update subform2?
Allen Browne wrote:
Hmm. There's a few possible things here.

You might try adding a text box to the main form, with properties:
Control Source
=[subTrainingModule].[Form]![TrainingModuleTopicSK]
Name txtTrainingModuleTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterFields property of the 2nd
subform.

What that does is to set the 2nd subform to be dependent on a text box
on
the main form, rather than a control in another subform. You might find
that
this dependency (the normal one Access expects for subforms) solves the
timing problem.

If that doesn't work, is subform2 based on a query that contains more
than
one table? If so, there are a couple of other potential issues.
a) Double-check that the key field in the query is from the table you
actually want to write the record to. If it's from the other table, the
attempt to assign the value will fail and the value of the real key
field
won't be set.

b) Open the other table in design view (i.e. the one you are not
appending
the record to.) Are there any fields with a Default Value set? If so,
delete
the Default Value. Access can misinterpret this and try
(unsuccessfully) to
assign the value, even though you are not trying to add a record to
that
table. Any controls on your subform that come from the other table(s)
may
also need their Default Value cleared.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@a3g2000cwd.googlegro ups.com...
>I have a main form with two subforms. The first subform has the child
link to the main form identity key.
>
subform1 - Master Field: SK
Child Field: TrainingMasterSK
>
The second subform has a master-child link to the first subform.
>
subform2 - Master Field: subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK
>
All of the links are working and I can see the correct data. I'm
able
to enter new records on the first subform with no problem.
>
But when I go to enter a record in the second subform I get the
following error message: "The value you entered isn't valid forthis
field". This is happening in the BeforeInsert event.
>
Once I move past the message box then the data in that controls shows
up. When I check the table that this subform is using I can see that
when a new record is created the TrainingMasterSK field is populated
but the TrainingModuleTopicSK field isn't.
>
The TrainingMasterSK field comes from the main form and the
TrainingModuleTopicSK field comes from the first subform. And
because
the TrainingModuleTopicSK isn't being populated once the record is
saved there is no link to the first subform.
>
I'm under the assumption that the second subform should automatically
fill in the TrainingModuleTopicSK field when a new record is entered.
>
So at this point I have no idea what to do or how to get around this
problem. Any help would be appreciated.

[Allen Browne]

Re-reading this thread, I wonder if this is a much simpler problem.

If one of the fields had something in its Default Value property that was
not valid, it could result in this message, probably at the timing you
describe, and would then continue anyway.

The fact that the initial choice in the combo blanks out could indicate the
problem is there. For example, if the combo is bound to a Number field, but
the bound column is zero-width, you won't see the number, just the text. If
you set its Default Value to the text, it would be invalid data to store in
the number field. Clearing the Default Value would solve the problem.

Likewise, if the Default Value for this field in your *table* is zero, but
there is no zero in the combo's RowSource table, that would be an invalid
value also.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Allen,

I tried everything that you suggested and I'm still having the same
problem as previously stated.

The other thing I didn't mention was that when you first click on the
combobox in subform2 when you move to select a choice it blanks out. I
don't know if that gives a clue as to what's going on or not.

But at this point I'm still stuck.

Allen Browne wrote:

>Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

Then compact the database:
Tools | Database Utilities | Compact/Repair

Double-check the name of the text box on your main form matches the name
in
the LinkMasterFields property of the problem subform.

Check that the LinkChildFields property refers to a text box in the
subform
(not merely a field in the subform's RecordSource.)

If that all checks out okay, close Access. Make a backup copy of the
file.
Decompile the database by entering something like this at the command
prompt
while Access is not running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
Then compact again.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@80g2000cwy.googlegr oups.com...

Allen,

I tried your method. And the subforms are working in sync properly.
But when I go to add a record to subform2 I'm getting the following
error - The link MasterFields property setting has produced this
error: "The object doesn't contain the Automation object
txtTrainingModuleTopicSK."

I checked the Masterfield in subform2 and it is still set to
txtTrainingModuleTopicSK. Any ideas? It doesn't matter what field I
try to enter data in on subform2, it returns the same error message.

Thanks again for the help.
Ecohouse wrote:
Thanks for the help Allen. The subform isn't based on a query, only
one table. If I add the control on the main form I assume that I have
to requery it form subform1 whenever I move to a new record?

Once the new control receives the new value will it automatically
update subform2?
Allen Browne wrote:
Hmm. There's a few possible things here.

You might try adding a text box to the main form, with properties:
Control Source
=[subTrainingModule].[Form]![TrainingModuleTopicSK]
Name txtTrainingModuleTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterFields property of the
2nd
subform.

What that does is to set the 2nd subform to be dependent on a text
box
on
the main form, rather than a control in another subform. You might
find
that
this dependency (the normal one Access expects for subforms) solves
the
timing problem.

If that doesn't work, is subform2 based on a query that contains
more
than
one table? If so, there are a couple of other potential issues.
a) Double-check that the key field in the query is from the table
you
actually want to write the record to. If it's from the other table,
the
attempt to assign the value will fail and the value of the real key
field
won't be set.

b) Open the other table in design view (i.e. the one you are not
appending
the record to.) Are there any fields with a Default Value set? If
so,
delete
the Default Value. Access can misinterpret this and try
(unsuccessfully) to
assign the value, even though you are not trying to add a record to
that
table. Any controls on your subform that come from the other
table(s)
may
also need their Default Value cleared.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@a3g2000cwd.googlegro ups.com...
I have a main form with two subforms. The first subform has the
child
link to the main form identity key.

subform1 - Master Field: SK
Child Field: TrainingMasterSK

The second subform has a master-child link to the first subform.

subform2 - Master Field:
subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK

All of the links are working and I can see the correct data. I'm
able
to enter new records on the first subform with no problem.

But when I go to enter a record in the second subform I get the
following error message: "The value you entered isn't valid
forthis
field". This is happening in the BeforeInsert event.

Once I move past the message box then the data in that controls
shows
up. When I check the table that this subform is using I can see
that
when a new record is created the TrainingMasterSK field is
populated
but the TrainingModuleTopicSK field isn't.

The TrainingMasterSK field comes from the main form and the
TrainingModuleTopicSK field comes from the first subform. And
because
the TrainingModuleTopicSK isn't being populated once the record is
saved there is no link to the first subform.

I'm under the assumption that the second subform should
automatically
fill in the TrainingModuleTopicSK field when a new record is
entered.

So at this point I have no idea what to do or how to get around
this
problem. Any help would be appreciated

[Ecohouse]

I've tried removing the default values and the same thing still
happens. It doesn't matter what field I try to enter data in subform2.
Once it's entered I still get the error message. The combo boxes are
setup properly and I've tested them various ways.

But when I click on any combox box in subform2 it sort of flashes and
no list appears, then I have to click on the combobox again then the
dropdown list appears. Once I make the update I get the error message.
After the error message the record disappears. If I looked in the
related table I can see that the linked field is not filled in with a
value. That's where I think the problem is.

I've tried various ways around this. I tried to put subform2 on the
subform1 form. But then I wasn't able to have subform1 be a continuous
form. I need to see both subforms as continuous forms.

I've been dealing with the problem for days now and I still have no
idea how to get around it. Do you have any idea how I could trap the
error and code around it? I tried trapping the error and I couldn't do
that either. I think it's because it's an Access generated error.

Thanks again for all the help.

Allen Browne wrote:

Re-reading this thread, I wonder if this is a much simpler problem.

If one of the fields had something in its Default Value property that was
not valid, it could result in this message, probably at the timing you
describe, and would then continue anyway.

The fact that the initial choice in the combo blanks out could indicate the
problem is there. For example, if the combo is bound to a Number field, but
the bound column is zero-width, you won't see the number, just the text. If
you set its Default Value to the text, it would be invalid data to store in
the number field. Clearing the Default Value would solve the problem.

Likewise, if the Default Value for this field in your *table* is zero, but
there is no zero in the combo's RowSource table, that would be an invalid
value also.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Allen,

I tried everything that you suggested and I'm still having the same
problem as previously stated.

The other thing I didn't mention was that when you first click on the
combobox in subform2 when you move to select a choice it blanks out. I
don't know if that gives a clue as to what's going on or not.

But at this point I'm still stuck.

Allen Browne wrote:

Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

Then compact the database:
Tools | Database Utilities | Compact/Repair

Double-check the name of the text box on your main form matches the name
in
the LinkMasterFields property of the problem subform.

Check that the LinkChildFields property refers to a text box in the
subform
(not merely a field in the subform's RecordSource.)

If that all checks out okay, close Access. Make a backup copy of the
file.
Decompile the database by entering something like this at the command
prompt
while Access is not running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
Then compact again.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...
Allen,

I tried your method. And the subforms are working in sync properly.
But when I go to add a record to subform2 I'm getting the following
error - The link MasterFields property setting has produced this
error: "The object doesn't contain the Automation object
txtTrainingModuleTopicSK."

I checked the Masterfield in subform2 and it is still set to
txtTrainingModuleTopicSK. Any ideas? It doesn't matter what field I
try to enter data in on subform2, it returns the same error message.

Thanks again for the help.
Ecohouse wrote:
Thanks for the help Allen. The subform isn't based on a query, only
one table. If I add the control on the main form I assume that I have
to requery it form subform1 whenever I move to a new record?

Once the new control receives the new value will it automatically
update subform2?
Allen Browne wrote:
Hmm. There's a few possible things here.

You might try adding a text box to the main form, with properties:
Control Source
=[subTrainingModule].[Form]![TrainingModuleTopicSK]
Name txtTrainingModuleTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterFields property of the
2nd
subform.

What that does is to set the 2nd subform to be dependent on a text
box
on
the main form, rather than a control in another subform. You might
find
that
this dependency (the normal one Access expects for subforms) solves
the
timing problem.

If that doesn't work, is subform2 based on a query that contains
more
than
one table? If so, there are a couple of other potential issues.
a) Double-check that the key field in the query is from the table
you
actually want to write the record to. If it's from the other table,
the
attempt to assign the value will fail and the value of the real key
field
won't be set.

b) Open the other table in design view (i.e. the one you are not
appending
the record to.) Are there any fields with a Default Value set? If
so,
delete
the Default Value. Access can misinterpret this and try
(unsuccessfully) to
assign the value, even though you are not trying to add a record to
that
table. Any controls on your subform that come from the other
table(s)
may
also need their Default Value cleared.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@a3g2000cwd.googlegro ups.com...
>I have a main form with two subforms. The first subform has the
>child
link to the main form identity key.
>
subform1 - Master Field: SK
Child Field: TrainingMasterSK
>
The second subform has a master-child link to the first subform.
>
subform2 - Master Field:
subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK
>
All of the links are working and I can see the correct data. I'm
able
to enter new records on the first subform with no problem.
>
But when I go to enter a record in the second subform I get the
following error message: "The value you entered isn't valid
forthis
field". This is happening in the BeforeInsert event.
>
Once I move past the message box then the data in that controls
shows
up. When I check the table that this subform is using I can see
that
when a new record is created the TrainingMasterSK field is
populated
but the TrainingModuleTopicSK field isn't.
>
The TrainingMasterSK field comes from the main form and the
TrainingModuleTopicSK field comes from the first subform. And
because
the TrainingModuleTopicSK isn't being populated once the record is
saved there is no link to the first subform.
>
I'm under the assumption that the second subform should
automatically
fill in the TrainingModuleTopicSK field when a new record is
entered.
>
So at this point I have no idea what to do or how to get around
this
problem. Any help would be appreciated

[Ecohouse]

I was finally able to get things working. I removed the masterlink and
childlink fields in subform2 and using the recordsource property
instead.

I used the following for the recordsource:

SELECT ModuleComponents.* FROM ModuleComponents WHERE
(((ModuleComponents.TrainingModuleSK)=[Forms]![TrainingMaster]![subTrainingModule].[Form]![SK]));

I was also able to enter new records in subform2. But it wasn't
filling in some of the fields. So in the afterinsert of subform2, I
put in the following code:

Private Sub Form_AfterInsert()
'----------------------------------------
' 12/22/06 Updates new record on subform2
'----------------------------------------
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String

On Error GoTo Form_AfterInsert_Error

Set cnn = CurrentProject.Connection

strSQL = "Select * From ModuleComponents WHERE TrainingModuleSK = 0;"
rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic

'Check for new record
If rst.RecordCount 0 Then
'Update blank identity keys
strSQL = "UPDATE ModuleComponents SET
ModuleComponents.TrainingModuleTopicSK = " & _

Forms!TrainingMaster!subTrainingModule.Form!Traini ngModuleTopicSK & ",
" & _
"ModuleComponents.TrainingModuleSK = " &
Forms!TrainingMaster!subTrainingModule.Form!SK & _
" WHERE (ModuleComponents.TrainingModuleTopicSK=0) AND
(ModuleComponents.TrainingModuleSK=0);"

cnn.Execute strSQL
End If

Me.Requery

Form_AfterInsert_Exit:
Set cnn = Nothing
Set rst = Nothing
Exit Sub

Form_AfterInsert_Error:
MsgBox Err.Description, , "Form After Insert"
Resume Form_AfterInsert_Exit

End Sub
This filled in the missing information in the new record on subform2.
And the requery brings up the new information in subform2.

I appreciate all the help that you guys gave me. This was a long time
figuring out.

But in the big picture I used the example in the northwinds db and I
also used the Access method of linking of subforms. So in the big
picture it was my fault for trusing MS ACCESS!!!

Thanks again for all the help. It was greatly appreciated.

>
Allen Browne wrote:

Re-reading this thread, I wonder if this is a much simpler problem.

If one of the fields had something in its Default Value property that was
not valid, it could result in this message, probably at the timing you
describe, and would then continue anyway.

The fact that the initial choice in the combo blanks out could indicate the
problem is there. For example, if the combo is bound to a Number field, but
the bound column is zero-width, you won't see the number, just the text. If
you set its Default Value to the text, it would be invalid data to store in
the number field. Clearing the Default Value would solve the problem.

Likewise, if the Default Value for this field in your *table* is zero, but
there is no zero in the combo's RowSource table, that would be an invalid
value also.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Allen,
>
I tried everything that you suggested and I'm still having the same
problem as previously stated.
>
The other thing I didn't mention was that when you first click on the
combobox in subform2 when you move to select a choice it blanks out. I
don't know if that gives a clue as to what's going on or not.
>
But at this point I'm still stuck.
>
Allen Browne wrote:
>Make sure the Name AutoCorrect boxes are unchecked under:
> Tools | Options | General | Name AutoCorrect
>Explanation of why:
> http://allenbrowne.com/bug-03.html
>>
>Then compact the database:
> Tools | Database Utilities | Compact/Repair
>>
>Double-check the name of the text box on your main form matches the name
>in
>the LinkMasterFields property of the problem subform.
>>
>Check that the LinkChildFields property refers to a text box in the
>subform
>(not merely a field in the subform's RecordSource.)
>>
>If that all checks out okay, close Access. Make a backup copy of the
>file.
>Decompile the database by entering something like this at the command
>prompt
>while Access is not running. It is all one line, and include the quotes:
> "c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
> "c:\MyPath\MyDatabase.mdb"
>Then compact again.
>>
>"Ecohouse" <vi*******@netzero.comwrote in message
>news:11**********************@80g2000cwy.googlegr oups.com...
Allen,
>
I tried your method. And the subforms are working in sync properly.
But when I go to add a record to subform2 I'm getting the following
error - The link MasterFields property setting has produced this
error: "The object doesn't contain the Automation object
txtTrainingModuleTopicSK."
>
I checked the Masterfield in subform2 and it is still set to
txtTrainingModuleTopicSK. Any ideas? It doesn't matter what field I
try to enter data in on subform2, it returns the same error message.
>
Thanks again for the help.
>
>
Ecohouse wrote:
>Thanks for the help Allen. The subform isn't based on a query, only
>one table. If I add the control on the main form I assume that I have
>to requery it form subform1 whenever I move to a new record?
>>
>Once the new control receives the new value will it automatically
>update subform2?
>>
>>
>Allen Browne wrote:
Hmm. There's a few possible things here.
>
You might try adding a text box to the main form, with properties:
Control Source
=[subTrainingModule].[Form]![TrainingModuleTopicSK]
Name txtTrainingModuleTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterFields property of the
2nd
subform.
>
What that does is to set the 2nd subform to be dependent on a text
box
on
the main form, rather than a control in another subform. You might
find
that
this dependency (the normal one Access expects for subforms) solves
the
timing problem.
>
If that doesn't work, is subform2 based on a query that contains
more
than
one table? If so, there are a couple of other potential issues.
a) Double-check that the key field in the query is from the table
you
actually want to write the record to. If it's from the other table,
the
attempt to assign the value will fail and the value of the real key
field
won't be set.
>
b) Open the other table in design view (i.e. the one you are not
appending
the record to.) Are there any fields with a Default Value set? If
so,
delete
the Default Value. Access can misinterpret this and try
(unsuccessfully) to
assign the value, even though you are not trying to add a record to
that
table. Any controls on your subform that come from the other
table(s)
may
also need their Default Value cleared.
>
"Ecohouse" <vi*******@netzero.comwrote in message
news:11**********************@a3g2000cwd.googlegro ups.com...
>I have a main form with two subforms. The first subform has the
>child
link to the main form identity key.
>
subform1 - Master Field: SK
Child Field: TrainingMasterSK
>
The second subform has a master-child link to the first subform.
>
subform2 - Master Field:
subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK
>
All of the links are working and I can see the correct data. I'm
able
to enter new records on the first subform with no problem.
>
But when I go to enter a record in the second subform I get the
following error message: "The value you entered isn't valid
forthis
field". This is happening in the BeforeInsert event.
>
Once I move past the message box then the data in that controls
shows
up. When I check the table that this subform is using I can see
that
when a new record is created the TrainingMasterSK field is
populated
but the TrainingModuleTopicSK field isn't.
>
The TrainingMasterSK field comes from the main form and the
TrainingModuleTopicSK field comes from the first subform. And
because
the TrainingModuleTopicSK isn't being populated once the record is
saved there is no link to the first subform.
>
I'm under the assumption that the second subform should
automatically
fill in the TrainingModuleTopicSK field when a new record is
entered.
>
So at this point I have no idea what to do or how to get around
this
problem. Any help would be appreciated