Duplicate rows added to a table

[msshaw]

Hi,

I have a piece of code that I have inhereted - see below - However, the code adds 2 identical lines to AvailBedsHist.

I have stepped through and cannot find anything wrong with the code, and neither can my colleagues here at work.

However, I did notice that it has only begun occuring after 1/1/2000....... possiable Y2K bug?

On Error GoTo Commit_Click_Err
    
Dim PrtFolio As Database
Dim AvailBedsHist As Recordset       ' source file
Dim AvailBeds As Recordset           ' output file
Set PrtFolio = DBEngine.Workspaces(0).Databases(0)
Set AvailBedsHist = PrtFolio.OpenRecordset("AvailBedsHist", DB_OPEN_TABLE)
Set AvailBeds = PrtFolio.OpenRecordset("AvailBeds", DB_OPEN_TABLE)
    
'_________________________________________________________________________
  
    AvailBeds.Index = "FinHomesID"
    AvailBeds.Seek "=", Me!FinHomeID
    
    If AvailBeds.NoMatch Then
        MsgBox ("The FinHomesID you have entered is not correct. No changes are saved.")
    Else
        AvailBeds.Edit
        AvailBeds("AvailableBeds") = Me!AvailBedsNew
        AvailBeds("DateUpdated") = Me!Date
        AvailBeds("Home") = Me!cmbHomeName
        AvailBeds.Update
        
        AvailBedsHist.AddNew
        AvailBedsHist("FinHomeID") = Me!FinHomeID
        AvailBedsHist("HomeNameBrief") = Me!cmbHomeName
        AvailBedsHist("Event") = Me!cmbEvent
        AvailBedsHist("Date") = Me!Date
        AvailBedsHist("AvailBedsOld") = Me!AvailBedsOld
        AvailBedsHist("BedsChange") = Me!BedsChange
        AvailBedsHist("AvailBedsNew") = Me!AvailBedsNew
        AvailBedsHist("EventNote") = Me!EventNote
        AvailBedsHist.Update
        
        MsgBox ("AvailBeds is updated for Home " & Me!FinHomeID)
    End If
 
    AvailBedsHist.Close
    AvailBeds.Close

Commit_Click_Exit:
    Exit Sub

Commit_Click_Err:
    MsgBox Error$
    MsgBox ("Your changes have not been saved")
    Exit Sub

I look forward to your replies!

Regards,

Mark

 

[gusbrunston]

[tt]
Hi Mark:

I'm no guru, but it looks to me like you are opening the same records twice, once as "AvailBedsHist" and again as "AvailBeds". Thus, you get two records inserted into the same table.

Hope this is helpful. [tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[msshaw]

Hi Gus,

Thanks for your prompt reply, however

Set AvailBedsHist = PrtFolio.OpenRecordset("AvailBedsHist", DB_OPEN_TABLE)
Set AvailBeds = PrtFolio.OpenRecordset("AvailBeds", DB_OPEN_TABLE)[code]

One is the AvailBedsHist table and the other is the AvailBeds table, so I can't see your idea resolving the issue unfortunately.... any more ideas welcome!

 

[gusbrunston]

[tt]
For my own education, please:

Do you get three records? One in a table named "AvailBeds", and two in a table name "AvailBedsHist"?

Thanks, [tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[msshaw]

The AvailBeds holds only 1 record per location, with the upto date information - hence that is why it is edited, and the appropriate line found in the data.

The AvailBedsHist holds - in theory - 1 record per adjustment to AvailBeds.

HTH