Date Scheduler (1 Viewer)

sloaner14

Registered User.
Local time
Today, 00:06
Joined
Apr 25, 2002
Messages
32
I am trying to figure out a way to schedule date. I need to be able to have a begin date, end date, time, and check boxes to select the days of the meeting. I basically want to take a student, put them in a class, enter the begin date, end date, times, select the days of the week, and have it generate the schedule. Thanks for any help.
 

RichO

Registered Yoozer
Local time
Yesterday, 18:06
Joined
Jan 14, 2004
Messages
1,036
This sounds like a basic Access project but VBA would not be the place to look. You need to start by creating a table with the desired fields, and a form for data entry into the table. The form wizard can help you with this.


Get that working correctly and then start to incorporate the data into a report. You'll probably need a query as well.
 

sloaner14

Registered User.
Local time
Today, 00:06
Joined
Apr 25, 2002
Messages
32
I have a table, and form built. What I have is, two text boxes to allow the user to enter the Begin Date and the End Date, I also have check boxes that allow the user to select the days of the class. After they enter both dates, and select like MWF or TTH, have a generate schedule button. I need it to enter all dates on the correct days from BeginDate to EndDate.
 

RichO

Registered Yoozer
Local time
Yesterday, 18:06
Joined
Jan 14, 2004
Messages
1,036
So what you're saying, as an example is if a class begins on 4/12/2004 and ends on 4/23/04 and runs MWF you want the report to show:

4/12/04
4/14/04
4/16/04
4/19/04
4/21/04
4/23/04

Is this what you are needing?
 

sloaner14

Registered User.
Local time
Today, 00:06
Joined
Apr 25, 2002
Messages
32
Exactly, I guess I just didn't know how to ask that.
 

sloaner14

Registered User.
Local time
Today, 00:06
Joined
Apr 25, 2002
Messages
32
Sorry, I should tell me reasoning for this. I work at a school, and I get the job of building db's. I buidling one for a program that wants to be able to track all their students. It going to work as an attendance sheet as well. They want to be able to schedule students, and track the attendance to the student to see if they meet their required classes for the days they are scheduled.
 

RichO

Registered Yoozer
Local time
Yesterday, 18:06
Joined
Jan 14, 2004
Messages
1,036
If possible, could you post a zip of your form and table?

This will take some VBA code and a make table query using SQL to turn the date range into a list of dates. I don't want to put too much thought into it until I know exactly what I am looking at.

I'm using Access 2K.
 

RichO

Registered Yoozer
Local time
Yesterday, 18:06
Joined
Jan 14, 2004
Messages
1,036
I found a little extra free time so I put together a piece of code to assemble a table of class dates...

Code:
    Dim db  As database
    Dim rs  As Recordset
    Dim sql As String
    Dim i   As Integer
    Dim d   As Integer
    Dim x   As Integer
    Dim y   As Integer
    Dim dt  As Date
        
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM tblClasses)
    Do While Not rs.EOF
        d = 0
        x = 2: y = 6
        If rs!Days = "TR" Then x = 3: y = 5
Loop1:
        dt = DateAdd("d", d, rs!StartDate)
        For i = x To y Step 2
        If DatePart("w", dt) = i Then
            sql = "INSERT INTO ClassSchedule (Class, Dates) SELECT '" & rs!Class
            sql = sql & "' AS Expr1, #" & dt & "# AS Expr2 "
            db.Execute sql
        End If
        Next i
        d = d + 1
        If dt < rs!EndDate Then GoTo Loop1
        rs.MoveNext
     Loop
This code looks at a table with 4 fields: StartDate, EndDate, Days (MWF or TR) and Class (which is the name of the class).

It appends to a blank table to create a list of dates that fall into MWF or TR within the class' date range. From there you can run a report on the table. If you use this you will have to add a piece of code to clear out the table each time before you append to it.

I would guess that some VBA experts are looking at this code thinking "There a much easier way to do it". I'm still quite green when it comes to VBA, not being aware of many of the built in functions.

Hope this helps you out.
 

sloaner14

Registered User.
Local time
Today, 00:06
Joined
Apr 25, 2002
Messages
32
I have been off all weekend. I will try the code you posted, but I also threw together an example. I too am green to vba, I have code that will work, but it is huge. I know there is an easier way. I dont know all the date functions, and what they can do.
 

