Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-07-2004, 01:07 PM   #1
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
Date Scheduler

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.

sloaner14 is offline   Reply With Quote
Old 04-08-2004, 03:39 PM   #2
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,036
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
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.
RichO is offline   Reply With Quote
Old 04-09-2004, 03:19 PM   #3
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
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.

sloaner14 is offline   Reply With Quote
Old 04-09-2004, 03:57 PM   #4
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,036
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
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?
RichO is offline   Reply With Quote
Old 04-09-2004, 04:06 PM   #5
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
Exactly, I guess I just didn't know how to ask that.
sloaner14 is offline   Reply With Quote
Old 04-09-2004, 04:09 PM   #6
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
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.
sloaner14 is offline   Reply With Quote
Old 04-09-2004, 04:10 PM   #7
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
Damn I can type today.

sloaner14 is offline   Reply With Quote
Old 04-09-2004, 05:11 PM   #8
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,036
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
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 is offline   Reply With Quote
Old 04-09-2004, 09:50 PM   #9
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,036
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
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.
RichO is offline   Reply With Quote
Old 04-13-2004, 09:40 AM   #10
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
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.
Attached Files
File Type: zip TSchedule.zip (22.6 KB, 134 views)
sloaner14 is offline   Reply With Quote
Old 04-13-2004, 03:43 PM   #11
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,036
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
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?
RichO is offline   Reply With Quote
Old 04-14-2004, 05:22 AM   #12
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
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.
sloaner14 is offline   Reply With Quote
Old 04-14-2004, 07:48 AM   #13
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,036
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
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.
Attached Files
File Type: zip TSchedule.zip (40.4 KB, 116 views)
RichO is offline   Reply With Quote
Old 04-14-2004, 01:07 PM   #14
sloaner14
Registered User
 
Join Date: Apr 2002
Location: lawton, ok, us
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
sloaner14
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.
Attached Files
File Type: zip TSchedule.zip (24.7 KB, 110 views)
sloaner14 is offline   Reply With Quote
Old 04-14-2004, 02:33 PM   #15
RichO
Registered Yoozer
 
RichO's Avatar
 
Join Date: Jan 2004
Location: Wisconsin, USA
Posts: 1,036
Thanks: 14
Thanked 2 Times in 2 Posts
RichO is on a distinguished road
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?

RichO is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 10:29 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World