Full Version: Help with DCount syntax?
UtterAccess Forums > Microsoft® Access > Access Forms
kgeil
Hi, I'm trying to find a way to prevent duplicate entries in a table holding contact info for people. I'm looking for a way to alert the user of a possible duplicate record when entering the last name. I've been trying to do this for quite some time, and at this point, I have just created a one table (Contacts), two field (Fname, LastName) database to test my code.
On the data entry form for the Contacts table, I have been trying to enter code for the before update event on the LastName text box. I think I am close, but I'm having trouble with the syntax of DCount; I can't get the criteria just right.
I'll worry about the MsgBox later, I'm just praying that that will work, but I can't get to it without fixing the DCount line. Below is my attempt at DCount.
Is there an easier way than this to prevent possible duplicates?
Thanks to everyone for the great teaching; this forum is fantastic.
Kevin
Private Sub LastName_BeforeUpdate(Cancel As Integer)
Dim answer As String
Dim msg As String
Dim userinput As String
userinput = Me.[LastName]
msg = "This is a possible duplicate. Do you want to continue?"
If DLookup("LastName", "Contacts"= "=userinput) > 0 Then
answer = MsgBox(msg, vbYesNo, "Possible Duplicate")
If answer = vbNo Then Cancel = True
End If
End Sub
PaulBrand
Fly code here but it should get you close.
Private Sub LastName_BeforeUpdate(Cancel As Integer)
Dim answer As String
Dim msg As String
Dim userinput As String
userinput = Me.txtLastName ' Referring to the control name rather than field here so make sure it's the name
'of the text box
msg = "This is a possible duplicate. Do you want to continue?"
If DCount("LastName", "Contacts","LastName = " & userinput) > 1 Then
answer = MsgBox(msg, vbYesNo, "Possible Duplicate")
If answer = vbNo Then
me.Undo
Exit Sub
End If
End Sub
wink.gif
Jack Cowley
Try this line of code:
CODE
If Not IsNull(DLookup("[LastName]", "Contacts", "[LastName] = '" & Me.NameOfControlWithUserInput & "'")) Then

hth,
Jack
kgeil
So, I've tried both of the lines of code below, and the best response I get from access is an error: method or data member not found
If DCount("LastName", "Contacts","LastName = " & userinput) > 1 Then
If Not IsNull(DLookup("[LastName]", "Contacts", "[LastName] = '" & Me.LastName & "'")) Then
Jack Cowley
Replace the code in red with the actual name of the control on your form that has the last name:

If Not IsNull(DLookup("[LastName]", "Contacts", "[LastName] = '" & Me. LastName & "'")) Then

Edit - Also, read this article

hth,
Jack

Edited by: Jack Cowley on Wed May 17 10:44:04 EDT 2006.
kgeil
On right click/properties, name and control source are both LastName; is this what you refer to?
lso, what is DCount? is it specific to VBA? SQL? I've found it very hard to find information on it.
Thanks,
Kevin
dashiellx2000
Frmo Access Help:
You can use the DCount function to determine the number of records that are in a specified set of records (a domain). Use the DCount function in Visual Basic, a macro, a query expression, or a calculated control.
For example, you could use the DCount function in a module to return the number of records in an Orders table that correspond to orders placed on a particular date.
Syntax
DCount(expr, domain[, criteria])
The DCount function has the following arguments.
Argument Description
expr An expression that identifies the field for which you want to count records. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.
domain A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name.
criteria An optional string expression used to restrict the range of data on which the DCount function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DCount function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise the DCount function returns a Null.
Remarks
Use the DCount function to count the number of records in a domain when you don't need to know their particular values. Although the expr argument can perform a calculation on a field, the DCount function simply tallies the number of records. The value of any calculation performed by expr is unavailable.
Whether you use the DCount function in a macro or module, a query expression, or a calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly.
Use the DCount function in a calculated control when you need to specify criteria to restrict the range of data on which the function is performed. For example, to display the number of orders to be shipped to California, set the ControlSource property of a text box to the following expression:
=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")
If you simply want to count all records in domain without specifying any restrictions, use the Count function.
Tip The Count function has been optimized to speed counting of records in queries. Use the Count function in a query expression instead of the DCount function, and set optional criteria to enforce any restrictions on the results. Use the DCount function when you must count records in a domain from within a code module or macro, or in a calculated control.
You can use the DCount function to count the number of records containing a particular field that isn't in the record source on which your form or report is based. For example, you could display the number of orders in the Orders table in a calculated control on a form based on the Products table.
The DCount function doesn't count records that contain Null values in the field referenced by expr, unless expr is the asterisk (*) wildcard character. If you use an asterisk, the DCount function calculates the total number of records, including those that contain Null fields. The following example calculates the number of records in an Orders table.
intX = DCount("*", "Orders")
If domain is a table with a primary key, you can also count the total number of records by setting expr to the primary key field, since there will never be a Null in the primary key field.
If expr identifies multiple fields, separate the field names with a concatenation operator, either an ampersand (&) or the addition operator (+). If you use an ampersand to separate the fields, the DCount function returns the number of records containing data in any of the listed fields. If you use the addition operator, the DCount function returns only the number of records containing data in all of the listed fields. The following example demonstrates the effects of each operator when used with a field that contains data in all records (ShipName) and a field that contains no data (ShipRegion).
intW = DCount("[ShipName]", "Orders") ' Returns 831.
intX = DCount("[ShipRegion]", "Orders") ' Returns 323.
intY = DCount("[ShipName] + [ShipRegion]", "Orders") ' Returns 323.
intZ = DCount("[ShipName] & [ShipRegion]", "Orders") ' Returns 831.
Note The ampersand is the preferred operator for performing string concatenation. You should avoid using the addition operator for anything other than numeric addition, unless you specifically wish to propagate Nulls through an expression.
Unsaved changes to records in domain aren't included when you use this function. If you want the DCount function to be based on the changed values, you must first save the changes by clicking Save Record on the File menu, moving the focus to another record, or by using the Update method.
Jack Cowley
DCount is a domain aggregate function in VBA. Search Access help for DCount() for more details, but it has the very same syntax as DLook() as described in the article I suggested you read.
The name of the control is found in the Name property under the Other tab of the controls property sheet, as you discovered. The Name property is the one you want to use in your DLookup code. You can type in Smith in this control, but a Stop in your code and by placing the cursor over Me.LastName you should see "Smith".
If you get the error that the method or data member not found then that item should be highlighted when you compile your code. Do a Compile and Access will show you the bad line or bit of code....
hth,
Jack
kgeil
Awesome, thank you very much. It will be a long time before I can truly admit the amount of time I spent with this little bit of code. Thanks again,
evin
Jack Cowley
Kevin -
I'm glad to hear you have solved your problem and I am glad we were able to help.
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.