Control Option Buttons based on selected date (1 Viewer)

[g-richardson]

Hi,

I'm building a workCube reservation system and I've been tasked to have it work kind of like an airplane seat reservation system.

I've laid out my form with option buttons representing the location of each available space. (space1, space2, space3...space16)

My desired outcome is to be able to select a date from a calendar popup and have the options buttons react to that date if they have been reserved. (change color and indicate "reserved").

I've tried to create the form based on a query which represents the "booked" table.

Tables:

Employee
(k)empID
emplyeeName

Space
(k)spaceID
space

Booked
(k)spaceID
(k)empID
(k)bookdate

Some rules a space can be booked by any ONE employee on any day.


How can I get any and all of the option buttons to react if there is a reservation in place on the day indicated by the calendar?


:banghead: thanks for any help.

 

[billmeye]

In the On Current event and the After Update for your date control (may not fire after using date picker, may need to move to another field first) you can test to determine if the cubical is taken and changed the color of the button accordingly:

If determine is space is booked Then
Me.Space1.Backcolor = RGB(64, 64, 64)
Else
Me.Space1.Backcolor = RGB(128, 128, 128)
End If

 

[g-richardson]

Thanks Billmeye...I see what you are doing here, but so far I'm not able to create the "If determine space is booked" part.

Brief form layout (could need better design), so as stated earlier, form based on data in the Reservations table. So on the form, I have a subform of a calendar (found elsewhere in this forum)...and a field of Option Buttons laide out in the cube design. I'm also trying to bring in another subform which shows a list of the cubes reserved for that day.

I'm okay with any suggestions to form design that can help me reach objective...if I need to remove the calnedar subform or the subform showing booked spaces...I'm okay with that, I'll design around. My again is to get the command button to react if it is booked on a day that is identified on the form.

I'm still trying some things. but I do appreciate what you've provided...I think when I get the Determination part down, your assist will help.

 

[billmeye]

I think visually it would be great if the form showed 16 CUBE shaped unbound and locked(not for data entry) text box controls large enough to fit the name of the person reserving the space. It would help if the control names match the Space names in your Space table, i,e., Space1, Space2 like you said so we can make it easy to assign them based on your Booked table.

In the Unbound DateSelection control After Update Event (or add a button and put the code in the On Click event) you could parse through the Booked table to determine what is booked and add the employee name and change the colors:

Dim Rst as DAO.Recordset, MySQL as String
Dim SpaceName as String, EmpName as String
Dim X as Integer
'First clear and reset color
For X = 1 to 16
Me("Space" & X) = ""
Me("Space" & X).BackColor = RGB(0,0,0) empty color
Next X
MySQL = "SELECT * FROM Booked WHERE (((tblBooked.bookdate)=#" & Me.DateSelection & "#));"
Set Rst = CurrentDb.Openrecordset(MySQL,dbopendynaset)
Rst.MoveLast
Rst.MoveFirst
If Rst.Recordcount > 0 Then
Do While Not Rst.EOF
SpaceName = Dlookup("[space]","Space","[SpaceID]=" & RST!SpaceID)
EmpName = Dlookup("[emplyeeName]","Employee","[empID]=" & RST!empID)
Me(SpaceName) = EmpName
Me(SpaceName).BackColor = RGB(125, 125, 125) 'choose your booked color, may also need to change font color for good contrast
Rst.MoveNext
Loop
End If
Rst.Close
Set Rst = Nothing

Next step would be to place code behind each CUBE control to allow for clicking and booking the cube.

Let me know what you think.

 

[g-richardson]

Hey Billmeye...It looks like I've got some work to do with this. I keep getting fatal errors that I haven't been able to resolve yet. I'm going to keep working on this because I really like the concept laid out. Now I just need to get it to fire.

Thanks for your efforts