Disabeling the Before Update Event on Edit

[d9pierce1]

Hi all,


Getting close to having this project wrapped up thank heavens! I added a Prevent Duplicate Records Before Update (See Code Below) and it works real nicely, however, There are two issues I would like to see if I can get some help solving?

1. I can no longer edit the existing records. I am thinking there is maybe a line or two of code that can be put in the (Code Below) to allow this function to be disabled during edits? Or do I need to create a dupchk checkbox and create some function to CancelEvent if....

2. Is there a way on a bound form, to check if a duplicate exist before writing it to the table. Example, If I have in table a StateName field with Tennessee, then I hit add new record, Type in Tennessee123 it see this as an existing record as it all ready recorded it to my table that matches Tennessee. It really didnt see it as a different Tennessee, just wrote it as I typed. This may not be an issue when I actually learn how to do the unbound forms in a short while.

Thanks so much,
Dave

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim SN As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Set rsc = Me.RecordsetClone
    SN = Me.TxtStateName.Value
    stLinkCriteria = "[StateName]=" & "'" & SN & "'"
    
    If DCount("StateName", "tbl_State", _
    stLinkCriteria) > 0 Then
    Me.Undo
    MsgBox "Warning State of " _
    & SN & " is already in Database." _
    & vbCr & vbCr & "You will now been taken to the record.", _
    vbInformation, "Duplicate Information"
    
    rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If
    Set rsc = Nothing
End Sub

[Micron]

In a similar situation, I had one form that was opened either to add a record OR edit one, but not both operations for that one opening. In the code that opens the form, I used the OpenArgs property to provide either "edit" or "new" or something like that. Then in form code I tested for openargs value such as

If Me.OpenArgs = "edit" Then
do edit stuff
End If

If Me.OpenArgs = "new" Then
do append stuff
End If

If you have more modes (such as view or want to open to a specific record) then a Select Case block might be better.

[Micron]

Had to stop due to an interruption thus the answers being in separate posts.
Usually the best approach to not allowing duplicates in the case of data like State names is to provide an unbound list in a combo or listbox. The list comes from a lookup table, which is a table of values meant to control what the user can do. Not to be confused with a table field with a lookup. The control row source is bound to the table; the control isn't bound (has no control source property value). If it's a combo, you can employ a 'filter as you type' feature so that the control begins to weed out the values that don't match the entered text. You can also use the Not In List event to allow the addition of values that are not already there. However, you cannot prevent Tennessee123 when Tennessee is already there no matter which of these methods you use since they're not duplicates. You could only make additions through an administrator if that helps, which might make sense for States, but not for values like street names.

If there aren't too many controls involved, another approach could be to use the control BeforeUpdate even to check, and then remove the offending value. This would be better than waiting until the entire record is entered. This surely would likely not tick off your users after filling out many fields and then you wipe them all out with Undo just because of just one offending entry.

[d9pierce1]

HI,
I have been playing with this today for most of the day. Its just a short form for inputting states and abrev's so no combo, no list, nothing. Two txt fields. I dont know a lot about this but have learned to start small and make sure everything works one step at a time as once something is added, its a lot easier to figure out what happened when it didnt work. Just my two cents.

With that, I have added a new field to the code below... so it checks two txt boxes against two fields in the table. Is their a way to get an OR statement in that? So example, If I type Illinois and IT (Instead of IL) into the record, It goes to the Record of State Name and vise versa....?????
And again, still am seeking something on the edit issue talked about above post.
Thanks
Dave

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim SN As String
    Dim ST As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Set rsc = Me.RecordsetClone
    SN = Me.TxtStateName.Value
    ST = Me.TxtState.Value
    stLinkCriteria = "[StateName]=" & "'" & SN & "'"
    stLinkCriteria = "[State]=" & "'" & ST & "'"
    
    If DCount("StateName", "Tbl_State", _
    DCount("State", "Tbl_State", _
    stLinkCriteria)) > 0 Then
    Me.Undo

[davegri]

Why are you typing in state names and 2 letter abbrevs? You have a tbl_State. It simply needs 50 correct rows. Any form needing to input state information can look it up with a combobox, and it will always be correct. Any table needing to store a state would store a FK pointing to the correct tbl_State PK.

[d9pierce1]

Hi davegri,
Thanks and let me explain, this form state and such is just an example that I am starting with. I have a lot more forms that will require this same instance. I just doing a sample form to see how this works and doesn't work in so that I dont create a ton of code that doesn't work or that I cant find a solution to. So, its not just states, there are lots more that will require something exact or similar that I can tweek as needed.

