Table not updating when using recordset edit/update

[Ken]

Hi,
First off, I'm using Access 2003 (in 2003 format) and Windows 2000
workstation.
I have a table that I am storing daily counts of different types of
business exceptions. In order to count those exceptions, I use code to
loop through the other tables. Then I create a virtual recordset of
the count table and add the new counts via variables from the VBA code.
Everything works fine (the variables have the right counts, etc.),
EXCEPT... when I go open the count table afterwards, the new record
with the latest daily counts is not there.

Also (recently occuring along with the above problem) I've noticed that
when I go back to code to make changes, I get a message stating that
the database is not opened exclusively and change that I make cannot be
saved. The db is on my desktop and noone else has access to it (since
I'm still developing it).

Any ideas why the record is not writing to the table and why I'm
getting the above message? Also, ideas on how I can do this another
way are definitely welcome. Thanks in advance for any help!

Ken

Here is a snippet of the relevant code...

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("qryDailyCountofDFRExceptions")
If rs.RecordCount = 0 Then
rs.AddNew
Else
End If
rs.MoveLast
With rs
PreviousDayDFRExceptions = !DFR000 + !DFR001 + !DFR005 +
!DFR010 + !DFR020 _
+ !DFR030 + !DFR040 + !DFR050 + !DFR060 + !DFR070 +
!DFR080 _
+ !DFR090 + !DFR100 + !DFR110 + !DFR900
.AddNew
!Date = Date
!DFR000 = DFR000
!DFR001 = DFR001
!DFR005 = DFR005
!DFR010 = DFR010
... more of the same.....
!ExceptionsAddedFromPreviousDay = NewDFRExceptions
!ExceptionsClosedFromPreviousDay = PreviousDayDFRExceptions +
NewDFRExceptions - DFRExceptions
.Update
End With
Set db = Nothing
Set rs = Nothing

 

[Guest]

Hi Ken,

In looking at the code, I don't see where you set "db" to anything, so it is
still 'undefined' when you try to open the recordset "rs".

You can use:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb() ' <=== ADD

Set rs = db.OpenRecordset("qryDailyCountofDFRExceptions")


or you can use:

'comment out next line
' Dim db As DAO.Database
Dim rs As DAO.Recordset

'changed db to CurrentDb
Set rs = CurrentDb.OpenRecordset("qryDailyCountofDFRExceptions")
------snip ---
------snip ---
'comment out next line
' Set db = Nothing
Set rs = Nothing



I am unsure what you are trying to do here:

If rs.RecordCount = 0 Then
rs.AddNew
Else
End If
rs.MoveLast
With rs
' yada yada
.AddNew


As I understand it, a new record is always added at the end of a
table/recordset. I've seen a lot of code that opens a recordset, then addes a
new record. So, I don't think you need the .MoveLast.

You check if the recordcount = 0 then have .AddNew. No data entered and no
..Update. Then six lines later there is another .AddNew????

Since you are adding a new record, does it matter if the record count is
zero? Just add the new record.

Or you can do this:

If rs.RecordCount > 0 Then
rs.MoveLast
End If
With rs
'---snip---


To check if a recordset is empty or not, you can check to see if the pointer
is at the beginning of the recordset AND at the end of the recordset -
impossible if there is at least one record in the recordset.

'checks if ther ARE records
If Not (rs.BOF and rs.EOF) then
'has records
End If

or

'checks if there are NOT records
If rs.BOF and rs.EOF then
'no records
End If


HTH