Duplicate date/time checking

  • Thread starter Thread starter SJJ via AccessMonster.com
  • Start date Start date
S

SJJ via AccessMonster.com

i have a field on a table called booking and the fields are startdate and
starttime.
i put this code onto the form which contains both of these fields.
but it is not working..any suggestion. i want to check for duplicate entering
and prevents from enerting another duplicate date and time.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "[StartDate] = '" & Me!txtStartDate & "' And [StartTime] = '" &
Me!txtStartTime & "'"
If Not rs.NoMatch Then
MsgBox "Date/Time used"
Cancel = True
End If
End Sub

thanks in advance
 
Dates need to be delimited with #, not ' (and need to be in mm/dd/yyyy
format, regardless of what your Regional Settings may have as the Short
Date*).

Try:

rs.FindFirst "[StartDate] = " & Format(Me!txtStartDate, "\#mm\/dd\/yyyy\#")
& " And [StartTime] = " &
Format(Me!txtStartTime, "\#mm\/dd\/yyyy\#")

* Okay, this isn't strictly true. Access will work with any unambiguous
format, such as yyyy-mm-dd or dd mmm yyyy. The issue is that if your user
has set their Short Date format to dd/mm/yyyy, it won't work properly for
the first 12 days of each month.
 
starttime is a sring i modified it and still it is not working..
it works sometime and sometimes it says syntax error in experssion

and hightlight this line

rs.FindFirst "[StartDate] = '" & Format(Me!txtStartDate, "\#mm\/dd\/yyyy\#")
& "And [StartTime] = '" & Me!txtStartTime & "'"

any suggestions

thanks
 
You didn't remove the single quotes from your code. You also need to delimit
times with #:

rs.FindFirst "[StartDate] = " & Format(Me!txtStartDate, "\#mm\/dd\/yyyy\#")
& " And [StartTime] = " & Format(Me!txtStartTime, "\#hh\:nn\:ss\#")

Make sure there's a space between the quote and the keyword And.

Why do you have StartDate and StartTime as two separate fields? It's far
better to store them as a single StartDateTime.
 
the time is not a actual time. it like "morning", "afternnon" like that..so
no need limitation for it..

i have to use two fields because on the same date they can choose diferent
times..so must need two fields.
i did pu space between the quote and the keyword but it is not appears here..
but i have space in my actual code.

any ideas y not working.
 
Paste exactly what you've got. I'd pointed out that you had an unnecessary
single quote in what you posted before, and you didn't comment on that.
 
it's works but every time i start the form it says the "Date/Time used"..i
have to close the form then open again then it works..

any suggestion.

thanks
 
Back
Top