[AccessD] A2K: Same Name Validation

At 9/9/2004 04:39 AM, you wrote:
>Darren,
>
>I do this in one of my apps:
>
>Function DuplicateCount(fnIn, snIn) As Long
>Dim k As Variant, criteria as String
>   criteria = "FirstName = '" & fnIn & "' AND LastName = '" & snIn & "'"
>   k = Nz(DCount("*", "tblWhatever", criteria))
>   DuplicateCount = k
>End Function

Try this (air code):

Function DuplicateCount (strFirstName as string , strLastName as string) as 
Long

Dim strSQL as String
Dim rs as DAO.Recordset

strSQL ="Select FirstName From Names_tbl Where FirstName = ' " strFirstName 
& "' And LastName = '" strLastName & "'"
set rs = CurrentDB.OpenRecordset(strSQL)
rs.MoveLast   ' force RecordCount to reflect actual number of rows
DuplicateCount = rs.RecordCount

' clean up after yourself
rs.Close
set rs = nothing
strSQL = vbNullString

End Function

The Domain Aggregate functions are very slow; Select is much faster.

Some comments:

"k" should not be a variant unless you really need it to be. It is a slow 
datatype. Long would be much preferred. Besides, you don't need "k" at all. 
You can assign an expression to the function name:

      DuplicateCount = Nz(DCount("*", "tblWhatever", criteria))

would work just fine.  If you are worried about Nz returning a zero if the 
DCount returns null, then just do:
      DuplicateCount = Nz(DCount("*", "tblWhatever", criteria), 0)
                                                                                                                             ^
which forces a zero to be returned if you pass it a null no matter what the 
datatype the DCount returns. I never depend on defaults (too many details 
to remember) and so I always pass the 0 myself. It might also be a little 
faster if a null is passed in because the function knows is doesn't have to 
figure out what to pass back if you are explicit about it. (I used to be a 
real-time programmer, and I can't break the habit of counting the 
milliseconds. And nowadays, since memory is so plentiful, I will usually 
burn memory if it makes my code faster.)

Still, none of this addresses the problem of what to do if you do find 
duplicates. I think you have to show the user the current row, or rows, and 
somehow let them decide to enter another row with the same first and last 
name or select one of the existing ones for display or edit.



Regards,
Paul Strauss


----------

•  Web Site Design, Graphics, and Hosting (www.RationalHosting.net)
•  MS Access Database Custom Applications
•  Technical Support and Consulting

718-253-5535 / 917-975-1729
<mailto:Paul at PStrauss.net>Paul at PStrauss.net