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

Modifying DCount Function

iBasho
P: 16
Hi
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?

Thanks.
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

Example:

Expand|Select|Wrap|Line Numbers
  1. Function CheckID(CodeID as String, CustomerName as String) as Boolean
  2.  
  3.          Dim db as DAO.Database
  4.          Dim rs as DAO.Recordset
  5.          On Error Goto Err_CheckID
  6.  
  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
  22.  
  23.           set rs = nothing
  24.           set db = nothing
  25.           exit function
  26.  Err_CheckID:
  27.      CheckID = False
  28.  
  29.           rs.close
  30.           db.close
  31.  
  32.           set rs = Nothing
  33.           set db = Nothing
  34.  
  35. End Function
  36.  
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.