Solved

Access 2013 - When saving a Record, check for duplications when you are modifying a Record.

Posted on 2015-01-22
15
184 Views
Last Modified: 2015-04-26
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"
 
Else

 txtCount.Value = "No"
 
End If


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.

Xailon Suroga
0
Comment
Question by:Xailon
  • 6
  • 5
  • 4
15 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
the count will be 2, only if you have
two records with the same "[SOFTWARE_TITLE]"

in what event are you doing the test for DCount?
try using the your test AFTER the MODIFIED record was saved.
0
 

Author Comment

by:Xailon
Comment Utility
Hello Rey,

That's exactly what I'm checking against.  The idea is that if I am modifying a Record, in this case the SOFTWARE_TITLE, and the check is "2" or "Yes", that everything stops, a message box pops up and the User is informed that they are attempting to add a duplicate Record.  Once "OK" is selected, then the rest of the code I have resets the Form as need be.  That all works just fine when I am saving a new Record but that check is simply >0, which if I am adding a new Record, it should be new.  When I am adding a new Record, if there is anything already existing, which would be >0, then there is an issue.

However, when modifying a Record, >0 will always be a "Yes" because that Record will always exist unless you change it to a completely different Record.  As an example, if you change the SOFTWARE_TITLE from "access" to "Access" and run the >0 check, it will come up as "Yes".  That is to be expected and that check works in my "TEST DCOUNT Code" or in the original "DCOUNT Code".  What I want to check is for a duplication that may occur via the modification of a Record.  As an example, "Access" already exists as a SOFTWARE_TITLE but for some reason I don't know this and I think that I need to simply modify the Record "Tester 01" to the SOFTWARE_TITLE of "Access" and everything will be OK.  If that occurs, the "Test DCOUNT Code" should detect that as a "2" or as a "Yes" and stop the process of saving a Record.  It ALWAYS detects it as something else, and therefore my "Count Text Box" always shows "No", which indicates that the "DCOUNT Code" is not working as desired.  However, Microsoft Access discovers real quick that I am attempting to save a duplicate Record and tries to fix the issue.  That though gets quite ugly.  I do not want Microsoft Access handling those issues.

Both the DCOUNT code for when I save a new Record and when I modify one runs when I click on the "Save Record" Command Button that is specifically for each action.  In other words, I have a "Save Record" Command Button for new Records and a "Save Record" Command Button for the modification of Records.  Everything works just fine for the "Save Record" Command Button that I use when I save a new Record.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
try using the before update event of  textbox "txtInputSoftwareTitle" to test for the existence of record.. use for testing

private sub txtInputSoftwareTitle_beforeUpdate(Cancel as integer)
If DCount("[SOFTWARE_TITLE]", "Software_Titles", "[SOFTWARE_TITLE]= '" & Me![txtInputSoftwareTitle] & "'") > 0  then

 msgbox "Title already exists!"
 Cancel=true
 me.txtInputSoftwareTitle.setfocus
exit sub
end if
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you can also try using the FORM's Before Update event.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I would use the BeforeUpdate event of the control.  You have to alter the test based on whether this is an add or an update.  And in the case of an update, you want to know if the title actually changed.

Dim RecCount
RecCount = DCount("[SOFTWARE_TITLE]", "Software_Titles", "[SOFTWARE_TITLE]= '" & Me![txtInputSoftwareTitle] & "'") 
If Me.NewRecord  OR Me.txtInputSoftwareTitle & "" <> Me.txtInputSoftwareTitle.OldValue & "" Then  'new record or title changed
    If RecCount <> 0 Then
        Msgbox "This Software Title already exists.  Please enter a unique value.",vbokOnly
        Cancel = True
        Exit Sub
    End if
End If

Open in new window


You should also have the two unique columns defined as unique and required in the table definition.  The reason for doing this validation is simply to give the user a more customized error message.

You could do the validation in the BeforeUpdate event of the form but I prefer to do this particular validation at the control level.  In the BeforeUpdate event of the form, you should check each of the required fields to ensure they are not empty.  If a user never dirtied the two controls, the control level event wouldn't fire so you have to catch the empties just before the record is saved.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
.



I think I already mentioned using the beforeUpdate event of the textbox  "txtInputSoftwareTitle"



.
0
 

Author Comment

