# Solved: VBA - Access DCount Question



## Chris_E (Feb 16, 2006)

Hi all

I'm using a DCount() function to pass back the number of records in a table which match a control's value on a form. My first attempt worked beautifully. So then I extended the application as follows.

I have two unbound controls "LowNumber" and "HighNumber" on my form.

I'm asking the user to enter two telephone numbers and the application will then, if all is well, add these numbers and all sequential numbers in between to a table called MasterTable. So the user might add 50 numbers from, (eg) 1 to 50 as long as no one number in that sequence is already on the database.

There maybe as many as 11,000 numbers in the database which is sitting in a b.e SQL server space so its not appropriate to ask em to 'go find'!

So, the controls on the form receive the telephone numbers, LowNumber is the starting phone number and HighNumber is the highest phone number.

Then, I use a loop and DCount function to see if each number and the numbers in between are already entered in the database:

Code:

----------------
Private Sub cmd_chk_for_duplicate_Click()
Dim LTotal As Double
Dim NumChk As Double

For NumChk = LowNumber To HighNumber
LTotal = DCount("mstrKey", "MasterTable", "[mstrPRN] = NumChk" 
If LTotal <> 0 Then
MsgBox LTotal & " duplicate Premium Rate Number(s) has or have been been identified: 0" & NumChk & Chr$(13) & "Please review this record!", vbCritical, "Duplicate record Found"
GoTo Duplicate_found
End If
Next NumChk

' Move to next Form Section

Me.Call_Cost_Data.Enabled = True
Me.Call_Cost_Data.SetFocus
Exit Sub

Duplicate_found:
Me!LowNumber.Value = Null
Me!HighNumber.Value = Null
Me!LowNumber.SetFocus

End Sub

____

But this fails on the DCount line.

Clearly I have a syntax error relating to the LTotal = DCount("mstrKey", "MasterTable", "[mstrPRN] = NumChk", but does anyone know where the gremlin is?

Cheers in anticipation

Chris


----------



## cristobal03 (Aug 5, 2005)

Egh...I sort of disagree with this usage because phone numbers usually should be treated as text data types.

Anyway, the DCount fails because in VBA all values contained within quotes are treated literally. You are attempting to interpolate a variable, so you need to use a *concatenation* method.

In other words,

*Dcount("mstrKey", "MasterTable", "[mstrPRN] = NumChk")*​Looks for column *mstrPRN* in table *MasterTable* for the string value *NumChk*. Not having the entire module, at the very least you need to do something more like this:

*DCount("mstrKey", "MasterTable", "[mstrPRN] = " & NumChk)*​If *mstrKey* and *mstrPRN* are variable names not column names, you should drop the quotes around the variables.

*DCount(mstrKey, "MasterTable", "[" & mstrPRN & "] = " & NumChk)*​Does that make sense?

HTH

chris.


----------



## Chris_E (Feb 16, 2006)

Once more Chris - you're a superstar.

DCount("mstrKey", "MasterTable", "[mstrPRN] = " & NumChk)

did the trick

Thanks!

Chris


----------

