Extra data added to tables

C

Chrism

When I add data to a table from a form through recordset coding, and when the
form closes, it adds an extra row of data in the table. If I set the fields
to Null the extra data added will be blank, otherwise it will just add the
same data that was previously entered. This only happens when the form is
closed through a command button. Any help or comment on this wil be greatly
appreciated.
 
D

Dirk Goldgar

In
Chrism said:
When I add data to a table from a form through recordset coding, and
when the form closes, it adds an extra row of data in the table. If
I set the fields to Null the extra data added will be blank,
otherwise it will just add the same data that was previously entered.
This only happens when the form is closed through a command button.
Any help or comment on this wil be greatly appreciated.
Click to expand...

What's the code for the button's Click event? It sounds like maybe
you're manually adding a record that the bound form is going to save
automatically.
 
C

Chrism

That was what I was thinking, but tried working around it so many ways. It
probably is something so easy that I totally overlooked. Here is the code
for the button. Again thanks for the help!!!



Dim rstmanuf As ADODB.Recordset

If IsNull(Me.manuf) Then
MsgBox "Enter a Manuf"
Else

Set rstmanuf = New ADODB.Recordset

With rstmanuf
..ActiveConnection = CurrentProject.Connection
..CursorType = adOpenKeyset
..LockType = adLockOptimistic
..Open "tblmanuf"
..AddNew
!Manufacturer = Me.manuf
..Update

End With

End If
rstmanuf.Close
Set rstmanuf = Nothing

Me.manuf = Null
Me.manuf.SetFocus
DoCmd.Close
DoCmd.OpenDataAccessPage "frmmisc"



End Sub
 
D

Dirk Goldgar

In
Chrism said:
That was what I was thinking, but tried working around it so many
ways. It probably is something so easy that I totally overlooked.
Here is the code for the button. Again thanks for the help!!!

Dim rstmanuf As ADODB.Recordset

If IsNull(Me.manuf) Then
MsgBox "Enter a Manuf"
Else

Set rstmanuf = New ADODB.Recordset

With rstmanuf
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "tblmanuf"
.AddNew
!Manufacturer = Me.manuf
.Update

End With

End If
rstmanuf.Close
Set rstmanuf = Nothing

Me.manuf = Null
Me.manuf.SetFocus
DoCmd.Close
DoCmd.OpenDataAccessPage "frmmisc"

End Sub
Click to expand...

Is that form bound? If it is, and any of the bound controls -- such as
[manuf], maybe -- have been modified, then setting the control to Null
isn't going to stop the form from saving the record when you close it.
Why are you adding the record via a recordset, rather than letting the
form save it?

Assuming I'm right, and the form is bound to tblmanuf, then you need to
take either of two approaches:

1. Don't use a bound form. Then you can use the recordset as you're
doing, to add the record to the table (though it seems like a laborious
way to do it).

2. Use a bound form, as you have it, and don't bother with the
recordset. Just force the form to save the current record before
opening your data access page:

Me.Dirty = False
DoCmd.OpenDataAccessPage "frmmisc"
DoCmd.Close acForm, Me.Name, acSaveNo

There's a third alternative, which is to leave everything pretty much as
it is, but *undo* the bound form before closing it -- instead of
"Me.manuf = Null", write "Me.Undo". However, I don't see the point of
using a bound form *and* a recordset to update the same table.
 
C

Chrism

Yes, the table was bound. I guess that is what happens when you try to
follow to many help sites, and continously make changes. I am trying to do
everything ADODB and code it instead of having Access do the work. I used
to be dang good at it, but I guess that is what happens if you do not work
with it for 5 years!!! O well thanks for the help
 
Top