Dcount - Criteria

[Sangeetha]

Hi,

I have a form, which has a certificate number and company ID. I want to
disallow the user to enter duplicate certificate number in the same company
ID. In other words, the certificate number can be the same , for different
company ID.

I made the following macro, and linked it to the after update event of teh
field [certno]. It is not working.

DCount("[certno]","[01_certificate_validate]","[certno]=form.[01_shareholder_sf]")>1

where, [certno] is the fielsd,w hich I need to validate
[01_certificate_validate] is the query, which shows the certificate number
for the current company ID
[01_shareolder_sf] is the form, whcih is currntly loaded , where certno
field exists

please help.

 

[John Spencer]

Try this syntax. You must specify the control on the form and you must specify
the forms collection.

[Forms]![Name of Form]![Name of Control]

DCount("*","[01_certificate_validate]","[certno]=Forms![01_shareholder_sf]![Name
Of Control With CertNo]")>1

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Sangeetha]

Thanks ! I am new to access programming. Please let me know, where to place
the control on the form..

Try this syntax. You must specify the control on the form and you must specify
the forms collection.

[Forms]![Name of Form]![Name of Control]

DCount("*","[01_certificate_validate]","[certno]=Forms![01_shareholder_sf]![Name
Of Control With CertNo]")>1

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I have a form, which has a certificate number and company ID. I want to
disallow the user to enter duplicate certificate number in the same company
ID. In other words, the certificate number can be the same , for different
company ID.

I made the following macro, and linked it to the after update event of teh
field [certno]. It is not working.

DCount("[certno]","[01_certificate_validate]","[certno]=form.[01_shareholder_sf]")>1

where, [certno] is the fielsd,w hich I need to validate
[01_certificate_validate] is the query, which shows the certificate number
for the current company ID
[01_shareolder_sf] is the form, whcih is currntly loaded , where certno
field exists

please help.

 

[John Spencer]

You said you already had a form with a certificate number.

I don't know where you are using the expression you posted.

Also, since the same certificate number can be used by more than one company
ID you would need to add that to the test.

DCount("*","[01_certificate_validate]","[certno]=Forms![01_shareholder_sf]![Name
Of Control With CertNo] AND CompanyID = Forms![01_ShareHolder_SF]![Name of
CompanyID Control]")>1

Now to make this even more complex, I am guessing that you are attempting to
do this using a subform. If so, the syntax has to change to refer to the
subform controls.

The above expression (if it works) is going to return True or False. That
implies that you are intending to do something based on the test, what that is
I have no idea.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks ! I am new to access programming. Please let me know, where to place
the control on the form..

Try this syntax. You must specify the control on the form and you must specify
the forms collection.

[Forms]![Name of Form]![Name of Control]

DCount("*","[01_certificate_validate]","[certno]=Forms![01_shareholder_sf]![Name
Of Control With CertNo]")>1

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I have a form, which has a certificate number and company ID. I want to
disallow the user to enter duplicate certificate number in the same company
ID. In other words, the certificate number can be the same , for different
company ID.

I made the following macro, and linked it to the after update event of teh
field [certno]. It is not working.

DCount("[certno]","[01_certificate_validate]","[certno]=form.[01_shareholder_sf]")>1

where, [certno] is the fielsd,w hich I need to validate
[01_certificate_validate] is the query, which shows the certificate number
for the current company ID
[01_shareolder_sf] is the form, whcih is currntly loaded , where certno
field exists

please help.