Strange Message

R

Robert T

Hello:

I have a simple flat file table that tracks my Time and Attendance at work.
Most of the fields are filled in as defaults, the only field I actually type
in is [Pay_Period] which is a text box. It used to be a look up, however, I
converted it back to a text box. It is a text field and this week I'm
entering the number 32 for the current week.

When I type in the first 3, a dialog box pops up saying,
"Field Cannot be Updated"

I'm totally baffled. I checked for code behind the button on the form and
couldn't find anything. I went into table design and couldn't find anything
other than the fact the field is Indexed with Duplicates allowed.

After I close the dialog box, I can type in the full number 32. Any thoughts
or ideas?

Thanks,
Robert
 
A

Allen Browne

Robert, this is a bit of a long shot, but:

1. Is this form based on a query?

2. Does the query have more than one table?

3. Do any of the fields in the other tables (i.e. other than the one you are
actually adding the record to) have a Default Value?

If the answer is Yes to all 3, you might find that this Default Value is
fooling Access into trying to create a record in the lookup table, and of
course this fails.

If that's what's going on, removing the Default Value from all fields in the
lookup table, and also the Default Value from any text boxes that are bound
to the lookup table (not the table you want to add records to) will solve
the problem.
 
R

Robert T

1. Is this form based on a query? YES

2. Does the query have more than one table? NO

The field, Pay_Period, used to have a lookup to tblPayPeriods. However, I
decided it wasn't saving time, it was a lot easier to simply type in the pay
period. I therefore removed the lookup to tblPayPeriods and converted it back
to a text box.

All of the other fields on the form/table have a default value such as:
Date(), 8:30 am, 5:00 pm, etc.

Does any of the above help?

Thanks,
Robert
 
A

Allen Browne

Okay it has to be something simpler then.

If it only happens on the first keystroke when you try to add a new record,
we are looking for what is invalid.

Could there be an invalid default value in one of the controls?

Could there be a control that has a Name that is the same as a field, but it
is bound to something else.

You mentioned code behind a button: Does it only happen if you click the
button? In form design view, if you open the code window, does it compile
without error (Compile on the Debug menu, in the code window)?

If you cannot identify anything there, here are some generic steps to
address 2 issue problems Access gets itself into:

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/Repair

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 (holding down the Shift key if you have any startup code),
and compact again.
 
R

Robert T

Hello Allen:

I could not find any conflicts with the control names, at least none that
caught my attention as someone relatively new to Access. It happens whether I
click on the command button for a new record or if I use the 5th arrow at the
bottom of the form for a new record.

I unchecked Auto Correct.
I compacted the database several times after closing Access 2003.
I couldn’t decompile the database, I received a message that it was an
unavailable location.

None of the above helped. I tried entering a new record in a datasheet and
the field could not be updated message did NOT pop up when entering a value
in the Pay_Period field. The issue only arises in the custom form I created.
Therefore, the problem is obviously related to some code I must have screwed
up.

Does the above help?

Thanks,
Robert
 
A

Allen Browne

To get the path you need to use for the Decompile, open the Immediate Window
(Ctrl+G) and enter:
? Currentdb.Name

At what point does the error occur?
As soon as you move to the new record?
If so, look at any code you have in the Current event of the form.

Or when you begin to enter a new record?
If so, look at any code in the BeforeInsert event of the form.
Also, anything associated with default values.

You might try eliminating all code from the form's module. (You can save it
in Notepad as a text file.) If that does not solve the problem, then it has
to do with the controls themselves, and not the code, so start eliminating
controls until you find the culprit. If it does solve the problem, start
adding the code back until you find the culprit.
 
R

Robert T

Allen:

This is what happens.

[1] I have code that inserts the next ID number in the first field and moves
to the 2nd field, [Pay_Period].

[2] The message pops up after I enter the first number. I click in the
dialog box and then I can enter the 2nd digit as so the number will be "32"
[text field].

[3] I like your idea of removing one control at a time to see if that's the
offending control.

Here's the code behind the New Record button.

Private Sub btnNewRecord_Click()
On Error GoTo Err_btnNewRecord_Click

DoCmd.GoToRecord , , acNewRec
Me!Pay_Period.SetFocus
Exit_btnNewRecord_Click:
Exit Sub

Err_btnNewRecord_Click:
MsgBox Err.Description
Resume Exit_btnNewRecord_Click

End Sub
 
R

Robert T

Allen:

I had the correct path, our network wouldn't allow me to run decompile.

I used your suggestion and found the offending control on the form! However,
I have no idea why this field would have any impact on the [Pay_Period]field.

I have a field for [Start_Time] and [End_Time]

The offending control is a calculated field called [Total_Time] with the
following DEFAULT expression:

=HoursAndMinutes([End_Time] - [Start_Time])

When I removed the [Total_Time] control from the form, I was able to type in
a number into the [Pay_Period] field with no problems. But why?
 
R

Robert T

Problem Solved!!!

I moved the calculated expression:

=HoursAndMinutes([End_Time] - [Start_Time])

from the default value to the Control Source and that resolved the issue.
However, I don't understand why making it the default value caused that
problem with the [Pay_Period]field.

Thanks for all of your help Allen, you were very patient and your advice was
right on the money.

Robert
 
Top