Access won't dump variables

  • Thread starter Thread starter waterman
  • Start date Start date
W

waterman

Setup: Access front end with a SQL Server backend.

I have a form that allows users to enter data into fields
on a form that can be added to a recordset at the press
of a button. The results of the entry are then displayed
on a subform.

It takes the first entry just fine, but when the user
attemtps to enter the second entry, it can't seem to
forget the first entry. The code I'm using is shown
below.

Private Sub Submit_Click()
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblTransactions",
dbOpenDynaset, dbSeeChanges)

With rst
.AddNew
MsgBox Me![MemID] ' This will always show the
value currently in the field

!MemberID = Me![MemID]' This will always be the
first entry, no matter what is currently in the field at
the moment

MsgBox Me![LastName]' This will always show the
value currently in the field

!MemberLastName = Me![LastName]' This will always
be the first entry, no matter what is currently in the
field at the moment


.Update
End With


Set dbs = Nothing
Set rst = Nothing
Forms!frmCallentry!subfrmEntry.Requery

Me![MemID] = Null
Me![LastName] = Null

End Sub

You'll notice before I use a message box to verify what
value the MemID and LastName field hold, and they always
display the data that I typed into the fields. However,
when Ithe subroutine adds records to the table, it always
populates the data from the first entry.
 
Hi

I would think that if the values shown by the message boxes are correct then
the code should be trying to add these values as a new record.

Have you tried adding new values manually to the table?
SQL Server can block new entries under some conditions.

Is the database set up to show errors and debug?

With Access 2000 and 2002 set up normally, the code below would normally
generate an error message.
ADO is the default recordset and the code below is for DAO.

For these versions of Access you would have to set a reference to Microsoft
DAO 3.6 Object Library.
Then Dim dbs As DAO.Database and Dim rst As DAO.Recordset

Cheers,
Peter
 
Back
Top