A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Check For Existing Record



 
 
Thread Tools Display Modes
  #21  
Old May 22nd, 2008, 11:09 PM posted to microsoft.public.access.forms
Beetle
external usenet poster
 
Posts: 1,254
Default 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  
Old May 22nd, 2008, 11:17 PM posted to microsoft.public.access.forms
ridgerunner
external usenet poster
 
Posts: 118
Default 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  
Old May 22nd, 2008, 11:23 PM posted to microsoft.public.access.forms
Beetle
external usenet poster
 
Posts: 1,254
Default 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  
Old May 22nd, 2008, 11:30 PM posted to microsoft.public.access.forms
ridgerunner
external usenet poster
 
Posts: 118
Default 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  
Old May 22nd, 2008, 11:41 PM posted to microsoft.public.access.forms
Beetle
external usenet poster
 
Posts: 1,254
Default 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  
Old May 23rd, 2008, 12:04 AM posted to microsoft.public.access.forms
Beetle
external usenet poster
 
Posts: 1,254
Default 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  
Old May 23rd, 2008, 12:05 AM posted to microsoft.public.access.forms
ridgerunner
external usenet poster
 
Posts: 118
Default 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  
Old May 23rd, 2008, 12:45 AM posted to microsoft.public.access.forms
Beetle
external usenet poster
 
Posts: 1,254
Default 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  
Old May 23rd, 2008, 02:08 AM posted to microsoft.public.access.forms
ridgerunner
external usenet poster
 
Posts: 118
Default 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  
Old May 23rd, 2008, 02:19 AM posted to microsoft.public.access.forms
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:23 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.