Error 3164-can't update field

[Anita]

I'm running into run-time error 3164, field can not be updated, when I try to
pass a value to a field. This field, "Event ID," exists in the query my form
is based on, but is not a visible control on the form.

Private Sub EventDate_BeforeUpdate(Cancel As Integer)
'Created November 2006 - currently in testing

'If user attempts to change the date of the Last Updated event, this sub
creates a _
new Event record and plugs the just-inputted data into it. This prevents
user from _
incorrectly overriding Last Updated data for all existing records tied to
the current _
Last Updated Event record.

Dim intNewEventDate As Date
Dim intNewEventID As Integer
Dim conEvent As Connection
Dim rstEvent As Recordset

intNewEventDate = Me.EventDate
Cancel = True

Set conEvent = CurrentProject.Connection
Set rstEvent = New Recordset

With rstEvent
.Source = "Select * from Event"
.ActiveConnection = conEvent
.LockType = adLockOptimistic
.Open
.AddNew
![Event Type] = "Last Updated"
![EventDate] = intNewEventDate
intNewEventID = ![Event ID].Value
.MoveFirst
End With

rstEvent.Close
Set rstEvent = Nothing
conEvent.Close
Set conEvent = Nothing

Me.[Event ID].Value = intNewEventID ******Run-time error 3164**********
Me.Requery

 

[Ken Snell \(MVP\)]

Is EventID an autonumber field? If not, post more details about what it is
and more details about your form's setup/design/properties.

If it is an autonumber field, you cannot update such a field.

 

[Van T. Dinh]

Another aspect is to check your naming convention. The declaration:

Dim intNewEventDate As Date

is a bit different from the usual naming conventions ...

 

[Anita]

I have a table called Events where EventID is an autonumber field. The
"recordset" code below adds a new record to this table and captures the newly
created EventID in the intNewEventID variable.

However, the underlying table for the LastUpdated form where this event is
triggered is not the Events table; it's a join table called
Entity-Event-Join. Event ID is just a regular Number field in this join
table. (I designed it this way because one Last Updated event can apply to
many different Entities and Entity Types.)

The Last Updated form has two controls: Event Date & Information Source
(i.e., Last Updated 1/1/06, Information Source: XYZ Mailing List). The Event
ID (from the join table) is part of the form's underlying query.

The Event Table holds the type of event (Last Updated) and the event date,
along with the Event ID autonumber field.

If someone attempts to manually change the date on the Last Updated subform,
I want to create a new Event, with the newly-changed date, and assign this
new Event to the Last Updated form. That way, a user can't inadvertently
reset the Last Updated date for every record already associated with a
particular Event ID.

Is EventID an autonumber field? If not, post more details about what it is
and more details about your form's setup/design/properties.

If it is an autonumber field, you cannot update such a field.


--

Ken Snell
<MS ACCESS MVP>

I'm running into run-time error 3164, field can not be updated, when I try
to
pass a value to a field. This field, "Event ID," exists in the query my
form
is based on, but is not a visible control on the form.

Private Sub EventDate_BeforeUpdate(Cancel As Integer)
'Created November 2006 - currently in testing

'If user attempts to change the date of the Last Updated event, this sub
creates a _
new Event record and plugs the just-inputted data into it. This prevents
user from _
incorrectly overriding Last Updated data for all existing records tied to
the current _
Last Updated Event record.

Dim intNewEventDate As Date
Dim intNewEventID As Integer
Dim conEvent As Connection
Dim rstEvent As Recordset

intNewEventDate = Me.EventDate
Cancel = True

Set conEvent = CurrentProject.Connection
Set rstEvent = New Recordset

With rstEvent
.Source = "Select * from Event"
.ActiveConnection = conEvent
.LockType = adLockOptimistic
.Open
.AddNew
![Event Type] = "Last Updated"
![EventDate] = intNewEventDate
intNewEventID = ![Event ID].Value
.MoveFirst
End With

rstEvent.Close
Set rstEvent = Nothing
conEvent.Close
Set conEvent = Nothing

Me.[Event ID].Value = intNewEventID ******Run-time error 3164**********
Me.Requery

 

[Ken Snell \(MVP\)]

OK, sounds as if you have your table structure set up correctly for the
Entity-Event-Join table. That's good. So, we'll need to do a bit of
debugging in the form's code to identify why the field cannot be updated.

One problem may be that you're running the code in the BeforeUpdate event of
EventDate control, which I assume is bound to a field in the form's record
source query. While you're editing that field, I don't believe you can
change the value of another field in that same record in the form.
Therefore, you should probably move the code to the AfterUpdate event of the
EventDate control. Try that and see if the problem is gone.
--

Ken Snell
<MS ACCESS MVP>

I have a table called Events where EventID is an autonumber field. The
"recordset" code below adds a new record to this table and captures the
newly
created EventID in the intNewEventID variable.

However, the underlying table for the LastUpdated form where this event is
triggered is not the Events table; it's a join table called
Entity-Event-Join. Event ID is just a regular Number field in this join
table. (I designed it this way because one Last Updated event can apply to
many different Entities and Entity Types.)

The Last Updated form has two controls: Event Date & Information Source
(i.e., Last Updated 1/1/06, Information Source: XYZ Mailing List). The
Event
ID (from the join table) is part of the form's underlying query.

The Event Table holds the type of event (Last Updated) and the event date,
along with the Event ID autonumber field.

If someone attempts to manually change the date on the Last Updated
subform,
I want to create a new Event, with the newly-changed date, and assign this
new Event to the Last Updated form. That way, a user can't inadvertently
reset the Last Updated date for every record already associated with a
particular Event ID.

Is EventID an autonumber field? If not, post more details about what it
is
and more details about your form's setup/design/properties.

If it is an autonumber field, you cannot update such a field.


--

Ken Snell
<MS ACCESS MVP>

I'm running into run-time error 3164, field can not be updated, when I
try
to
pass a value to a field. This field, "Event ID," exists in the query my
form
is based on, but is not a visible control on the form.

Private Sub EventDate_BeforeUpdate(Cancel As Integer)
'Created November 2006 - currently in testing

'If user attempts to change the date of the Last Updated event, this
sub
creates a _
new Event record and plugs the just-inputted data into it. This
prevents
user from _
incorrectly overriding Last Updated data for all existing records tied
to
the current _
Last Updated Event record.

Dim intNewEventDate As Date
Dim intNewEventID As Integer
Dim conEvent As Connection
Dim rstEvent As Recordset

intNewEventDate = Me.EventDate
Cancel = True

Set conEvent = CurrentProject.Connection
Set rstEvent = New Recordset

With rstEvent
.Source = "Select * from Event"
.ActiveConnection = conEvent
.LockType = adLockOptimistic
.Open
.AddNew
![Event Type] = "Last Updated"
![EventDate] = intNewEventDate
intNewEventID = ![Event ID].Value
.MoveFirst
End With

rstEvent.Close
Set rstEvent = Nothing
conEvent.Close
Set conEvent = Nothing

Me.[Event ID].Value = intNewEventID ******Run-time error 3164**********
Me.Requery