Attachments

RichO

Registered Yoozer
Local time
Yesterday, 18:06
Joined
Jan 14, 2004
Messages
1,036
I'm confused by a couple of things in your DB.

First, in the both the subform and tblAttend the Date field is formatted as "Short Time". Is this correct?

Second, the Activity, Start Date, End Date, Start Time, and End Time fields as well as the day check boxes are unbound so they carry over from one record to the next regardless of class selection. Shouldn't these fields all be stored in the tblAttend table along with the others?

Do classes only run either M-W-F or T-R, or are there possible combinations of any of the days?
 

sloaner14

Registered User.
Local time
Today, 00:06
Joined
Apr 25, 2002
Messages
32
I guess the format is wrong. Date should be short date and Time should be short time. The unbound controls at the top are just holders for the user to type in the Activity, BDate, EDate, select the Days, BTime, and ETime. Then the user should be able to click the generate button and based on what is in the unbound controls, the schedule is created. The activities can be any combination of days (M, MW, MWF, MT, T, MF, etc.) That is why I have check boxes. Thanks for all this help.
 

RichO

Registered Yoozer
Local time
Yesterday, 18:06
Joined
Jan 14, 2004
Messages
1,036
This is how I altered the code for any combination of days...

Code:
    Dim db      As Database
    Dim sql     As String
    Dim dt      As Date
    Dim day(7)  As Integer
    
    Set db = CurrentDb
    db.Execute "DELETE * FROM TblSchedule"
    
    day(1) = Nz(Abs(chkSunday))
    day(2) = Nz(Abs(chkMonday))
    day(3) = Nz(Abs(chkTuesday))
    day(4) = Nz(Abs(chkWednesday))
    day(5) = Nz(Abs(chkThursday))
    day(6) = Nz(Abs(chkFriday))
    day(7) = Nz(Abs(chkSaturday))
     
    dt = txtBDate
    
    While dt <= txtEDate
       If day(DatePart("w", dt)) = 1 Then
          sql = "INSERT INTO tblSchedule (Dates) SELECT #" & dt & "# AS Expr1 "
          db.Execute sql
       End If
       dt = DateAdd("d", 1, dt)
    Wend
Again, this builds a table containing all of the dates that fall into the schedule. I also added a simple report to your sample DB that reads from the schedule dates and current controls on your form.

Good luck.
 

Attachments

sloaner14

Registered User.
Local time
Today, 00:06
Joined
Apr 25, 2002
Messages
32
RichO, this is exactly what I am looking for, but I need it to append to tblAttend. I got it to do that, but I can only get it to append the dates. I need it to append the SSN, cboActivity, txtBTime, and txtETime information. I have worked to try to get it to do that, but I am really new to SQL. I have put a table with records to give you an idea of what I need. Thanks again.
 

Attachments

RichO

Registered Yoozer
Local time
Yesterday, 18:06
Joined
Jan 14, 2004
Messages
1,036
Storing all those dates in that table is not an efficient way to do this. It would take up a lot of unnecessary space in the table. If nothing else, the dates should stay in a separate table with a field for the student's ID for linking.

However, what I would recommend is storing the start date, end date, and day check boxes in the attend table, and then anytime you need to see the schedule of dates, you can use the VBA code to do it. I assume you want to be able to print reports with the list of dates.

What do you think?
 

sloaner14

Registered User.
Local time
Today, 00:06
Joined
Apr 25, 2002
Messages
32
I understand the issues with size, but this what the client wants. What you have done is perfect, I just need the other information in the attendance table. If I try to explain the maddness, it may drive you insane. Like I said your stuff is perfect, I also need the other info. Please don't try to understand. lol
 

RichO

Registered Yoozer
Local time
Yesterday, 18:06
Joined
Jan 14, 2004
Messages
1,036
OK I think this is what you want...

The SQL statement in VBA just needed to be set up to pull all the rest of the fields off the form and store them in the table along with the dates. If that's what the client wants, so be it. :confused:

I also locked the continuous subform fields because they appear to be just there to display the class schedule and you don't want a user trying to enter data in them because that will mess up the table.
 

Attachments

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom