DCount Not Working

Hi Experts,
Can you please help me with the following. I don't know why the following code isn't working.

According to the sample data, a msgbox should appear but it doesn't if i were to repeatedly click on the cmdbutton.

Thanks
Ric

Private Sub Command9_Click()
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("tblClassMeeting")
    With rs
         .AddNew
         !ClassProductID = Me.ClassProductID
         !ClassDate = Me.ClassDate
         .Update
    End With
rs.Close
Call ClassMeetingUpdate_AfterUpdate
End Sub

Private Sub ClassMeetingUpdate_AfterUpdate()
If DCount("[ClassMeetingID]", "tblClassMeeting", "[ClassProductID]=" & Me.ClassProductID & " and [ClassDate] = " & Me.ClassDate) > 0 Then
        MsgBox "Duplicate Entry"
Cancel = True
      DoCmd.RunCommand acCmdUndo
      DoCmd.GoToRecord , , acNewRec
   End If
End Sub

Open in new window

ClassMeetingForm.jpg
ClassMeetingSampleData.jpg
Ronniel Allan CastanitoIT ManagerAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
......and [ClassDate] =#" & format(Me.ClassDate, "yyyy-mm-dd") & "#")
0
 
dqmqCommented:
Probably need delimiters on the date:

DCount("[ClassMeetingID]", "tblClassMeeting", "[ClassProductID]=" & Me.ClassProductID & " and [ClassDate] = #" & Me.ClassDate)  & "#"
0
 
bgrandjeanCommented:
You need #s around your date to indicate it is a date in the where condition, try it like this:

If DCount("[ClassMeetingID]", "tblClassMeeting", "[ClassProductID]=" & Me.ClassProductID & " and [ClassDate] = #" & Me.ClassDate & "#") > 0 Then
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Ronniel Allan CastanitoIT ManagerAuthor Commented:
Still have problem with the following line:

DoCmd.RunCommand acCmdUndo
0
 
Ronniel Allan CastanitoIT ManagerAuthor Commented:
When i use Me.Undo, the last record would Undo but using the DoCmd doesn't. Would it be a problem if i used Me.Undo instead?
0
 
dqmqConnect With a Mentor Commented:
There is nothing to UNDO.  You need to put the DCOUNT logic BEFORE you update the recordset.

Also, I recommend you reconsider the primary key for ClassMeeting table.  It should prevent the duplication you are concerned about.  Alternatively, you can add a no-duplicates index on ClassProductID + ClassDate.    
0
 
bgrandjeanCommented:
I think you are too late to do an Undo at this point.  You should do the check for an existing item before you do the add.
0
 
Ronniel Allan CastanitoIT ManagerAuthor Commented:
How do i set up no-duplicates for ClassProductID + ClassDate?
Is it just a matter of selecting Index: Yes (No duplicates) for the two fields?
IndexNoDuplicates.jpg
0
 
Ronniel Allan CastanitoIT ManagerAuthor Commented:
Thanks
0
 
dqmqCommented:
Creating an index like THAT only allows you to have one column in the index. In other words, you will end up with a separate unique index on each column. From table design, you need to open the Indexes menu.  There, you can create an index with multiple columns.  
0
All Courses

From novice to tech pro — start learning today.