Command button on a form to generate a new record not working properly

I have the following code in an onclick event of a command button on a form.  But it creates a new record even if the ID already exists in the table.  Why?

Private Sub cmdScheduleReminder_Click()

Dim RS As DAO.Recordset

        Set RS = CurrentDb.OpenRecordset("tblTasks")
        
        If DCount("*", "tblTasks", "ID = " & Me.txtID) > 0 Then
        
            MsgBox "This appears to be a new reminder which does not already exist in the program.  A new reminder is being created."

            RS.AddNew
            'RS!ID = Me.txtID
            RS!DateCreated = Me.txtDateCreated
            RS!TaskDescription = "REMINDER" & " - " & Me.cboTaskDescription
            RS!Priority = Me.cboPriority
            RS!ScheduleTask = Me.cboTaskWhenComment
            RS!TaskWhenSpecificDate = Me.txtReminderDate
            RS!ReminderDate = Me.txtReminderDate
            RS!TaskType = Me.cboTaskType
            RS!ParetoPrincipal = Me.cboParetoPrincipal
            RS.Update
            
            MsgBox "The new reminder has been created in the program."
        
        Else

        MsgBox "This reminder already exists and will be edited if you have made changes."
        
            RS.Edit
            'RS!IDID = Me.txtID
            RS!DateCreated = Me.txtDateCreated
            RS!TaskDescription = "REMINDER" & " - " & Me.cboTaskDescription
            RS!Priority = Me.cboPriority
            RS!ScheduleTask = Me.cboTaskWhenComment
            RS!TaskWhenSpecificDate = Me.txtReminderDate
            RS!ReminderDate = Me.txtReminderDate
            RS!TaskType = Me.cboTaskType
            RS!ParetoPrincipal = Me.cboParetoPrincipal
            RS.Update

        End If

End Sub

Open in new window

SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
Set RS = CurrentDb.OpenRecordset("tblTasks")
Why do that?  DCount is an evil thing
Open the recordset with a WHERE clause and check RecordCount
Do This!

Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("Select * from tblTasks where ID = " & Me.txtID.Value)
With RS
    If .RecordCount = 0 Then
        MsgBox "This appears to be a new reminder which does not already exist in the program.  A new reminder is being created."
         .AddNew
    Else
        MsgBox "This reminder already exists and will be edited if you have made changes."        
        .Edit
     End if

            !DateCreated = Me.txtDateCreated
            !TaskDescription = "REMINDER" & " - " & Me.cboTaskDescription
            !Priority = Me.cboPriority
            !ScheduleTask = Me.cboTaskWhenComment
            !TaskWhenSpecificDate = Me.txtReminderDate
            !ReminderDate = Me.txtReminderDate
            !TaskType = Me.cboTaskType
            !ParetoPrincipal = Me.cboParetoPrincipal
            .Update  
End with
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
Is this form bound?

Where does the control "txtID" get its value from?  If the form is bound to an Access table, then that field would normally be filled in automatically.

But I think you probably simply need to change:

If DCount("*", "tblTasks", "ID = " & Me.txtID) > 0 Then

to:

If DCount("*", "tblTasks", "ID = " & Me.txtID) = 0 Then
0
Dale FyeCommented:
I like Nick's answer better!
0
SteveL13Author Commented:
Nice!  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.