Recordset-Creating Duplicate

  • Thread starter Thread starter Charles D Clayton Jr
  • Start date Start date
C

Charles D Clayton Jr

A2K
I have two tables: tblDrawings & tblRevisions. They have a 1 to many
relationship. I have a form that puts new values into the
tblRevisions but I am noticing something strange. It will generate
two values for the FK. The first one will be a brand new value (and
consequently it is adding a new record to tblDrawings which I do not
want). The second one has the correct value for the FK. So everytime
I want to add one record, it adds two. Here is the code that I am
using,

' Open recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Source = "qryInputForm_Isos_Rev"
rst.Open Options:=adCmdTable

' Add Revision Info
With rst
.AddNew
.Fields("ID_Isos") = Me.cboInputForm_RevIso.Column(0)
.Fields("rev") = Me.txtRev
.Fields("RevLet") = Me.RevLet
.Fields("Eng") = Me.txtEng
.Fields("DC") = Me.txtDC
.Fields("Date Received") = Me.txtDateReceived
.Update
End With

Does anybody know of a reason for this?

Thanks,

Charles D Clayton Jr
 
Charles D Clayton Jr said:
A2K
I have two tables: tblDrawings & tblRevisions. They have a 1 to many
relationship. I have a form that puts new values into the
tblRevisions but I am noticing something strange. It will generate
two values for the FK. The first one will be a brand new value (and
consequently it is adding a new record to tblDrawings which I do not
want). The second one has the correct value for the FK. So everytime
I want to add one record, it adds two. Here is the code that I am
using,

' Open recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Source = "qryInputForm_Isos_Rev"
rst.Open Options:=adCmdTable

' Add Revision Info
With rst
.AddNew
.Fields("ID_Isos") = Me.cboInputForm_RevIso.Column(0)
.Fields("rev") = Me.txtRev
.Fields("RevLet") = Me.RevLet
.Fields("Eng") = Me.txtEng
.Fields("DC") = Me.txtDC
.Fields("Date Received") = Me.txtDateReceived
.Update
End With

Does anybody know of a reason for this?

Thanks,

Charles D Clayton Jr
Click to expand...

At first guess, I'd say the problem is likely to be in the query,
"qryInputForm_Isos_Rev". Does that query join tblDrawings and
tblRevisions? It seems to me that if you just want to add a record to
tblRevisions, you should open a recordset on that table only.
 
I considered that and I changed the query so that it only has one
table. Even when I change the source to just the tblRevisions, it
still does the same. Adds two new records to tblRevisions. The first
will have the wrong value for the foreign key (which is to say that it
will have created a new record in tblDrawings) and the second will
have the correct value for the foreign key.

I double check my code and commented everything out except this:
Dim rst As ADODB.Recordset
Dim ctl As Control
'On Error GoTo IsoSaveError
Set rst = New ADODB.Recordset

I even commented out the On Error routine. So even when I have the
source as the tblRevisions, it still does this.

I do not know exactly what to do. I suppose that I could always check
tblDrawings and if after this routine has run, it has added a
completely new record, just automatically delete it but that seems a
completely wrong way to solve the problem.

Charles D Clayton Jr
 
Where is this code running? Behind a single main form? Is that form
bound to a table or query? If bound, what is it bound to?
 
Sorry for the delay but I was sick yesterday. The form is bound to a
query. The query combines fields from both the tblDrawings and
tblRevisions.

Charles D Clayton Jr
 
My guess is that this is the source of your problem. I'd have to see
the SQL of the form's recordsource query, but my guess is that it is set
up in such a way that every record you add to the form is actually
adding records to both tblDrawings and tblRevisions. Then you have this
other code you posted that independently adds yet another record to
tblRevisions, so you end up with two records in tblRevisions for each
record you add to tblDrawings via the form.

I don't know what the purpose of the form is, what controls you have on
the form, or why the form is based on a join of the two tables instead
of on tblDrawings alone, so I can't say whether you should (a) revise
the form's recordsource query, (b) base the form on tblDrawings alone,
(c) drop the code that adds a record to tblRevisions, (d) restructure
the form into a main form/subform arrangement, with the main form based
on tblDrawings and the subform based on tblRevisions, or (e) do
something else entirely.

If I'm right about what's going on, I hope that you have enough
information now to see what's wrong and fix it, knowing what you know
about your application. If not, post back with the SQL of the query and
the information I alluded to above that "I don't know", and maybe
together we can figure out the best solution.
 
Thanks so much for your help. Your questions did cause me to change
the recordsource at runtime to only tblRevisions and now it works
fine. Again, thanks so much for your help.

Charles D Clayton Jr
 
Back
Top