Access 2013 - When saving a Record, check for duplications when you are modifying a Record.
Posted on 2015-01-22
I have created an Access 2013 Database that we will use to inventory our I.T. Equipment such as Hardware and Software.
In a "Lookup Table" entitled, "Software_Titles", I have three fields, two of which cannot have duplications:
SOFTWARE_TITLE = Primary Key. No Duplications
SOFTWARE_LICENSE_KEY = No Duplications
SOFTWARE_LICENSES_AVAILABLE = Duplications are Ok.
On the form entitled, "Software_Titles" the fields that correspond to the data fields in the "Software_Titles" Table are as follows:
txtInputSoftwareTitle = SOFTWARE_TITLE
txtInputSoftwareLicenseKey = SOFTWARE_LICENSE_KEY
txtInputSoftwareLicensesAvailable = SOFTWARE_LICENSES_AVAILABLE
When I add a new Record, I use the following "DCOUNT Code" to ensure that I am not adding any duplications. As an example, this is what I use for the SOFTWARE_TITLE:
Case DCount("[SOFTWARE_TITLE]", "Software_Titles", "[SOFTWARE_TITLE]= '" & Me![txtInputSoftwareTitle] & "'") > 0
This code works brilliantly when I am adding a new Record. However, I also provide the opportunity to modify an existing Record in the same "Software_Titles" Form, just-in-case someone discovers that they spelled Access with only one "C" or something. I do this through a "Modify Record" Command Button and a separate "Save Record" Command Button. I have no issues with accessing a Record for modification or saving it when the modification basically results in a new Record. I did discover though through testing that when modifying an existing Record, it was possible to create a duplicate Record by modifying, let's say the txtInputSoftwareTitle Field, into a name that already existed. I thought that I would just simply use the same "DCOUNT Code" to check for that issue that I use when adding a new Record, especially since all of the fields are the same as well as the table and everything else. However, the code does not work exactly as expected because it always checks as "Yes". This is because technically, you will always have an existing Record, whether you are modifying a Record into one that already exists or simply modifying your existing Record from say all lower-case such as "access" to proper case such as "Access". I thought that if I would modify the last part of the "DCOUNT Code" to = 2 then that would solve the issue as if through the modification of a Record, if I was actually duplicating the Record, then the count of the SOFTWARE_TITLE would be "2" where as if I was simply modifying the current Record to a different style such as from all lower-case to proper case, then the check should be "1".
Somehow it just doesn't work. I ran into several errors both in the table and through Access. I then decided to boil everything down to just a check on the "DCOUNT Code" and I modified it to where if it counts the SOFTWARE_TITLE as "2" then put "Yes" in a Text Field or put "No" in the same text field. If I modify txtInputSoftwareTitle from Tester 02 to Tester 01 of which already exists, that count should be "2" and therefore, should put a "Yes" in the Text Box. It puts a "No", which means for whatever reason, the "DCOUNT Code", which works just fine when adding a new Record does not work when modifying one. This is my current test code:
If DCount("[SOFTWARE_TITLE]", "Software_Titles", "[SOFTWARE_TITLE]= '" & Me![txtInputSoftwareTitle] & "'") = 2 Then
txtCount.Value = "Yes"
txtCount.Value = "No"
What am I missing here? Why does the "DCOUNT Code" not work correctly when I am modifying a Record? I am exhausted trying to solve this. Please help.