ADO AddNew Method creates TWO Records

  • Thread starter Thread starter George Hoyt via AccessMonster.com
  • Start date Start date
G

George Hoyt via AccessMonster.com

The following code snipit adds TWO records to the table?

****************************************************
Dim objMyCon As Object
Dim objMyRS As Object
Dim fldLnkData As Long

Set objMyCon = Application.CurrentProject.Connection
Set objMyRS = CreateObject("ADODB.Recordset")
'
' Set up and save all the data just entered on the form
'
objMyRS.Open "tblPlayers", objMyCon, adOpenStatic, adLockOptimistic,
adCmdTable
If objMyRS.Supports(adAddNew) Then
objMyRS.AddNew
objMyRS!txtLastName = Me![txtLastName]
objMyRS!txtFirstName = Me![txtFirstName]
objMyRS!txtMiddleInitial = Me![txtMiddleInitial]
objMyRS.Update
End If
fldLnkData = objMyRS!ctrPlayerID ' Get the Primary
Key for this record
objMyRS.Close
************************************************

The goal is to add the data to tblPlayers and retrieve the Autoincrement
Primary Key and use it for creating additional tables with this value as
the foreign key. This works, EXCEPT it adds a record, then ADDS it again
and the variable fldLnkData ends up with the SECOND added row? I've tried
using an "INSERT ..." SQL, and NUMEROUS other ways to stop this and I
can't. On the other hand, the following snipit is used to Add the foreign
key to a table for later use, and it gets added correctly ... i.e., no
duplication.
*********************************************** objMyRS.Open
"tblPlayerAddresses", objMyCon, adOpenKeyset, adLockOptimistic, adCmdTable
If objMyRS.Supports(adAddNew) Then
objMyRS.AddNew
objMyRS!lnkPlayerID = fldLnkData
objMyRS.Update
End If
objMyRS.Close
********************************************************

At a loss .. just can't figure it out?? Is it related to the Autoincrement
somehow?
 
George Hoyt via AccessMonster.com said:
The following code snipit adds TWO records to the table?
Click to expand...

The goal is to add the data to tblPlayers and retrieve the Autoincrement
Primary Key and use it for creating additional tables with this value as
the foreign key. This works, EXCEPT it adds a record, then ADDS it again
and the variable fldLnkData ends up with the SECOND added row? I've tried
using an "INSERT ..." SQL, and NUMEROUS other ways to stop this and I
can't. On the other hand, the following snipit is used to Add the foreign
key to a table for later use, and it gets added correctly ... i.e., no
duplication.
Click to expand...

At a loss .. just can't figure it out?? Is it related to the Autoincrement
somehow?
Click to expand...

George Hoyt,

Hmmm.

Above you stated that "fldLnkData ends up with the SECOND added row?"
Fields/columns cannot "end up" with rows, so I'm not sure what you
mean.

Can I ask why the first table update uses a Static cursor, and the
second table update uses a Keyset cursor?

