473,841 Members | 1,671 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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: TrainingMasterS K

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

subform2 - Master Field: subTrainingModu le.Form!Trainin gModuleTopicSK
Child Field: TrainingModuleT opicSK

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 TrainingMasterS K field is populated
but the TrainingModuleT opicSK field isn't.

The TrainingMasterS K field comes from the main form and the
TrainingModuleT opicSK field comes from the first subform. And because
the TrainingModuleT opicSK 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 TrainingModuleT opicSK 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.

Dec 19 '06 #1
9 9719
Hmm. There's a few possible things here.

You might try adding a text box to the main form, with properties:
Control Source =[subTrainingModu le].[Form]![TrainingModuleT opicSK]
Name txtTrainingModu leTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterField s 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*******@netz ero.comwrote in message
news:11******** **************@ a3g2000cwd.goog legroups.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: TrainingMasterS K

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

subform2 - Master Field: subTrainingModu le.Form!Trainin gModuleTopicSK
Child Field: TrainingModuleT opicSK

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 TrainingMasterS K field is populated
but the TrainingModuleT opicSK field isn't.

The TrainingMasterS K field comes from the main form and the
TrainingModuleT opicSK field comes from the first subform. And because
the TrainingModuleT opicSK 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 TrainingModuleT opicSK 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.

Dec 20 '06 #2
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 =[subTrainingModu le].[Form]![TrainingModuleT opicSK]
Name txtTrainingModu leTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterField s 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*******@netz ero.comwrote in message
news:11******** **************@ a3g2000cwd.goog legroups.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: TrainingMasterS K

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

subform2 - Master Field: subTrainingModu le.Form!Trainin gModuleTopicSK
Child Field: TrainingModuleT opicSK

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 TrainingMasterS K field is populated
but the TrainingModuleT opicSK field isn't.

The TrainingMasterS K field comes from the main form and the
TrainingModuleT opicSK field comes from the first subform. And because
the TrainingModuleT opicSK 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 TrainingModuleT opicSK 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.
Dec 20 '06 #3
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*******@netz ero.comwrote in message
news:11******** **************@ 79g2000cws.goog legroups.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 =[subTrainingModu le].[Form]![TrainingModuleT opicSK]
Name txtTrainingModu leTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterField s 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*******@netz ero.comwrote in message
news:11******* *************** @a3g2000cwd.goo glegroups.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: TrainingMasterS K

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

subform2 - Master Field: subTrainingModu le.Form!Trainin gModuleTopicSK
Child Field: TrainingModuleT opicSK

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 TrainingMasterS K field is populated
but the TrainingModuleT opicSK field isn't.

The TrainingMasterS K field comes from the main form and the
TrainingModuleT opicSK field comes from the first subform. And because
the TrainingModuleT opicSK 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 TrainingModuleT opicSK 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.

Dec 20 '06 #4
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
txtTrainingModu leTopicSK."

I checked the Masterfield in subform2 and it is still set to
txtTrainingModu leTopicSK. 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 =[subTrainingModu le].[Form]![TrainingModuleT opicSK]
Name txtTrainingModu leTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterField s 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*******@netz ero.comwrote in message
news:11******** **************@ a3g2000cwd.goog legroups.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: TrainingMasterS K
>
The second subform has a master-child link to the first subform.
>
subform2 - Master Field: subTrainingModu le.Form!Trainin gModuleTopicSK
Child Field: TrainingModuleT opicSK
>
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 TrainingMasterS K field is populated
but the TrainingModuleT opicSK field isn't.
>
The TrainingMasterS K field comes from the main form and the
TrainingModuleT opicSK field comes from the first subform. And because
the TrainingModuleT opicSK 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 TrainingModuleT opicSK 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.
Dec 20 '06 #5
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 LinkMasterField s 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\m saccess.exe" /decompile
"c:\MyPath\MyDa tabase.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*******@netz ero.comwrote in message
news:11******** **************@ 80g2000cwy.goog legroups.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
txtTrainingModu leTopicSK."

I checked the Masterfield in subform2 and it is still set to
txtTrainingModu leTopicSK. 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
=[subTrainingModu le].[Form]![TrainingModuleT opicSK]
Name txtTrainingModu leTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterField s 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*******@netz ero.comwrote in message
news:11******** **************@ a3g2000cwd.goog legroups.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: TrainingMasterS K

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

subform2 - Master Field: subTrainingModu le.Form!Trainin gModuleTopicSK
Child Field: TrainingModuleT opicSK

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 TrainingMasterS K field is populated
but the TrainingModuleT opicSK field isn't.

The TrainingMasterS K field comes from the main form and the
TrainingModuleT opicSK field comes from the first subform. And
because
the TrainingModuleT opicSK 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 TrainingModuleT opicSK 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.

