Save query duplicating records

[Carol57]

I have a query - see coding below that saves not only new records, but also the existing records, which results in duplicate entries to the underlying table. Is there a way that I can show the existing entries, but only save the new entries.


Private Sub Save_Click()
Dim cKeyID As Long
'Update the database
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset
Dim str As String

Set db = CurrentDb
Set qdf = db.QueryDefs("EmployeeAbsence&quot
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm


Set rst = qdf.OpenRecordset(dbOpenDynaset).Clone

If rst.RecordCount > 0 Then
rst.MoveFirst

Do While Not rst.EOF
HoldDate = rst!Date
HoldApp = rst!Details
Me.ctCalendar1.DateText(HoldDate) = HoldApp
Me.ctCalendar1.DateImage(HoldDate) = rst!Image

rst.MoveNext
Loop
End If

For cKeyID = ctCalendar1.DateStart To ctCalendar1.DateEnd Step 1
If Me.ctCalendar1.DateText(cKeyID) <> Empty Or Me.ctCalendar1.DateImage(cKeyID) <> 0 Then
rst.AddNew
rst!Details = ctCalendar1.DateText(cKeyID)
rst!Image = Me.ctCalendar1.DateImage(cKeyID)
rst!Employee = Me.Employee

If ctCalendar1.DateText(cKeyID) = Empty Then
rst!Details = &quot; &quot;
End If

rst!Date = cKeyID
rst.Update
End If
Next

rst.Close
End Sub


Thanks

 

[Isadore]

Carol: don't have too much time this morning but remember that when you use the &quot;AddNew&quot; command, it'll add a NEW ROW; so what I'd suggest is that you do an Update/Edit in one routine, and an AddNew in another; keeping them separate. In the above code I would go ahead and close out the recordset, then open it up a second time doing your updates, then your addnew, or v.v.

Your logic looks good; if you want to try the above routine be sure to &quot;Update&quot; the first part before moving to the second part. As a general rule I go ahead and close the recordset out and then move on and start again but technically you might not have to do that - but at least run through your update for each routine before moving on. Post back if you get it to work.

 

[Carol57]

I did get it to work by setting a delete before the addnew -
See below:

Set rst = qdf.OpenRecordset(dbOpenDynaset).Clone

If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
rst.Delete
rst.MoveNext

Loop
End If


I have not been doing any access programming for a while as I moved into another area of programming, so my access is a little rusty, along with having the flu. Anyways, I figured that after this series of updates, I will be on the ball again.

Thanks for your reply.

 

[Isadore]

Carol: Good luck to you; hope you get better soon - luck me, I haven't had the flu in > a decade 8).

I've left Access to, for the most part, to program in ASP.NET (but do run an Access/ASP.NET web site at

http://frontpage.auburn.edu/icaae/index.aspx).

If you ever get into this area they have a nice forum over at ASP.NET.

You're routine is interesting, to say the least. The only advice I could give is to try and keep the routines separate - in the above case you could substitute with a delete &quot;*&quot; type query. Good luck. Someone else may drop by and provide additional insight - in a bit of time crunch right now.