Equipment booking and time range

[phill86]

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

If BkdStartTimeID = #12:00:00 PM# Then
 
[Forms]![F_EquipBooking].[SF_EquipBooking]![Box12].BackColor = lngRed
 
If BkdEndTimeID = #3:00:00 PM# Then
 
[Forms]![F_EquipBooking].[SF_EquipBooking]![Box15].BackColor = lngRed

I hope my ramblings make sense any help is much appreciated

Regards Phill

[nico5038]

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

If BkdStartTimeID = #12:00:00 PM# Then
 
[Forms]![F_EquipBooking].[SF_EquipBooking]![Box12].BackColor = lngRed
 
If BkdEndTimeID = #3:00:00 PM# Then
 
[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:

intStart = format(BkdStartTimeID,"hh")
intEnd = format(BkdEndTimeID ,"hh")
WHILE intStart <= intEnd
   Me.fields("Box" & format(intStart,"00").Backcolor = vbRed
   intStart = intStart + 1
WEND
 

Just make sure all boxes are there and that the BkdEndTimeID has a value.

Getting the idea ?

Nic;o)

[phill86]

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:

intStart = format(BkdStartTimeID,"hh")
intEnd = format(BkdEndTimeID ,"hh")
WHILE intStart <= intEnd
   Me.fields("Box" & format(intStart,"00").Backcolor = vbRed
   intStart = intStart + 1
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

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

[nico5038]

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:

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

as Access requires an extra "form reference".

Nic;o)

[NeoPa]

...

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

...

The second closing parenthesis should be immediately after the first (before .BackColor).

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

[phill86]

Hi Phill,

Sorry my error, I assumed the code to be part of the form.
Just would have thought you would need to use:

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

Forms![F_EquipBooking]![SF_EquipBooking].Form.Fields("Box" & Format(intstart, "00")).BackColor = vbRed
 
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

[nico5038]

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

Forms![F_EquipBooking]![SF_EquipBooking].Form.Fields("Box" & Format(intstart, "00")).BackColor = vbRed
 
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:

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

As the fields of a form are called "Controls" :-)

Nic;o)

[phill86]

Sorry Phill, I mixed up forms and records, try:

Forms![F_EquipBooking]![SF_EquipBooking].Form.Controls("Box" & Format(intstart, "00")).BackColor = vbRed
 
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