I just updated a field in a table using the Index Design function. I want a field to be unique and to treat upper and lower case entries the same. Therefore if I already have an entry of ABC123 I do not want to be able to enter abc123.
Using the the standard method that i have mentioned above I am able to create duplicates if the case is different.
How can i fix this. Preferably all entered text would be saved as uppercase automatically for aesthetic reasons as well.
thanks
Hello,
To do this you need to check if an item is already present before adding it. This needs to be done through a macro. A sample can be found here → Add entries to listbox or combobox. This sample refers to a combo box but the concept applies to most situations. To this you add to the SQL UPPER or LOWER to the data being checked so as to compare regardless of case. Example:
Where UPPER(MYFIELD) = UPPER(COMPARED TO DATA)
Edit 2018-04-09:
Yes, the answer specifies: This sample refers to a combo box but the concept applies to most situations. You should be able to use this concept and apply it to your situation.
With a macro executed before the record is updated (this is an event) obtain the part number entry. Then use that in the SQL to see if it is already present in the table. Now if it is, cancel the update. If it isn’t proceed with the update. You can eliminate the creation of the new entry if not needed.
Also, if you want something to be in upper case convert it:
'Obtain entered data before record is written'
myVariable = EnteredData
'Change to upper case'
newVariable = Ucase(myVariable)
'Put data back'
EnteredData = newVariable
'Write the record'
Then your entered info is written as upper case. You would need to do this wherever you write new records.
There is no way to place anything so it does it ...no matter which form is used to enter it. Each form must call a macro when needed.
Excellent, thanks. I have looked at the example post but it seems specific to a combobox entry and also creates a new entry if the name does not already exist. What I was hoping for is a solution attached to the PartNumber field of my PartNumber table that would not allow a duplication no matter which form is used to enter it. To simplify things is it possible to force all entries in a particular field to be upper case? The form I am referring to is ECNListByCustomer. Also uploaded example.
Please see edit in answer.
I liked the idea of using a combobox to check if an entry already exists and if not then create it. I have been trying to apply this to one of my forms but I get errors coming from the macro I already have in the form. May be to do with the same form having several macros and refering to the same form as Form1, Form2 etc in different macros. I will post a new question linked to this.
So i struggled a little with the above suggestion, however it was useful as it lead me to search for examples of the use of UCase. After trying a few variations the simplest one for me is this one.
sub CAPS( oEv as variant )
oEv.Source.Text = UCase( oEv.Source.Text )
exit sub
end sub
I can simply apply the same macro to any text field I want to convert.
The main reason being to try and get the unique index working. Previously it was allowing the same names to be entered if the they had just one letter in a different case.
Fingers crossed this will fix it.
Unfortunately I could not find a way to use the above macro when adding data through a table control so not much further forward. Works fine for a normal text field though.