by:Xailon
Comment Utility
I've tried the suggested code and that didn't work.  I've tried using the Forms Before and After Update and that didn't work.  What I can tell is that the "DCOUNT Code" is not including the value that is in the Text Box when it does its count and only uses it as a value to check what's already in the Table.  This is why the "DCOUNT Code" works when you ask it to check >0 or =1 but not >1 or =2.  If there is some way to fix that issue, then the following code as an example would work just fine when checking if you are modifying a current Record's "Software Title" into one that already exists, understanding that at least itself should already exists so that the check will not produce a "Yes" if it only counts itself:

DCount("[SOFTWARE_TITLE]", "Software_Titles", "[SOFTWARE_TITLE]= '" & Me![txtInputSoftwareTitle] & "'") > 1 And DCount("[SOFTWARE_LICENSE_KEY]", "Software_Titles", "[SOFTWARE_LICENSE_KEY]= '" & Me![txtInputSoftwareLicenseKey] & "'") = 1
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
i don't understand why you want to show a count of >1 or =2.
if the record already exists >0 will tell you that it is YES.
0
 

Author Comment

by:Xailon
Comment Utility
Hello Rey,

You are absolutely correct and when I think about it more, the code I'm trying to use may not actually be the correct one, regardless, because I am modifying an existing Record.  I think that the code that I am trying to work will only work on a new Record, which is why when I check for >1 or =2, it simply does not work.  It's bad circular logic.  As a result, let me explain what I am trying to accomplish in another way.  

If I were to tell Access verbally what I needed, like say if I were talking to a computer from "Star Trek", I would say "When I click on the 'cmdSaveModify' Command Button, I want you to do the following things:  

First, check to see if there are any blank values in the 'txtInputSoftwareTitle', the 'txtInputSoftwareLicenseKey' and the 'txtSoftwareLicensesAvailable' Text Boxes.  If there are blank values in any of those Text Boxes, stop everything and do not save the Record.  Then notify the User via an "OK Only" Message Box that they cannot save the Record if any of the fields have blank values.  When they click on the "OK" Button within the message, they are sent back to the form where the focus is on the 'txtInputSoftwareTitle' Text Box.

If the previous checks is OK, then check if no changes were made in the 'txtInputSoftwareTitle' and the 'txtInputSoftwareLicenseKey' Text Boxes.  If there were no changes made to any of those Text Boxes, then just save the Record.

If there were changes made in the 'txtInputSoftwareTitle' and the 'txtInputSoftwareLicenseKey' Text Boxes, then check if those changes match what was already in those Text Boxes before any changes were made to any of them.  If they do, then just save the Record.

If changes were made in the 'txtInputSoftwareTitle' and the 'txtInputSoftwareLicenseKey' Text Boxes and those changes do NOT match what was already in any of those Text Boxes before any changes were made to them, then check if those new values matches anything in the 'Software_Titles' Table.  If they do NOT, then just save the Record.

However, if those values do match values within the 'Software_Titles' Table, then send an "OK Only" Message Box to the User notifying them that the changes made results in a Duplicate Record.  Ensure that if the check for a duplication is only for the 'Software Title' or the 'Software License Key' that the message produced is customizable to that fact and then send the User back to the Form and to the Text Box where the duplication is taking place after the User clicks on the "OK Button" within the Message Box .  If the duplication is taking place on both of the Text Boxes being checked, then send an "OK Only" Message Box letting the User know that both of those Text Boxes will result in a Duplicate Record and then send the User back to the Form focusing on the 'txtInputSoftwareTitle' Text Box".

Now, since I can't verbally tell Access to do any of that, would you happen to know of the Access 2013 Code that will?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
What I can tell is that the "DCOUNT Code" is not including the value that is in the Text Box when it does its count and only uses it as a value to check what's already in the Table.
If the record has not been saved, then the value in the current record will not be included.  You don't want the current record to be saved!!!!!  It makes no sense to validate a record AFTER it has been saved.  The code I gave you takes that into consideration.  My code also shows you how to compare the "new" value to the .OldValue property.

When you say that code "doesn't work", that is meaningless.  You really need to specify what you were expecting to happen that didn't AND you need to post the code you actually used since the code we post for you is only a suggestion.

As I said earlier, I like to check for duplication in the BeforeUpdate event of the control.  That way the user gets the message immediately.  However, it is not possible to check for empty controls in the control level events.  You MUST do that at the Form level.  So in the FORM's BeforeUpdate event, you need something like the following for each field that is required.  As I also mentioned earlier, best practice is to set these fields as required in the table definition and to add a unique index for each.  That will allow the database engine to do its job to protect your data and it will not allow the record to be saved if the field is empty or contains a duplicate.  The ONLY reason you are validating in the form is to give the user a better experience with custom error messages.