Dec 21 '06 #6
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 LinkMasterField s 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\m saccess.exe" /decompile
"c:\MyPath\MyDa tabase.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*******@netz ero.comwrote in message
news:11******** **************@ 80g2000cwy.goog legroups.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
txtTrainingModu leTopicSK."

I checked the Masterfield in subform2 and it is still set to
txtTrainingModu leTopicSK. 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
=[subTrainingModu le].[Form]![TrainingModuleT opicSK]
Name txtTrainingModu leTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterField s 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*******@netz ero.comwrote in message
news:11******** **************@ a3g2000cwd.goog legroups.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: TrainingMasterS K
>
The second subform has a master-child link to the first subform.
>
subform2 - Master Field: subTrainingModu le.Form!Trainin gModuleTopicSK
Child Field: TrainingModuleT opicSK
>
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 TrainingMasterS K field is populated
but the TrainingModuleT opicSK field isn't.
>
The TrainingMasterS K field comes from the main form and the
TrainingModuleT opicSK field comes from the first subform. And
because
the TrainingModuleT opicSK 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 TrainingModuleT opicSK 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.
Dec 21 '06 #7
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*******@netz ero.comwrote in message
news:11******** **************@ 73g2000cwn.goog legroups.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 LinkMasterField s 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\m saccess.exe" /decompile
"c:\MyPath\MyDa tabase.mdb"
Then compact again.

"Ecohouse" <vi*******@netz ero.comwrote in message
news:11******* *************** @80g2000cwy.goo glegroups.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
txtTrainingModu leTopicSK."

I checked the Masterfield in subform2 and it is still set to
txtTrainingModu leTopicSK. 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
=[subTrainingModu le].[Form]![TrainingModuleT opicSK]
Name txtTrainingModu leTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterField s 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*******@netz ero.comwrote in message
news:11******** **************@ a3g2000cwd.goog legroups.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: TrainingMasterS K

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

subform2 - Master Field:
subTrainingModu le.Form!Trainin gModuleTopicSK
Child Field: TrainingModuleT opicSK

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 TrainingMasterS K field is
populated
but the TrainingModuleT opicSK field isn't.

The TrainingMasterS K field comes from the main form and the
TrainingModuleT opicSK field comes from the first subform. And
because
the TrainingModuleT opicSK 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 TrainingModuleT opicSK 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

Dec 22 '06 #8
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*******@netz ero.comwrote in message
news:11******** **************@ 73g2000cwn.goog legroups.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 LinkMasterField s 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\m saccess.exe" /decompile
"c:\MyPath\MyDa tabase.mdb"
Then compact again.

"Ecohouse" <vi*******@netz ero.comwrote in message
news:11******** **************@ 80g2000cwy.goog legroups.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
txtTrainingModu leTopicSK."

