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.

 

[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!!!!

 

[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.

 

[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.)

 

[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?