Checking for Duplicate

[sburgess]

I am creating a Return Funds database. I have a table set up, which has a Primary Key, with an Auto-Number. I also have a Field named "JO", which is set up for no duplicates. I have set up a Form with this field, and what I want it to do, when a user enters a JO (Job Order) into this field, and that number already exists, I want it to pop-up a message, saying that it's a duplicate, and to enter another number. I have tried adding this code to the After_Update, OnLostFocus, and OnExit, but can not get it to work. Can I get some help? Thanks!

:banghead:



Private Sub JO___AfterUpdate()

Dim tmpID As Long

tmpID = Nz(DLookup("[JO]", "tblMasterReturnFunds", "[JO]=" & Me!JO), 0)
If tmpID <> 0 Then
MsgBox "This Job Order already exists in the database", vbOKOnly
End If

End Sub

[CreganTur]

I have tried adding this code to the After_Update, OnLostFocus, and OnExit, but can not get it to work.

Can you explain what you mean by "can not get it to work"? Is it throwing an error, or is it just not throwing the message when you enter in a value you know to be a duplicate?

You're going to want to use the BeforeUpdate event because this event takes place after the field has been edited, but before the edit has been written to the table.

If your JO is a number data type, then your dlookup is fine as it is written. But, if it is a String, then you need to wrap it in single quotes to tell the dlookup function that it's working with a string (see example code below). I also changed 'Me!JO' to 'Me.JO'- the reason is that it's better practice to use a dot (.) with the Me keyword because it allows you to use intellisense. If you use a bang (!) it turns intellisense off.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim check

check = DLookup("[JO]", "tblMasterReturnFunds", "[JO]='" & Me.JO & "'")
Select Case check
Case Null
MsgBox "This Job Order already exists in the database", vbOKOnly
Cancel = True 'this cancels the update
End Select

End Sub

[sburgess]

I will try to use the BeforeUpdate. I'm not getting anything when I add a JO that I know is a duplicate. No error, nothing, until I go to Save the Form, then it tells me it's a duplicate. I want it to do it as soon as I put the JO in and click out of the cell.

I will also change the dLookup to a string value, as I do believe that I have this field set up as text, not a numeric, as I have an input mask that has text in it.

Thanks for the information and I will let you know if it works!

: pray2:

[sburgess]

It's still doing the same. It does not give me an error, until I try to save the form. I noticed that you using the BeforeUpdate on the Form, not on the field. I tried it both ways, and it's still having problems. Now, it will not let me save the form, but I need to get a message as soon as a duplicate is put in the field.

The field 'JO', is a text field, because of an input mask that I have set up. It's set up for no duplicates.

Any help??? (my review will be contingent on this up and running. I have the majority of it, but this one is giving me a problem.)

:think:

[nepotist]

Hi,
I think the inital code for the after update event for the text box is right.As you want the user to know while he/she is updating data in the form.
One suggestion that I would give is one you find the duplicate set the focus to the test box again.
I dont understand what you mean by it is giving me error when you save the form.
If I understand it ... you are making the changes in the design view and then you save the form....and then u test it in the form view. is it poping the duplicate message when you save the form and before you go to form view??

[sburgess]

I am making the changes in Design View, saving the design, and then checking it in the Form view. I'm not getting any messages or errors, when I swith to Form View.

It's not giving me a message until I have the Save Form button that I created. Then it gives me a message that says (I uploaded a copy of the message). Then I close the form, without saving.

:bug:

[sburgess]

Additionally, I wouldn't mind the message that it's giving me when I try to save the form, but I want it to show up as soon as the duplicate entered. That way, the user doesn't get all the way to the end of the record, and then has to re-enter everything, because of a duplicate. And that is the only field that should not have any duplicates.

Here is a screen capture of the form. This is still a very much a work in progress.

[nepotist]

First Thing I would suggest is try to break the break the program at the code for the text box , and the step in to the code to see if it is actually executing it. That way you could actually see what is happening.
If you dont mind could you upload a sample for us to have a look at it.

Apart from that I have nothing else to suggest , As I am still new to this :D

[sburgess]

Here is the code that I have set up. It's set up at the form level, to run BeforeUpdate:


Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!DateModified = Now
Dim check

check = DLookup("[JO]", "tblMasterReturnFunds", "[JO]='" & Me.JO & "'")
Select Case check
Case Null
MsgBox "This Job Order already exists in the database", vbOKOnly
Cancel = True 'this cancels the update
End Select

End Sub