refresh with code after update (1 Viewer)

PWG · Aug 16, 2019

Im trying to prevent duplicate entry when entering data into a field in a form.
I want to know as soon as I enter the data that it is a duplicate.
I have found that I can do it by using a macro Refresh in the after update
The problem I now have is the standard response dialog boxes.
I am working with the North wind template and changing it.
Can I use code to do the refresh to check if it is a duplicate and the get the response for the dialog box from the string table.

CJ_London · Aug 16, 2019

in the control afterupdate event put some code along the lines of

if dcount("*","myTable","myField=" & txtmyField)>0 then msgbox txmyField & " already entered"

PWG · Aug 16, 2019

Thanks for your help for some reason my computer has just died black screen of death

theDBguy · Aug 16, 2019

Looks like this thread is related to this one.

Micron · Aug 17, 2019

If you want to prevent duplicate data and can't simply prevent it by having a unique index or primary key on a particular field, then the time to detect that a value would be a dupe is BeforeUpdate, not AfterUpdate.

Pat Hartman · Aug 17, 2019

The purpose of Refresh is to update the data displayed on the form. A side effect of this action is that the current record is saved if it is dirty. Since most records require something other than just a single key field it is almost always wrong to force Access to save a record when only a single value has been entered. Apparently, you have no validation code in your form so you are not experiencing a problem.

A better technique would be to use a dLookup() in the AfterUpdate event of the "key" control to determine if that key value already exists. If it does, you can cancel the update and display a message to the user and place the cursor back in the key field so he can try again.

PS - if you want to save a record, use the action that does that. Don't use some other method that accidentally, as a side effect, performs a save.

Code:

DoCmd.RunCommand acCmdSaveRecord

In some cases, you might run into an error (I personally have never seen it) so you could consider the following trick. It is a trick so I suggest commenting it that you are using it to save the current record because the normal save command doesn't work in this instance.

Code:

If Me.Dirty Then
    Me.Dirty = False
End If

Micron · Aug 17, 2019

use a dLookup() in the AfterUpdate event of the "key" control to determine if that key value already exists. If it does, you can cancel the update

Be it the form AfterUpdate event or a control AfterUpdate, how do you cancel this update? The whole premise of After update is that the event fires after the control or form is updated. There is no cancel (only Zuul).

I just don't see the point in determining if a value already exists after you save it, be that on the record or on the field. If it's on the control, you have to send the user back. Then what if they leave the record? At that point, it's committed

The_Doc_Man · Aug 17, 2019

There is another possible event you could use to place a test... the LostFocus event is a good place to test for having accepted a duplicate (using the DLookup method) and if you find that you HAVE entered/accepted a duplicate,

1. Issue a MessageBox of the vbOKOnly variety that says "The value " & Me.Textboxname & " is a duplicate of an existing value. Please enter a new one."

2. Then issue Me.Textboxname.SetFocus to force focus back to the duplicated value.

Pat's warning about not using a side-effect of something else is correct, and the LostFocus test does not violate her suggestion. Note that there is an implication in the setting of how the TAB and ENTER keys work on the form because it is possible to have either of those keys implicitly save a record. (If you use either of them on the last field on the form, e.g.) So you might have to diddle around with TAB/ENTER behavior. That is found under the File >> Options >> Current Database complex control if I recall correctly.

PWG · Aug 17, 2019

Thank you all for your help. I am very new to code so am finding it hard to follow.
Please correct me if I am wrong
I do not not want to save the to my table and have found that I can do that if I refresh the form in the before update.
But you are saying I should use DLookup to check for the duplicate is that correct.
As this is the first field that I enter on my form will this check as soon as I leave the feild

Dreamweaver · Aug 17, 2019

When I have combination keys to handle the problem of somebody selecting say a product for a workorder that has already been added I add the below to the Form OnError

Code:

Select Case DataErr
    Case 3022
    MsgBox "You have added this date for the selected employee please change it for another", vbInformation + vbOKOnly, "Duplicate Employee Time"
    Response = acDataErrContinue
End Select

PWG · Aug 17, 2019

That sounds like what I want to do. On my new part form I want the user not to enter a new part number that is already in the product table. But I want to tell them before they have filled out the whole form only to find it is a duplicate.
So where would you put this code

Dreamweaver · Aug 17, 2019

It works when you have combination keys but for normal tables with just one key you need to handle things as the doc man said I.E. by validating the screen before allowing the user to move on.

PWG · Aug 17, 2019

so I tried this and it does not seem to work

Code:

Private Sub Product_Code_BeforeUpdate(Cancel As Integer)
    On Error GoTo ErrorHandler
    
    Refresh
    
End Sub

ErrorHandler:
' Resume statement will be hit when debugging
    If eh.LogError("Product_Code_AfterUpdate") Then Resume
End Sub

Dreamweaver · Aug 17, 2019

the before update is where you get a change to check records before they are updated I think I saw somebody tell you to use dlookup to check the table for a dup entry??

arnelgp · Aug 17, 2019

replace "yourProductTable" and "ProductFieldName" with correct table/fieldname.

Code:

Private Sub Product_Code_BeforeUpdate(Cancel As Integer)
Cancel = Nz(DCount("1","yourProductTable","ProductFieldName='" & [Product_Code] & "'"), 0) > 0
If Cancel
    Msgbox "Product code already in table."
    Me.Product_Code.Undo
End If
End Sub

PWG · Aug 17, 2019

I get a syntax error with the If Cancel highlighted red

PWG · Aug 17, 2019

Private Sub Product_Code_BeforeUpdate(Cancel As Integer)
Cancel = Nz(DCount("1", "Products", "Part Number='" & [Product_Code] & "'"), 0) > 0
If Cancel
MsgBox "Part Number already in table."
Me.Product_Code.Undo
End If
End Sub

MajP · Aug 17, 2019

There is no "Then"

arnelgp · Aug 17, 2019

and then, and add bracket to Part Number:

Code:

Private Sub Product_Code_BeforeUpdate(Cancel As Integer)
    Cancel = Nz(DCount("1", "Products", "[Part Number]='" & [Product_Code] & "'"), 0) > 0
    If Cancel Then
        MsgBox "Part Number already in table."
        Me.Product_Code.Undo
    End If
End Sub

PWG · Aug 17, 2019

You guys are legends I have be trying to do this for 4 days Thanks so much

refresh with code after update (1 Viewer)

Registered User.

Super Moderator

Registered User.

I’m here to help

AWF VIP

Super Moderator

AWF VIP

Immoderate Moderator

Registered User.

Well-known member

Registered User.

Well-known member

Registered User.

Well-known member

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

You've got your good things, and you've got mine.

..forever waiting... waiting for jellybean!

Registered User.

Similar threads

Users who are viewing this thread