Automated Scheduling Process (It's been a while since I've used Access)

[avarusbrightfyre]

So here goes:

I work at a place that has several different locations and they would like to have an automated scheduling feature. My instincts tell me this should be possible, but I'm having a hard time putting it together in my head. My approach is this:

I have a table for work locations, with days open/closed and opening/closing times, and positions that need to be filled


I have a table for employees, with days available for work
I have a schedule table that will need to be autopopulated using the two previous tables to create entries for each date in the range for each position that needs to be filled, without duplicating employees in different positions.

I know this is pretty vague, but I'm kind of looking for a general idea of where to start and we can get more specific from there. My first thought is some sort of loop that goes through each date in the range, then each position on the first table and fills it with an employee that is not already assigned a spot on that date.

It's been a year or more since I've done any Access programming, so any help would be appreciated.

[June7]

The more 'automation' the more code. Regardless, someone probably has to push a button, even if it is just to open the db every day. I do have a task scheduled in Windows Task Manager that automatically runs a script file that opens db every day.

Yes, looping through recordset object(s) is one way to accomplish.

[avarusbrightfyre]

The more 'automation' the more code. Regardless, someone probably has to push a button, even if it is just to open the db every day. I do have a task scheduled in Windows Task Manager that automatically runs a script file that opens db every day.

Yes, looping through recordset object(s) is one way to accomplish.

Ok, I thought I was on the right track with looping. I have a DO loop that adds entries to the specified table for each date between the two specified dates in text boxes the user will use. Now I need a loop inside of that one that adds a record to my "schedule" table for each record within my "staff positions" table. Basically, for each date within the specified date range, I want it to use the "staff management" table to fill all of the positions for that day, then move on to the next day. So my set up is like this:

Private Sub Command.Click

On Error GoTo Error:

'Set variable named date, set it to the start of the date range
Dim CurrentDate As Date
CurrentDate = Me.StartDate

'If the current date is within the range, continue to loop
Do While CurrentDate <= Me.EndDate
'Scan Branch Positions Table to fill each date
For Each [ID] In BranchStaffManagement >>>>> This is what I want it to do, but can't figure out how to reference the proper table fields

'Add Record based on all the fields in [Staff Management Table]
Dim dbPersonnel As dao.Database
Dim rstSchedule As dao.Recordset
Set dbPersonnel = CurrentDb
Set rstSchedule = dbPersonnel.OpenRecordset("Schedule")
rstSchedule.AddNew
rstSchedule("WorkDate").Value = CurrentDate
'Will add further fields once I figure out the automation
rstSchedule.Update
Next

CurrentDate = CurrentDate + 1
Loop
DoCmd.Close acForm, "Schedule Selection"
Error:
MsgBox "Please enter a starting and ending date in the provided spaces."
End Sub

I'm sure I'm missing quite a lot in there. Like I said, it's been a while since I've done VBA and google isn't helping me very much. Any ideas? I'm sure I'm just making this harder than it really is.

[June7]

Try something like:

Do While CurrentDate <= Me.EndDate

CurrentDb.Execute "INSERT INTO Schedule(WorkDate, StaffID) SELECT " & CurrentDate & " AS WorkDate, ID FROM BranchStaffManagement"

CurrentDate = CurrentDate + 1

Loop

The SELECT part can pull fields from a query that joins tables.

That can certainly schedule everyone for the given date range but if you need to make specific assignments, that is another matter. If 4 people are available for the same assignment for the same period, how should the code determine which gets it? Are assignments daily or for a period? If for a period then the code would have to prevent overlapping assignments. Very complicated logic code.

[avarusbrightfyre]

Try something like:

Do While CurrentDate <= Me.EndDate

CurrentDb.Execute "INSERT INTO Schedule(WorkDate, StaffID) SELECT " & CurrentDate & " AS WorkDate, ID FROM BranchStaffManagement"

CurrentDate = CurrentDate + 1

Loop

The SELECT part can pull fields from a query that joins tables.

That can certainly schedule everyone for the given date range but if you need to make specific assignments, that is another matter. If 4 people are available for the same assignment for the same period, how should the code determine which gets it? Are assignments daily or for a period? If for a period then the code would have to prevent overlapping assignments. Very complicated logic code.

Yeah, at the moment I'm just trying to schedule based off of the assigned positions on the staff management page. It will cut down on the redundant data entry. Each person will be assigned to a unique position for now. I may attempt something more complex later, but for now just that.

[June7]

I have no idea what 'staff management page' is. Since I don't know you db structure, I can't be specific with advice. So did you try something along the lines of the Execute?

[avarusbrightfyre]

I have no idea what 'staff management page' is. Since I don't know you db structure, I can't be specific with advice. So did you try something along the lines of the Execute?

I was simply confirming that I don't plan to get complicated quite yet, lol. I'm trying to figure out the Execute, once I do, I'll let you know.

[avarusbrightfyre]

I don't think I quite understand what this is doing. When I use this code, it will add to the Schedule table using the positions I want, but I can't get it to add any other fields.

Just to make things clearer for me, I adjusted the code you posted to work with the tables I have:

-----------------------------------------------
Dim CurrentDate as Date (Create variable for date)
CurrentDate = Me.StartDate (Set variable to value user puts into textbox named "StartDate")

Do While CurrentDate <= Me.EndDate

CurrentDb.Execute "INSERT INTO Schedule(WorkDate, Position) SELECT " & CurrentDate & " AS WorkDate, Position FROM Positions"

CurrentDate = CurrentDate + 1

Loop
-------------------------------------

The above code adds entries based on my "Positions" table as I want it to, but it also makes the date December 30, 1899...which is not the date I'm putting into the text box named "StartDate"

I also want to add entries from other fields, but when I try to do that, the code doesn't do anything. Here is what I'm trying to do:

---------------------
CurrentDb.Execute "INSERT INTO Schedule(WorkDate, Branch, Position, LastName, FirstName) SELECT " & CurrentDate & " AS WorkDate, Branch FROM Positions, Position FROM Positions, LastName FROM Positions, FirstName FROM Positions"
---------------------

Is the above code possible using this method? Also, why is the variable not being assigned the value in the StartDate text box and being given that specific date?

Thanks!

[June7]

Don't repeat FROM POSITIONS.

Also, I neglected the # delimiters.

CurrentDb.Execute "INSERT INTO Schedule(WorkDate, Branch, Position, LastName, FirstName) SELECT #" & CurrentDate & "# AS WorkDate, Branch, Position, LastName, FirstName FROM Positions"

[avarusbrightfyre]

Don't repeat FROM POSITIONS.

Also, I neglected the # delimiters.

CurrentDb.Execute "INSERT INTO Schedule(WorkDate, Branch, Position, LastName, FirstName) SELECT #" & CurrentDate & "# AS WorkDate, Branch, Position, LastName, FirstName FROM Positions"

Ah, that makes a lot of sense. I'll play around with it and see I if I can get it to work. Thanks!