Access Form Conditional Formatting Expression Needed

T

TGV

Hi All,

What is the expression should be used in conditional formatting (Access
Form) for displaying the Duplicate entry in a Field.

Please Advice

TGV
 
T

TGV

Hi Ken,

Thanks for sending me a brief note about using conditional formatting.

I have tried all the methods mentioned below but the same is not working for
me. I am just a beginner of Access and I don't know much more about it.

I am giving below my access file detail for you, so based on that please
explain me the steps

Actually my Table consist User Id, User Name, like that 12 fields. The
primary key is set for User Id. The user id is a text data type only.

The data entry person will do entry in the access form. Sometimes the Same
details (that is User Id, User Name and 12 fields) will be received in the
second time for doing entry. In that case the person will feed all the 14
fields and give tab, in that time access displays that the value u have
entered is trying to create duplicate entries because of Primary Key. In the
end of the entry only i will get this message and due to this my 14 field
second time entry will be the loss of time. So here i want to use the
conditional formatting for User Id so whenever i type the User Id as second
time in that instant itself it should show some change in colour or back
ground for duplicate entry in User Id.

So please guide me with step by step method.

And in your below message u have mentioned "Your Id" i dont know what is
mean by "Your Id". Please clarify.

Once again thanks for your support.

TGV

KenSheridan via AccessMonster.com said:
If by 'duplicate entry' you mean that there is at least one other row in the
table with the same value in the same column (YourField in the example below,
which also assumes it is a text data type) then, providing the table has a
primary key or otherwise uniquely indexed column (YourID in the example below)
try the following:

Not IsNull(DLookUp("[YourID]","[YourTable]","[YourField] = """ & [YourField]
& """ And [YourID]<>" & [YourID]))

Without a key or otherwise uniquely indexed column the following should work
but would, I think, be slower:

DCount("*","[YourTable]","[YourField]=""" & [YourField] & """")>1

Domain methods like DLookup and DCount are notoriously slow, however, so the
response time as you navigate through the form is likely to be perceivable in
either case. You might find it faster to base the form on a query which
returns TRUE or FALSE in a computed column, e.g.

SELECT [YourTable].*,
DLookUp("[YourID]","[YourTable]","[YourField] = """ & [YourField]
& """ And [YourID]<>" & [YourID]) IS NOT NULL AS IsDup
FROM [YourTable];

or:

SELECT [YourTable].*, DCount("*","[YourTable]","[YourField]="""
& [YourField] & """")>1 AS IsDup
FROM [YourTable];

The conditional formatting expression would then simply be:

[IsDup]

Ken Sheridan
Stafford, England
Hi All,

What is the expression should be used in conditional formatting (Access
Form) for displaying the Duplicate entry in a Field.

Please Advice

TGV
 
T

TGV

Thank you very much kensheridan!!! the below mentioned code is working
nicely.

strCriteria = "[User Id] = """ & [User Id] & """"

If Not IsNull(DLookUp("[User Id]","[YourTable]",strCriteria)) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

Once again Thank you,

TGV

KenSheridan via AccessMonster.com said:
The methods I outlined won't work in that case as there never can be
duplicate User ID values, do using the DCount or DLookup method to find them
is a non-starter. You could try simply looking for an existing row with the
key value as that being entered:

Not IsNull(DLookUp("[User Id]","[YourTable]","[User Id] = """ & [User Id] &
""""))

Or:

DCount("*","[YourTable]","[User Id]=""" & [User Id] & """")>0

I'm not sure, however, whether the conditional formatting would kick in
immediately or not; its not something I've never seen tried.

A more usual approach in these situations would to use the User ID control's
BeforeUpdate event procedure to validate the entry. This has a Cancel
argument whose return value can be set to True in the event of the value
already existing in the table:

Const conMESSAGE = "This user already exists in the table."
Dim strCriteria As String

strCriteria = "[User Id] = """ & [User Id] & """"

If Not IsNull(DLookUp("[User Id]","[YourTable]",strCriteria)) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If

This will execute immediately on the User ID value being entered. The only
caveat here is that if, in a multi-user environment two or more users enter
the same User ID simultaneously in a new row, this will not work as the row
won't yet have been committed to the table. The first user to save the
record would succeed, but the other(s) would incur an error when trying to do
so. To cater for such a scenario you'd have to adopt a more complex solution,
such as storing all user User ID values in a separate table as its key, and
attempting to insert a new row into this table with DAO or ADO code in the
User ID control's BeforeUpdate event procedure. You could then trap the
error if the key of the other table is violated when an attempt to insert a
row is made and set the return value of the BeforeInsert event procedure's
cancel argument to True. Even this is not entirely satisfactory, however, as
a user could start to insert a row, entering a User ID, in which case another
user entering the same value would be prevented from doing so, but the first
user could then abort the new record before saving it.

Ken Sheridan
Stafford, England
Hi Ken,

Thanks for sending me a brief note about using conditional formatting.

I have tried all the methods mentioned below but the same is not working for
me. I am just a beginner of Access and I don't know much more about it.

I am giving below my access file detail for you, so based on that please
explain me the steps

Actually my Table consist User Id, User Name, like that 12 fields. The
primary key is set for User Id. The user id is a text data type only.

The data entry person will do entry in the access form. Sometimes the Same
details (that is User Id, User Name and 12 fields) will be received in the
second time for doing entry. In that case the person will feed all the 14
fields and give tab, in that time access displays that the value u have
entered is trying to create duplicate entries because of Primary Key. In the
end of the entry only i will get this message and due to this my 14 field
second time entry will be the loss of time. So here i want to use the
conditional formatting for User Id so whenever i type the User Id as second
time in that instant itself it should show some change in colour or back
ground for duplicate entry in User Id.

So please guide me with step by step method.

And in your below message u have mentioned "Your Id" i dont know what is
mean by "Your Id". Please clarify.

Once again thanks for your support.

TGV
If by 'duplicate entry' you mean that there is at least one other row in the
table with the same value in the same column (YourField in the example below,
[quoted text clipped - 41 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top