Appointment Scheduler (1 Viewer)

[Jerry Stoner]

=DateDiff("d",DatePart("w",Now()-2),Now())
Returns Monday of current week

Now Ive got a form (unbound) with a txtBox set to the above and 6 more referencing txtMon + 1 to 6 for tues to sun.
So far so good...

How do I allow increments for subsequent weeks?

Or is there a better way?

 

[Jerry Stoner]

Never Mind...

Got it

 

[Cosmos75]

John.Woody's file doesn't work in Access 2000

John.Woody,

I can't get your DB to work in Access 2000, it said it might be a problem with some DAO language that is no loner supported. Any ideas how to fix this?

Thanks!

 

[RichMorrison]

Jerry,

You wrote
<<
Ok Ive got a daily schedule made for up to 10 people with 15 minute intervals for the day.

Next issue is a weekly form in a "crosstab" format but not really a crosstab since it must be updateable. Im thinking a form showing Monday through Friday with 7 subforms one for each... well you get the idea.
>>

I would try something different. The idea of 7 subforms scares me.

If you want a "week at a glance", then I would make a table with Start Date, End Date, and Monday through Sunday in separate fields. This is not normalized but it is much easier to update with a form.

You can select the "current" week's data with a query that returns rows where the current date is between the Start and End dates. You can page forward by adding 7 days and requerying the form.

Them is my thoughts.

RichM

 

[Jerry Stoner]

Good point Rich Ill try that.7 subforms is a bit brutal.Currently I have a tblApt with 300,000 + records showing Date and Appointment time (seperate tables actually hold Dates for 20 years and Time slots for 1 day) for 20 years and 10 people to use the scheduler. Idea is say for a doctors office. Multiple providers having appointments at the same time.

Then a Provider form for to limit to view 1 provider at a time and a SchedDate subform to limit Date with a sub subform showing 15 min intervals for a given provider on a given day.
Im using a select query tied to a calender colnrol now.

<Edit>
Oh yeah I forgot, also an unbound frmCust to add client names by ID to the schedule. Havent figured how to automate adding a client to schedule yet. Copy and paste ID into time slot works for now. Pretty seamless except for the Copy/Paste part.
</Edit>

Anyway I like your idea but maybe a make table query to get the appropriate weeks data from the main table then update back to Main tbl when done? Not sure if Ill gain or lose processing time/stability but Ill try it both ways and see what happens.

Comments, rude remarks and alternate solutions welcome.

 

[John.Woody]

Cosmos75

All I did was upgrade it from Access v1 and get the correct VB references. It may be worth checking those are all there, from the VB window.

It looks like it is having trouble in Windows 2K. The windows API utilities were designed to run in 3.1. They also work in Win95 and Win98 but not Win 2K.

Make sure you have 'Utility' in the vb refrences, then go through the forms and look at the on open event. If its something like "=wu_CentreDoc(Form.Hwnd)" then delete that and try again.

Alternatively use a Win98 PC.

HTH
John

 

[Jerry Stoner]

hmm...

Ive built the monthly form and it works well...till I try to move to a different week.

=DateDiff("d",DatePart("w",Now()-2),Now()) is my control source for txtMon in my subform.
Ive added an unbound txtChangeWeek and a Command Button cmdJumpWeeks to my main form. Here is the code on the Click event:

Private Sub cmdJumpWeeks_Click()
[sfrmWeeklySched].Form![txtMon].ControlSource = DateDiff("d", DatePart("w", Now() - 2), Now()) + Me.txtChangeWeek * 7
End Sub

Now the control source starts off as
=DateDiff("d",DatePart("w",Now()-2),Now())

When I put the number 1 in txtChangeWeek and click the command button the control source becomes 37480.
If I use 2 the control source becomes 37487.
Obvoiusly Im counting dates here but the display is #Name?

I thought the control source would become
DateDiff("d", DatePart("w", Now() - 2), Now()) + Me.txtChangeWeek * 7
Instead of 37480

What am I missing here?

 

[RichMorrison]

Jerry,

I'm just scanning this on Sunday, but I think you want "DateAdd" instead of "DateDiff".

Something like
NextMonday = DateAdd("d",7,ThisMonday)

Not sure if that is the exact syntax, but it's close.

RichM

 

[Jerry Stoner]

Yeah Im not goig too far on Sun either:
=DateDiff("d",DatePart("w",Now()-2),Now()) +DateAdd("d",7,ThisMonday)

Is that what you mean?

Until tommorow, Thanks.

 

[RichMorrison]

you wrote
<<
=DateDiff("d",DatePart("w",Now()-2),Now()) +DateAdd("d",7,ThisMonday)

Is that what you mean?
>>

NO !!!!!!!!!!

Don't use DateDiff.
Use DateAdd as in:
NextMonday = DateAdd("d",7,ThisMonday)
where NextMonday and ThisMonday are date variables.

RichM

 

[Jerry Stoner]

Thanks Rich, will try out when I get a chance.