Error 3164 - Reserved Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Does anybody know what 'Run-time error '3164': Reserved Error' means? It is
happening when my code tries to do the following:

Docmd.Runcommand acCmdDeleteRecord

The form that it occurs on uses a query that takes data from two tables with
a 1-N relationship as the record source. This is the first time I have
attempted to use a query as the datasource so I suspect it has something to
do with this but I don't know what exactly.

Thanks is advance...

Andy
 
Andy said:
Does anybody know what 'Run-time error '3164': Reserved Error' means? It is
happening when my code tries to do the following:

Docmd.Runcommand acCmdDeleteRecord

The form that it occurs on uses a query that takes data from two tables with
a 1-N relationship as the record source. This is the first time I have
attempted to use a query as the datasource so I suspect it has something to
do with this but I don't know what exactly.

Thanks is advance...

Andy
Click to expand...

According to my system, error 3164 is "Field cannot be updated."
 
I'm using win XP and Access 2002. When the error message box appeared I
tried clicking help but nothing loaded - I just got a grey screen. I also
tried searching MSDN for error 3164 but didn't get anywhere. Is it possible
that my Help is corrupt so the message I get is just saying that the error
number 3164 is reserved for use by the system rather than the proper error
message?

I'm not entirely sure why it would be throwing a 'Field cannot be updated'
error when I am trying to delete a record?!
 
Andy said:
I'm using win XP and Access 2002. When the error message box appeared I
tried clicking help but nothing loaded - I just got a grey screen. I also
tried searching MSDN for error 3164 but didn't get anywhere. Is it possible
that my Help is corrupt so the message I get is just saying that the error
number 3164 is reserved for use by the system rather than the proper error
message?

I'm not entirely sure why it would be throwing a 'Field cannot be updated'
error when I am trying to delete a record?!
Click to expand...

Go to the Immediate window and type the following:

? AccessError(3164)

I'm sure it's something to do with the fact that your record source is based
on more than one table. Are you clear as to what deleting a record from
this record source is supposed to mean with regard to the underyling tables?
Should it delete one record from the "many" table, or should it delete the
record from the "one" table, and ALL of the records from the "many" table?
And, even if you are clear about what you want, have you thought through how
Access is supposed to figure it out?

Updating record sources based on multiple tables is fraught with problems,
and I should think that deleting is even more so.
 
Thanks for the AccessError tip - I've lost count of the number of times I've
automatically clicked debug without remembering the message!

In this particular instance the deletion occurs when the user hits a Cancel
command button while in the process of adding a new record. Therefore I do
want Access to delete the 'one' record and all related 'many' records (as
well as the main body of the form there are also a couple of subforms).

Not too sure about how Access would figure it out. The relationships do
have cascade delete so I kind of thought that Access would start at the top
and work its way through -maybe that was rash assumption! Based on what
you've said about the problems of updating and deleting multiple tables, I
wonder if I would be better to either merge the tables used in the source
query into 1 or to run an SQL statement to delete the record drectly from the
'1' table thus triggering the cascade? Do either of these sound like a
better option?
 
Andy said:
Thanks for the AccessError tip - I've lost count of the number of times I've
automatically clicked debug without remembering the message!

In this particular instance the deletion occurs when the user hits a Cancel
command button while in the process of adding a new record. Therefore I do
want Access to delete the 'one' record and all related 'many' records (as
well as the main body of the form there are also a couple of subforms).

Not too sure about how Access would figure it out. The relationships do
have cascade delete so I kind of thought that Access would start at the top
and work its way through -maybe that was rash assumption! Based on what
you've said about the problems of updating and deleting multiple tables, I
wonder if I would be better to either merge the tables used in the source
query into 1 or to run an SQL statement to delete the record drectly from the
'1' table thus triggering the cascade? Do either of these sound like a
better option?
:
Click to expand...

Ah, now that's a different matter. If the user has not yet saved the new
record, then there will not be a deletion occurring. Can you post the
Cancel button's code, identifying the line that the error occurs at?
 
The record does get saved by some previous code. The first control the user
enters requires them to enter a company code which is a primary key field.
In order for them to enter data into fields that are on the many side of the
relationship(s) the '1' side record needs to be saved, therefore when the
user exits the company code field the record is saved:

Private Sub txtCompanyCode_Exit(Cancel As Integer)
If IsNull(Me.txtCompanyCode) Then
msgBox "You must enter a company code." & D_LINE _
& "If you clicked New by mistake and want to cancel then enter 99 in the
company code box. Then hit Cancel."
Cancel = True
Else
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

The code that is in the Cancel buttons click event is as follows:

''''''''''''''''''''''''''''''''''''''''''''''''''
'' Some code here to validate the record status ''
''''''''''''''''''''''''''''''''''''''''''''''''''
'identify the status of the record
Select Case lngRecStatus
Case R_NEW

'Check that the user really wants to cancel and if so delete the newly
created record
If msgBox(C_CHECK & "The record you have just created will not be" _
& " saved to the database.", vbYesNo, C_TITLE) = vbYes Then

