Hi,
This is my first forum post. I am developing a schedule/rota system in Access 2000. For security reasons Access is locked down tight and I am not able to use controls like the MS FlexGrid etc.
I have normalised my data and created several tables, one of which holds shifts:
tbl_shifts: ShiftID, FK_EmployeeID, Start_Date, Start_Time, End_Time, FK_Duty_Type
where FK is a foreign key, start date is the start date of a shift and start/end times define when the shift starts and ends.
I need to display shifts for employees in following format:
Week Com Monday Tuesday Wednesday
J Smith 02/05/05 OD 17:00-22:00 OD 18:00-02:00
J Smith 02/05/05
R Carter 02/05/05 OD 02:00-09:00 OD 17:00-03:30
R Carter 02/05/05 OD 22:00-07:00
P Jones 02/05/05 TR 09:00-17:00 OD 17:00-03:30
P Jones 02/05/05
Where OD and TR are specific duty types.
I have created a form which will eventually hold a sub form to display the schedule/rota. The user can enter a start date into a text box and click a button to show the rota for that week. At least it will once I’ve finishedJ
Using the start date I have created a query which returns all records between start date and six days after, and concatenates the duty type, start time and end time:
PARAMETERS Forms!frm_Rota_Display!txtWeekCom DateTime;
SELECT fullName([First_Name],[Last_Name]) AS txtName, tbl_Rota_Shifts.Start_Date, fnctShift([Duty_Short],[Start_Time],[End_Time]) AS txtShift
FROM tbl_Employees INNER JOIN (tbl_Duty_Types INNER JOIN tbl_Rota_Shifts ON tbl_Duty_Types.Duty_TypesID = tbl_Rota_Shifts.FK_Duty_Type) ON tbl_Employees.EmployeeID = tbl_Rota_Shifts.FK_EmployeeID
WHERE (((tbl_Rota_Shifts.Start_Date) Between [Forms]![frm_Rota_Display]![txtWeekCom] And ([Forms]![frm_Rota_Display]![txtweekcom]+6)) AND ((tbl_Rota_Shifts.Shift_Changed) Like False));
The query works as a stand alone query but I am trying to call it using VBA and I get an error message:
Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, ‘UPDATE’.
If I remove the parameters from the query then I can capture the data but obviously being able to only capture data for an appropriate week is key.
The VBA code used is below:
Private Sub cmdUpdateDisplay_Click()
On Error GoTo Err_cmdUpdateDisplay_Click
Rem macro code to capture rota data for a given week and place in an array
Rem Sort it
Rem then display it in form
Dim rst As ADODB.Recordset ' create recordset object
Dim y As Integer
Set rst = New ADODB.Recordset ' instantiate recordset object
rst.ActiveConnection = CurrentProject.Connection ' set the recordset active connection to the current project
rst.CursorType = adOpenStatic ' set the cursor type to allow forward and backward movement through recordset
rst.Open "qry_concatenated_shift_Times" ' open the recordset with the results of query
ReDim arrWeeklyRota(rst.RecordCount, 2) ' redimensions the array to be the same size as the count of records
'Debug.Print rst.RecordCount ' prints the count of records in immediate window
rst.MoveFirst ' move to first record
For y = 1 To rst.RecordCount ' from beginning to end of recordset
Rem place values from records into array
arrWeeklyRota(y, 0) = rst![txtName]
arrWeeklyRota(y, 1) = rst![Start_Date]
arrWeeklyRota(y, 2) = rst![txtShift]
Rem can delete/comment out - used to check what data is returned
Debug.Print arrWeeklyRota(y, 0)
Debug.Print arrWeeklyRota(y, 1)
Debug.Print arrWeeklyRota(y, 2)
If rst.EOF = False Then ' if end of file has not been reached...
rst.MoveNext ' move to next record
End If
Next
rst.Close ' close the recordset
Set rst = Nothing ' and kill
Exit_cmdUpdateDisplay_Click:
Exit Sub
Err_cmdUpdateDisplay_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateDisplay_Click
End Sub
I read one thread that suggested using DAO would be better but my attempts to do this have caused different problems.
Can anyone suggest how I can use parameters in this situation or point me to threads that have dealt with this. I had a good look this afternoon but could not see anything.
Thank you
Frozbie
This is my first forum post. I am developing a schedule/rota system in Access 2000. For security reasons Access is locked down tight and I am not able to use controls like the MS FlexGrid etc.
I have normalised my data and created several tables, one of which holds shifts:
tbl_shifts: ShiftID, FK_EmployeeID, Start_Date, Start_Time, End_Time, FK_Duty_Type
where FK is a foreign key, start date is the start date of a shift and start/end times define when the shift starts and ends.
I need to display shifts for employees in following format:
Week Com Monday Tuesday Wednesday
J Smith 02/05/05 OD 17:00-22:00 OD 18:00-02:00
J Smith 02/05/05
R Carter 02/05/05 OD 02:00-09:00 OD 17:00-03:30
R Carter 02/05/05 OD 22:00-07:00
P Jones 02/05/05 TR 09:00-17:00 OD 17:00-03:30
P Jones 02/05/05
Where OD and TR are specific duty types.
I have created a form which will eventually hold a sub form to display the schedule/rota. The user can enter a start date into a text box and click a button to show the rota for that week. At least it will once I’ve finishedJ
Using the start date I have created a query which returns all records between start date and six days after, and concatenates the duty type, start time and end time:
PARAMETERS Forms!frm_Rota_Display!txtWeekCom DateTime;
SELECT fullName([First_Name],[Last_Name]) AS txtName, tbl_Rota_Shifts.Start_Date, fnctShift([Duty_Short],[Start_Time],[End_Time]) AS txtShift
FROM tbl_Employees INNER JOIN (tbl_Duty_Types INNER JOIN tbl_Rota_Shifts ON tbl_Duty_Types.Duty_TypesID = tbl_Rota_Shifts.FK_Duty_Type) ON tbl_Employees.EmployeeID = tbl_Rota_Shifts.FK_EmployeeID
WHERE (((tbl_Rota_Shifts.Start_Date) Between [Forms]![frm_Rota_Display]![txtWeekCom] And ([Forms]![frm_Rota_Display]![txtweekcom]+6)) AND ((tbl_Rota_Shifts.Shift_Changed) Like False));
The query works as a stand alone query but I am trying to call it using VBA and I get an error message:
Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, ‘UPDATE’.
If I remove the parameters from the query then I can capture the data but obviously being able to only capture data for an appropriate week is key.
The VBA code used is below:
Private Sub cmdUpdateDisplay_Click()
On Error GoTo Err_cmdUpdateDisplay_Click
Rem macro code to capture rota data for a given week and place in an array
Rem Sort it
Rem then display it in form
Dim rst As ADODB.Recordset ' create recordset object
Dim y As Integer
Set rst = New ADODB.Recordset ' instantiate recordset object
rst.ActiveConnection = CurrentProject.Connection ' set the recordset active connection to the current project
rst.CursorType = adOpenStatic ' set the cursor type to allow forward and backward movement through recordset
rst.Open "qry_concatenated_shift_Times" ' open the recordset with the results of query
ReDim arrWeeklyRota(rst.RecordCount, 2) ' redimensions the array to be the same size as the count of records
'Debug.Print rst.RecordCount ' prints the count of records in immediate window
rst.MoveFirst ' move to first record
For y = 1 To rst.RecordCount ' from beginning to end of recordset
Rem place values from records into array
arrWeeklyRota(y, 0) = rst![txtName]
arrWeeklyRota(y, 1) = rst![Start_Date]
arrWeeklyRota(y, 2) = rst![txtShift]
Rem can delete/comment out - used to check what data is returned
Debug.Print arrWeeklyRota(y, 0)
Debug.Print arrWeeklyRota(y, 1)
Debug.Print arrWeeklyRota(y, 2)
If rst.EOF = False Then ' if end of file has not been reached...
rst.MoveNext ' move to next record
End If
Next
rst.Close ' close the recordset
Set rst = Nothing ' and kill
Exit_cmdUpdateDisplay_Click:
Exit Sub
Err_cmdUpdateDisplay_Click:
MsgBox Err.Description
Resume Exit_cmdUpdateDisplay_Click
End Sub
I read one thread that suggested using DAO would be better but my attempts to do this have caused different problems.
Can anyone suggest how I can use parameters in this situation or point me to threads that have dealt with this. I had a good look this afternoon but could not see anything.
Thank you
Frozbie
