Error when adding a new record

[Guest]

I have a form with a simple backend to it. There are two tables with a 1 -
many relation based on a single field.

I want the form to be able to show each staff member (the one side) and
their holidays (the many side). I also want it so a new staff member can be
added. This caused problems at first because the many side can't be input
until the one side has been saved. I got around this by saving the record
after the link field had been input. This now works fine.

My problem however is this. When I click on the add record button on the
form the focus is set to this link field, but as soon as I start to input I
get an Access error saying "Field cannot be updated". I ok this warning and I
am allowed to continue entering the data and everything works fine.

Why am I getting the error (that doesn’t actually stop me from updating the
field) and how can I stop it from appearing?

 

[Allen Browne]

Keith, I presume this happens in the holidays form.
What is the RecordSource of this form?

If it is a query that includes both tables, make sure that none of the
fields in the Staff table have a Default Value defined (in table design),
and that none of those fields have a Default Value set for their text box on
the form.

 

[Guest]

The source of the form is a simple query which joins the two tables in
question. The joining field did have a default of 0 but I have removed that
from both tables. Unfortunatly it didn't make a difference.

 

[Allen Browne]

Are you absolutely certain there are no more default values for the fields
in the table or the controls on the form? The timing of the problem makes it
sound like this issue.

If not, the form's BeforeInsert event fires at this moment, so it could be
something in there.

Also, make sure you have the correct key field outputting from the query. It
must be the foreign key field from the Holiday table, and not the primary
key field of the Staff table.

 

[Guest]

Allen,

Thanks for your help, but I seem to have found out the problem. The field I
was changing was in the staff table, but that was ok as I was trying to add a
new member of staff at the time, hence the refresh after that field got
updated.

The problem was a field called year. It was setup in the query to only equal
the year value in another table by means of the Dlookup command. Once I
removed this field from the form (not the query) everything started working
properly. All I need to do now is force an automatic update of the year field.

Once again thanks for your help. Just going through your suggestions helped
my find the solution.

Keith

 

[Allen Browne]

A field named Year will cause problems.

That's a function name in VBA, so the name is ambigous, and you get
problematic results.

There are over 1000 words that can cause you grief. There's a list of them
in tblBadWord in this utility:
Database Issue Checker
at:
http://allenbrowne.com/AppIssueChecker.html