By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,437 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Equipment booking and time range

100+
P: 121
Hi,

I have an equipment booking form that a user is able to book a piece of equipment on a selected date and time. I have written code to prevent the user being able to book the piece of equipment if it has already been booked by a different user on the same date/time.

I want to display in a time grid format when that piece of equipment is booked. I currently have 16 green coloured boxes on my form each representing an hour's time interval so box 1 is 09:00 - 10:00 and so on. If the selected piece of equipment is booked on that day for a given time interval for example 12:00 - 15:00 I want to change the colour of the box's that represent 12:00 - 13:00 13:00 - 14:00 and 14:00 to 15:00 to red (which represents the equipment is already booked)

I currently have code that gives me a start and end time for the booking which enables me to change the start and end time box's to red as follows.Just cant think of a way to change the boxes in between

Expand|Select|Wrap|Line Numbers
  1. If BkdStartTimeID = #12:00:00 PM# Then
  2.  
  3. [Forms]![F_EquipBooking].[SF_EquipBooking]![Box12].BackColor = lngRed
  4.  
  5. If BkdEndTimeID = #3:00:00 PM# Then
  6.  
  7. [Forms]![F_EquipBooking].[SF_EquipBooking]![Box15].BackColor = lngRed
I hope my ramblings make sense any help is much appreciated

Regards Phill
May 5 '08 #1
Share this Question
Share on Google+
7 Replies


nico5038
Expert 2.5K+
P: 3,072
Hi,

I have an equipment booking form that a user is able to book a piece of equipment on a selected date and time. I have written code to prevent the user being able to book the piece of equipment if it has already been booked by a different user on the same date/time.

I want to display in a time grid format when that piece of equipment is booked. I currently have 16 green coloured boxes on my form each representing an hour's time interval so box 1 is 09:00 - 10:00 and so on. If the selected piece of equipment is booked on that day for a given time interval for example 12:00 - 15:00 I want to change the colour of the box's that represent 12:00 - 13:00 13:00 - 14:00 and 14:00 to 15:00 to red (which represents the equipment is already booked)

I currently have code that gives me a start and end time for the booking which enables me to change the start and end time box's to red as follows.Just cant think of a way to change the boxes in between

Expand|Select|Wrap|Line Numbers
  1. If BkdStartTimeID = #12:00:00 PM# Then
  2.  
  3. [Forms]![F_EquipBooking].[SF_EquipBooking]![Box12].BackColor = lngRed
  4.  
  5. If BkdEndTimeID = #3:00:00 PM# Then
  6.  
  7. [Forms]![F_EquipBooking].[SF_EquipBooking]![Box15].BackColor = lngRed
I hope my ramblings make sense any help is much appreciated

Regards Phill
Good thing to try to make the time visible in this way.
I would use the Box name to manipulate the boxes in a loop.
You already use 12, 13, 14, 15, etc as the box name so a loop like:
Expand|Select|Wrap|Line Numbers
  1. intStart = format(BkdStartTimeID,"hh")
  2. intEnd = format(BkdEndTimeID ,"hh")
  3. WHILE intStart <= intEnd
  4.    Me.fields("Box" & format(intStart,"00").Backcolor = vbRed
  5.    intStart = intStart + 1
  6. WEND
  7.  
Just make sure all boxes are there and that the BkdEndTimeID has a value.

Getting the idea ?

Nic;o)
May 5 '08 #2

100+
P: 121
Good thing to try to make the time visible in this way.
I would use the Box name to manipulate the boxes in a loop.
You already use 12, 13, 14, 15, etc as the box name so a loop like:
Expand|Select|Wrap|Line Numbers
  1. intStart = format(BkdStartTimeID,"hh")
  2. intEnd = format(BkdEndTimeID ,"hh")
  3. WHILE intStart <= intEnd
  4.    Me.fields("Box" & format(intStart,"00").Backcolor = vbRed
  5.    intStart = intStart + 1
  6. WEND
Just make sure all boxes are there and that the BkdEndTimeID has a value.

Getting the idea ?

Nic;o)
Hi Nic

Thanks for your help....

I can get it working to an extent if i include this line i get the folloing error message...

Compile error

Invalid use of me keyword
Expand|Select|Wrap|Line Numbers
  1. Me.Fields ("Box" & Format(intstart, "00").BackColor = vbRed)
