Solved Duplicate value check - complicated.

[Marshall Brooks]

I obfuscating a bit, but here are my requirements:

• If Field1 contains an "X" as part of the field.value, I don't want it to be duplicated. (The field may have a v (version numbers) and some trailing numbers and I want to ignore those.) If the field value exists in the table, I want to display an input box and prompt with the correct format and test the new input to see if it exists.
• If Field1 does not contain an X, I am okay (and expect to have) duplicates, provided that I don't have two records with matching values for Field1 and Field2, which is a date field.

Issues:

• The code below works fine for the first condition. However, it only works from the AfterUpdate, or (perhaps better) from the LostFocus event. If I try to run it from BeforeUpdate, I get an error that I can't write the new value to the field b/c the BeforeUpdate event is preventing it. That seems to be fine, but I just read 4 or 5 threads on here saying that validation (which is basically what this is) should alwas and only be done from the BeforeUpdate event. But I don't see how to do that, unless I pass the acceptable value to a function to write to the field, but that seems overly complicated.
  
  
• I'm not sure how to handle the second case. What I think needs to happen:
  
  • Run a DLookUp (or ELookup) to see if the new Field1 value already exists in the table.
  • If it does, create and run a query or SQL statement to extract Field2 for records that match the proposed value of Field1.
  • Run a DLookup (or Elookup) on the query to see if any of the query resuults values for Field2 match Field2 of the current record.
  • If I have a match, prompt for a new value and test the new value with the new query.

I'm stuck on how to build a temporary query in VBA which can be discarded later in the step.

