Event Procedure for DLookup

[Tango524]

I am trying to make sure no duplicate records are entered. I am not sure where to put my dlookup code. I have beforeupdate macros for some fields to copy the previous record fields to the new record fields. If I put this code in those fields as a beforeupdate event, I get this error: "Procedure Declaration does not match descritpion of event or procedure have the same name." If I put this code in another event procedure, it doesn't do anything. Please help! Thank you.

If (Not IsNull(DLookup("[EmployeeID]", "WSbyEmployee", "[EmployeeID] ='" & Me!EmployeeID.Int & "'&quot)) Then
MsgBox "Data Entry Error. You have entered a duplicate record. Please check the timesheet to be certain you have not already entered this information."
Cancel = True
Me!EmployeeID.Undo
End If

 

[Tango524]

I am using the exit event procedure, and now I get a data type mismatch error 3464. Here is my code:

If (Not IsNull(DLookup("[EntryID]", "WSbyEmployee", "[EntryID] = '&Me![EntryID]'&quot)) Then
MsgBox "Data Entry Error. You have entered a duplicate record. Please check the timesheet to be certain you have not already entered this information."
cancel = True
Me!EntryID.Undo
End If

Any help would be great! Thank you!

 

[psemianonymous]

I'd guess your error is in the DLookup itself:

DLookup("[EntryID]", "WSbyEmployee", "[EntryID] = '&Me![EntryID]'"))) 

'change to
DLookup("[EntryID]", "WSbyEmployee", "[EntryID] = '" & Me![EntryID] & "'")))

This is all assuming your "EntryID" is a text field. If it's a number field, remove the single quotes.

 

[jbehrne]

Hi,

I also think that the issue is with your DLookup itself. In my opinion the best place to run this code would be the "After Update" of your "EntryID" field on your form. That way the check is run right after the user sets this value and it can be checked immediately. Also, try putting this in the code:

Me!EntryID.SetFocus

after your Me!EndtryID.Undo to set the focus of the form on your field when the user exits the msgbox.

HTH,

jbehrne

 

[Tango524]

Is there a way with Dlookup to check the entire record? For example, it is okay to have duplicate values of EID and duplicate values of TID separately, but it is not okay to have duplicate values of EID and TID together. I am checking the duplicate values by seeing if dlookup returns null. Would this code be valid?

= DLookup("[EntryID]", "WSbyEmployee", "'[EntryID]=Forms![DataEntryWorksheetbyEmployee]![EntryID]' & '[EmployeeID]=Forms![DataEntryWorksheetbyEmployee]![EmployeeID]' & '[Date]=Forms![DataEntryWorksheetbyEmployee]![Date]' & '[TaskCode]=Forms![DataEntryWorksheetbyEmployee]![TaskCode]' & '[TypeCode]=Forms![DataEntryWorksheetbyEmployee]![TypeCode]' & '[StartingDocID]=Forms![DataEntryWorksheetbyEmployee]![StartingDocID]' & '[EndingDocID]=Forms![DataEntryWorksheetbyEmployee]![EndingDocID]' & '[HoursIn]=Forms![DataEntryWorksheetbyEmployee]![HoursIn]' & '[MinutesIn]=Forms![DataEntryWorksheetbyEmployee]![MinutesIn]' & '[HoursOut]=Forms![DataEntryWorksheetbyEmployee]![HoursOut]' & '[MinutesOut]=Forms![DataEntryWorksheetbyEmployee]![MinutesOut]'&quot


Thank you so much for your help!

 

[hdgirl]

If (Not IsNull(DLookup("[WorksOrderNumber]", "WSbyWorkRequest", "[WorksOrderNumber] ='" & Me!WorksOrderNumber.Int & "'&quot)) Then
MsgBox "Data Entry Error"
Cancel = True
Me!WorksOrderNumber.Undo
End If

I have tried the above code and it gets stuck on the cancel = true line of code saying variable not defined any ideas where i am going wrong?
CJB

 

[judgehopkins]

Here is code I have used to prevent a duplicate order number being entered into a control on the form known as OrderNumber. I always put a lot of comments in my code; I am not sure that the formatting in this message shows all my comments correctly, so be careful when copying and pasting this code.

Private Sub OrderNumber_AfterUpdate()
'This prevents a duplicate order number; OrderNumber is a long integer
Dim varTemp As Variant
'Initializes variant
    varTemp = DLookup("[OrderNumber]", "Order", "[OrderNumber] = Forms![Order]![OrderNumber]")
'Checks to see if this order number has been used before
    If varTemp = Forms!Order!OrderNumber Then
'If order number has been used before
            Me!Refinance.SetFocus
            Me!OrderNumber.SetFocus
'An Access bug makes you set the focus to the prior control on a form
'before you can re-set the focus to the control you want it on
            Me.Undo
'Clears OrderNumber control on the form
            MsgBox "You have entered a duplicate job number.  Enter another.", vbOKOnly, "Duplicate Job Number Found"
    End If
End Sub

Judge Hopkins

"...like hunting skunks underwater...."
John Steinbeck, The Grapes of Wrath

 

[hdgirl]

I don't want to prevent a duplicate order just a message to tell the user that the number they have entered doesn't exist CJB

 

[judgehopkins]

hdgirl, I was responding to the person who started this thread (i.e., Tango524).

However, if you will notice, the first part of my code checks to see if an order number has been used. You could use that and then do whatever you needed to do:

Private Sub OrderNumber_AfterUpdate()
'This prevents a duplicate order number; OrderNumber is a long integer
Dim varTemp As Variant
'Initializes variant
varTemp = DLookup("[OrderNumber]", "Order", "[OrderNumber] = Forms![Order]![OrderNumber]&quot
'Checks to see if this order number has been used before
If varTemp = Forms!Order!OrderNumber Then
'If order number has been used before
'Your stuff here
End If
End Sub

Judge Hopkins

"...like hunting skunks underwater...."
John Steinbeck, The Grapes of Wrath

 

[danvlas]

Tango524: check if your exit event procedure has the syntax:

Sub Object_Exit(Cancel As Integer)

I have a feeling you don't have the correct argument there, that's why you get that error message. It has nothing to do with DLookup syntax...

Good luck,




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[hdgirl]

Thanks Daniel

But where do i place the Sub Object_Exit(Cancel As Integer)? because i placed it after my End IF & it wasn't happy!

CJB