If Me.somefield & "" = "" Then
    Me.somefield.SetFocus
    Msgbox "some error message.",vbokOnly
    Cancel = True
    Exit Sub
End If

By concatenating a ZLS to the field, I can check for both null and Zero Length String in the same If.  Another way to do it is to use the Len() function -- If Len(Me.somefield) = 0 Then
0
 

Author Comment

by:Xailon
Comment Utility
I figured it out.  

THERE ARE FOUR KEYS TO THIS ISSUE.

The first key is that you can't leverage the "Finalized Code" in anything but a separate event like what I would recommend being a "Save Record" Command Button specifically for when you modify a Record that only appears when you are in a "Modify Record" mode.

The second key is to have the original values of the Text Boxes that you are checking for duplication saved somewhere before a change is made to them so that you can leverage that data to compare to the "modifications" made in the Text Boxes, or in other words, the new value that you have entered into those Text Boxes.  Once you have those original values, then the "DCOUNT Code" that worked just fine when only checking for a duplication of a Record when a new Record was being created will work.

The third key is to understand that the "original data" in those Text Boxes will be viewed as "Old Value" in Access.  In other words, Access will see it as txtInputTextBox.OldValue.

The forth key is to understand that you are capturing the "Old Value" the moment that the Text Boxes have a change.  This would mean that you have to activate the saving of the "Old Value" via a "Change Command" in those Text Boxes.


These are the steps to putting that all together:

IN REVIEW 1:  I am checking whether or not through a modification of a Record if the modifications of the Record via one of or both of the Text Boxes I want to check results in a duplication of the data in the Table that the Text Boxes and the Form the Text Boxes are on are associated with.

IN REVIEW 2:  The Table is called Software_Titles.  The two Text Boxes I am checking for duplications are entitled,   txtInputSoftwareTitle and txtInputSoftwareLicenseKey.


STEPS FOR SUCCESS:

First Step:  Create two Text Boxes on the Form.  Title them something that you can tell later which is storing which Text Boxes' "Old Value".  The two I created I titled, txtSoftwareTitleOldValue and txtSoftareLicenseKeyOldValue.  If you are concerned with the aesthetics of your Form, you can hide these Text Boxes and make them any size that you want.  That doesn't affect the code.

Second Step:  Create a Code in the "On Change" Event in EACH of the Text Boxes that you want to capture the "Old Value" and transfer it to the Text Boxes that will hold that value.  The key here is to put in the same code for each Text Boxes' "On Change" Event.  It seems strange that you would need code for a change of the value of a Text Box that hasn't necessarily changed into a Text Box that's value has changed but it seems to have issues if you don't.  Hey, you can only fight Access so far.  In each Text Boxes' "Private Sub ........_Change()" Event I put in the following code:

 txtSoftwareTitleOldValue.Value = txtInputSoftwareTitle.OldValue
 txtSoftwareLicenseKeyOldValue.Value = txtInputSoftwareLicenseKey.OldValue

Third Step:  You start the comparisons with the testing of whether or not there was a change in the Text Boxes you are testing for duplication.  These are the checks:

 ** If there were no changes, then just save the Record.  

 ** If there were changes in either Text Box, check if those changes were the same as the "Old Value".  This covers whether you just wanted to change say the "Software Title" from TESTER 01 to tester 01.  In that case, there wouldn't be a change that would produce a duplication (provided that you code for Case Sensitivity, which I will explain later.), If those changes are the same as the "Old Value", then just save the Record.

 ** If their were changes in either Text Box that does not equal what was in the "Old Value", then check if the new value equals anything in the Table.  If it does not, then just save the Record.  This covers the changing of the Record's "Software Title" from Tester 01 to Test 01 and / or the "Software License Key" from ABC1234 to ABCD1234 where either or both of the changes should result in a new Record and therefore, should be saved.  

** If however there was a change in any of the Text Boxes that you are checking for duplication, the changes do not equal what the "Old Value" was and when you check if the new value is anything that already exists in the Table and that check equals "Yes", THEN you have a duplication and you need to stop the saving of the record.

***SPECIAL NOTE ON THE FIRST CHECK*** The code will NOT work if you do not leverage the "UCase" function.  Otherwise, "TESTER 01" will come across as not the same data if you change it to "tester 01" and therefore will be viewed as a duplication.


Now, before I present the Code, I was also checking for "blank data" so if that's a concern as well, then that is in this code.

HERE IS THE CODE THAT PUTS EVERYTHING ALL TOGETHER OF WHICH I USE WHEN CLICKING ON A "SAVE RECORD" COMMAND BUTTON THAT I USE SPECIFICALLY FOR THE SAVING OF THE MODIFICATION OF A RECORD.

Private Sub cmdSaveModifyRecord_Click()

'Set variables.

  Dim nReturn As Integer
 
'Set action for possible error.

  On Error GoTo Err_cmdSaveModifyRecord_Click
 
'Turn off Microsoft Warnings.

  DoCmd.Hourglass True
 
'Turn off Microsoft Access Warning Messages.

  DoCmd.SetWarnings False
  DoCmd.Hourglass False
 
 
'Case Statements that key off that you are in "Modify Record Mode".

Select Case lblModifyRecordMode.Visible = True

'The "IsNull" parts are to ensure that a Record cannot be saved if any of the three fields are left blank.

 Case IsNull(txtInputSoftwareTitle.Value) = True
   
  nReturn = MsgBox("The 'Software Title' Field cannot be left blank when saving a Record.", vbOKOnly, "BLANK 'SOFTWARE TITLE' FIELD DETECTED")
   
  txtInputSoftwareTitle.SetFocus
 
 Case IsNull(txtInputSoftwareLicenseKey.Value) = True
 
  nReturn = MsgBox("The 'Software License Key' Field cannot be left blank when saving a Record.", vbOKOnly, "BLANK 'SOFTWARE LICENSE KEY' FIELD DETECTED")
 
  txtInputSoftwareLicenseKey.SetFocus
 
 Case IsNull(txtInputSoftwareLicensesAvailable.Value) = True
 
  nReturn = MsgBox("The 'Software Licenses Available' Field cannot be left blank when saving a Record.", vbOKOnly, "BLANK 'SOFTWARE LICENSES AVAILABLE' FIELD DETECTED")
 
  txtInputSoftwareLicensesAvailable.SetFocus

'It was discovered that if the Form is not reset after any of the fields are checked for blanks via the "IsNull" Code then you could save any of the fields with a blank value.  It seems as if the "IsNull" check only works the first time the fields are
'checked and the first time that the Form is opened.  You need to constantly check for blanks on all fields at any point when the Form is in use.  This code addresses that issue.
 
 Case txtInputSoftwareTitle.Value = "" Or txtInputSoftwareLicenseKey.Value = "" Or txtInputSoftwareLicensesAvailable.Value = ""
 
 nReturn = MsgBox("At least one of the fields in the Record does not have data entered.  You must have all fields entered with data in order to save a Record.", vbOKOnly, "NO DATA DETECTED")
 
 txtInputSoftwareTitle.SetFocus
 
 
'Check if the changes made in the "txtInputSoftwareTitle" and the "txtInputSoftwareLicenseKey" Text Boxes equals what was originally in those Text Boxes before any changes were made.  If the
'check equals, "No", then check if what is entered in those Text Boxes duplicates anything that is already in the "Software_Licenses" Table.  If the check equals, "Yes", then stop everything
'and let the User know via an "OK Only" Message Box that they have entered in duplicate data in both of those Text Boxes.  Then set the focus back to the "txtInputSoftwareTitle" Text Box.
 
 Case UCase(txtInputSoftwareTitle.Value) <> UCase(txtSoftwareTitleOldValue.Value) And UCase(txtInputSoftwareLicenseKey.Value) <> UCase(txtSoftwareLicenseKeyOldValue.Value)
 
  If DCount("[SOFTWARE_TITLE]", "Software_Titles", "[SOFTWARE_TITLE]= '" & Me![txtInputSoftwareTitle] & "'") > 0 And DCount("[SOFTWARE_LICENSE_KEY]", "Software_Titles", "[SOFTWARE_LICENSE_KEY]= '" & Me![txtInputSoftwareTitle] & "'") > 0 Then
 
  nReturn = MsgBox("You have entered a 'Software Title' and a 'Software License Key' that already exists in this Database.  Please enter accurate and unique data for both fields", vbOKOnly, "DUPLICATE FIELDS DETECTED")
 
  txtInputSoftwareTitle.SetFocus
 
  End If
 
