Table not updating when using recordset edit/update

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

.

Relevant Pages

  • Re: Control = StrConv([Control], 3)
    ... Scott ... >> Fred- I think a list of exceptions would be best, I can only see a handful ... > Function ConvExceptionsInField(StrIn As String) As String ... > Dim strWord As String ...
    (microsoft.public.access.formscoding)
  • Re: simple question
    ... That usually means that you haven't specified the correction database ... Using con As New ... Dim cmd As New system.data.OleDb.OleDBCommand("Select * from ... 'Handle exceptions from the database here ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: Table not updating when using recordset edit/update
    ... Dim rs As DAO.Recordset ... I've seen a lot of code that opens a recordset, ... In order to count those exceptions, ... NewDFRExceptions - DFRExceptions ...
    (microsoft.public.access.modulesdaovba)
  • Re: How to Update records when no duplicates allowed
    ... The point, although unstated, that I wanted to make, is that exceptions are ... this would be a stored procedure, but you convert to dynamic SQL ... >> Bob Lehmann ... >> Dim ds As New DataSet ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Control = StrConv([Control], 3)
    ... > Fred- I think a list of exceptions would be best, I can only see a handful ... Function ConvExceptionsInField(StrIn As String) As String ... Dim strWord As String ... Dim intY As Integer ...
    (microsoft.public.access.formscoding)