With that said, would it be possible to remove the code in the "Before Update on Form" and put that code in the save button? Here is what I am thinking....
If I put a check for dups check box on form, then on save if chkdup = True, then it would fire this code, if chkdup = false, then it would go to cmdsave? I could force my add button to check box, and my edit button to uncheck box and allow me to edit. Or would this just add the record before I hit save to the table and not undo it? I am just trying to find a solution to if I add a record and it exist, then it goes to that record, but allows me to edit that record and save, Keeping in mind that it is just not this form and table, there will be many instances such as this. I am trying to get it to work on a sample before going forward with it. this is just a simple form and such that I am testing to see how I need future functions to work.

Thanks
Dave

[davegri]

Well, another approach would be to define the StateName and ST fields in the table 'no duplicates'. Then add an error handler to inform user in a friendly way.
Perhaps if you post the db with your efforts, we could analyse from a more informed position.

[Micron]

Again, there isn't much you can do to prevent bad data entry. If you're appending and enter IT instead of IL, there's nothing to stop that beyond the fact that IT might already be there, valid or not. The only real control you have is to prevent duplication on any given field, or a combination of fields if that fits the bill. That would be a composite primary key or composite index situation.

As mentioned, the best time to validate data is often on the control's before update event rather than on a form or button event. The problem with those is that it's possible to cause Access to attempt or actually save a record, such as leaving a main form by giving focus to a subform. Not so much of an issue if there's no main form, but the fact is there's no cut and dried answer to your questions.

[d9pierce1]

Hi davegri,
Attached is the db.zip. I like the way the it works finally, just cant edit a record and it hangs up if I in put while adding, Texas and UU just to test different situations. It see the state is new but the statename is all ready used so it doesn't know what to do.
Thank you,
Dave
Test Buttons.zip

[davegri]

I don't know what you want to do.
Is the rule that the State can appear only once in the entire file or just once for each StateName?
Is the rule that the StateName can appear only once in the entire file, but with any State?
Is the rule that the combination of StateName and State must be unique and appear only once in the file?
Or other rule(s)?

Take some examples:
Existing records:
1. Utah UT
2. Ohio UT
3. Ohio OO
4. Ohio OH


User Enters:
UTAH TT
UTUT UT
Ohio OO
Ohio OH
Ohio TT
Ohio UT
Utah OO
and on and on. What to do in each of these examples and what existing record to edit if deemed a duplicate; the one containing the StateName or the one containing the State?

[d9pierce1]

Hi davegri,
In this instance, What I am seeking is to check if either or both of them exist. If I add lets say Florida, FL.. then it would see this exist all ready and go to that record. If I miss spell Florida as Forida but still have FL then it would see the FL and go to that record Instead of adding Forida as a new record. If I typed in Florida but entered in FT then Florida exist and go to that record without adding FT as a new record. Basically if either of these exist in table, then it goes to that record, without adding new. Then I need to be able to edit it. Currently if I added Florida, and FT, it would just hang in limbo, have to refresh or close and open. I dont want it to hang so I beleive an OR statement needs to be in there somewhere. How to stop the before update in edit mode I cant figure out either.

Throughout my real db, there will be three instances of this used if I can get it figured out. One like above, one with just one field, and one with multi fields to check in two different tables. OR & AND If statements.

Hope this makes sense and thank you for all your assistance.
Dave

[davegri]

What I am seeking is to check if either or both of them exist

Working on logic..

What about if both exist but are in different records?
Existing
Florida FL
Utah UT
Alabama AL

User Enters
Utah AL

Which record to edit, Utah UT or Alabama AL? Both are correct.
Or go ahead and add Utah AL?
Or is this a bad data condition that requires the user to do something else?

[d9pierce1]

Thank you, you are awsome!
That is a bad condition where I would like it to have user interface, such as both records exist, you cannot add this, vbOK and return if that makes sense.
Thanks

[Minty]

To go back to one of your original questions - The reason you can't edit the records (I think ) is that once created your record count will =1 .
So your DCount check needs to be for >1 .

Unless I've missed the point.

[d9pierce1]

Thank you,
If I go to edit a record, make a change, then the before update event fires on save, and it says record all ready exist, goto that record and wont let me change anything about that record. Has something to do with the before update event. Not sure why its doing that?
Thanks
Dave