Hi

I have a function that reads into a recordset information from a table. A variable vDateOfBirth is used to calculate the persons age from the file and the file is updated. This works fine. I would however like to backup the file after age calculation. I started to do another loop which starts at the top of table 2 and updates it sequentially the same as table 1. However when I put a breakpoint in the 2nd loop it is not even reaching it....can anyone tell me where I am going wrong and is this the best way to do it?

Heres the code:

Function CalcAge()
Dim DB As Database
Dim sSQL As String
Dim tbl1 As Recordset
Dim vPersonID As Long
Dim vLastName As String
Dim vFirstName As String
Dim vBank As String
Dim vBranch As String
Dim vSortCode As String
Dim vAccountNumber1 As String
Dim vAccountNumber2 As String
Dim vAccountNumber3 As String
Dim vAccountNumber4 As String
Dim vDateOfBirth As Variant
Dim vNINO As Variant
Dim vAddress1 As String
Dim vAddress2 As String
Dim vAddress3 As String
Dim vAddress4 As String
Dim vPostCode As String
Dim vFormDate As Variant
Dim vTodaysDate As Variant
Dim vTaxCode As String
Dim vnotes As String


Set DB = DBEngine.Workspaces(0).Databases(0)
Set tbl1 = DB.OpenRecordset("R85details")
Set tbl2 = DB.OpenRecordset("Master")

tbl1.MoveFirst


Do Until tbl1.EOF
    vPersonID = tbl1![PersonID]
    vLastName = tbl1![LastName]
    vFirstName = tbl1![FirstName]
    vBank = tbl1![Bank]
    vBranch = tbl1![Branch]
    vSortCode = tbl1![SortCode]
    vAccountNumber1 = tbl1![AccountNumber1]
    vAccountNumber2 = tbl1![AccountNumber2]
    vAccountNumber3 = tbl1![AccountNumber3]
    vAccountNumber4 = tbl1![AccountNumber4]
    vDateOfBirth = tbl1![DateOfBirth]
    vNINO = tbl1![NINo]
    vAddress1 = tbl1![Address1]
    vAddress2 = tbl1![Address2]
    vAddress3 = tbl1![Address3]
    vAddress4 = tbl1![Address4]
    vPostCode = tbl1![PostCode]
    vFormDate = tbl1![FormDate]
    vTodaysDate = tbl1![TodaysDate]
    vTaxCode = tbl1![TaxCode]
    'vNotes = tbl1![Notes]


       Dim varAge As Variant
       Dim age As Integer
       Dim vsTaxCode As String



      varAge = DateDiff("yyyy", vDateOfBirth, Now)
      If Date < DateSerial(Year(Now), Month(vDateOfBirth), _
                           Day(vDateOfBirth)) Then
         varAge = varAge - 1
      End If
      age = CInt(varAge)

      If age >= 16 Then
        vsTaxCode = "N"
       Else
        vsTaxCode = "B"
      End If

      tbl1.Edit
      tbl1![TaxCode] = vsTaxCode
      tbl1.Update

      Do Until tbl2.EOF
        tbl2.AddNew
        tbl2![PersonID] = vPersonID
        tbl2.Update
        tbl2.MoveNext
      Loop


tbl1.MoveNext


Loop


End Function


Cheers
Tony

__________________
Cheers
Tony

Hi Tony,

This is an infinite loop.

Do Until tbl2.EOF
        tbl2.AddNew
        tbl2![PersonID] = vPersonID
        tbl2.Update
        tbl2.MoveNext
Loop

Even if your code could enter it, it could never exit. vPersonID will be added to the [PersonID] field ad infinitum, provided [PersonID] is not your Master table's primary key field. If it is, your code will throw Run-time error 3022 when you try and create a duplicate primary key value.

The loop will be entered, however, if some records already exist in the Master table when the loop is reached. A recordset will be created that includes records already in the table, and vPersonID will just keep getting appended to the end of that recordset. If the master table is empty when the loop is reached, then:

Set tbl2 = DB.OpenRecordset("Master")

will create an empty recordset, which means tbl2.EOF is already TRUE, which means:

Do Until tbl2.EOF

will never execute.

HTH,

Bob

whoops, silly me....will have to rethink.

Cheers.

Cheers
Tony

Hi Tony,

It appears as though you are trying to modify records in recodrset "R85details", then add each modified record to your Master recordset. I may not have this quite right, but is the second loop even necessary to accomplish that? Wouldn't simply adding:

' Add the modified record in the initial loop
tbl2.AddNew
tbl2![PersonID] = vPersonID
tbl2.Update

accomplish your aim? Can you simply dispense with the second loop?

Bob

Cheers Bob, can't see the wood from the trees...that works just as well

Cheers
Tony