'Check if the changes were only made in the "txtInputSoftwareTitle" Text Box and if those changes equals what was originally in that Text Box before any changes were made.  If the check equals, "No", then check if
'what is entered in that Text Box duplicates anything that is already in the "Software_Licenses" Table.  If they check equals, "Yes", then stop everything and let the User know via an
'"OK Only" Message Box that the "Software Title" they have entered is a Duplicate Record.  Then set the focus back to the "txtInputSoftwareTitle" Text Box.

 
  Case UCase(txtInputSoftwareTitle.Value) <> UCase(txtSoftwareTitleOldValue.Value)
 
   If DCount("[SOFTWARE_TITLE]", "Software_Titles", "[SOFTWARE_TITLE]= '" & Me![txtInputSoftwareTitle] & "'") > 0 Then
 
   nReturn = MsgBox("You have entered a 'Software Title' that already exists in this Database.  Please enter an accurate and unique 'Software Title'.", vbOKOnly, "DUPLICATE 'SOFTWARE TITLE' DETECTED")
 
   txtInputSoftwareTitle.SetFocus
 
   End If
   
'Check if the changes made just in the "txtInputSoftwareLicenseKey" Text Box equals what was originally in that Text Box before any changes were made.  If the check equals, "No", then check if
'what is entered in that Text Box duplicates anything that is already in the "Software_Licenses" Table.  If the check equals, "Yes", then stop everything and let the User know via an
'"OK Only" Message Box that the "Software License Key" they have entered is a Duplicate Record.  Then set the focus back to the "txtInputSoftwareLicenseKey" Text Box.
   
  Case UCase(txtInputSoftwareLicenseKey.Value) <> UCase(txtSoftwareLicenseKeyOldValue.Value)
 
   If DCount("[SOFTWARE_LICENSE_KEY]", "Software_Titles", "[SOFTWARE_LICENSE_KEY]= '" & Me![txtInputSoftwareLicenseKey] & "'") > 0 Then
 
   nReturn = MsgBox("You have entered a 'Software License Key' that already exists in this Database.  Please enter an accurate and unique 'Software License Key'.", vbOKOnly, "DUPLICATE 'SOFTWARE LICENSE KEY' DETECTED")
 
   txtInputSoftwareLicenseKey.SetFocus
 
   End If
   
'Having checked all other parameters, if everything checks out just fine and this is a fully filled out and unique Record on the parts that need to be unique, then simply save the Record
'notifying the User that the requested command function took place and then reset the Form.
 
 Case Else
 
  DoCmd.GoToRecord , , acNewRec
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
 
  nReturn = MsgBox("The Record has been saved.", vbOKOnly, "RECORD SAVING")
 
  cmdLookupRecord.SetFocus
 
  lblAddRecordMode.Visible = False
  lblModifyRecordMode.Visible = False
 
  cboViewRecordShortcut.Visible = False
  cboInputRecordShortcut.Visible = False
 
  cmdSaveModifyRecord.Visible = False
  cmdUndoRecord.Visible = False
  cmdDeleteRecord.Visible = False
 
  cmdAddRecord.Visible = True
 
  cmdAddRecord.SetFocus
 
  txtInputSoftwareTitle.Visible = False
  txtInputSoftwareLicenseKey.Visible = False
  txtInputSoftwareLicensesAvailable.Visible = False
 

End Select

'Turn Microsoft and Microsoft Access Warning Messages back on.

  DoCmd.SetWarnings True
 
'Finalize the "Save Record" Command Function.

Exit_cmdSaveModifyRecord_Click:
  Exit Sub
 
Err_cmdSaveModifyRecord_Click:
  MsgBox Err.DESCRIPTION
  Resume Exit_cmdSaveModifyRecord_Click

