AddNew not working - no error given

  • Thread starter Deanna S via AccessMonster.com
  • Start date
D

Deanna S via AccessMonster.com

This should be simple and I've done this in many apps, but for some reason, I
cannot get this code to work:

Dim db As Database
Set db = CurrentDb
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblStaff", dbOpenDynaset)
With rs
.AddNew
!UserName = txtUserName
!FirstName = txtFirstName
!LastName = txtLastName
.Update
.Bookmark = .LastModified
End With

There is no error given and it steps through as if it's working perfectly,
yet the record is not inserted. The key is autonumber and none of the fields
besides the ones above are required. Any ideas????

Thanks,
Deanna
 
A

Allen Browne

That should work, though it would be better to use the database variable you
declared instead of another instance of Currentdb:
Set rs = db.OpenRecordset("tblStaff", dbOpenDynaset)

To help debug it, you could add:
Debug.Print !StaffID
and see what number comes out. Do this before the .Update, and after setting
the Bookmark. The numbers should match.

Probably worth repairing the database too:
Tools | Database Utilities | Compact and Repair

If it is still not sorted out, what version of Access is this?
Is tblStaff a local table, or attached?
If attached, Access (JET), or what?
 
D

Deanna S via AccessMonster.com

Thanks for the suggestions. I tried all suggestions from both replies and it
still doesn't work. The StaffID is incrementing (it's autonumber) but the
record doesn't save in the table. So odd. This is a 2002 db and the table is
in Access, not linked. Any other ideas???

Thanks
Try rs.Fields("UserName") etc.
This should be simple and I've done this in many apps, but for some reason, I
cannot get this code to work:
[quoted text clipped - 18 lines]
Thanks,
Deanna
 
D

David C. Holley

(Stupid me)

Check that the txtUserName, txtFirstName, txtLastName variables are
being set by adding

Debug.print Now, txtUserName, txtFirstName, txtLastName

As a second step, I would hard code the three (e.g. UserName =
"UserNameVariable). This will help to narrow down where the problem is.
Although I think that the DEBUG will reveal where the problem lies.
 
A

Allen Browne

Just for debugging purposes, try:

Dim db As Database
Dim rs As DAO.Recordset
Dim lngNewID as Long
Dim strWhere As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblStaff", dbOpenDynaset)

With rs
.MoveLast
Debug.Print .RecordCount & " records. Before new, last ID was " &
!StaffID
.AddNew
!UserName = Me.txtUserName
!FirstName = Me.txtFirstName
!LastName = Me.txtLastName
lngNewID = !StaffID
.Update

strWhere = "StaffID = " & lngNewID
.FindFirst strWhere
Debug.Print "NoMatch is " & .NoMatch & " finding " & strWhere


.MoveLast
Debug.Print .RecordCount & " records. After new, last ID is " &
!StaffID
End With

rs.Close
Set rs = Nothing

If that does not idenfity the issue, then check to make sure you have SP8
for JET 4. Locate msjet40.dll (typically under windows\system32).
Right-click and choose Properties. On the Version tab, you should see
4.0.8xxx.0. The xxx digits don't matter, but if you don't see the 8,
download the service pack from:
http://support.microsoft.com/gp/sp

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Deanna S via AccessMonster.com said:
Thanks for the suggestions. I tried all suggestions from both replies and
it
still doesn't work. The StaffID is incrementing (it's autonumber) but the
record doesn't save in the table. So odd. This is a 2002 db and the table
is
in Access, not linked. Any other ideas???

Thanks
Try rs.Fields("UserName") etc.
This should be simple and I've done this in many apps, but for some
reason, I
cannot get this code to work:
[quoted text clipped - 18 lines]
Thanks,
Deanna
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top