Here's the example code which is working if I don't run it from BeforeUpdate (Note: This uses Enhanced Message Box and Allen Browne's ELookup, but you should get the idea):

Private Sub Field1_AfterUpdate()
    If InStr(Me.Field1 & "", "X") > 0 Then
     If Nz(ELookup("[Field1]", "[Table1]", "[Field1] Like '" & Me.Field1 & "*'"), 0) <> 0 Then
             strResult = Dialog.Box(Prompt:=DocNumber & " already exists. Choose action?" & "", Buttons:=(1024 + 32), TITLE:="Duplicate Warning:", _
            LabelButton1:="Cancel", LabelButton2:="Insert Anyway", LabelButton3:="Try Again", DefaultButton:="3")
            If strResult = vbBt1 Then
                Me.Undo
                Exit Sub
            ElseIf strResult = vbBt3 Then
                Do While Nz(ELookup("[Field1]", "[Table1]", "[Field1] Like '" & Me.Field1 & "*'"), 0) <> 0
                     InptbxResult = InptBx("Enter Field1 Value. If S-XX already exists, use S-X1, S-X2, etc. (e.g. Number-1S-X1.)", "Enter Field 1 Value:", ME.Field1)
                     If InptbxResult = "vbCancel" Then
                        Exit Do
                    Else
                        Me.Field1 = InptbxResult
                    End If
                Loop
            End If
        End If
    Else ' No X's in Field 1
    ' Check for duplicate with the same number with the same Field2 value.

    End If
End Sub

Thanks in advance!!!

 

[The_Doc_Man]

I'm stuck on how to build a temporary query in VBA which can be discarded later in the step.

For that 2nd bullet point "create and run a query or SQL statement to extract Field2 for records that match the proposed value of Field1", you could open a recordset based on a dynamically built SQL "SELECT" query. Then you could examine the list of what matches.

However, when all you are doing is whether you have a match or not, a DCount with appropriate (again dynamic) criteria is probably enough unless I totally misread what you are asking. And the good news is that the Domain Aggregate functions cleanup after themselves reasonably well so they would be viable candidates for the "matching X" types of question.

Before getting long-winded here, does this seem like it helps you, direction-wise?

 

[Marshall Brooks]

@The_Doc_Man - Yes, that helps with the concept, I still don't understand the mechanics.

you could open a recordset based on a dynamically built SQL "SELECT" query

I think I can figure out the SQL Select query - something like "SELECT 'Field1', 'Field2' FROM Table1 WHERE Table1.Field1 = <Like, not sure how to do that in SQL> Me.Field1.

Syntax is probably not quite correct, but I can build the query in query builder and than adapt it to VBA.

I'm not sure how to create a recordset from the SQL statement.

However, when all you are doing is whether you have a match or not, a DCount with appropriate (again dynamic) criteria is probably enough unless I totally misread what you are asking.

Well, DCount and DLookup would basically be doing the same thing, although Dcount is probably faster. I had some redundant steps. I don't need to do the DLookup on Field one, the query does that, if it contains records, I do the Dcount on matches for field two.

Actually, I think it could be done in one step with:

"SELECT 'Field1', FROM Table1 WHERE Table1.Field1 = <Like, not sure how to do that in SQL> Me.Field1 and Table1.Field2=Me.Field2.

And then if the SQL returns Any records, I know the value for Field 1 is a duplicate and needs to be changed.

 

[Pat Hartman]

The code below works fine for the first condition. However, it only works from the AfterUpdate, or (perhaps better) from the LostFocus event. If I try to run it from BeforeUpdate, I get an error that I can't write the new value to the field b/c the BeforeUpdate event is preventing it. That seems to be fine, but I just read 4 or 5 threads on here saying that validation (which is basically what this is) should alwas and only be done from the BeforeUpdate event. But I don't see how to do that, unless I pass the acceptable value to a function to write to the field, but that seems overly complicated.

BeforeUpdate event of the FORM.

When you put validation code in the control's BeforeUpdate event, it only runs if the control gets the focus AND is modified so the validation is useless for enforcing a required value. However, All the validation code in the Form's BeforeUpdate event runs if ANY control has been changed. ALSO, the control's events do not fire at all if the control was modified by VBA code.

There are reasons for doing your validation in the control's BeforeUpdate events but as you have discovered, that code will be different if as a result of the validation, you want to modify the value in the control. Also, when you do validation in control events, you almost always need to repeat that validation in the FORM's BeforeUpdate event so I use the FORM's BeforeUpdate event unless I have a specific reason to use the control's events. A specific reason might be that the field must be unique and is required. This allows me to prevent the user from continuing on without entering a unique value. However, unless you are prepared to lock the form until a valid value is entered, you STILL need to validate the field in the Form's BeforeUpdate event anyway because control level events can never serve to make a field required because they don't run if the control never gets the focus.

 

[Marshall Brooks]

@The_Doc_Man - Thanks I think I figured it out.

For issue two, I didn't realize Dcount can have multiple criteria, so I should be able to just use that: https://www.access-programmers.co.uk/forums/threads/dcount-multiple-criteria.142068/ That simplifies the process and I don't need to create a query at all.

If I needed a query, I can use Create and Delete one like this: https://stackoverflow.com/questions...rary-query-in-sql-that-another-sql-query-uses - That will come in useful for a future project.

***
For issue 1:

I haven't tested it yet, but I think I have a workaround for the first issue. At least I understand what the cause is. Basically, I was trying to do too many things with one event.

I was trying to use one field event to: Validate the data (data is in the correct format); verify the data wouldn't create a duplicate entry; prompt the user to update the value if both conditions were not met; re-check the new value; update the field. I was also trying to call this event from other subroutines to validate the field contained value data before the subroutine used the data.

Field1_BeforeUpdate won't work for this b/c the field can't be updated from the Before Update event. I'm not sure exactly why it works that way, but it does.

Field1_AfterUpdate (of Field1_LostFocus) actually did work. The problem here was when I called Field1After_Update from a subroutine to check the data, I got a false positive fo a duplicate entry. The reason is the value of Me.Field1 did exist in the bound table for Field1 b/c the field had been saved and it WAS the value in the table. (I could maybe check if two values exist in the table if the value was changed or one if it was not, but that seems complicated).

What I plan to do it use Field1_AfterUpdate only to check for duplicates. AfterUpdate fires when the value has been changed, so this makes sense. (I may add a check to only do the check if Field1.OldValue is different from Field1.Value. This would prevent a false positive if the field contained 123, someone changed it to 1234 without saving and then changed it back to 123.) I will use Field1.LostFocus to check for valid data. This way the validation check won't create false duplicate positiives.

(Another approach would be to create a "Field1_Validate_Data" subroutine and call that from Field1_AutoUpdate and from other Subroutines.) Tomato/Tomahto. Advantage of the first method is that the check is made automatically whenever the filed is manually accessed (although it is technically a waste of processing checking the previously validated and unchanged data is still valid). Advantage of the second is it is somewhat more intuitive. (From the subroutine, Call Field1_Validate_Data is eaiser to interrpret than Call Field1_LostFocus - which in this case is being used to validate the data. Thinking it over, I'll probably use the Field1_Validate_Data subroutine method.

***
@Pat_Hartman - I agree with your advice, but playing devil's advocate here:

When you put validation code in the control's BeforeUpdate event, it only runs if the control gets the focus AND is modified so the validation is useless for enforcing a required value.

But if the data in the control was validated previously and was not changed, there is no reason to validate it again since it hasn't changed.

However, All the validation code in the Form's BeforeUpdate event runs if ANY control has been changed.

One of the reasons I don't really like this approach. If I have 30 controls on my form, I either have to validate all of them, even though only 1 or two changed, or I have to at least check all of them and if they are unchanged, skip over them.

ALSO, the control's events do not fire at all if the control was modified by VBA code.

True - although you can call the event manually from your VBA code.

 

[Marshall Brooks]

I'm getting there, but I'm running into problems.

My usual method of checking for duplicates is to use Nz(Elookup(...), 0) <> 0 rather than Dcount - b/c I don't care HOW many duplicates there are, I just care if there is at least one. DocNumber is a string variable with the value of Me.Field1. Field2 is a date field.

First attempt was:
If Nz(ELookup("[Field1]", "[tblTable1]", "[Field1] Like '" & DocNumber & "*'" And "[Field2] = #" & Me.[Field2] & "#"), 0) <> 0 Then
And that gave me a Type Mismatch although the syntax looked correct. So I changed 0 to "" incase it didn't like the date field returning 0 and still got a Type Mismatch.

So I tried:
If DCount("[Field1]", "[tblTable1]", "[Field1] Like '" & DocNumber & "*'" And "[Field2] = #" & Me.[Field2] & "#") <> 0 Then
And that also gave me a type mismatch.

So I tried building a query and checking if the query contained any records.
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.CreateQueryDef("qryExistingField1", "SELECT '[Field1]', '[Field2' From tblOpsSup")
That apparently worked, but I don't see the query in the Navigation pane to know what values it contains, and I know you have to do some odd things when using variables with SQL syntax, and I don't know how to create a "Like" statement in SQL.

 

[cheekybuddha]

If DCount("[Field1]", "[tblTable1]", "[Field1] Like '" & DocNumber & "*'" And "[Field2] = #" & Me.[Field2] & "#") <> 0 Then
'                                                                       ^
'                                                                       |
'                                                You closed your string too early here
' Try:
If DCount("[Field1]", "[tblTable1]", "[Field1] Like '" & DocNumber & "*' And [Field2] = #" & Me.[Field2] & "#") <> 0 Then

 

[Marshall Brooks]

@cheekybuddha - THANK YOU!!!!

 

[Pat Hartman]

But if the data in the control was validated previously and was not changed, there is no reason to validate it again since it hasn't changed.

NOTICE that the After update event does not have a cancel argument. That is CRITICAL if you are doing validation because it is setting cancel to True that allows you to stop Access from saving the record. Therefore, it is ABSOLUTELY necessary for you to validate the control again in the form's BeforeUpdate event if you used the control's AfterUpdate event for validation (bad idea in my opinion) because it doesn't matter how many error messages you display or how many times you put focus back into the control, Access will save the record if you don't cancel the Update event.

One of the reasons I don't really like this approach. If I have 30 controls on my form, I either have to validate all of them, even though only 1 or two changed, or I have to at least check all of them and if they are unchanged, skip over them.

True. But, the upside to this is you could end up finding bad data that was saved before you added proper validation.

You can believe me or not. Your choice. I created a couple of videos and a database that shows you that Access will save the bad data if you do not cancel the Update event. If you want code in two places, that is really poor practice. If you want code in one place, use the Form level event. Also, if you ever modify the value of a control using code, the control level events won't run. Granted, you ought not to have bad code but it happens and unless you are using the form level event, that value will never be validated.

Think of it this way. ALL the controls need validating when you add a record but only some need validating when you update a record. Also, when only some of the records need validating, there is frequently relationships that need to be checked. For example, StartDate must always be <= EndDate. This gets pretty convoluted when you try to validate the two controls in the control's BeforeUpdate event. I think you're being penny wise and pound foolish to worry about all the controls being validated when any of them changes. Efficiency is critical when you are running a code loop. Simplicity wins out for one time execution.

I suggest watching at least one of the videos before trying to use the sample database so you have some understanding of how it works. I'm pretty sure you will be amazed when you watch the events fire.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

True - although you can call the event manually from your VBA code.

There is always a way to code around design defects. Most people don't even know about this particular gotcha'. PS, it is poor practice to call event code from other procedures. Other developers will not expect it and you may forget you did it and change the event code to the detriment of the calling code. Therefore, if you need to call code from more than one place, the clearest option is to create a procedure and put the common code there. You can call the procedure from multiple places. No one will miss that. They will always be aware that if they change the procedure, they are impacting all the procedures that call it.

You will never be able to save bad data using one of my forms. However, I have a pretty good shot at saving bad data in one of your forms regardless of how many events you end up using to stop it. Your choice. One event or multiples. It takes a minimum of 4 events to even come close to keeping bad data from being saved when you don't use the form's BeforeUpdate event. And even then, if the user can close the app using the X, your bad data will be saved regardless of how many events you put code into to try to stop it. I think closing using the Task Manager might also allow the bad data to be saved although I never tested it. But I think task manager tries a normal shut down first. The user can however push the power off button. That definitely prevents Access from closing normally and in that case nothing gets saved.

If Nz(ELookup("[Field1]", "[tblTable1]", "[Field1] Like '" & DocNumber & "*'" And "[Field2] = #" & Me.[Field2] & "#"), 0) <> 0 Then
And that gave me a Type Mismatch although the syntax looked correct. So I changed 0 to "" incase it didn't like the date field returning 0 and still got a Type Mismatch.

One of the fields is not the data type you think it is. Is DocNUMBER actually a string or is it a number?

Also, it would seem that DocNumber is not atomic and that violates first normal form. The basic foundation of any relational database. You might want to consider breaking the field up into its constituent atomic parts.

And finally, since the criteria can result in multiple rows, why not simply use dCount() or eCount() instead.

 

[Marshall Brooks]

@Pat Harman - Valid points and appreciated. At some point I will probably add in full validation to the forms BeforeUpdate Event. (Presently, that event has a "Confirm that you want to save?" prompt.

One of the fields is not the data type you think it is. Is DocNUMBER actually a string or is it a number?

Doc Number is a string and is essentially atomic. The problem was in the quote positions as @cheekybuddha pointed out.

And finally, since the criteria can result in multiple rows, why not simply use dCount() or eCount() instead.

Actually, in this case, I used DCount, although I think ELookup would have worked also, if I had the syntax correct.

As said in reply #6 - I normally use ELookup when checking for duplicates, b/c it finds the first entry and one entry is too many. If my proposed value is "123" and Elookup finds "123", that is good enough and I need to stop. I don't need to know that "123" exists fifteen times (and it should only show up once. I don't actually know which is faster.

I wasn't aware of Ecount - https://allenbrowne.com/ser-66.html - learned something new!!!

Thanks again!

 

[cheekybuddha]

I don't need to know that "123" exists fifteen times (and it should only show up once. I don't actually know which is faster.

You need to know whether it exists.

So the results you're interested in are 0 and >0.

You don't have to fanny around with testing for Null etc ([D|E]Count() returns Null if criteria is not matched. [D|E]Count() just returns a number, never Null)

[D|E]Count() just makes your life easier for this particular task.

 

[Pat Hartman]

Valid points and appreciated. At some point I will probably add in full validation to the forms BeforeUpdate Event. (Presently, that event has a "Confirm that you want to save?" prompt.

I guess I'm not sure why you are bothering since you are allowing them to confirm bad data. But whatever.

Doc Number is a string and is essentially atomic.

If it is atomic then why are you using Like to search only part of the field? Use = and search a complete field. Some RDBMS' will force a full table scan when you use LIKE, even if it is Like xxx* Some are smart enough to search the index. I seem to remember that Jet/ACE always force a full-table scan if you use any wild cards. Of course, if there is no index, you will always be doing a full table scan so you should add an index.

 

[Marshall Brooks]

Essentially atomic ... We let the users add a version number at the end of the field while the document is in draft form. (Yes, we could/maybe should add a separate field for VersionNum, but we didn't.)

 

[Pat Hartman]

Essentially atomic ... We let the users add a version number at the end of the field while the document is in draft form.

Then it is not atomic. PERIOD. It has at least two separate values. The version should have been a separate field. The mushed field violates first normal form whether you think it does or not. Is "essentially" atomic something like "essentially" alive?

 

[GPGeorge]

Then it is not atomic. PERIOD. It has at least two separate values. The version should have been a separate field. The mushed field violates first normal form whether you think it does or not. Is "essentially" atomic something like "essentially" alive?

At the risk of aggravating the situation, with regard to something being "essentially" one thing or perhaps sometimes another thing, I like to use the analogy that ambiguity is required only in humor, politics and poetry. In database design, ambiguity is, "essentially", a fatal flaw .

 

[Marshall Brooks]

Okay - It's not atomic. That said, it works well for us. I'm not sure exactly how to do it differently. Our team is used to putting the version number after the document number.

Obviously, I could have two fields - document number and version number and separate labels and text boxes for each field, but I'm using a lot of screen real estate and complicating things for the users (arguably).

I could continue to have one field on the form and combine document number and version in the one field, then if you clicked in the text box to update it, another pop-up came up with the two fields separated, but that seems a lot more complicated than just allowing a version number at the end of the field. (But sometimes complications are required.)

Is there a simpler solution that I am missing?

 

[Pat Hartman]

but that seems a lot more complicated than just allowing a version number at the end of the field. (But sometimes complications are required.)

I know that you are not going to change your ways and so please ignore my following comments. They are for others who might be considering violating first normal form for "convenience"

Having two fields means never having to split them in code or in a query. It means not having to use LIKE to find the relevant records. You can easily add code to the dbl-click event of the version number to generate the next sequence number.

But, if the original field would otherwise be unique and define this particular row, the version may need to be a separate table. An example of a field that always needs a version number is a Part Number. If you are not in the design aspect, you will almost always see it as combined number and since your parts come from multiple manufacturers, you don't control how the version is generated and so you can't normalize the schema. The common fields, ie the ones that don't change when the version changes stay in the parent record and the fields that do change with the version move down to the new version table and anything that needs to refer to this original field would actually now have to point to the version table. Having to make this particular split after the fact could be very painful depending on how many forms/queries need to be changed. So, whenever you need a version, think very carefully at the beginning whether you should actually add a child table.