I checked the Masterfield in subform2 and it is still set to
txtTrainingModu leTopicSK. 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
=[subTrainingModu le].[Form]![TrainingModuleT opicSK]
Name txtTrainingModu leTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterField s 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*******@netz ero.comwrote in message
news:11******** **************@ a3g2000cwd.goog legroups.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: TrainingMasterS K
>
The second subform has a master-child link to the first subform.
>
subform2 - Master Field:
subTrainingModu le.Form!Trainin gModuleTopicSK
Child Field: TrainingModuleT opicSK
>
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 TrainingMasterS K field is
populated
but the TrainingModuleT opicSK field isn't.
>
The TrainingMasterS K field comes from the main form and the
TrainingModuleT opicSK field comes from the first subform. And
because
the TrainingModuleT opicSK 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 TrainingModuleT opicSK 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
Dec 22 '06 #9
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 ModuleComponent s.* FROM ModuleComponent s WHERE
(((ModuleCompon ents.TrainingMo duleSK)=[Forms]![TrainingMaster]![subTrainingModu le].[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_AfterInser t()
'----------------------------------------
' 12/22/06 Updates new record on subform2
'----------------------------------------
Dim cnn As New ADODB.Connectio n
Dim rst As New ADODB.Recordset
Dim strSQL As String

On Error GoTo Form_AfterInser t_Error

Set cnn = CurrentProject. Connection

strSQL = "Select * From ModuleComponent s WHERE TrainingModuleS K = 0;"
rst.Open strSQL, cnn, adOpenStatic, adLockOptimisti c

'Check for new record
If rst.RecordCount 0 Then
'Update blank identity keys
strSQL = "UPDATE ModuleComponent s SET
ModuleComponent s.TrainingModul eTopicSK = " & _

Forms!TrainingM aster!subTraini ngModule.Form!T rainingModuleTo picSK & ",
" & _
"ModuleComponen ts.TrainingModu leSK = " &
Forms!TrainingM aster!subTraini ngModule.Form!S K & _
" WHERE (ModuleComponen ts.TrainingModu leTopicSK=0) AND
(ModuleComponen ts.TrainingModu leSK=0);"

cnn.Execute strSQL
End If

Me.Requery

Form_AfterInser t_Exit:
Set cnn = Nothing
Set rst = Nothing
Exit Sub

Form_AfterInser t_Error:
MsgBox Err.Description , , "Form After Insert"
Resume Form_AfterInser t_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*******@netz ero.comwrote in message
news:11******** **************@ 73g2000cwn.goog legroups.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 LinkMasterField s 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\m saccess.exe" /decompile
> "c:\MyPath\MyDa tabase.mdb"
>Then compact again.
>>
>"Ecohouse" <vi*******@netz ero.comwrote in message
>news:11******* *************** @80g2000cwy.goo glegroups.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
txtTrainingModu leTopicSK."
>
I checked the Masterfield in subform2 and it is still set to
txtTrainingModu leTopicSK. 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
=[subTrainingModu le].[Form]![TrainingModuleT opicSK]
Name txtTrainingModu leTopicSK
Format General Number
(Omit the Format if it's not a Number field.)
You can then use that name in the LinkMasterField s 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*******@netz ero.comwrote in message
news:11******** **************@ a3g2000cwd.goog legroups.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: TrainingMasterS K
>
The second subform has a master-child link to the first subform.
>
subform2 - Master Field:
subTrainingModu le.Form!Trainin gModuleTopicSK
Child Field: TrainingModuleT opicSK
>
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 TrainingMasterS K field is
populated
but the TrainingModuleT opicSK field isn't.
>
The TrainingMasterS K field comes from the main form and the
TrainingModuleT opicSK field comes from the first subform. And
because
the TrainingModuleT opicSK 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 TrainingModuleT opicSK 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
Dec 22 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
5262
by: Jeff Magouirk | last post by:
Dear All, I have written an update trigger that should write a message to an audit table When I try to update any field in the table I recieve the following error message - Stirng or Binary data would be trunicated The statement has been termined.
12
3185
by: Dave Hammond | last post by:
I recently noticed the stylesheet link in an html page had the href set to a PHP script, as in: <LINK REL="stylesheet" href="some_css.php" type="text/css"> Presumably the file being referenced was actually an executable PHP script and not a css file that happened to have a .php extension. Based on that assumption, I tried the same thing with a Perl script (the webserver being tested happens to have mod_perl installed but not mod_php),...
5
10214
by: F. Barth | last post by:
Hello, I've posted this problem to one other newsgroups, and gotten some diagnostic help, but I still need a solution. The full text of the message box is: "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data". This displays without a run-time error number. This error comes about when a user (I've been able to reproduce this error on 2 different workstations; one running...
0
1382
by: John Phelan-Cummings | last post by:
What Works I have a subform (Events1) with a combo box field called “eventname”. The combo box displays the correct information from the second column of its lookup table, i.e., column #1: “eventID”, column #2: “eventname”, (The ”Column Count” is “2”; the “Column Widths” is “ 0";1" “; the “Bound Column” is “1”; and “Limit to List” is “Yes”. The, “eventsname” field is located on a subform called, “Events1”.). Once a choice is made...
5
4214
by: BizTalk Architect | last post by:
Hello, I cant get past this error, can anyone help? ITCallNotificationEvent objCallNotification = (ITCallNotificationEvent)sender; ITCallInfo callInfo = objCallNotification.Call;
2
8320
by: Hexman | last post by:
Hello All, In SS EE I have nulls in a column. I want to select and eventually change to a zero (its a smallint column). I've tried selecting 'null', 'dbnull', etc. Then I read about the ISNULL function. From the example I got, I received the "...requires 2 arguments...." error. Read up on it but it doesn't click with me. Could someone explain the error? And secondly, how to select null fields? Thanks,
23
5148
by: deathtospam | last post by:
A day or two ago, I wrote a quick ASPX page with a CS codebehind using Visual Studio .NET 2005 -- it worked, I saved it and closed the project. Today, I came back to the project, reopened the solution, and was greeted with the following error: ======================================================================== It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error...
9
4638
by: xian2 | last post by:
Hi All, I have been wroking on the databse for a while now and am on the stage of creating forms. I am trying to create a subform at the moment but am having some problems. I am using the wizard to create the form and add the necessary fields as below: The tables are as follows: tblTourBookings (Main Table linked to others)
3
2238
by: Kelii | last post by:
I've been beating my head against this problem for the last several days, and I haven't found a decent solution yet. So I'm turning to the group for some moral support or better yet some answers. Background: I want the user to be able to close the form by using the "x" button in the window title bar, I do not want to disable this. I have found a nice solution in the Access Developer's Handbook that disables the "x", however I am hoping...
0
10927
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10609
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10667
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9446
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7025
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5691
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5880
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4498
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4082
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.