Help with my Calendar - One for you VBA'ers

[yus786]

In the process of putting my Student Db together and with the help of everyone here - I'm doing quite well.

I have attached a calendar I'm struggling with. I got this from http://www.mvps.org/access/ and have tweaked it to my liking.

I have increased the Attendance Type to

0 No Record
1 Present
2 Sick
3 Holiday
4 Late
5 Excused
6 Weekend

So my 'clicks' on the calendar will scroll through the above and insert different colours accordingly.

HOWEVER i would like the weekends to permanently be AttID of 6 (currently all fields are AttID of 0)

Can someone help me please?

 

[khawar]

HOWEVER i would like the weekends to permanently be AttID of 6 (currently all fields are AttID of 0

Cant understand this

 

[yus786]

Cant understand this

If you open the Calendar Form - all the dates are currently grey meaning AttID of 0

If you click on any date - it changes colour. The different colours relate to the different types of AttID

AttID of 6 = Weekend.

Now rather than someone going in each week and physically clicking on the weekends to get the AttID of 6

I want the form to be loaded with the weekends pre populated.

Basically i want the calendar form to open with Mon to Fri as grey and Sat/Sun as Dark Charcoal

Thanks

 

[khawar]

But only changing color on your form will not add data to your table

 

[yus786]

But only changing color on your form will not add data to your table

So how can i do this?

I want to run queries that ignores Weekends

SO if a child was

Present Monday to Friday in Week 1
Present Monday to Thursday and Late on Friday in Week 2
And so on

I want to be able to run queries and reports - that only only look at Monday to Friday.

Any idea how this can be done?

I thought if the Weekends were AttID of 6 - i can have this in my query that will ignore any AttID of 6

Thanks for your help khawar - you've been really helpful.

 

[khawar]

You can use this in your query
Where attid not in(6)

 

[yus786]

You can use this in your query
Where attid not in(6)

yes but currently all the fields are attid of 0 meaning No Record

I want the form to load with the weekends pre-populated as attid of 6

 

[raskew]

Hi -

First thing I noticed was that the calendar is incorrectly populated with days/dates. To correct,

In Sub RefDates()
Convert
D2 = DatePart("w", D1, vbMonday)
Do Until DatePart("w", D1, vbMonday) = 1
To:
D2 = DatePart("w", D1, vbSunday)
Do Until DatePart("w", D1, vbSunday) = 1

Bob

 

[yus786]

Hi -

First thing I noticed was that the calendar is incorrectly populated with days/dates. To correct,

In Sub RefDates()
Convert
D2 = DatePart("w", D1, vbMonday)
Do Until DatePart("w", D1, vbMonday) = 1
To:
D2 = DatePart("w", D1, vbSunday)
Do Until DatePart("w", D1, vbSunday) = 1

Bob

Thanks but i prefer to start the week on Monday. Am i right in saying the above will start the week on a Sunday?

 

[raskew]

Start an entry for a student. Now, today is Wednesday, 23-July-08. Is that what the calendar shows?

Changing the code from vbMonday to vbSunday allows the calendar to populate correctly. Doesn't have to do with anything else.

Bob

Added:

OK, I take it back, I think. I was viewing the calendar as it's normally displayed, i.e. Sunday is the first column on the left, Saturday is the last column on the right. Failed to note that you'd created your own version with Monday as the first column, Sunday as the last.