By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,668 Members | 1,481 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,668 IT Pros & Developers. It's quick & easy.

Modifying DCount Function

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

Expand|Select|Wrap|Line Numbers
  1. ..Private Sub CARDID_AfterUpdate()
  2. If DCount("*", "[qryDay60]", "[CARDID] = '" & Me.[CARDID] & "'") Then
  3. 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."
  4. End If
  5. 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?

Jan 3 '08 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 374
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


Expand|Select|Wrap|Line Numbers
  1. Function CheckID(CodeID as String, CustomerName as String) as Boolean
  3.          Dim db as DAO.Database
  4.          Dim rs as DAO.Recordset
  5.          On Error Goto Err_CheckID
  7.          Set db = currentDb()
  8.          Set rs = db.OpenRecordSet("SELECT * FROM [DatabaseName] " & _
  9.                      "WHERE CodeID = '" & CodeID & "'" & _
  10.                      , dbOpenDynaset)
  11.           With rs
  12.                  If Not .EOF Then
  13.                          .Edit
  14.                          !FullName = CustomerName
  15.                          'Add additional fields that you want to update
  16.                          .Update
  17.                 End If
  18.           end with
  19.           CheckID = True
  20.           rs.close
  21.           db.close
  23.           set rs = nothing
  24.           set db = nothing
  25.           exit function
  26.  Err_CheckID:
  27.      CheckID = False
  29.           rs.close
  30.           db.close
  32.           set rs = Nothing
  33.           set db = Nothing
  35. 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.
Jan 5 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.