If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Check For Existing Record
I have a unique index set on two fields in my table, InspDate and StoreNo. I
am trying to trap the error of attempting to add a duplicate by having the code below in the LostFocus Event for the InspDate. I am running around in circles. Can someone please help? Private Sub InspDate_LostFocus() If DMInspections.StoreNo = True Then ElseIf DMInspDet.InspDate = True Then MsgBox "Store and Inspection Date already exist. Please correct" Me.InspDate.SetFocus Else: Me.DMnameID.SetFocus Exit Sub End If End Sub tia ridgerunner |
#2
|
|||
|
|||
Check For Existing Record
Usually verification code is placed in the BeforeUpdate event of a control
using a Domain function to look for duplicates. ridgerunner wrote: I have a unique index set on two fields in my table, InspDate and StoreNo. I am trying to trap the error of attempting to add a duplicate by having the code below in the LostFocus Event for the InspDate. I am running around in circles. Can someone please help? Private Sub InspDate_LostFocus() If DMInspections.StoreNo = True Then ElseIf DMInspDet.InspDate = True Then MsgBox "Store and Inspection Date already exist. Please correct" Me.InspDate.SetFocus Else: Me.DMnameID.SetFocus Exit Sub End If End Sub tia ridgerunner -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Check For Existing Record
Here is my solution for multiple field duplication. I first join the two
fields in a query. For your example I would do this in a query. NoDuplicates:[InspDate]&""&[StoreNo] Now that you have one field named NoDuplicates to deal with, in the "Before Update" event of InspDate you would use this code If not IsNull(DLookup("[NoDuplicates]", (YourQueryName), "[NoDuplicates]=" & Forms(YourFormName)![InspDate])) Then MsgBox "Store and Inspection Date already exist. Please correct", vbCritical, "Duplicate Entry" End If Exit Sub Dont know if this will help but this works for me. "ridgerunner" wrote: I have a unique index set on two fields in my table, InspDate and StoreNo. I am trying to trap the error of attempting to add a duplicate by having the code below in the LostFocus Event for the InspDate. I am running around in circles. Can someone please help? Private Sub InspDate_LostFocus() If DMInspections.StoreNo = True Then ElseIf DMInspDet.InspDate = True Then MsgBox "Store and Inspection Date already exist. Please correct" Me.InspDate.SetFocus Else: Me.DMnameID.SetFocus Exit Sub End If End Sub tia ridgerunner |
#4
|
|||
|
|||
Check For Existing Record
Can you please explain "Domain Function".
"ruralguy via AccessMonster.com" wrote: Usually verification code is placed in the BeforeUpdate event of a control using a Domain function to look for duplicates. ridgerunner wrote: I have a unique index set on two fields in my table, InspDate and StoreNo. I am trying to trap the error of attempting to add a duplicate by having the code below in the LostFocus Event for the InspDate. I am running around in circles. Can someone please help? Private Sub InspDate_LostFocus() If DMInspections.StoreNo = True Then ElseIf DMInspDet.InspDate = True Then MsgBox "Store and Inspection Date already exist. Please correct" Me.InspDate.SetFocus Else: Me.DMnameID.SetFocus Exit Sub End If End Sub tia ridgerunner -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
Check For Existing Record
Either the Dlookup() or DCount() functions as the next post shows.
ridgerunner wrote: Can you please explain "Domain Function". Usually verification code is placed in the BeforeUpdate event of a control using a Domain function to look for duplicates. [quoted text clipped - 16 lines] tia ridgerunner -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200805/1 |
#6
|
|||
|
|||
Check For Existing Record
Why would you concatenate the fields? (And you've forgotten to concatenate
the StoreNo into the argument you're passing to DLookup) As well, you need to check in the BeforeUpdate of both InspDate and StoreNo, since you can't be sure what order the fields will be filled in. Use a generic function like: Function DuplicateValue() As Boolean If IsNull(Format(Forms![YourFormName]![InspDate]) And _ IsNull(Forms![YourFormName]![StoreNo]) = False Then DuplicateValue = (IsNull(DLookup("StoreNo", "[YourTableName]", _ "[InspDate] = " & Format(Forms![YourFormName]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) End If End Function You can then call that function in the BeforeUpdate event of both controls: Private Sub InspDate_BeforeUpdate(Cancel As Integer) If DuplicateValue() = True Then MsgBox "Store and Inspection Date already exist. Please correct", _ vbCritical, "Duplicate Entry" Cancel = True End If End Sub Private Sub StoreNo_BeforeUpdate(Cancel As Integer) If DuplicateValue() = True Then MsgBox "Store and Inspection Date already exist. Please correct", _ vbCritical, "Duplicate Entry" Cancel = True End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Ryan Tisserand" wrote in message ... Here is my solution for multiple field duplication. I first join the two fields in a query. For your example I would do this in a query. NoDuplicates:[InspDate]&""&[StoreNo] Now that you have one field named NoDuplicates to deal with, in the "Before Update" event of InspDate you would use this code If not IsNull(DLookup("[NoDuplicates]", (YourQueryName), "[NoDuplicates]=" & Forms(YourFormName)![InspDate])) Then MsgBox "Store and Inspection Date already exist. Please correct", vbCritical, "Duplicate Entry" End If Exit Sub Dont know if this will help but this works for me. "ridgerunner" wrote: I have a unique index set on two fields in my table, InspDate and StoreNo. I am trying to trap the error of attempting to add a duplicate by having the code below in the LostFocus Event for the InspDate. I am running around in circles. Can someone please help? Private Sub InspDate_LostFocus() If DMInspections.StoreNo = True Then ElseIf DMInspDet.InspDate = True Then MsgBox "Store and Inspection Date already exist. Please correct" Me.InspDate.SetFocus Else: Me.DMnameID.SetFocus Exit Sub End If End Sub tia ridgerunner |
#7
|
|||
|
|||
Check For Existing Record
I would recommend using the form BeforeUpdate event.
There is no certainty that any control will get the focus. When more than one field, thus more than one control, the testing is more complex and likely to be less accurate. -- Dave Hargis, Microsoft Access MVP "ruralguy via AccessMonster.com" wrote: Usually verification code is placed in the BeforeUpdate event of a control using a Domain function to look for duplicates. ridgerunner wrote: I have a unique index set on two fields in my table, InspDate and StoreNo. I am trying to trap the error of attempting to add a duplicate by having the code below in the LostFocus Event for the InspDate. I am running around in circles. Can someone please help? Private Sub InspDate_LostFocus() If DMInspections.StoreNo = True Then ElseIf DMInspDet.InspDate = True Then MsgBox "Store and Inspection Date already exist. Please correct" Me.InspDate.SetFocus Else: Me.DMnameID.SetFocus Exit Sub End If End Sub tia ridgerunner -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
Check For Existing Record
I am getting this message "Runtime error 424" "Object Required". The second
and third lines, below, are highlighted in the debug screen. Private Sub InspDate_BeforeUpdate(Cancel As Integer) If Not IsNull(DLookup("[NoDuplicates]", (qryFrmAddDMInsp), "[NoDuplicates]=" & frmAddDMInspections![InspDate])) Then MsgBox "Store and Inspection Date already exist. Please correct", vbCritical, "Duplicate Entry" End If Exit Sub End Sub "Ryan Tisserand" wrote: Here is my solution for multiple field duplication. I first join the two fields in a query. For your example I would do this in a query. NoDuplicates:[InspDate]&""&[StoreNo] Now that you have one field named NoDuplicates to deal with, in the "Before Update" event of InspDate you would use this code If not IsNull(DLookup("[NoDuplicates]", (YourQueryName), "[NoDuplicates]=" & Forms(YourFormName)![InspDate])) Then MsgBox "Store and Inspection Date already exist. Please correct", vbCritical, "Duplicate Entry" End If Exit Sub Dont know if this will help but this works for me. "ridgerunner" wrote: I have a unique index set on two fields in my table, InspDate and StoreNo. I am trying to trap the error of attempting to add a duplicate by having the code below in the LostFocus Event for the InspDate. I am running around in circles. Can someone please help? Private Sub InspDate_LostFocus() If DMInspections.StoreNo = True Then ElseIf DMInspDet.InspDate = True Then MsgBox "Store and Inspection Date already exist. Please correct" Me.InspDate.SetFocus Else: Me.DMnameID.SetFocus Exit Sub End If End Sub tia ridgerunner |
#9
|
|||
|
|||
Check For Existing Record
You are missing quote marks------------v---------------------v
If Not IsNull(DLookup("[NoDuplicates]", (qryFrmAddDMInsp), "[NoDuplicates]=" & frmAddDMInspections![InspDate])) Then Should be: If Not IsNull(DLookup("[NoDuplicates]", "qryFrmAddDMInsp", "[NoDuplicates]=" & frmAddDMInspections![InspDate])) Then -- Dave Hargis, Microsoft Access MVP "ridgerunner" wrote: I am getting this message "Runtime error 424" "Object Required". The second and third lines, below, are highlighted in the debug screen. Private Sub InspDate_BeforeUpdate(Cancel As Integer) If Not IsNull(DLookup("[NoDuplicates]", (qryFrmAddDMInsp), "[NoDuplicates]=" & frmAddDMInspections![InspDate])) Then MsgBox "Store and Inspection Date already exist. Please correct", vbCritical, "Duplicate Entry" End If Exit Sub End Sub "Ryan Tisserand" wrote: Here is my solution for multiple field duplication. I first join the two fields in a query. For your example I would do this in a query. NoDuplicates:[InspDate]&""&[StoreNo] Now that you have one field named NoDuplicates to deal with, in the "Before Update" event of InspDate you would use this code If not IsNull(DLookup("[NoDuplicates]", (YourQueryName), "[NoDuplicates]=" & Forms(YourFormName)![InspDate])) Then MsgBox "Store and Inspection Date already exist. Please correct", vbCritical, "Duplicate Entry" End If Exit Sub Dont know if this will help but this works for me. "ridgerunner" wrote: I have a unique index set on two fields in my table, InspDate and StoreNo. I am trying to trap the error of attempting to add a duplicate by having the code below in the LostFocus Event for the InspDate. I am running around in circles. Can someone please help? Private Sub InspDate_LostFocus() If DMInspections.StoreNo = True Then ElseIf DMInspDet.InspDate = True Then MsgBox "Store and Inspection Date already exist. Please correct" Me.InspDate.SetFocus Else: Me.DMnameID.SetFocus Exit Sub End If End Sub tia ridgerunner |
#10
|
|||
|
|||
Check For Existing Record
Thank you. Can you please tell me where I need to put the Function?
"Douglas J. Steele" wrote: Why would you concatenate the fields? (And you've forgotten to concatenate the StoreNo into the argument you're passing to DLookup) As well, you need to check in the BeforeUpdate of both InspDate and StoreNo, since you can't be sure what order the fields will be filled in. Use a generic function like: Function DuplicateValue() As Boolean If IsNull(Format(Forms![YourFormName]![InspDate]) And _ IsNull(Forms![YourFormName]![StoreNo]) = False Then DuplicateValue = (IsNull(DLookup("StoreNo", "[YourTableName]", _ "[InspDate] = " & Format(Forms![YourFormName]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) End If End Function You can then call that function in the BeforeUpdate event of both controls: Private Sub InspDate_BeforeUpdate(Cancel As Integer) If DuplicateValue() = True Then MsgBox "Store and Inspection Date already exist. Please correct", _ vbCritical, "Duplicate Entry" Cancel = True End If End Sub Private Sub StoreNo_BeforeUpdate(Cancel As Integer) If DuplicateValue() = True Then MsgBox "Store and Inspection Date already exist. Please correct", _ vbCritical, "Duplicate Entry" Cancel = True End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Ryan Tisserand" wrote in message ... Here is my solution for multiple field duplication. I first join the two fields in a query. For your example I would do this in a query. NoDuplicates:[InspDate]&""&[StoreNo] Now that you have one field named NoDuplicates to deal with, in the "Before Update" event of InspDate you would use this code If not IsNull(DLookup("[NoDuplicates]", (YourQueryName), "[NoDuplicates]=" & Forms(YourFormName)![InspDate])) Then MsgBox "Store and Inspection Date already exist. Please correct", vbCritical, "Duplicate Entry" End If Exit Sub Dont know if this will help but this works for me. "ridgerunner" wrote: I have a unique index set on two fields in my table, InspDate and StoreNo. I am trying to trap the error of attempting to add a duplicate by having the code below in the LostFocus Event for the InspDate. I am running around in circles. Can someone please help? Private Sub InspDate_LostFocus() If DMInspections.StoreNo = True Then ElseIf DMInspDet.InspDate = True Then MsgBox "Store and Inspection Date already exist. Please correct" Me.InspDate.SetFocus Else: Me.DMnameID.SetFocus Exit Sub End If End Sub tia ridgerunner |
Thread Tools | |
Display Modes | |
|
|