(Unnecessary Commentary: I think you should declare your variables
properly, ala "Dim objMyCon AS ADODB.Connection", "Dim objMyRS AS
ADODB.RecordSet", but that's just me.)

Well, when I do some simple testing of your code altered for two of my
own existing tables, ItemMaster (with AutoNumber Primary Key), and
PricingMaster (no AutoNumber). (I only changed the field names,
nothing else.):

Public Sub ADO_CursorUpdate_DoubleRowAdd()

Dim objMyCon As Object
Dim objMyRS As Object
Dim fldLnkData As Long

Set objMyCon = Application.CurrentProject.Connection
Set objMyRS = CreateObject("ADODB.Recordset")
'
' Set up and save all the data just entered on the form
'
objMyRS.Open "ItemMaster", objMyCon _
, adOpenStatic _
, adLockOptimistic _
, adCmdTable
If objMyRS.Supports(adAddNew) Then
objMyRS.AddNew
objMyRS!Description = "Long Nails"
objMyRS!VendorItemID = "NB55"
objMyRS.Update
End If
fldLnkData = objMyRS!ItemID ' Get the PrimaryKey for this
record
Debug.Print fldLnkData
objMyRS.Close

objMyRS.Open "PricingMaster", objMyCon _
, adOpenKeyset _
, adLockOptimistic _
, adCmdTable
If objMyRS.Supports(adAddNew) Then
objMyRS.AddNew
objMyRS!ItemID = fldLnkData
objMyRS!StoreID = 1
objMyRS!StartDate = #1/1/2005#
objMyRS!enddate = #12/31/2005#
objMyRS!Price = 0.5
objMyRS.Update
End If
objMyRS.Close

End Sub

The above code works on my tables just like you'd think it would.
It's adds in the "Long Nails" item to ItemMaster, and then adds an
associated price to PricingMaster, including new ItemID.

No double row was created.

I tried it three times, and three times, no error. The third time
through, I altered the Dim statements to my favored format, and it
still functioned normally.

Interesting. :/


Chris O.
 
Chris

Thanks for the feedback!

First, when I mentioned the fldLnkData and second row, I meant the second
generated autoincrement #.

ONE difference in your code vice mine ... I used the data from an entry
form (Me!control ...) and you used hard-coded data.

I tried using hard-coded data as a test and it works as advertized. i.e.,
NO double entry.

For some reason, when setting the field data using the form control entries
causes a double entry.

I have NO clue why? Some characteristic pf the form?

Any further thoughts?

Regards

George
 
George Hoyt via AccessMonster.com said:
Chris

Thanks for the feedback!

First, when I mentioned the fldLnkData and second row, I meant the second
generated autoincrement #.

ONE difference in your code vice mine ... I used the data from an entry
form (Me!control ...) and you used hard-coded data.

I tried using hard-coded data as a test and it works as advertized. i.e.,
NO double entry.

For some reason, when setting the field data using the form control entries
causes a double entry.

I have NO clue why? Some characteristic pf the form?

Any further thoughts?

Regards

George
Click to expand...

It's hard to tell without being able to examine the Forms and
Controls.

And >here< I wander off into guess work . . . (sorry . . .)

All I can think of is that maybe the Form or Controls on the Form
remain pointed at some other *leftover* code, in addition to the code
present, or otherwise bound to some source of data.


And >here< I go create a Form based on ItemMaster. I put a command
button on the form. I put my version of the code into the command
button's Click event procedure.

I modify the code back to referring to Controls on a Form (for
ItemMaster, anyway).

I run the Form, click the command button, and . . .

.. . . I thought something odd might happen (it jumped, and instead of
adding the "next" higher number, jumped two numbers). The controls
were still bound to the table via the RecordSource of the Form. (I
was waiting to get two records, but that didn't happen, it just jumped
two autonumbers).

I get rid of that, and try again . . .

.. . . And, yes. Once I made sure the Text Boxes were unbound, and
that the Navigation Buttons (on the Format Tab) was set to "NO", the
code functioned normally.


Sincerely,

Chris O.
 
Chris

Thanks for taking the time to evaluate. Strangely, I've experienced BOTH
.... my original problem of adding 2 records AND the skipping of
autoincrement #'s. I thought if I solved ONE, I'd inherently solve both.

I tried the following code rather than the .AddNew and it seems to work
fine ...

The first command is the command that is created if you add a command
button to your form and answer the wizard questions to Add Record. Then I
opened the table to get the Primary Key for the last entry for use as
foreign keys in other tables. Of course I make the assumption that the
last entry I make is in fact the last record!!

Not certain what is peculiar about the .AddNew when using form controls for
data entry?

**************************************************

DoCmd.GoToRecord , , acNewRec
'
' Get the primary key of the record just added
'
objMyRS.Open "tblPlayers", objMyCon, adOpenStatic, adLockOptimistic,
adCmdTable
objMyRS.MoveLast
fldLnkData = objMyRS!ctrPlayerID ' Get the Primary
Key for this record
objMyRS.Close
'
' Set the foreign key in the related tables
'
objMyRS.Open "tblPlayerAddresses", objMyCon, adOpenKeyset,
adLockOptimistic, adCmdTable
If objMyRS.Supports(adAddNew) Then
objMyRS.AddNew
objMyRS!lnkPlayerID = fldLnkData
objMyRS.Update
End If
objMyRS.Close

***********************************************

Again, thanks for our help. Not sure what's going on, but this method of
doing what I'm trying to do will work ... I hope :>)

George
 
Back
Top