Syntax error on vb code no one has been able to solve this yet. i am new to this and

[R2D2]

Ihave tryed many different ways to get this to work biyt am still getting sytax errors.
Can any find the problem or come up wiyj an alternitive?

Private Sub addrecord_Click()
On Error GoTo Err_addrecord_Click

If Dcount ("[Reservation Date] = " & ReservationDate & " AND "[Asset ID] = " & AssetID & " AND " & TimeBookedIn & " BETWEEN [Time Booked Out] AND [Time Booked In] OR " & TimeBookedOut & " BETWEEN [Time Booked Out] AND [Time Booked In])") > 0 Then
'MsgBox "Dulpicte Record, change asset ID or booking time"
Else
DoCmd.GoToRecord , , acNewRec


End If




Exit_addrecord_Click:
Exit Sub

Err_addrecord_Click:
MsgBox Err.Description
Resume Exit_addrecord_Click

End Sub



I want to prevent any double booking can any on help

I am a beginner in access so go easy on me ļ

For example:

Customer 1 wants an asset from:
Date: 11/11/04
Time Booked Out14:00
Time Booked In16:00

Customer 2 wants to book the same asset from:
Date: 11/11/04
Time Booked Out 15:00
Time Booked In 17:00

The tables I have:

Reservations
Reservation ID
Reservation Date
Asset ID
Booked Out
Booked In
Customer ID


Asset
Asset ID
Asset Description
Asset Photo

Service Records
Service Record ID
Asset ID
Description
Problem Date
Resolved Date

Customers
Customer ID
Customer First Name
Customer Last Name

Assets
Asset ID
Time Collected
Time Returned

[lintz]

Try something like this.....HTH

'First select all records in your database then when you click Save use this code to check for a duplicate.
 
Do Until YourRecordset.EOF = True
 
If YourRecordset![Reservation Date] = " & ReservationDate & " _ AND YourRecordset![Asset ID] = " & AssetID & " 
AND " & _ TimeBookedIn & " BETWEEN YourRecordset![Time Booked Out] AND YourRecordset![Time Booked In] OR
 " & TimeBookedOut & " _ BETWEEN YourRecordset![Time Booked Out] AND Time Booked In > 0 Then
MsgBox "Dulpicte Record, change asset ID or booking time"
Exit Sub
End If
 
YourRecordset.Movenext
 
Loop
 
DoCmd.GoToRecord , , acNewRec
 
 
End If

[Ecniv]

Hi

Probably Lintz's code will work (post up if it does!)

strSql="Select tblReservations.* From tblReservations "
strSql=strSql & "Where [RDate]=#" & dteDate & "# and [AssetID]=" & lngAssetID & " and ( "
strSql=strsql & "#" & dteTimeOut & "# between [bookedout] and [bookedin] or #" & dteTimeIn & "# between [bookedout] and [bookedin])"
 
 
'open a recordset with the above sql
if rst.eof then
'---- create a record its ok
else
'---- there is at least one record within that time for that Asset and date
'---- so do a notice to the user...
end if

dte - variables of Date
rst - variable of recordset (either DAO or ADO)
strSql - variable of string (holds sql statement)

The Sql should bring back all records for the asset specified, on the date specified, and most importantly bring back any records which the times are in between. If no records are returned then you can go ahead and save...

Worth the time to experiment and post up which method works best?


Vince