Checking for duplicates

[hardrock]

Ok guys, I have another problem...last one for today. When i click save on my form, it will check for nulls or 0 entries on 3 text boxes... I'm using fruit in this example for arguments sake and it works ok... I'd like to introduce testing of duplicate entries somewhere in this code, so on each textbox, i've added an afterupdate routine to test for duplicate entries within my table (see below). This seems to work on a one off basis but wont work say if i try and edit apples whilst stuck between pears and grapes!!! What is the best way to test for duplicates in this instance??

Private Sub SaveRecord_Click()

If IsNull(apples) Or apples = 0 Then
MsgBox "Please Enter Number of apples", vbInformation, ""
apples.SetFocus

ElseIf IsNull(pears) Or pears = 0 Then
MsgBox "Please Enter number of pears", vbInformation, ""
pears.SetFocus

ElseIf IsNull(grapes) Or grapes = 0 Then
MsgBox "Please Enter number of grapes", vbInformation, ""
grapes.SetFocus

Else

[myapples] = apples
[mypears] = pears
[mygrapes] = grapes

end if




Private Sub apples_AfterUpdate()

Dim testfruit As String

testfruit = Nz(DLookup("[myapples]", "RECORDS", "[myapples]='" & Me.apples & "'"))

If IsNull(testfruit) Then

Else
MsgBox "A quantity of this fruit already exists on database", vbInformation, ""
apples.SetFocus
exit
End If

<Same afterupdate routines as above for pears and grapes>

 

[RuralGuy]

Put your control validation code in the BeforeUpdate event of the control instead of the AfterUpdate event. Then set Cancel = True to hold the focus in the control.

 

[missinglinq]

Allan's advice is spot on, as usual. You also might have a problem with your code for checking for empty controls. Using SaveRecord_Click() to do this validation is fine if your users always save a record using this button, but what if they enter data, don't click on SaveRecord, but instead move to another record or simply close the form? Your validation code is then bypassed! Better to put this kind of code in the Form_BeforeUpdate event and, as Allan said, use Cancel = True.

Linq

 

[hardrock]

re:

Thankyou muchly, for your replies gents. The control code is now in the beforeupdate as below, but where do i set or put the cancel = true?? never done that before

Private Sub apples_BeforeUpdate()

Dim testfruit As String

testfruit = Nz(DLookup("[myapples]", "RECORDS", "[myapples]='" & Me.apples & "'"))

If IsNull(testfruit) Then

Else
MsgBox "A quantity of this fruit already exists on database", vbInformation, ""
apples.SetFocus
exit
End If
end sub

 

[RuralGuy]

Try:

Private Sub apples_BeforeUpdate(Cancel As Integer)

   If Not IsNull(DLookup("[myapples]", "RECORDS", "[myapples]='" & Me.apples & "'")) Then
      MsgBox "A quantity of this fruit already exists on database", vbInformation
      Cancel = True
   End If
   
End Sub

 

[hardrock]

re:

Allan, you really are an Access guru! i am very greatful for your help.

 

[RuralGuy]

Thanks for the compliment and I'm glad I could help. Linq is much better that I am but sometimes I type faster.

 

[hardrock]

re:

Try:

Private Sub apples_BeforeUpdate(Cancel As Integer)

   If Not IsNull(DLookup("[myapples]", "RECORDS", "[myapples]='" & Me.apples & "'")) Then
      MsgBox "A quantity of this fruit already exists on database", vbInformation
      Cancel = True
   End If
   
End Sub

Allan, if you get this message, somewhere in this code, i want to return the original value of apples back into it's textbox on the form should a duplicate be found. Just after the msgbox i put something like apples = (DLookup("[myapples]", "RECORDS", "[myapples]='" & Me.apples & "'")) but i get a run time error when i do this. It says the macro or function set to the beforeupdate or validationrule property for this field is preventing myapp saving data in this field. Any ideas?

 

[RuralGuy]

Try:

Private Sub apples_BeforeUpdate(Cancel As Integer)

   If Not IsNull(DLookup("[myapples]", "RECORDS", "[myapples]='" & Me.apples & "'")) Then
      MsgBox "A quantity of this fruit already exists on database", vbInformation
      Cancel = True
      Me.apples.UnDo
   End If
   
End Sub

 

[hardrock]

re:

It doesn't return it back allan? but atleast the code doesn't bum out. Any other ideas?

 

[RuralGuy]

That should work. Change it to:

Private Sub apples_BeforeUpdate(Cancel As Integer)

   If Not IsNull(DLookup("[myapples]", "RECORDS", "[myapples]='" & Me.apples & "'")) Then
      MsgBox "A quantity of this fruit already exists on database", vbInformation
      Cancel = True
      MsgBox "Old Value is [" & Me.apples.OldValue & "]"
      Me.apples.UnDo
   End If
   
End Sub

 

[RuralGuy]

This *is* a bound control, right? The ControlSource of the control in the [apples] field of your RecordSource?

 

[hardrock]

nope

It doesn't allan, it still keeps the newly typed value in, and doesn't return to the original value. It does put the focus in the textbox though? Its a unbound textbox

 

[RuralGuy]

UnDo does *not* work on unbound controls. Are you just interested in clearing the TextBox?

 

[hardrock]

Maybe I need to go back a step!

When the form loads, the value for the textbox apples on the form is got by

apples = DLookup("myapples", "Query1")

Query1 is basically a filtered record from the main table RECORDS.

I basically need this line of code (or similar) to work just before the cancel = true. if i add the line above i get this error message "the macro or function set to the beforeupdate or validationrule property for this field is preventing myapp saving data in this field"

For e.g.

Private Sub apples_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[myapples]", "RECORDS", "[myapples]='" & Me.apples & "'")) Then
MsgBox "A quantity of this fruit already exists on database", vbInformation

me.apples = DLookup("myapples", "Query1") ' get error message with this line of code

Cancel = True

End If

End Sub

 

[RuralGuy]

Why are you not using bound forms?