DAO AddNew/Edit Methods

[Ronald Dodge]

Access 2002, SP2 on W2K Pro, SP4
DAO 3.6 (Variables declared to appropriate libraries to avoid ambiguities)
Forms UNBOUND (Mainly cause bound forms don't allow for mouse user
friendliness and strict data validation checks at the appropriate times at
the same time)

In my code, the DAO recordset is put into either Edit or AddNew mode
provided the validation had passed and the checks allows for it, which this
is done within my custom validation code. I have an Add/Update command
button on the form. At the time I press that command button after filling
in the fields, when it comes to the line:

drsRES.Fields("fldDSC").Value = Me.tbxDSC.Value

it errors out with the Run-time error code of 3020 and text of "Update or
CancelUpdate without AddNew or Edit." However, as I checked this out
further, the EditMode on the recordset at that same point of time is showing
a value of '2' (numeric form), which is the same as the constant, adEditAdd.

Given the above, why would I get the above error message, if the EditMode on
the recordset is indicating that it's in AddNew mode? All I can think of
doing at this point, setup a form level variable to indicate if the
recordset should be in edit, addnew, or none mode for updating purposes,
which then have the actual full updating take place all within the
Add/Update command button as it doesn't seem to bother the code that way.

 

[Dirk Goldgar]

Access 2002, SP2 on W2K Pro, SP4
DAO 3.6 (Variables declared to appropriate libraries to avoid
ambiguities) Forms UNBOUND (Mainly cause bound forms don't allow for
mouse user friendliness and strict data validation checks at the
appropriate times at the same time)

In my code, the DAO recordset is put into either Edit or AddNew mode
provided the validation had passed and the checks allows for it,
which this is done within my custom validation code. I have an
Add/Update command button on the form. At the time I press that
command button after filling in the fields, when it comes to the line:

drsRES.Fields("fldDSC").Value = Me.tbxDSC.Value

it errors out with the Run-time error code of 3020 and text of
"Update or CancelUpdate without AddNew or Edit." However, as I
checked this out further, the EditMode on the recordset at that same
point of time is showing a value of '2' (numeric form), which is the
same as the constant, adEditAdd.

Given the above, why would I get the above error message, if the
EditMode on the recordset is indicating that it's in AddNew mode?
All I can think of doing at this point, setup a form level variable
to indicate if the recordset should be in edit, addnew, or none mode
for updating purposes, which then have the actual full updating take
place all within the Add/Update command button as it doesn't seem to
bother the code that way.

Have you placed a breakpoint on the line where you invoke drsRES.AddNew,
and traced the execution line by line from there?

 

[Ronald Dodge]

Kinda hard to do it that way cause with this form cause of user input
required between the time the recordset is put into
Addnew/EditInProgress/None mode and the time when it's updated, if there is
any. Originally, I had it put the recordset into AddNew mode, of which it
worked that way, then the form would move the cursor to the description
field. It's at that point, the recordset should still be in AddNew mode
while use fills in the description and rather to enable it or not for use.

Fields:

ID
Reason
Enable

When the user clicks on the Add/Update command button, the recordset's
EditMode property shows a numeric value of 2, which is the AddNew mode, and
even to the point that the code reaches the point of entering the data, but
yet, it errors back with the 3020 run time error code. What I did to get
around this issue, I created a form level long integer variable to use the
same 3 values that the EditMode property uses, then compare that way when
it's time to update. It's now only the Add/Update command that puts the
recordset in the proper mode, then records the entry, then updates it, which
then the variable I use in place of using the EditMode is set back to 0.
This work around has already proven to be note worthy as it does the job
that was intended to be done with checking the EditMode property and having
the recordset declared at the form's global level.

 

[Ronald Dodge]

Thank you for asking as I have checked through my code of where I could and
it all showed the EditMode property = 2

I won't be back for 2 weeks.

 

[Ronald Dodge]

Now that I'm back from the holidays, I'm back to work with the coding of my
project. Hope you had a good holiday time period as I did.

After thinking over what to do with regards to how I'm going to control the
numbering of the different records in the different tables and given the
different issues that I have had in mind, I have actually decided to create
an internal table that would contain an ID field, the Table name, Prefix
(text before the actual number), Record Number, Check Digit, and Suffix
(text after the actual number). There's several reasons why I had toyed
with this idea and have actually decided to put it to use.

Here's just a few:

By having a check digit, which is control by an algorythm, this allows for
reduction of data entry errors (or human errors).
Allow for Administrators to control if Prefixes/Suffixes are shown on
reports to help distinguish between different ID numbers.
Allow for the option of storing Prefixes/Suffixes within the individual
tables.
Have the new ID number shown within the form for new records without having
to do any guessing work like one would have to do when using the
AutoNumbering system, thus this particular reason has also allowed me to
help address the issue I found as part of a work around solution.

Setting this up was actually pretty easy as I only had to put in a few
additional checks and controls. The main reason I didn't put in a DB name
within the record numbering table is cause part of the table name also
contains the 3 letter code to the particular BE DB file that the table is
stored in to allow for uniqueness and easily distinguishing what table is in
what BE DB file. I had to setup different BE DB files similar to how
libraries are setup on AS/400 systems, but only broken down even more given
Access' size limitations.

I will need to go back and make some changes to the currently created
impacted tables and the codes behind the different forms, but at least it's
still early in the project (coding wise), so I don't have to do too much of
this tedious work. The initial stuff that I have done has mainly to test
things out as far as how it would work in Access, and what sort of
undocumented issues (or may be documented, but not known to me anyhow) that
I would find and have to deal with.