Check if Record already exists and prevent duplicated Record

[TXNEWT]

>>Split from: How to verify if record already exist in another table <<

Thanks ZMBD!

Sorry for the delayed response. Building the string first worked like a champ.

Now that this part is working, any advise on how to stop the record from being added when this condition is not met. As it is now I get the message box, but the record still gets added to the table.

Justin


Thanks ZMBD, That works but only for the first record. After that it acts the same as when it was in the after update event.

What would cause this?

[zmbd]

basically, move this check to the before_update event. :)
BeforeUpdate Event [Access 2003 VBA Language Reference] old link; however, the method hasn't changed. :O)
See if the record is present, if so, then cancel=true and me.undo.

This can be done at the form (Form.BeforeUpdate Event (Access2013)), record, control levels.

[zmbd]

Sorry,
Should have said to use the "BeforeInsert" method.
This way, before the record is even inserted into the table the code should run, check for prior records, set CANCEL=True to cancel the insert. Follow the above link, very short example at the bottom of the page and the page has a decent explanation of the event.

[TXNEWT]

This sounds like its exactly what I need, but I don't see the BeforeInsert as an option in the property list for my test box?

Also, I am a bit confused on put all the code together.

Here is what I currently have, Can you help?

Private Sub UNITID_BeforeUpdate(Cancel As Integer)
Dim tmpBID As String
 
zstr = "UNITID= '" & Me!UNITID & "'"
 
tmpBID = Nz(DLookup("UNITID", "BARCODESUB", zstr), " ")
 
 
   If tmpBID = " " Then
      MsgBox "This Serial# does not exist in proir production manifest!", vbDefaultButton1
 
 
 
   End If
 
End Sub

[zmbd]

+ The before insert is a form level event:

Private Sub Form_BeforeInsert(Cancel As Integer) 

+ Just thought of a snag here, if the primary key is an autonumber field, then the Before_Insert event will
trigger before we get the data entered into the control.
- The autonumber field is going to increment as soon as the insert event succeeds - and will trigger the event before you have a chance to enter anything into the other fields. AFAIK, there's no way to stop this outside of the form_before_insert event - and that's the snag.


+ Let's go back to your code in the control's before_update event. You have about 95% of it done.

Private Sub UNITID_BeforeUpdate(Cancel As Integer)
    Dim zStr As String
On Error GoTo zerror
    zStr = "UNITID='" & Me!UNITID & "'"
    '
    If (DLookup("UNITID", "BARCODESUB", zStr) & "") = "" Then
        MsgBox "This Serial# does not exist in proir production manifest!"
        Me.Undo
        Cancel = True
    End If
zerrrtrn
    Exit Sub
zerror:
    MsgBox Prompt:="ErrS: " & Err.Source & vbCrLf & "ErrN: " & Err.Number & vbCrLf & "ErrD: " & Err.Description
Resume zerrrtrn
End Sub

This should stop any record inserts for the missing serial number. The above is a melding of code I already use in a production database (I check to see if the item has already been entered into the tracking database) and your code. This stops the insert; however, keep in mind that the autonumber will increment and is one reason why the autonumber shouldn't be used for anything meaningful outside of being a unique record identifier.

I also suggest that if you haven't done so, you set the option explicit as shown in the trouble shooting section of > Before Posting (VBA or SQL) Code:
<alt><F11> to open the VBA editor...
Menu>Tools>Options>
>Editor tab, UNCHECK "auto Syntax Check" you're vba code will still highlight if in error, just no annoying message boxes while you type!
>> Check mark the "Require Variable Declaration" - you will need to explictly declare variables used in the code.
*Everything else on this tab check mark*, I prefer the tab step to be either 2 or 4 spaces.
>The other tabs' default values are fine for most people.

This will help you with trouble shooting in the future :)