ACCESS/VBA: can't get Dcount to work correctly

Question

here is a portion of code I'm using to enable/disable a button (which allows to create a new revision) depending on the result.

If the dcount returns 0, it means the next revision doesn't exist yet (and thus can be created) if the dcount returns 1, it means the next revision already exists (and thus the button will be disabled)

DCount("index_rit", "t_données_rit", "[RITn°]=" & Me.RITn°.Value & " AND [Revision]=" & Me.Revision.Value + 1)

My problem is that for some reason, even if a record corresponds to the values entered in the dcount, it still returns 0.

I tried both with ant without the [] for the fields.
I also tried using * instead of index_rit...

What did I get wrong ?

Answer 1

got it. it was a format problem: RITn° is of the form xx-xx, so I had to put it like this:

"[RITn°]='" & Me.RITn°.Value & "' AND [Revision]=" & Me.Revision.Value + 1)

(added the single quotes)

Answer 2

First thing is that the DCount (and other ‘D’ functions) are generally considered to be a bit of a performance drag. Try replacing it with this quick function. You just pass it a query and it will return the record count

Private Function GetRecordCount(strSearch as String) as Long
Dim rst As ADODB.Recordset
Dim RecCount As Long

Set rst = New ADODB.Recordset

With rst
   .ActiveConnection = CodeProject.Connection
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
   .Source = strSearch
   .Open Options:=adCmdText
   if .EOF = true then
        RecCount=0
   else
        RecCount=.Collect(0)
   End If

   .Close
End With

GetRecordCount = RecCount

Set rst = Nothing

End Function

This uses ADO but could/should use DAO if you are just using access tables but that is another question for another time!