Modifying DCount Function

[iBasho]

Hi
I am using the DCount function to check and alert users for existing IDs in my database after a new record is entered.

..Private Sub CARDID_AfterUpdate()
If DCount("*", "[qryDay60]", "[CARDID] = '" & Me.[CARDID] & "'") Then
MsgBox "This card ID number already exists in the system. Once you enter the dependent code you will be able to determine if this is a subsequent reinbursement submission from the same member or a new submission from another subscriber on the same policy. Please check to make sure that the member was NOT initially ineligible."
End If
End Sub..

I am trying to modify it so that it looks only at records with the same ID in year 2008. The YEAR field is set up as text.
I tried to add this condition in the criteria section of the DCount function, but that got me nowhere. I still get the msgbox for existing records in 2007 with the same ID.

Any idea on how to fix this?

Thanks.

[PianoMan64]

Can you provide some more detail as to how the query that you're using is layed out. It would be much easier if you simply has a small peace of VBA to do a lookup on the ID code

Example:

Function CheckID(CodeID as String, CustomerName as String) as Boolean
 
         Dim db as DAO.Database
         Dim rs as DAO.Recordset
         On Error Goto Err_CheckID
 
         Set db = currentDb()
         Set rs = db.OpenRecordSet("SELECT * FROM [DatabaseName] " & _
                     "WHERE CodeID = '" & CodeID & "'" & _
                     , dbOpenDynaset)
          With rs
                 If Not .EOF Then
                         .Edit
                         !FullName = CustomerName
                         'Add additional fields that you want to update
                         .Update
                End If
          end with
          CheckID = True
          rs.close
          db.close
 
          set rs = nothing
          set db = nothing
          exit function
 Err_CheckID:
     CheckID = False
 
          rs.close
          db.close
 
          set rs = Nothing
          set db = Nothing
 
End Function
 

Copy and paste this code into your Module. then this way you can call it from any form or report when you need it.