What's wrong with this code?

[BRC]

Hi all
I am using the following code to check for duplicate record and if it
exists I want to display it in the form. It finds the duplicate (if
it exists) as it should however when the sub ends the form is at the
1st record in the of the query. I would think that it would display
the record it found with the "bookmark". What am i missing? Thanks
for any help.

Code by Keri Hartwell (with slight modifications

Private Sub event_AfterUpdate()
Dim dcvar As String
dcvar = Me.[date] & " " & Me.event
Me.DupChk = dcvar

Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst (DupChk = dcvar)
If Not rst.NoMatch Then
MsgBox "Record Exists"
Cancel = True
Me.Undo
Me.Bookmark = rst.Bookmark

End Sub

 

[Dirk Goldgar]

In

Hi all
I am using the following code to check for duplicate record and if it
exists I want to display it in the form. It finds the duplicate (if
it exists) as it should however when the sub ends the form is at the
1st record in the of the query. I would think that it would display
the record it found with the "bookmark". What am i missing? Thanks
for any help.

Code by Keri Hartwell (with slight modifications

Private Sub event_AfterUpdate()
Dim dcvar As String
dcvar = Me.[date] & " " & Me.event
Me.DupChk = dcvar

Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst (DupChk = dcvar)
If Not rst.NoMatch Then
MsgBox "Record Exists"
Cancel = True
Me.Undo
Me.Bookmark = rst.Bookmark

End Sub

I suspect that this line:

rst.FindFirst (DupChk = dcvar)

.... isn't doing what you intend. The argument of FindFirst should be a
string that specifies filter criteria. Are you trying to find records
that have the same value for [date] and [event]? If so, you may want
something like this:

rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" & _
" AND " [event] = " & Me.event

That's assuming [event] is a numeric field. If it's a text field, then
try this:


rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" & _
" AND " [event] = " & Chr(34) & Me.event & Chr(34)

That does assume that [event] won't contain the double-quote character
("). If it may, we have to get fancier yet:

rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" & _
" AND " [event] = " & _
Chr(34) & Replace(Me.event, """", """""") & Chr(34)

 

[BRC]

In

Hi all
I am using the following code to check for duplicate record and if it
exists I want to display it in the form. It finds the duplicate (if
it exists) as it should however when the sub ends the form is at the
1st record in the of the query. I would think that it would display
the record it found with the "bookmark". What am i missing? Thanks
for any help.

Code by Keri Hartwell (with slight modifications

Private Sub event_AfterUpdate()
Dim dcvar As String
dcvar = Me.[date] & " " & Me.event
Me.DupChk = dcvar

Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.FindFirst (DupChk = dcvar)
If Not rst.NoMatch Then
MsgBox "Record Exists"
Cancel = True
Me.Undo
Me.Bookmark = rst.Bookmark

End Sub

I suspect that this line:

rst.FindFirst (DupChk = dcvar)

... isn't doing what you intend. The argument of FindFirst should be a
string that specifies filter criteria. Are you trying to find records
that have the same value for [date] and [event]? If so, you may want
something like this:

rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" & _
" AND " [event] = " & Me.event

That's assuming [event] is a numeric field. If it's a text field, then
try this:

rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" & _
" AND " [event] = " & Chr(34) & Me.event & Chr(34)

That does assume that [event] won't contain the double-quote character
("). If it may, we have to get fancier yet:

rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" & _
" AND " [event] = " & _
Chr(34) & Replace(Me.event, """", """""") & Chr(34)

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

dirk
Thanks for the input. The event field is a string and I actually
created a new text field in the table called dupchk and populated
with with date & " " & event. (I thought this would crate a string
field that i could compare directly.
I tried using the code you suggested (if event is a string)
rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" & _
" AND " [event] = " & Chr(34) & Me.event & Chr(34)
I am getting a syntax error message and the code turns red in the vb
editor.
I tried parsing it to
rst.FindFirst "[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" and it
doesn't like that either. me.date is a date field so I am not sure
what's going on. Using access 2003, Win XP Pro.

 

[Dirk Goldgar]

In

Thanks for the input. The event field is a string and I actually
created a new text field in the table called dupchk and populated
with with date & " " & event. (I thought this would crate a string
field that i could compare directly.
I tried using the code you suggested (if event is a string)
rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" & _
" AND " [event] = " & Chr(34) & Me.event & Chr(34)
I am getting a syntax error message and the code turns red in the vb
editor.
I tried parsing it to
rst.FindFirst "[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" and it
doesn't like that either. me.date is a date field so I am not sure
what's going on. Using access 2003, Win XP Pro.

I made a typo, is what's wrong: I left off the closing parenthesis of
the Format expression. It should have been:

"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#)" & _

[...] I actually
created a new text field in the table called dupchk and populated
with with date & " " & event. (I thought this would crate a string
field that i could compare directly.

I would not recommend that -- you are adding unnecessary data to each
record, and de-normalizing your database to do it, creating the
possibility that a record may, through some update, end up with a value
in DupChk that no longer matches the values in [date] and [event]. Yes,
the FindFirst call could be simpler, but that doesn't add enough benefit
to compensate for the drawbacks.

 

[BRC]

In

Thanks for the input. The event field is a string and I actually
created a new text field in the table called dupchk and populated
with with date & " " & event. (I thought this would crate a string
field that i could compare directly.
I tried using the code you suggested (if event is a string)
rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" & _
" AND " [event] = " & Chr(34) & Me.event & Chr(34)
I am getting a syntax error message and the code turns red in the vb
editor.
I tried parsing it to
rst.FindFirst "[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" and it
doesn't like that either. me.date is a date field so I am not sure
what's going on. Using access 2003, Win XP Pro.

I made a typo, is what's wrong: I left off the closing parenthesis of
the Format expression. It should have been:

"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#)" & _

[...] I actually
created a new text field in the table called dupchk and populated
with with date & " " & event. (I thought this would crate a string
field that i could compare directly.

I would not recommend that -- you are adding unnecessary data to each
record, and de-normalizing your database to do it, creating the
possibility that a record may, through some update, end up with a value
in DupChk that no longer matches the values in [date] and [event]. Yes,
the FindFirst call could be simpler, but that doesn't add enough benefit
to compensate for the drawbacks.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

Dirk
Again, thank you for the input, The paren at the date solved part of
the problem but I am still getting syntax error. It seems to be having
a problem with the event varible after the "and'. I noticed that
there are 7 double quotes in the code you provided which I believe may
be the problem but no matter where i put the 8th " I am getting
errors. Any thoughts
(puntucation in these statements is really my weak spot. I can't seem
to get a really sound grasp of it). Thanks again.
rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" & _
" AND " [event] = " & Chr(34) & Me.event & Chr(34)

 

[Dirk Goldgar]

In

Again, thank you for the input, The paren at the date solved part of
the problem but I am still getting syntax error. It seems to be having
a problem with the event varible after the "and'. I noticed that
there are 7 double quotes in the code you provided which I believe may
be the problem but no matter where i put the 8th " I am getting
errors. Any thoughts
(puntucation in these statements is really my weak spot. I can't seem
to get a really sound grasp of it). Thanks again.
rst.FindFirst _
"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#" & _
" AND " [event] = " & Chr(34) & Me.event & Chr(34)

Oops. Sorry again. It's another typo, I'm afraid <hangs head in
shame>. Try this:

"[date] = " & Format(Me.[date], "\#mm/dd/yyyy\#)" & _
" AND [event] = " & Chr(34) & Me.event & Chr(34)