So i changed it to the following and when it runs this line it just ends the function. The code is within a module in a function by the way..
Expand|Select|Wrap|Line Numbers
  1. Forms![F_EquipBooking]![SF_EquipBooking].Fields ("Box" & Format(intstart, "00").BackColor = vbRed)
Regards Phill
May 5 '08 #3

nico5038
Expert 2.5K+
P: 3,072
Hi Nic

Thanks for your help....

I can get it working to an extent if i include this line i get the folloing error message...

Compile error

Invalid use of me keyword

Me.Fields ("Box" & Format(intstart, "00").BackColor = vbRed)

So i changed it to the following and when it runs this line it just ends the function. The code is within a module in a function by the way..

Forms![F_EquipBooking]![SF_EquipBooking].Fields ("Box" & Format(intstart, "00").BackColor = vbRed)

Regards Phill
Hi Phill,

Sorry my error, I assumed the code to be part of the form.
Just would have thought you would need to use:
Expand|Select|Wrap|Line Numbers
  1. Forms![F_EquipBooking]![SF_EquipBooking].form.Fields ("Box" & Format(intstart, "00").BackColor = vbRed)
as Access requires an extra "form reference".

Nic;o)
May 5 '08 #4

NeoPa
Expert Mod 15k+
P: 31,186
...
Expand|Select|Wrap|Line Numbers
  1. Me.Fields ("Box" & Format(intstart, "00").BackColor = vbRed)
...
The second closing parenthesis should be immediately after the first (before .BackColor).
Expand|Select|Wrap|Line Numbers
  1. Me.Fields("Box" & Format(intstart, "00")).BackColor = vbRed
May 5 '08 #5

100+
P: 121
Hi Phill,

Sorry my error, I assumed the code to be part of the form.
Just would have thought you would need to use:
Expand|Select|Wrap|Line Numbers
  1. Forms![F_EquipBooking]![SF_EquipBooking].form.Fields ("Box" & Format(intstart, "00").BackColor = vbRed)
as Access requires an extra "form reference".

Nic;o)

Hi,

I'm still having no luck I have tried both lines of code as follows and as soon as you run either line it just skips to end function

Expand|Select|Wrap|Line Numbers
  1. Forms![F_EquipBooking]![SF_EquipBooking].Form.Fields("Box" & Format(intstart, "00")).BackColor = vbRed
  2.  
  3. Forms![F_EquipBooking]![SF_EquipBooking].Form.Fields ("Box" & Format(intstart, "00").BackColor = vbRed)
what is this line supposed to do? im not sure that i understand...

Many thanks Phill
May 6 '08 #6

nico5038
Expert 2.5K+
P: 3,072
Hi,

I'm still having no luck I have tried both lines of code as follows and as soon as you run either line it just skips to end function

Expand|Select|Wrap|Line Numbers
  1. Forms![F_EquipBooking]![SF_EquipBooking].Form.Fields("Box" & Format(intstart, "00")).BackColor = vbRed
  2.  
  3. Forms![F_EquipBooking]![SF_EquipBooking].Form.Fields ("Box" & Format(intstart, "00").BackColor = vbRed)
what is this line supposed to do? im not sure that i understand...

Many thanks Phill
Sorry Phill, I mixed up forms and records, try:
Expand|Select|Wrap|Line Numbers
  1. Forms![F_EquipBooking]![SF_EquipBooking].Form.Controls("Box" & Format(intstart, "00")).BackColor = vbRed
  2.  
  3. Forms![F_EquipBooking]![SF_EquipBooking].Form.Controls("Box" & Format(intstart, "00").BackColor = vbRed)
As the fields of a form are called "Controls" :-)

Nic;o)
May 6 '08 #7

100+
P: 121
Sorry Phill, I mixed up forms and records, try:
Expand|Select|Wrap|Line Numbers
  1. Forms![F_EquipBooking]![SF_EquipBooking].Form.Controls("Box" & Format(intstart, "00")).BackColor = vbRed
  2.  
  3. Forms![F_EquipBooking]![SF_EquipBooking].Form.Controls("Box" & Format(intstart, "00").BackColor = vbRed)
As the fields of a form are called "Controls" :-)

Nic;o)
Hi Nic

Its working :) much appreciated

Phill
May 6 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.