Subform update

G

Glenn Suggs

Can anyone help with this? I have a subform on a main form and when I go to
a new record on the subform and type the first letter of info, I get an error
message that says "Field cannot be updated." I clicked the Help button on
the message but didn't find anything that applied to what it said.

Thanks in advance,
 
A

Allen Browne

Is this subform based on a query that contains more than one table?

If so, open the tables of this query in design view, and try removing the
Default Value from the fields of the table.

JET has problems sorting out the Default Values for a multi-table query. For
example, if you are trying to create a record in the related table, and the
lookup table has a Default Value on one of its fields, Access tries to apply
it and complains that it is not able to create the record for the lookup
table, which is not what you are trying to get it to do. Removing the
Default Value removes the cause of the problem.
 
G

Glenn Suggs

There is one table in the query behind the subform and none of the fields in
that table have a default set. The main form is based on one table (not in a
query) and also contains no fields with defaults. The error I get only
happens when I leave the main form and enter the subform and begin to enter
data.
 
A

Allen Browne

Okay, it's not that issue then.

We need to establish whether the problem is with saving the main form
record, or with starting a new entry in the subform. Does the error occur:
a) when you click in the subform (which forces Access to save the main form
record), or
b) when you start the new entry in the subform)?

If a) there is a problem with the main form's table.
If b) there is a problem with the subform's table.

Your original posts suggests b, so we will work on that assumption. Open the
main form in design view. Right-click the edge of the subform control, and
choose Properties. On the Data tab of the Properties box, what entries are
beside these properties:
Link Child Fields
Link Master Fields

The Link Master Fields should refer to a field in the main form's table.
What is the Type of this field (e.g. AutoNumber, Number, Text, ...)?

The Link Child Fields should refer to a field in the subform's table. What
is the Type and size of this field (e.g. Number (Long Integer), or Text (50
characters)?
 
G

Glenn Suggs

Yes, it is b, when starting a new entry in the subform. The Link Child and
Link Master fields are both Text fields with a length of 8.
 
A

Allen Browne

Glenn, you say this happens when you begin typing into the subform. Is the
true regardless of which control you type in, or is it specific to one
control? Is there any code in that control's events? Or in the BeforeInsert
event of the subform?

If not, we are probably looking at a corruption of some kind, but we need to
eliminate all other causes first.
 
G

Glenn Suggs

It happens when I begin typing in the subform in ANY control. The only code
in the subform is two KeyPress event procedures which causes typing in two
text boxes to be uppercase. But I took these procedures out and it didn't
help. I suppose that brings us down to the corruption issue.
 
A

Allen Browne

Okay, here's a fairly standard way of addressing several possible forms of
corruption:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. 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"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are
resolved.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html
 
Top