Marshall Brooks
Member
- Local time
- Today, 01:46
- Joined
- Feb 28, 2023
- Messages
- 696
I obfuscating a bit, but here are my requirements:
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):
Thanks in advance!!!
- 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.
- 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.
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):
Code:
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!!!