Find Date Problem

  • Thread starter Thread starter DavidW
  • Start date Start date
D

DavidW

I am trying to find if an existing date has already been entered for a
record through the before update of a textbox. The name of the text box is
"pumpda", the user types in a preffered date to use. What I hope to do is to
stop a duplicated record before they continue and deal with that problem. I
have tried

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[pumpda] = #" & Me!pumpda & "#"
If rs.NoMatch Then
Exit Sub
Else
MsgBox "Message", vbCritical + vbOKOnly, "Duplicate Record Warning"
Cancel = True
DoCmd.Close acForm, ("dateset"), acSaveNo
DoCmd.OpenForm ("resumerecord")
Exit Sub
End If
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

What this does it looks at everything as not a match including date that
dont exist yet. I am tryin to catch only existing dates in the records.
Thanks
David
 
You don't tell us what isn't working or what error you're getting?

Assuming that your form's recordset contains all records that you want to
search, the only thing I find in your code that is suspect is this line:
Set rs = Me.Recordset.Clone

Change that to
Set rs = Me.RecordsetClone

Also, your code should "destroy" the rs object before the subroutine ends so
that you don't have any chance of the object persisting and perhaps causing
problems later when ACCESS doesn't want to quit. Insert
Set rs = Nothing
in each block before the Exit Sub or End Sub statement.
 
Im Sorry for not being clear enough, I'll try harder next time.
What is going on is that there are no errors prompted, but when you type a
date that is not in the record yet, it thinks that it is for some reason,
and I get my message that is only suppose to appear if thier is a match
found
Any Ideals?
I did take the period out of Me.Recordset.Clone and it did not help
Thanks for resonding,
I thought I was the only one who had to work on Christmas Eve!
 
Not working....we're all volunteers here!

Is the date in the control pumpda in the format of mm/dd/yyyy? If not, the
FindFirst may found erroneous matches.

Try this:

rs.FindFirst "[pumpda] = #" & Format(Me!pumpda, "mm/dd/yyyy") & "#"
 
Back
Top