Mysterious Error Message

  • Thread starter Thread starter Peter Hallett
  • Start date Start date
P

Peter Hallett

I have a form with two sub-forms, one of which itself has
a sub-form (a sub-sub-form). The construction is
completely standard and the forms/sub-forms all display
properly, remain synchronized and correctly present the
data in the tables to which they are bound. On attempting
to enter the first value into any sub-sub-form field,
however, the error message:-
"You can't assign a value to this object
*The object may be a control on a read-only form...
*The object may be on a form that is open in design view
*The value may be too large for this field"
is displayed.
But none of these conditions applies. The
AllowEdits/Deletions/Additions switches are set to True
for the Main form and all sub-forms.
If the 'OK' button is clicked when the error message is
displayed, the message disappears, the rejected input is
shown in the field and data entry to the sub-form can then
be completed normally. If the bound tables are examined
after data has been entered, they are found to contain the
correct data, including the initially rejected first
character.
It is clearly unacceptable to have to remove the spurious
error message following the first sub-sub-form key-press
on every occasion that data entry is attempted to a new
form.
This problem does not affect the main form or either of
the sub-forms, including the parent of the sub-sub-form.
 
The trick is going to be to find out what is being assigned to what that
generates the error.

First try: Default Value
Is then an inappropriate Default Value in something?
For example, if you have a Default Value set for an AutoNumber field, the
assignment will fail. Check for a default value in the text boxes on the
form, and also in the fields in Table Design view.

Typically Access assigns zero as the Default Value for all numeric columns.
Your related tables will have a foreign key field. Having a zero in this
field is clearly a nonsense, so be sure to remove the zero. You should
probably set the Required property of the foreign key fields to Yes in table
design view at the same time.

2nd try: Form_BeforeInsert
If the problem is still there, check if there is anything in the
BeforeInsert event of the subform. This event runs at precisely the time you
receive the error.

3rd try: Link fields
Open the middle form (the parent of the sub-sub-form) in design view.
Right-click the edge of the subform control, and choose Properties. What do
you have in the LinkMasterFields/LinkChildFields property. Are these
correct? Are the data types and sizes compatible?

4th try: RecordSource
If the sub-sub-form is based on a query, try changing it to a table
temporarily if you can. If the problem disappears, then the problem is with
the query. For example, you may have chosen the primary key field of the
primary table when you needed the foreign key of the related table, or there
may be outer joins that are limiting the functionality and updateability of
some fields. Try adding a record directly to the source query and see what
happens.

Hopefully something there will lead you the right direction.
 
Back
Top