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 |
#21
|
|||
|
|||
Check For Existing Record
Try moving the "False" outside the parentheses.
Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then strMessage = strMessage & "Store and Inspection Date already exist." End If -- _________ Sean Bailey "ridgerunner" wrote: Sorry about the "ord". I found out why the syntax error was popping up: I had to pull this "\#yyyy\-mm\-dd\#") & _ to the line above. I now do not received any syntax errors but the code is not catching a duplicate entry. This is how it looks now. I had to make a correction to the table name. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "ridgerunner" wrote: Yes, ord wrap is messing things up. "Then" is in the correct place in the property sheet or should I call it module, but this screen makes it look like it is one line down. At any rate, the code below is highlighted with I get the syntax error message. If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "Douglas J. Steele" wrote: You may have fallen victim of word-wrap. Then is supposed to be on the line above, after = False) That's odd, though. It looks correct when I look at what I posted to you. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "ridgerunner" wrote in message ... I am sorry I missed seeing this earlier. I have the part about making certain that a store and a date are entered covered in a command button on the form. I copied and pasted the code but I am getting a syntax error message and "IF" through the "THEN" are in red when I run compile. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Douglas J. Steele" wrote: Well, you'd put it in the same module as the rest of the code associated with the form. However, I agree with Klatuu that it probably makes more sense just to put the code in the form's BeforeUpdate event, as opposed to in the BeforeUpdate event of the two text boxes. Private Sub Form_BeforeUpdate(Cancel = True) Dim strMessage As String If IsNull(Me.StoreNo) Then strMessage = strMessage & "You must provide a Store Number." & vbCrLf End If If IsNull(Me.InspDate) Then strMessage = strMessage & "You must provide an Inspection Date." & vbCrLf End If If Len(strMessage) = 0 Then If IsNull(DLookup("StoreNo", "[YourTableName]", _ "[InspDate] = " & Format(Forms![YourFormName]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End If If Len(strMessage) 0 Then Cancel = True MsgBox strMessage, vbCritical End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ridgerunner" wrote in message ... 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 |
#22
|
|||
|
|||
Check For Existing Record
If I do that then I get the message
Compile Error: Expected: list separator or ) "Beetle" wrote: Try moving the "False" outside the parentheses. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then strMessage = strMessage & "Store and Inspection Date already exist." End If -- _________ Sean Bailey "ridgerunner" wrote: Sorry about the "ord". I found out why the syntax error was popping up: I had to pull this "\#yyyy\-mm\-dd\#") & _ to the line above. I now do not received any syntax errors but the code is not catching a duplicate entry. This is how it looks now. I had to make a correction to the table name. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "ridgerunner" wrote: Yes, ord wrap is messing things up. "Then" is in the correct place in the property sheet or should I call it module, but this screen makes it look like it is one line down. At any rate, the code below is highlighted with I get the syntax error message. If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "Douglas J. Steele" wrote: You may have fallen victim of word-wrap. Then is supposed to be on the line above, after = False) That's odd, though. It looks correct when I look at what I posted to you. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "ridgerunner" wrote in message ... I am sorry I missed seeing this earlier. I have the part about making certain that a store and a date are entered covered in a command button on the form. I copied and pasted the code but I am getting a syntax error message and "IF" through the "THEN" are in red when I run compile. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Douglas J. Steele" wrote: Well, you'd put it in the same module as the rest of the code associated with the form. However, I agree with Klatuu that it probably makes more sense just to put the code in the form's BeforeUpdate event, as opposed to in the BeforeUpdate event of the two text boxes. Private Sub Form_BeforeUpdate(Cancel = True) Dim strMessage As String If IsNull(Me.StoreNo) Then strMessage = strMessage & "You must provide a Store Number." & vbCrLf End If If IsNull(Me.InspDate) Then strMessage = strMessage & "You must provide an Inspection Date." & vbCrLf End If If Len(strMessage) = 0 Then If IsNull(DLookup("StoreNo", "[YourTableName]", _ "[InspDate] = " & Format(Forms![YourFormName]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End If If Len(strMessage) 0 Then Cancel = True MsgBox strMessage, vbCritical End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ridgerunner" wrote in message ... 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 |
#23
|
|||
|
|||
Check For Existing Record
Do you have double parentheses at the end (just before the = False)?
-- _________ Sean Bailey "ridgerunner" wrote: If I do that then I get the message Compile Error: Expected: list separator or ) "Beetle" wrote: Try moving the "False" outside the parentheses. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then strMessage = strMessage & "Store and Inspection Date already exist." End If -- _________ Sean Bailey "ridgerunner" wrote: Sorry about the "ord". I found out why the syntax error was popping up: I had to pull this "\#yyyy\-mm\-dd\#") & _ to the line above. I now do not received any syntax errors but the code is not catching a duplicate entry. This is how it looks now. I had to make a correction to the table name. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "ridgerunner" wrote: Yes, ord wrap is messing things up. "Then" is in the correct place in the property sheet or should I call it module, but this screen makes it look like it is one line down. At any rate, the code below is highlighted with I get the syntax error message. If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "Douglas J. Steele" wrote: You may have fallen victim of word-wrap. Then is supposed to be on the line above, after = False) That's odd, though. It looks correct when I look at what I posted to you. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "ridgerunner" wrote in message ... I am sorry I missed seeing this earlier. I have the part about making certain that a store and a date are entered covered in a command button on the form. I copied and pasted the code but I am getting a syntax error message and "IF" through the "THEN" are in red when I run compile. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Douglas J. Steele" wrote: Well, you'd put it in the same module as the rest of the code associated with the form. However, I agree with Klatuu that it probably makes more sense just to put the code in the form's BeforeUpdate event, as opposed to in the BeforeUpdate event of the two text boxes. Private Sub Form_BeforeUpdate(Cancel = True) Dim strMessage As String If IsNull(Me.StoreNo) Then strMessage = strMessage & "You must provide a Store Number." & vbCrLf End If If IsNull(Me.InspDate) Then strMessage = strMessage & "You must provide an Inspection Date." & vbCrLf End If If Len(strMessage) = 0 Then If IsNull(DLookup("StoreNo", "[YourTableName]", _ "[InspDate] = " & Format(Forms![YourFormName]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End If If Len(strMessage) 0 Then Cancel = True MsgBox strMessage, vbCritical End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ridgerunner" wrote in message ... 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 |
#24
|
|||
|
|||
Check For Existing Record
Below is what I have now and I am not receiving any error messages but it is
not trapping the duplicates either. Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strMessage As String If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Beetle" wrote: Do you have double parentheses at the end (just before the = False)? -- _________ Sean Bailey "ridgerunner" wrote: If I do that then I get the message Compile Error: Expected: list separator or ) "Beetle" wrote: Try moving the "False" outside the parentheses. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then strMessage = strMessage & "Store and Inspection Date already exist." End If -- _________ Sean Bailey "ridgerunner" wrote: Sorry about the "ord". I found out why the syntax error was popping up: I had to pull this "\#yyyy\-mm\-dd\#") & _ to the line above. I now do not received any syntax errors but the code is not catching a duplicate entry. This is how it looks now. I had to make a correction to the table name. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "ridgerunner" wrote: Yes, ord wrap is messing things up. "Then" is in the correct place in the property sheet or should I call it module, but this screen makes it look like it is one line down. At any rate, the code below is highlighted with I get the syntax error message. If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "Douglas J. Steele" wrote: You may have fallen victim of word-wrap. Then is supposed to be on the line above, after = False) That's odd, though. It looks correct when I look at what I posted to you. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "ridgerunner" wrote in message ... I am sorry I missed seeing this earlier. I have the part about making certain that a store and a date are entered covered in a command button on the form. I copied and pasted the code but I am getting a syntax error message and "IF" through the "THEN" are in red when I run compile. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Douglas J. Steele" wrote: Well, you'd put it in the same module as the rest of the code associated with the form. However, I agree with Klatuu that it probably makes more sense just to put the code in the form's BeforeUpdate event, as opposed to in the BeforeUpdate event of the two text boxes. Private Sub Form_BeforeUpdate(Cancel = True) Dim strMessage As String If IsNull(Me.StoreNo) Then strMessage = strMessage & "You must provide a Store Number." & vbCrLf End If If IsNull(Me.InspDate) Then strMessage = strMessage & "You must provide an Inspection Date." & vbCrLf End If If Len(strMessage) = 0 Then If IsNull(DLookup("StoreNo", "[YourTableName]", _ "[InspDate] = " & Format(Forms![YourFormName]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End If If Len(strMessage) 0 Then Cancel = True MsgBox strMessage, vbCritical End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ridgerunner" wrote in message ... 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 |
#25
|
|||
|
|||
Check For Existing Record
When you moved the "False" outside the parentheses, did you keep the
last parentheses, or was it deleted? It should look like; If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then ^(double) strMessage = strMessage & "Store and Inspection Date already exist." End If See the notation where it should be double parentheses I did a quick test on one of my apps. If the False is outside the parentheses it works, if it's inside nothing happens. -- _________ Sean Bailey "ridgerunner" wrote: Below is what I have now and I am not receiving any error messages but it is not trapping the duplicates either. Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strMessage As String If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Beetle" wrote: Do you have double parentheses at the end (just before the = False)? -- _________ Sean Bailey "ridgerunner" wrote: If I do that then I get the message Compile Error: Expected: list separator or ) "Beetle" wrote: Try moving the "False" outside the parentheses. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then strMessage = strMessage & "Store and Inspection Date already exist." End If -- _________ Sean Bailey "ridgerunner" wrote: Sorry about the "ord". I found out why the syntax error was popping up: I had to pull this "\#yyyy\-mm\-dd\#") & _ to the line above. I now do not received any syntax errors but the code is not catching a duplicate entry. This is how it looks now. I had to make a correction to the table name. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "ridgerunner" wrote: Yes, ord wrap is messing things up. "Then" is in the correct place in the property sheet or should I call it module, but this screen makes it look like it is one line down. At any rate, the code below is highlighted with I get the syntax error message. If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "Douglas J. Steele" wrote: You may have fallen victim of word-wrap. Then is supposed to be on the line above, after = False) That's odd, though. It looks correct when I look at what I posted to you. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "ridgerunner" wrote in message ... I am sorry I missed seeing this earlier. I have the part about making certain that a store and a date are entered covered in a command button on the form. I copied and pasted the code but I am getting a syntax error message and "IF" through the "THEN" are in red when I run compile. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Douglas J. Steele" wrote: Well, you'd put it in the same module as the rest of the code associated with the form. However, I agree with Klatuu that it probably makes more sense just to put the code in the form's BeforeUpdate event, as opposed to in the BeforeUpdate event of the two text boxes. Private Sub Form_BeforeUpdate(Cancel = True) Dim strMessage As String If IsNull(Me.StoreNo) Then strMessage = strMessage & "You must provide a Store Number." & vbCrLf End If If IsNull(Me.InspDate) Then strMessage = strMessage & "You must provide an Inspection Date." & vbCrLf End If If Len(strMessage) = 0 Then If IsNull(DLookup("StoreNo", "[YourTableName]", _ "[InspDate] = " & Format(Forms![YourFormName]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End If If Len(strMessage) 0 Then Cancel = True MsgBox strMessage, vbCritical End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ridgerunner" wrote in message ... 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 |
#26
|
|||
|
|||
Check For Existing Record
Line wrap screwed up my reply, so my notation is totally iout of place
but anyway, it should be )) before the = False -- _________ Sean Bailey "ridgerunner" wrote: Below is what I have now and I am not receiving any error messages but it is not trapping the duplicates either. Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strMessage As String If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Beetle" wrote: Do you have double parentheses at the end (just before the = False)? -- _________ Sean Bailey "ridgerunner" wrote: If I do that then I get the message Compile Error: Expected: list separator or ) "Beetle" wrote: Try moving the "False" outside the parentheses. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then strMessage = strMessage & "Store and Inspection Date already exist." End If -- _________ Sean Bailey "ridgerunner" wrote: Sorry about the "ord". I found out why the syntax error was popping up: I had to pull this "\#yyyy\-mm\-dd\#") & _ to the line above. I now do not received any syntax errors but the code is not catching a duplicate entry. This is how it looks now. I had to make a correction to the table name. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "ridgerunner" wrote: Yes, ord wrap is messing things up. "Then" is in the correct place in the property sheet or should I call it module, but this screen makes it look like it is one line down. At any rate, the code below is highlighted with I get the syntax error message. If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "Douglas J. Steele" wrote: You may have fallen victim of word-wrap. Then is supposed to be on the line above, after = False) That's odd, though. It looks correct when I look at what I posted to you. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "ridgerunner" wrote in message ... I am sorry I missed seeing this earlier. I have the part about making certain that a store and a date are entered covered in a command button on the form. I copied and pasted the code but I am getting a syntax error message and "IF" through the "THEN" are in red when I run compile. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Douglas J. Steele" wrote: Well, you'd put it in the same module as the rest of the code associated with the form. However, I agree with Klatuu that it probably makes more sense just to put the code in the form's BeforeUpdate event, as opposed to in the BeforeUpdate event of the two text boxes. Private Sub Form_BeforeUpdate(Cancel = True) Dim strMessage As String If IsNull(Me.StoreNo) Then strMessage = strMessage & "You must provide a Store Number." & vbCrLf End If If IsNull(Me.InspDate) Then strMessage = strMessage & "You must provide an Inspection Date." & vbCrLf End If If Len(strMessage) = 0 Then If IsNull(DLookup("StoreNo", "[YourTableName]", _ "[InspDate] = " & Format(Forms![YourFormName]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End If If Len(strMessage) 0 Then Cancel = True MsgBox strMessage, vbCritical End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ridgerunner" wrote in message ... 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 |
#27
|
|||
|
|||
Check For Existing Record
Where do you have this located? I copied the code exactly into the
BeforeUpdate event for the form and it does not trap the error. I would like to trap the error after data is entered into the Store and InspDate fields, but right now I would like to see it work anywhere. I may be off line for a while. "Beetle" wrote: When you moved the "False" outside the parentheses, did you keep the last parentheses, or was it deleted? It should look like; If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then ^(double) strMessage = strMessage & "Store and Inspection Date already exist." End If See the notation where it should be double parentheses I did a quick test on one of my apps. If the False is outside the parentheses it works, if it's inside nothing happens. -- _________ Sean Bailey "ridgerunner" wrote: Below is what I have now and I am not receiving any error messages but it is not trapping the duplicates either. Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strMessage As String If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Beetle" wrote: Do you have double parentheses at the end (just before the = False)? -- _________ Sean Bailey "ridgerunner" wrote: If I do that then I get the message Compile Error: Expected: list separator or ) "Beetle" wrote: Try moving the "False" outside the parentheses. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then strMessage = strMessage & "Store and Inspection Date already exist." End If -- _________ Sean Bailey "ridgerunner" wrote: Sorry about the "ord". I found out why the syntax error was popping up: I had to pull this "\#yyyy\-mm\-dd\#") & _ to the line above. I now do not received any syntax errors but the code is not catching a duplicate entry. This is how it looks now. I had to make a correction to the table name. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "ridgerunner" wrote: Yes, ord wrap is messing things up. "Then" is in the correct place in the property sheet or should I call it module, but this screen makes it look like it is one line down. At any rate, the code below is highlighted with I get the syntax error message. If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "Douglas J. Steele" wrote: You may have fallen victim of word-wrap. Then is supposed to be on the line above, after = False) That's odd, though. It looks correct when I look at what I posted to you. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "ridgerunner" wrote in message ... I am sorry I missed seeing this earlier. I have the part about making certain that a store and a date are entered covered in a command button on the form. I copied and pasted the code but I am getting a syntax error message and "IF" through the "THEN" are in red when I run compile. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Douglas J. Steele" wrote: Well, you'd put it in the same module as the rest of the code associated with the form. However, I agree with Klatuu that it probably makes more sense just to put the code in the form's BeforeUpdate event, as opposed to in the BeforeUpdate event of the two text boxes. Private Sub Form_BeforeUpdate(Cancel = True) Dim strMessage As String If IsNull(Me.StoreNo) Then strMessage = strMessage & "You must provide a Store Number." & vbCrLf End If If IsNull(Me.InspDate) Then strMessage = strMessage & "You must provide an Inspection Date." & vbCrLf End If If Len(strMessage) = 0 Then If IsNull(DLookup("StoreNo", "[YourTableName]", _ "[InspDate] = " & Format(Forms![YourFormName]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End If If Len(strMessage) 0 Then Cancel = True MsgBox strMessage, vbCritical End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ridgerunner" wrote in message ... 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 |
#28
|
|||
|
|||
Check For Existing Record
Here is the code I'm using, copied as is from my app;
If IsNull(DLookup("InvoiceNumber", "tblDeliveries", "InvoiceNumber = """ & _ Me.txtInvoiceNumber & """ AND DealerID = " & Me.cboDealerID)) = False Then MsgBox strMsg, vbOKOnly + vbExclamation, "Duplicate Invoice" Cancel = True Me.txtInvoiceNumber.Undo End If It does exactly what I want, as long as the "False" is outside the parentheses. I'm using it in the Before Update of a control, not the form, but that shouldn't matter as to whether the code works or not. Keep in mind, I'm only making suggestions for things you can try, Perhaps there is some other factor that is causing your criteria not to evaluate correctly. -- _________ Sean Bailey "ridgerunner" wrote: Where do you have this located? I copied the code exactly into the BeforeUpdate event for the form and it does not trap the error. I would like to trap the error after data is entered into the Store and InspDate fields, but right now I would like to see it work anywhere. I may be off line for a while. "Beetle" wrote: When you moved the "False" outside the parentheses, did you keep the last parentheses, or was it deleted? It should look like; If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then ^(double) strMessage = strMessage & "Store and Inspection Date already exist." End If See the notation where it should be double parentheses I did a quick test on one of my apps. If the False is outside the parentheses it works, if it's inside nothing happens. -- _________ Sean Bailey "ridgerunner" wrote: Below is what I have now and I am not receiving any error messages but it is not trapping the duplicates either. Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strMessage As String If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Beetle" wrote: Do you have double parentheses at the end (just before the = False)? -- _________ Sean Bailey "ridgerunner" wrote: If I do that then I get the message Compile Error: Expected: list separator or ) "Beetle" wrote: Try moving the "False" outside the parentheses. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then strMessage = strMessage & "Store and Inspection Date already exist." End If -- _________ Sean Bailey "ridgerunner" wrote: Sorry about the "ord". I found out why the syntax error was popping up: I had to pull this "\#yyyy\-mm\-dd\#") & _ to the line above. I now do not received any syntax errors but the code is not catching a duplicate entry. This is how it looks now. I had to make a correction to the table name. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "ridgerunner" wrote: Yes, ord wrap is messing things up. "Then" is in the correct place in the property sheet or should I call it module, but this screen makes it look like it is one line down. At any rate, the code below is highlighted with I get the syntax error message. If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "Douglas J. Steele" wrote: You may have fallen victim of word-wrap. Then is supposed to be on the line above, after = False) That's odd, though. It looks correct when I look at what I posted to you. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "ridgerunner" wrote in message ... I am sorry I missed seeing this earlier. I have the part about making certain that a store and a date are entered covered in a command button on the form. I copied and pasted the code but I am getting a syntax error message and "IF" through the "THEN" are in red when I run compile. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Douglas J. Steele" wrote: Well, you'd put it in the same module as the rest of the code associated with the form. However, I agree with Klatuu that it probably makes more sense just to put the code in the form's BeforeUpdate event, as opposed to in the BeforeUpdate event of the two text boxes. Private Sub Form_BeforeUpdate(Cancel = True) Dim strMessage As String If IsNull(Me.StoreNo) Then strMessage = strMessage & "You must provide a Store Number." & vbCrLf End If If IsNull(Me.InspDate) Then strMessage = strMessage & "You must provide an Inspection Date." & vbCrLf End If If Len(strMessage) = 0 Then If IsNull(DLookup("StoreNo", "[YourTableName]", _ "[InspDate] = " & Format(Forms![YourFormName]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End If If Len(strMessage) 0 Then Cancel = True MsgBox strMessage, vbCritical End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ridgerunner" wrote in message ... 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 |
#29
|
|||
|
|||
Check For Existing Record
Thanks for posting your code. After much comparison back and forth with
mine, I realized the MsgBox wasn't working used yours as a model. I think I need this in both controls BeforeUpdate event to make this work properly. What a long day. Private Sub InspDate_BeforeUpdate(Cancel As Integer) Dim strMessage As String If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then MsgBox strMessage & "Store and Inspection Date already exist. Press ESC and start over." End If End Sub "Beetle" wrote: Here is the code I'm using, copied as is from my app; If IsNull(DLookup("InvoiceNumber", "tblDeliveries", "InvoiceNumber = """ & _ Me.txtInvoiceNumber & """ AND DealerID = " & Me.cboDealerID)) = False Then MsgBox strMsg, vbOKOnly + vbExclamation, "Duplicate Invoice" Cancel = True Me.txtInvoiceNumber.Undo End If It does exactly what I want, as long as the "False" is outside the parentheses. I'm using it in the Before Update of a control, not the form, but that shouldn't matter as to whether the code works or not. Keep in mind, I'm only making suggestions for things you can try, Perhaps there is some other factor that is causing your criteria not to evaluate correctly. -- _________ Sean Bailey "ridgerunner" wrote: Where do you have this located? I copied the code exactly into the BeforeUpdate event for the form and it does not trap the error. I would like to trap the error after data is entered into the Store and InspDate fields, but right now I would like to see it work anywhere. I may be off line for a while. "Beetle" wrote: When you moved the "False" outside the parentheses, did you keep the last parentheses, or was it deleted? It should look like; If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then ^(double) strMessage = strMessage & "Store and Inspection Date already exist." End If See the notation where it should be double parentheses I did a quick test on one of my apps. If the False is outside the parentheses it works, if it's inside nothing happens. -- _________ Sean Bailey "ridgerunner" wrote: Below is what I have now and I am not receiving any error messages but it is not trapping the duplicates either. Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strMessage As String If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Beetle" wrote: Do you have double parentheses at the end (just before the = False)? -- _________ Sean Bailey "ridgerunner" wrote: If I do that then I get the message Compile Error: Expected: list separator or ) "Beetle" wrote: Try moving the "False" outside the parentheses. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then strMessage = strMessage & "Store and Inspection Date already exist." End If -- _________ Sean Bailey "ridgerunner" wrote: Sorry about the "ord". I found out why the syntax error was popping up: I had to pull this "\#yyyy\-mm\-dd\#") & _ to the line above. I now do not received any syntax errors but the code is not catching a duplicate entry. This is how it looks now. I had to make a correction to the table name. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "ridgerunner" wrote: Yes, ord wrap is messing things up. "Then" is in the correct place in the property sheet or should I call it module, but this screen makes it look like it is one line down. At any rate, the code below is highlighted with I get the syntax error message. If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "Douglas J. Steele" wrote: You may have fallen victim of word-wrap. Then is supposed to be on the line above, after = False) That's odd, though. It looks correct when I look at what I posted to you. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "ridgerunner" wrote in message ... I am sorry I missed seeing this earlier. I have the part about making certain that a store and a date are entered covered in a command button on the form. I copied and pasted the code but I am getting a syntax error message and "IF" through the "THEN" are in red when I run compile. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Douglas J. Steele" wrote: Well, you'd put it in the same module as the rest of the code associated with the form. However, I agree with Klatuu that it probably makes more sense just to put the code in the form's BeforeUpdate event, as opposed to in the BeforeUpdate event of the two text boxes. Private Sub Form_BeforeUpdate(Cancel = True) Dim strMessage As String If IsNull(Me.StoreNo) Then strMessage = strMessage & "You must provide a Store Number." & vbCrLf End If If IsNull(Me.InspDate) Then strMessage = strMessage & "You must provide an Inspection Date." & vbCrLf End If If Len(strMessage) = 0 Then If IsNull(DLookup("StoreNo", "[YourTableName]", _ "[InspDate] = " & Format(Forms![YourFormName]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End If If Len(strMessage) 0 Then Cancel = True MsgBox strMessage, vbCritical End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ridgerunner" wrote in message ... 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]=" & |
#30
|
|||
|
|||
Check For Existing Record
NO! It should be in the form's BeforeUpdate event, not the BeforeUpdate
event of the individual controls. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "ridgerunner" wrote in message ... Thanks for posting your code. After much comparison back and forth with mine, I realized the MsgBox wasn't working used yours as a model. I think I need this in both controls BeforeUpdate event to make this work properly. What a long day. Private Sub InspDate_BeforeUpdate(Cancel As Integer) Dim strMessage As String If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then MsgBox strMessage & "Store and Inspection Date already exist. Press ESC and start over." End If End Sub "Beetle" wrote: Here is the code I'm using, copied as is from my app; If IsNull(DLookup("InvoiceNumber", "tblDeliveries", "InvoiceNumber = """ & _ Me.txtInvoiceNumber & """ AND DealerID = " & Me.cboDealerID)) = False Then MsgBox strMsg, vbOKOnly + vbExclamation, "Duplicate Invoice" Cancel = True Me.txtInvoiceNumber.Undo End If It does exactly what I want, as long as the "False" is outside the parentheses. I'm using it in the Before Update of a control, not the form, but that shouldn't matter as to whether the code works or not. Keep in mind, I'm only making suggestions for things you can try, Perhaps there is some other factor that is causing your criteria not to evaluate correctly. -- _________ Sean Bailey "ridgerunner" wrote: Where do you have this located? I copied the code exactly into the BeforeUpdate event for the form and it does not trap the error. I would like to trap the error after data is entered into the Store and InspDate fields, but right now I would like to see it work anywhere. I may be off line for a while. "Beetle" wrote: When you moved the "False" outside the parentheses, did you keep the last parentheses, or was it deleted? It should look like; If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then ^(double) strMessage = strMessage & "Store and Inspection Date already exist." End If See the notation where it should be double parentheses I did a quick test on one of my apps. If the False is outside the parentheses it works, if it's inside nothing happens. -- _________ Sean Bailey "ridgerunner" wrote: Below is what I have now and I am not receiving any error messages but it is not trapping the duplicates either. Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strMessage As String If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspections]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Beetle" wrote: Do you have double parentheses at the end (just before the = False)? -- _________ Sean Bailey "ridgerunner" wrote: If I do that then I get the message Compile Error: Expected: list separator or ) "Beetle" wrote: Try moving the "False" outside the parentheses. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo])) = False Then strMessage = strMessage & "Store and Inspection Date already exist." End If -- _________ Sean Bailey "ridgerunner" wrote: Sorry about the "ord". I found out why the syntax error was popping up: I had to pull this "\#yyyy\-mm\-dd\#") & _ to the line above. I now do not received any syntax errors but the code is not catching a duplicate entry. This is how it looks now. I had to make a correction to the table name. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[tblDMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "ridgerunner" wrote: Yes, ord wrap is messing things up. "Then" is in the correct place in the property sheet or should I call it module, but this screen makes it look like it is one line down. At any rate, the code below is highlighted with I get the syntax error message. If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "Douglas J. Steele" wrote: You may have fallen victim of word-wrap. Then is supposed to be on the line above, after = False) That's odd, though. It looks correct when I look at what I posted to you. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "ridgerunner" wrote in message ... I am sorry I missed seeing this earlier. I have the part about making certain that a store and a date are entered covered in a command button on the form. I copied and pasted the code but I am getting a syntax error message and "IF" through the "THEN" are in red when I run compile. Private Sub Form_BeforeUpdate(Cancel As Integer) If IsNull(DLookup("StoreNo", "[DMInspections]", _ "[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End Sub "Douglas J. Steele" wrote: Well, you'd put it in the same module as the rest of the code associated with the form. However, I agree with Klatuu that it probably makes more sense just to put the code in the form's BeforeUpdate event, as opposed to in the BeforeUpdate event of the two text boxes. Private Sub Form_BeforeUpdate(Cancel = True) Dim strMessage As String If IsNull(Me.StoreNo) Then strMessage = strMessage & "You must provide a Store Number." & vbCrLf End If If IsNull(Me.InspDate) Then strMessage = strMessage & "You must provide an Inspection Date." & vbCrLf End If If Len(strMessage) = 0 Then If IsNull(DLookup("StoreNo", "[YourTableName]", _ "[InspDate] = " & Format(Forms![YourFormName]![InspDate], "\#yyyy\-mm\-dd\#") & _ " AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then strMessage = strMessage & "Store and Inspection Date already exist." End If End If If Len(strMessage) 0 Then Cancel = True MsgBox strMessage, vbCritical End If End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ridgerunner" wrote in message ... 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]=" & |
Thread Tools | |
Display Modes | |
|
|