Populate Time Range In MS-Access

Tyler Johnson 0
Viewed 35

I am making a scheduling tool, which runs off of five-minute increments tied to the IDs of timeslots on another table. These timeslots have a start time, and stop time which are separate fields in the same record, and can be entered using a form, and I would like to create a range of five-minute increments as records between these two times.

The ultimate goal is to use these timeslots to make an outlook-style gui to show when there are timeslots.

I have been struggling to find a quick way to fill them in Access, and my attempts at coding are so pathetic I'm too embarrassed to show them.

Any advice would be greatly appreciated, Thank you!

ms-access-2016 ms-access
1 Answers

Sounds like you want to create a batch of five-minute increment records associated with a particular date. More than one way to accomplish. Simplest may be to have a table of time records. Each record would have a time value: 10:00, 10:05, etc. Then on UNBOUND form user selects time range start and end from comboboxes and enters a date in a textbox. An SQL action statement in VBA could be:

CurrentDb.Execute "INSERT INTO tblSchedules(SchedDate, SchedTime) " & _
             "SELECT #" & Me.tbxDate & "#, Mins FROM tblTimes " & _
             "WHERE Mins BETWEEN '" & Me.cbxStart & "' AND '" & Me.cbxEnd & "'"

Without this table of time records, VBA would use a looping structure to save one record at a time to destination table.

Dim strT As String, db As DAO.Database
strT = Me.cbxStart
Set db = CurrentDb
Do While strT <= Me.cbxEnd
    db.Execute "INSERT INTO tblSchedules(SchedDate, SchedTime) VALUES(#" & Me.tbxDate & "#, '" & strT & "')"
    strT = Format(DateAdd("n", 5, CDate(strT)), "hh:nn")
Loop

In these examples, time value is text, not an actual date/time type. This means a structure of "hh:nn" in 24-hour time: "01:00" ... "24:00". If you prefer to save as number or as a date/time type, modify tables and code as appropriate. Keep in mind that midnight for date/time type is stored without a time component and Format() function displays as 12:00:00 AM or 00:00:00. Example: Format(#1/1/2022#, "mm/dd/yyyy hh:nn:ss AM/PM") displays as 01/01/2022 12:00:00 AM. Ranges crossing midnight causes complication.

June70
Related