End Sub
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The first key is that you can't leverage the "Finalized Code" in anything but a separate event like what I would recommend being a "Save Record" Command Button specifically for when you modify a Record that only appears when you are in a "Modify Record" mode
--WRONG.  Validation code belongs in the BeforeUpdate event of the FORM or the CONTROL.  Your save button should force Access to save the record and that will execute the code.  Putting the code anywhere else ensures that bad data will creep into your tables.
The second key is to have the original values of the Text Boxes that you are checking for duplication saved somewhere before a change is made to them so that you can leverage that data to compare to the "modifications" made in the Text Boxes, or in other words, the new value that you have entered into those Text Boxes.
-- UNNECESSARY.  Access keeps three copies of what is in each control.  The .text property captures the characters as they are typed and can only be referenced when the control has the focus.  The .value property which contains the unsaved value of the data.  And the .OldValue property which contains the prior saved version of the control.  So, this is what is currently stored in the table.
The forth key is to understand that you are capturing the "Old Value" the moment that the Text Boxes have a change.  This would mean that you have to activate the saving of the "Old Value" via a "Change Command" in those Text Boxes.
--UNNECESSARY - why would you need to save something that will not change until you actually save the record?  Also the change event fires multiple times.  Once for each keystroke, not that it matters in this instance since you are simply saving something you don't need to save for each character the user types in the field.
***SPECIAL NOTE ON THE FIRST CHECK*** The code will NOT work if you do not leverage the "UCase" function.  Otherwise, "TESTER 01" will come across as not the same data if you change it to "tester 01" and therefore will be viewed as a duplication.
--UNNECESSARY.  Access is by default NOT case sensitive.  So, if you are having a case issue, it is because you have changed a default somewhere.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
This is some seriously outdated code.  It references a menu item from Access 95!!!!  And, to be even worse, it mentions it by number rather than name.  Come on now quickly, who knows what option 5 on the records menu was in A95?

And finally, in addition to being inefficient, you have completely deprived yourself of the use of intellisense by incorrectly referencing the form controls.  Using Me.controlname gives you intellisense and it also tells Access immediately what library the variable is defined in.  Controlname.property works, but why force yourself to type everything when Access will help you.  

Xailon,
You have created a convoluted, unnecessarily complex procedure to do something that is quite simple and straightforward and which I would do with less than 25% of the code.  I'm going to guess that you have a programming background but it is not Access.  The assumption you made was that Access is stupid and you know better.  Access is a rapid application development environment.  It is designed as a quick way to develop data-centric applications and so it does a great deal for you behind the scenes.  But only if you learn how to use its events and properties as they were intended to be used.  It is only when you don't understand events and properties that you end up with code like this.  But at least you have a solution.  If you have time, you might want to revisit the two code snippets I wrote for you since those are most of what you need as long as you put them in the correct events.
0
 

Accepted Solution

by:
Xailon earned 0 total points
Comment Utility
Wow Pat, you would have thought that I insulted your Mother there or something.  Hey, I'm an "End Results" kind of guy.  I tried all the suggestions here and none of them worked and I think it was mostly due the misunderstanding that I'm checking for duplications when I am modifying a record but the modifications aren't a duplication if I'm just changing the case of the Record but I also need to check for duplications when I'm just saving a new Record.  When I used that as the bases of my thinking, I was able to come up with the solution that I did and it worked.  I think you'll find that most people, just want it to work.  Hey, you have my base code.  If you can do it with 25% less code and much more efficiently then show the example and explain it in detail like I did mine.  I'll put it in my database and if it works, I'll eat the biggest humble pie ever and give you as many points as this program will allow.  I'll even post it on my Facebook page.  After all, I never said my way was better, that I'm some "Access Genius" or anything like that, I just said this is what I did to make it work.  It's not like my whole ego is wrapped up in this.  I just wanted it to work, hence me being humble enough to create an account in "Experts Exchange" and post the question.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I am sorry I offended you, I could tell you were very excited at your success, but I couldn't let your statements stand for the folks who find this in the future.  The first statement was especially damaging.  Without that, I probably would have simply ignored the misadvise.   Validation code in other than the BeforeUpdate events may display messages but can always be circumvented to allow bad data to be saved.  The single most important event in an Access form is the Form level BeforeUpdate event.  Once you understand that event, you are on your way to complete control over forms.  Your decision to save a copy of the OldValue  property is just strange.  Why would you not trust Access?  And to do it in the Change event is simply wasteful since in a field where the user is going to enter 5 characters, you are saving the same value 5 times.  It is innocuous since you are saving the same thing but still wasteful.

I posted code that would work for you but you were fixated on looking for something other than ">0".  You apparently didn't understand the code I wrote to show you how to use the NewRecord property to identify a new record or the code I showed to compare the OldValue property to the current contents of the Value property.

Here's a link to the help entry for "Option Compare"  http://support.microsoft.com/kb/98227/en-us/
The default is Option Compare Database which says to use the sort order set for the database.  You have either used something other than Option Compare Database in the class module or changed the database default and that is what is causing the case issue.

I'm glad you are happy with your solution.
0
 

Author Closing Comment

by:Xailon
Comment Utility
It worked.  That discovery was the foundation to the entire database where I could not only check for duplications on a new record but check for duplications when modifying a record correctly.  The code that I have developed since then has significantly changed but that discovery most certainly put me on the correct path.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now