Dynamic toggle buttons to control values in a single table

Chumpalot

Registered User.
Local time
Today, 14:35
Joined
Mar 12, 2015
Messages
76
Hi all,

I will try and keep this as succinct and to the point as I can, more for my own sanity and to save me getting confused and ultimately rambling.

I am trying to create what is essentially a sort of diary system in Access (2016). I know this isn't ideal and I should probably explore some sort of integration with Outlook but for now I'd like to contain it all within Access.

There are 230 countries attending a single event taking place across one day. There are timeslots within this single day that any country can attend from 0600 to midnight. The timeslots are 10 minutes each and a maximum of three countries can attend any one slot.

I have a form that records other ancillary information that isn't important here. This form is dynamic in that it pulls the country value through from a previous selection screen. I am trying to build an Open Table type timeslot selection screen which is essentially a grid with toggle buttons to represent times. These go green when they're clicked and the county value is stored into a textbox which I was going to link back to the timeslot table.

From here I am a little stuck. I am not sure how I should go about recording this in the table. Could it be that the toggle looks for the stored country value and then scans the table for the matching country value and then enters a value into the correct timeslot?

Table design
tbl_timeslots
CountryID
0600
0610
0620
0630
0640
0650
0700
etc
etc

Another issue is then how the timeslots look when opening the form. The ones that have been pressed will need to stay pressed (and green). I guess first things first though (code a little, test a lot etc).

Thank you for any and all help on this.

Regards
David
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:35
Joined
Oct 29, 2018
Messages
21,357
Hi David. None of these make sense to us without seeing it. Can you post some images or a copy of your db?
 

Cronk

Registered User.
Local time
Tomorrow, 01:35
Joined
Jul 4, 2013
Messages
2,770
You have a period of 6am to 12 midnight (18 hours) with 6 time slots each hour ie 108 time slots.


If I was designing such a system, I'd probably refer to time slots as SlotID and then have a booking table with EventDate, SlotID and CountryID.



The booking form. I can't see 108 lines being able to be displayed without having to scroll down but maybe buttons to select previous/next lot of 6 hour time slots. As to having a green background to indicate available slots, use conditional formatting based on no allocated country to that control.



You'll also need to be cognizant of the system maximum of 754 controls on a form.
 

isladogs

MVP / VIP
Local time
Today, 14:35
Joined
Jan 14, 2017
Messages
18,186
I'd also follow the approach suggested by Cronk.
I have several similar features built in to one of my commercial schools apps and used to organise room bookings, parents evening appointments and event management. The methods used in one or more may help you focus your design. See attached for user guides on two of those features if interested.
 

Attachments

  • ParentAppointments.zip
    936.3 KB · Views: 122
  • Room & Equipment Bookings in SDA.zip
    936.3 KB · Views: 117

Chumpalot

Registered User.
Local time
Today, 14:35
Joined
Mar 12, 2015
Messages
76
Thank you very much for the replies. I will take a look at the attached files and crack on with trying to get this done!

Thanks again.
David
 

Chumpalot

Registered User.
Local time
Today, 14:35
Joined
Mar 12, 2015
Messages
76
The booking form. I can't see 108 lines being able to be displayed without having to scroll down but maybe buttons to select previous/next lot of 6 hour time slots. As to having a green background to indicate available slots, use conditional formatting based on no allocated country to that control.

You'll also need to be cognizant of the system maximum of 754 controls on a form.

Indeed. It doesn't look too pretty at the moment and is a little funny on the eyes. I just have a block of 6 x 19 toggle buttons! :D

I'll keep building.

Cheers
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 14:35
Joined
Jul 9, 2003
Messages
16,244
There are 230 countries

Do you mean counties?

Seeing as there are only around 200 countries recognised in the World at any 1 time! - (Currently 195)



Sent from my Pixel 3a using Tapatalk
 

Chumpalot

Registered User.
Local time
Today, 14:35
Joined
Mar 12, 2015
Messages
76
Do you mean counties?

Seeing as there are only around 200 countries recognised in the World at any 1 time! - (Currently 195)



Sent from my Pixel 3a using Tapatalk

Apologies, I should have been a little bit more specific but I didn't think it was relevant.

I do have all of the countries and then there are some other entries added to the list. Organisations and other entities. Essentially they're all one and the same thing for the purposes of the database. :)
 

Chumpalot

Registered User.
Local time
Today, 14:35
Joined
Mar 12, 2015
Messages
76
Thanks for the replies all. I now have some of it working. By that I mean I can choose a timeslot using any one of the toggle buttons to essentially assign it to a country. This enters the following values into tbl_allocatedtimeslots:

- The countryID
- The time itself (in text format)

What I would like to do now is query this data on loading the form so that the form knows which toggle buttons to show as being green (allocated).

Any idea at all on how I would go about this?

David
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:35
Joined
Oct 29, 2018
Messages
21,357
Thanks for the replies all. I now have some of it working. By that I mean I can choose a timeslot using any one of the toggle buttons to essentially assign it to a country. This enters the following values into tbl_allocatedtimeslots:

- The countryID
- The time itself (in text format)

What I would like to do now is query this data on loading the form so that the form knows which toggle buttons to show as being green (allocated).

Any idea at all on how I would go about this?

David
Hi David. You might be able to use DLookup() or DCount() to look up if the time slot is allocated and then change the color to green.
 

Cronk

Registered User.
Local time
Tomorrow, 01:35
Joined
Jul 4, 2013
Messages
2,770
As I wrote in #3, IMO easiest is to use conditional formatting. Set the condition to Value >"" and the background color of the control to green for this condition.
 

Chumpalot

Registered User.
Local time
Today, 14:35
Joined
Mar 12, 2015
Messages
76
Sorry Cronk, I didn't see your reply in your original post - thank you.
 

Users who are viewing this thread

Top Bottom