'Delete record and set flag to true
DoCmd.RunCommand acCmdDeleteRecord <- Error here
booContinue = True
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''
'' Select Case continues here with other options ''
'''''''''''''''''''''''''''''''''''''''''''''''''''
 
Andy said:
The record does get saved by some previous code. The first control the user
enters requires them to enter a company code which is a primary key field.
In order for them to enter data into fields that are on the many side of the
relationship(s) the '1' side record needs to be saved, therefore when the
user exits the company code field the record is saved:

Private Sub txtCompanyCode_Exit(Cancel As Integer)
If IsNull(Me.txtCompanyCode) Then
msgBox "You must enter a company code." & D_LINE _
& "If you clicked New by mistake and want to cancel then enter 99 in the
company code box. Then hit Cancel."
Cancel = True
Else
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

The code that is in the Cancel buttons click event is as follows:

''''''''''''''''''''''''''''''''''''''''''''''''''
'' Some code here to validate the record status ''
''''''''''''''''''''''''''''''''''''''''''''''''''
'identify the status of the record
Select Case lngRecStatus
Case R_NEW

'Check that the user really wants to cancel and if so delete the newly
created record
If msgBox(C_CHECK & "The record you have just created will not be" _
& " saved to the database.", vbYesNo, C_TITLE) = vbYes Then

'Delete record and set flag to true
DoCmd.RunCommand acCmdDeleteRecord <- Error here
booContinue = True
End If

'''''''''''''''''''''''''''''''''''''''''''''''''''
'' Select Case continues here with other options ''
'''''''''''''''''''''''''''''''''''''''''''''''''''
Click to expand...

I can't tell you specifically why you are getting this error, but I can say
that the whole setup sounds very cumbersome. Why are you doing it like this
instead of using a main form/subform setup? If you are trying to force a
child record to be entered at the same time as the main record, then it
isn't going to work. What if there is a power cut or a computer crash or a
network glitch after the main record is saved but before the child record is
entered? How are subsequent child records supposed to be entered for
existing companies?
 
Thanks for this Brian, I am inclined to agree with you! The reason I opted
for using a query as the record source was because the many side of the
relationship holds historical data for the one side record. For the purposes
of data input only the most recent record needs to be displayed. Having
tried a simple form using a query as the record source as a test it seemed to
work - however, it obviously is fraught with other problems in reality.
Using Form/sub-forms is one option, the other one is to merge the 'one' and
'many' tables together since there are only 5 fields on the one side. I
realise this is 'bad' database design but it will be easier in terms of
designing the main form which already has 3 subforms on it.
 
Andy said:
Thanks for this Brian, I am inclined to agree with you! The reason I opted
for using a query as the record source was because the many side of the
relationship holds historical data for the one side record. For the purposes
of data input only the most recent record needs to be displayed. Having
tried a simple form using a query as the record source as a test it seemed to
work - however, it obviously is fraught with other problems in reality.
Using Form/sub-forms is one option, the other one is to merge the 'one' and
'many' tables together since there are only 5 fields on the one side. I
realise this is 'bad' database design but it will be easier in terms of
designing the main form which already has 3 subforms on it.
Click to expand...

Gotcha. I definitely wouldn't recommend denormalising your database to get
round what is basically an interface design issue. There's lots of ways you
could handle this. For a start, I don't see why the fact that you already
have three subforms should be a problem. Clearly you already have the
"many" side fields on the screen taking up as much room as they take up, so
why should it be a problem to have them displayed in a subform instead of on
the main form? If you do things like having the subform in Single Form view
rather than Continuous, and hiding the navigation buttons, then it needn't
even look much like a subform. Or, in the AfterInsert event of the main
form, you could pop up a dialog box for the entry of the child record.

If you do want to force a child record to be entered at the same time as the
main record, then your only option really is to use an unbound form, and to
code the updates within the scope of a single transaction. Theoretically,
since the introduction on Access 2002, it is possible to have multiple
updates by bound forms spanned by a single transaction, but I have spent
many hours working with this and I can promise you that not only is it very
tricky to implement, but it doesn't work properly either. If you want to
investigate further, I would suggest obtaining a copy of the Access 2002
Developer's Handbook.
 
Thanks for your help Brian - I will try and do this using sub forms - the
current subforms will need to be subforms on the new subform (if you see what
I mean) and the new subform will be quite big in its own right. Will this
matter too much? I think I might invest in a copy of the handbook as well
anyway!
 
Andy said:
Thanks for your help Brian - I will try and do this using sub forms - the
current subforms will need to be subforms on the new subform (if you see what
I mean) and the new subform will be quite big in its own right. Will this
matter too much? I think I might invest in a copy of the handbook as well
anyway!

:
Click to expand...

Those things will not matter much, the main thing is whether you can fit it
all on. Incidentally, there is a simple way to avoid having subforms of
subforms (sometimes you must have an alternative, because Access will "only"
let you nest subforms 3-deep). What you do is this:

1. Put an unbound hidden text box on the main form.
2. In the Current event of the "top" subform, you put a line of code that
updates the main form's hidden text box with the primary key of it's (the
subform's) current record.
3. Make the "sub" subform a subform of the main form, not of the "top"
subform, and link it to main form's hidden text box.

The Access (your version) Developer's Handbook is an absolute must for every
serious developer, but it is not for beginners.
 
Back
Top