Hi,
I am creating a database for my A level project and i am stuck on the validation for the booking part of the database, and was just wondering if anyone could help me out? It is a plumbing booking and stock database in the booking section I have start date, start time and duration field. I want to add validation on the start date field as an after update event. Any ideas would be extremely helpful. Thank you.
Tom
5 2753
please help me i am really stuck and if i can not work out a good way to do it using open ended booking i will have to use booking slots and that is just the easy way out.
Tom
ADezii 8,834
Recognized Expert Expert
Hi,
I am creating a database for my A level project and i am stuck on the validation for the booking part of the database, and was just wondering if anyone could help me out? It is a plumbing booking and stock database in the booking section I have start date, start time and duration field. I want to add validation on the start date field as an after update event. Any ideas would be extremely helpful. Thank you.
Tom
What is the Validation on the Start Date Field that you are looking for and is this Table, Form, or Field Validation?
Its on the start date field in the form to stop double booking, i want something like this
1.
Private Sub ScheduleEndTime _AfterUpdate()
2.
Dim db As DAO.Database
3.
Dim rs As DAO.Recordset
4.
Dim strMessage As String
5.
Dim startTime As Date
6.
Dim endTime As Date
7.
Dim test As Boolean
8.
9.
test = False
10.
If Me.NewRecord = True Then
11.
Set db = CurrentDb
12.
Set rs = db.OpenRecordse t("SELECT [ScheduleStartTi me], [ScheduleEndTime] " & _
13.
"FROM Schedule INNER JOIN ScheduleDetails " & _
14.
"ON Schedule.Schedu leID=ScheduleDe tails.ScheduleI D " & _
15.
"WHERE [CourtID]=" & Forms![Bookings]![BookingsSubform].Form![CourtID] & " " & _
16.
"AND [ScheduleDate]=#" & Forms![Bookings]![BookingsSubform].Form![ScheduleDate] & "#")
17.
18.
startTime = Me.ScheduleStar tTime
19.
endTime = Me.ScheduleEndT ime
20.
If rs.RecordCount = 0 Then Exit Sub
21.
rs.MoveFirst
22.
Do Until rs.EOF
23.
If startTime = rs!ScheduleStar tTime Or endTime = rs!ScheduleEndT ime Then
24.
test = True
25.
ElseIf startTime > rs!ScheduleStar tTime And startTime < rs!ScheduleEndT ime Then
26.
test = True
27.
ElseIf endTime < rs!ScheduleEndT ime And endTime > rs!ScheduleStar tTime Then
28.
test = True
29.
End If
30.
If test Then
31.
rs.MoveLast
32.
End If
33.
rs.MoveNext
34.
Loop
35.
36.
If test = False Then
37.
MsgBox ("Time is available")
38.
Else
39.
MsgBox ("Time is unavailable")
40.
Me.ScheduleStar tTime = Null
41.
Me.ScheduleEndT ime = Null
42.
End If
43.
44.
rs.Close
45.
Set rs = Nothing
46.
Set db = Nothing
47.
48.
End If
49.
50.
End Sub
from this http://www.thescripts. com/forum/thread604404.ht ml thread but i am not sure how to implement it so it works on mine.
Tom
Hi guys erm i tried to make that code above work in my database but it did not work out very well could anyone tell me what i have done wrong please. By the way it is in the form job* table which is based on the table of the same name. -
Private Sub Start_date_AfterUpdate()
-
-
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim strMessage As String
-
Dim startTime As Date
-
Dim endTime As Date
-
Dim test As Boolean
-
-
test = False
-
If Me.NewRecord = True Then
-
Set db = CurrentDb
-
Set rs = db.OpenRecordset("SELECT [Start_time], [End] " & _
-
"FROM Forms![Job* table] " & _
-
"ON Forms![job* table].Job_ID " & _
-
"WHERE [Job_ID]=" & Forms![Job* table]![Booking] & " " & _
-
"AND [Start_Date]=#" & Forms![Job* table].Form![Start_date] & "#")
-
-
startTime = Me.Start_time
-
endTime = Me.End
-
rs.MoveFirst
-
Do Until rs.EOF
-
If startTime = rs!Start_time Or endTime = rs!End Then
-
test = True
-
ElseIf Start_time > rs!Start_time And startTime < rs!End Then
-
test = True
-
ElseIf endTime < rs!End And endTime > rs!Start_time Then
-
-
test = True
-
-
End If
-
-
If test Then
-
-
rs.MoveLast
-
-
End If
-
-
rs.MoveNext
-
-
Loop
-
-
If test = False Then
-
MsgBox ("Time is available")
-
Else
-
MsgBox ("Time is unavailable")
-
Me.Start_time = Null
-
Me.End = Null
-
End If
-
-
rs.Close
-
Set rs = Nothing
-
Set db = Nothing
-
-
End If
-
-
End Sub
-
-
Tom
ADezii 8,834
Recognized Expert Expert
Hi guys erm i tried to make that code above work in my database but it did not work out very well could anyone tell me what i have done wrong please. By the way it is in the form job* table which is based on the table of the same name. -
Private Sub Start_date_AfterUpdate()
-
-
-
Dim db As DAO.Database
-
Dim rs As DAO.Recordset
-
Dim strMessage As String
-
Dim startTime As Date
-
Dim endTime As Date
-
Dim test As Boolean
-
-
test = False
-
If Me.NewRecord = True Then
-
Set db = CurrentDb
-
Set rs = db.OpenRecordset("SELECT [Start_time], [End] " & _
-
"FROM Forms![Job* table] " & _
-
"ON Forms![job* table].Job_ID " & _
-
"WHERE [Job_ID]=" & Forms![Job* table]![Booking] & " " & _
-
"AND [Start_Date]=#" & Forms![Job* table].Form![Start_date] & "#")
-
-
startTime = Me.Start_time
-
endTime = Me.End
-
rs.MoveFirst
-
Do Until rs.EOF
-
If startTime = rs!Start_time Or endTime = rs!End Then
-
test = True
-
ElseIf Start_time > rs!Start_time And startTime < rs!End Then
-
test = True
-
ElseIf endTime < rs!End And endTime > rs!Start_time Then
-
-
test = True
-
-
End If
-
-
If test Then
-
-
rs.MoveLast
-
-
End If
-
-
rs.MoveNext
-
-
Loop
-
-
If test = False Then
-
MsgBox ("Time is available")
-
Else
-
MsgBox ("Time is unavailable")
-
Me.Start_time = Null
-
Me.End = Null
-
End If
-
-
rs.Close
-
Set rs = Nothing
-
Set db = Nothing
-
-
End If
-
-
End Sub
-
-
Tom
If test evaluates to True in Lines 25, 27, or 30, a MoveLast was executed in Line #36 and then a MoveNext in Line #40 which will generate an Error. This could happen on the 1st Record in the Recordset before spanning the entire Recordset.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: PaulD |
last post by:
Can anyone point me in the direction of a sample Access booking
system? Trying to build a booking system were customers can book a
computer by date and timeslot, each timeslot having a limited amount
of computers.
Cheers for anyhelp, pointers given.
PaulD
|
by: Andy |
last post by:
Hi folks
I teach. At school, four IT rooms are booked using a paper based outline
timetable. Completing it is easy but basic and impossible to ensure
completion of all fields (name, year group, subject and software) and
analysis of bookings is a nightmare.
I just fancied pottering with Access, which I am pretty familiar with, to
see if I could create a means of booking a room using a database. Locking a
booking would then be a...
|
by: bredal Jensen |
last post by:
Hello gurus,
I'm building a small booking system and i have come accross quiet a tedious
pitfall.
"I need to make sure that people do not book for tomorrow when todays time
is greater or equal to 11."
|
by: 4004 |
last post by:
I would be grateful for help with the following:
For a booking system
tblBook(BookedItemId,DateBooked)
tblBookingPeriodAllowed(PeriodName, PeriodStartDate, PeriodEndDate)
I want:
|
by: samkut |
last post by:
Hi,
we are looking for a slim open source course booking system with the
following features:
* users can enroll for courses, cancel their bookings
* maximum number of participants
* administrator's pages: list of participants and their contact details
(email, phone etc.)
So far, we have found an extension to the mambo CMS
| |
by: thegame21 |
last post by:
Hi, I am currently creating a cinema system using access where a
booking can be made for a event. Each event when it is shown is
categoriesd as a performance. A booking must be made for each
performamce. I have a constructed a query between the booking, event
and peformance tables and created a subform on the booking form. The
event table is linked to the performance table which in turn is linked
to the booking table. I keep on getting the...
|
by: simba |
last post by:
Hello,
I am currently doing a project which requires me to develop an online booking system for hotels and integrate both bed and room booking. I have the room booking working but I cant seem to get the bed booking working. I have dorm rooms with 8 beds in them and I would like to have a query(or something) which allows me to make the dorm rooms bed booked instead of room booked. Anyone any ideas on how to make this happen, anything would be...
|
by: Peter Sutton |
last post by:
Greetings all,
My booking system includes 2 tables:
tblRooms with the key field RoomID, and some other fields AND a
tblBooked Rooms with a number of fields including RoomID and
BookedDate.
For a given date range, I can generate a vacant room report by
generating a temporary table of all the Rooms and BookedDates using a
cartesian join, and then either deleting the booked rooms, or by
|
by: 88stevie |
last post by:
Hi all,
I have a system and I'm a little stuck. It's a booking system.
It has rooms, etc and every room have a daily price.
Then it has special daily rates when assigned.
How do I calculate the total cost of a room if a daily rate exists?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |