Duplicate date/time checking

[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

 

[Douglas J. Steele]

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.

 

[SJJ via AccessMonster.com]

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

 

[Douglas J Steele]

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.

 

[SJJ via AccessMonster.com]

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.

 

[Douglas J Steele]

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.

 

[SJJ via AccessMonster.com]

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