Text Field - Can I add a Case Sensitive index to this?

[Cosmos75]

I have a text field that I want to index to assure that there are no duplicates. The problem I am having is that it is not case sensitive, i.e. "ABC" is treated the same way as "abc". Is there a way to have a case sensitive index or its equivalent?

 

[Jack Cowley]

Instead of indexing the field I would use code in the controls Before Update event to validate the entry. This way your code will stop if "ABC" = "abc". Also, you can pop up a nice message instead of the message Access gives you....

hth,
Jack

 

[Cosmos75]

Jack,

Thanks for the reply. I have code that does exactly what you say but was wondering if there was a way to absolutely be sure that that a record is not entered in the table by ANY means.

Kinda of like insuring that at the table design level that there will be no two identical Social Security Numbers. Wanted to see if there was a way to prevent it from happening at the design level rather than the coding level.

I have this piece of code to check if a record exists of not

CountRecord = DCount("Field1", "tblRecords", "StrComp([Field1]," & Chr(39) & me.txtField1 & Chr(39) & ",0) = 0")

If CountRecord > 0 then
'Record Exists
Else
'Record Does Not Exist
End If

 

[Jack Cowley]

Your users should not have access to the tables if there is a chance they will enter data directly. Hide the table(s) or use Access security to limit access to your table(s) and this should insure that the data is entered through your form(s).

Jack