Week Commencing Drop Down Box - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Week Commencing Drop Down Box

Author

Topic

stevecoombs
Starting Member

1 Post

Posted - 2005-01-05 : 21:11:33

Hello.

I am designing a rota system for work, it is always done weekly and done by week commencing.

I want to:
Enter in a Field the Year, Enter in another field the month (drop Down), in the 3rd box I want a drop down list to appear of all the weeks commencing in that month, e.g:

3 Jan 05
10 Jan 05
17 Jan 05.

Thats problem one. It would be great if someone could make a mock table and form with it working so I can cut and paste it.

This is how I have or am hoping to lay out my rota Form:

Bar Name: Cocktail Bar ¦
-----------------------¦------------¦
¦ ¦NAME¦ ¦
Shift (Morning) ¦ ¦Shift¦ ¦
¦ ¦
---------------------------------------------------
¦ ¦Name¦ ¦ ¦Name¦ ¦
Shift (Evening) ¦ ¦Shift¦ ¦ ¦Shift¦ ¦
-----------------------¦------------¦-------------¦

This is a small version, on a busy night I have upto 40 staff, Say I put Sally in on the morning Shift, Then again for Evening, how can I make Access do a check to see if she has already worked that day.

Look forward to your responds, have heard highly of this Forum.

Thanks.

Steve Coombs

<edit> Moved to Access forum </edit>

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-06 : 06:24:33

Suppose,
three combos on the form: cmbYear, cmbMonth, cmbMondays
of RowSourceType = "List of Values": 2005;2006;2007; & Jan;Feb;Mar;...;Dec;
and empty list for the 3rd combo; it will be populated on the fly, by this VB code:

Private Sub cmbMonth_AfterUpdate()
On Error GoTo 1
Dim i, r, s
s = ""
For i = 1 To 31
r = cmbMonth & " " & i & " " & cmbYear
If DatePart("w", CDate(r)) = vbMonday Then
s = s & r & ";"
End If
Next i
1
Me.cmbMondays.RowSource = s
End Sub

and the same code for Private Sub cmbYear_AfterUpdate()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources