Billiam said:
I am wondering if anyone could advise on a good timesheet design.
I would like my timesheet form to display the payperiod from a combobox.
I think I would just make a reference/lookup table with all the possible
values...Could this be done from a make table query which would
automatically
make the table based on the year? and is there an example of this, or
would
someone be able to help me as i have never done this before?
Here's some code that will help:
Public Function BuildCalendar(strTablename As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)
'********************************************************************
' Name: BuildCalendar
' Purpose: Create a calendar table
'
' Inputs: strTableName As String
' dtIn As Date
' dtmEnd As Date
' ParamArray varDays() As Variant
' Author: Arvin Meyer
' Date: January 09, 2000
' Comment: Days of week to be included in calendar
' as value list: 2,3,4,5,6 for Mon-Fri
' Use 0 to include all days of week
'
'********************************************************************
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long
Set db = CurrentDb
If CheckTable(strTablename) = True Then
If MsgBox(" That table exists replace it? " & _
strTablename & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTablename
db.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo Err_BuildCalendar
' create new table
strSQL = "CREATE TABLE " & strTablename & _
"(DateField DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (DateField))"
db.Execute strSQL
' refresh database window
Application.RefreshDatabaseWindow
If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTablename & "(DateField) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
db.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTablename & "(DateField) "
& _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") &
"#)"
db.Execute strSQL
End If
Next varDay
Next dtmDate
End If
Exit_BuildCalendar:
Set db = Nothing
Set tdf = Nothing
Exit Function
Err_BuildCalendar:
Resume Exit_BuildCalendar
End Function
