Originally Posted by
Alx
So here's what I have up to now...
Couple quick questions...
Is there a way to populate the calendar dates or do I have to fill them in by hand? See below for example code. This assumes you NEED a separate calendar table.
When it comes time to compile my monthly report, will I have to make a totals query for every sex, language and age group (M, F, En, Fr, etc) before combining them with a query query? I would need to know more specifically what you're trying to query. Regarding the age group table, it might be advisable to calculate the age group at the time of the report based on the date of birth. This way you won't need to update an age group table in as people graduate to the next group.
And finally, when I make my form, is there a way to lock the date You can lock the control in the properties window and have a slot already numbered for every bed for my data entry people to just select the user name from a roll up menu? (I just want to know if it's possible) Yes! Just have a command button that will query all the beds, then loop over the beds and for each one add a record to TblPresence for that day. See below for example code.
Thank you so much guys!
Code:
Public Sub test()
'PopulateCalendarTable Date, 10000
End Sub
Public Sub PopulateCalendarTable(StartDate As Date, NumOfDays As Long)
On Error GoTo ErrHandler_PopulateCalendarTable
Dim db As DAO.Database
Dim qry As String
Dim x As Long
'parameter validation
If NumOfDays < 1 Then
Err.Raise 1001, , "Number of days to add must be greater than or equal to 1."
End If
Dim LastDate As Variant
LastDate = DMax("CalendrierDate", "TblCalendrier")
If Not IsNull(LastDate) Then
If LastDate >= StartDate Then
Err.Raise 1002, , "You're attempting to add calendar days that are earlier than or equal to records that already exist."
End If
End If
Set db = CurrentDb
For x = 0 To NumOfDays - 1
qry = "INSERT INTO TblCalendrier(CalendrierDate) VALUES (#" & (StartDate + x) & "#);"
'Debug.Print qry
db.Execute qry, dbFailOnError
Next x
ExitHandler_PopulateCalendarTable:
Set db = Nothing
Exit Sub
ErrHandler_PopulateCalendarTable:
MsgBox Err.Description, vbInformation, "PopulateCalendarTable: Error #" & Err.Number
Resume ExitHandler_PopulateCalendarTable
End Sub
Code:
Private Sub LoadBeds(CalendarId as Variant)
On Error GoTo ErrHandler_LoadBeds
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim qry As String
If IsNull(CalendarId) Then Err.Raise 1001, , "Calendar ID is Null"
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM TblPresence WHERE DateCalendrier=" & CalendarId)
If Not (rs.BOF And rs.EOF) Then
'Query found some records
Err.Raise 1002, , "Records already exist for this day."
Else
'Query didn't find anything so let's add the beds
Set rs2 = db.OpenRecordset("SELECT * FROM TblLit")
If Not (rs2.BOF And rs2.EOF) Then
'got a list of all the beds, add records to the occupations table
rs2.MoveFirst
Do While Not rs2.EOF
rs.AddNew
rs!DateCalendrier= CalendarId
rs!Lit = rs2!BedId
rs!Usager = Null 'tbd by user in GUI
rs.Update
rs2.MoveNext
Loop
Else
'Query didn't find any beds
Err.Raise 1003, , "The beds table appears to be empty"
End If
rs2.Close
End If
rs.Close
'REQUERY YOUR SUBFORM HERE TO SHOW THE BEDS
ExitHandler_LoadBeds:
Set rs = Nothing
Set rs2 = Nothing
Set db = Nothing
Exit Sub
ErrHandler_LoadBeds:
MsgBox Err.Description, vbInformation, "LoadBeds: Error #" & Err.Number
Resume ExitHandler_LoadBeds
End Sub