Hi
My project is to create a timesheet.
I have created the timesheet and it works well. I have another form which I open first. It contains a command button that is used to open the Timesheet form. The code behind this button throws up an error 'User defind type not defind' pointing to the line 'Dim dbs As DAO.Database'
Im teaching myself so not to advance please. I have inserted the complete code below for the command button. Much appreciated.
Private Sub CmdStart_Click()
Dim tWkno As String
Dim tSOW As Date
Dim num As Integer
Dim code As String
Dim ch As String
Dim NewDate As Date
Dim dbs As DAO.Database 'code stops here'
Dim rstSt As DAO.Recordset 'record set for Staff table
Dim rstWe As DAO.Recordset 'record set for WorkingWeek table
Dim rstTi As DAO.Recordset 'record set for Timesheet table
Const tblSt = "tblStaff"
Const tblWe = "tblWorkingWeek"
Const tblTi = "tblTimeSheet"
Set dbs = CurrentDb
Set rstTi = dbs.OpenRecordset(tblTi) 'assigns recordset for Timesheet
Set rstSt = dbs.OpenRecordset(tblSt) 'assigns recordset for Staff
Set rstWe = dbs.OpenRecordset(tblWe) 'assigns recordset for WorkingWeek
rstWe.MoveLast 'goes to the last record in the Table
code = rstWe![WeekNo] 'stores the code for Staff Number in the code
ch = Left(code, 4) 'copies the 3 left characters from StaffNumber into ch
num = Val(Right(code, 3)) 'copies the 1 right characters from StaffNumber into num then converts it to a number
num = num + 1 'this adds 1 to the numerical part of StaffNumber
code = ch & num 'this joins the 3 left characters and the 3 digits to give a new StaffNumber
NewDate = rstWe![Startofweek] + 7
rstWe.AddNew 'creates a new record
DoCmd.GoToRecord , , acNewRec 'creates a new record
rstWe![WeekNo] = code 'copies the new Staff Number into StaffNumber of the new record
rstWe![Startofweek] = NewDate
rstWe.Update
rstSt.MoveFirst
Do Until rstSt.EOF
rstTi.AddNew
rstTi![StaffNo] = rstSt![StaffNo]
rstTi![WeekNo] = rstWe![WeekNo]
rstTi.Update
rstSt.MoveNext
Loop
rstTi.Close
rstWe.Close
rstSt.Close
End Sub
Cheers
Terry
My project is to create a timesheet.
I have created the timesheet and it works well. I have another form which I open first. It contains a command button that is used to open the Timesheet form. The code behind this button throws up an error 'User defind type not defind' pointing to the line 'Dim dbs As DAO.Database'
Im teaching myself so not to advance please. I have inserted the complete code below for the command button. Much appreciated.
Private Sub CmdStart_Click()
Dim tWkno As String
Dim tSOW As Date
Dim num As Integer
Dim code As String
Dim ch As String
Dim NewDate As Date
Dim dbs As DAO.Database 'code stops here'
Dim rstSt As DAO.Recordset 'record set for Staff table
Dim rstWe As DAO.Recordset 'record set for WorkingWeek table
Dim rstTi As DAO.Recordset 'record set for Timesheet table
Const tblSt = "tblStaff"
Const tblWe = "tblWorkingWeek"
Const tblTi = "tblTimeSheet"
Set dbs = CurrentDb
Set rstTi = dbs.OpenRecordset(tblTi) 'assigns recordset for Timesheet
Set rstSt = dbs.OpenRecordset(tblSt) 'assigns recordset for Staff
Set rstWe = dbs.OpenRecordset(tblWe) 'assigns recordset for WorkingWeek
rstWe.MoveLast 'goes to the last record in the Table
code = rstWe![WeekNo] 'stores the code for Staff Number in the code
ch = Left(code, 4) 'copies the 3 left characters from StaffNumber into ch
num = Val(Right(code, 3)) 'copies the 1 right characters from StaffNumber into num then converts it to a number
num = num + 1 'this adds 1 to the numerical part of StaffNumber
code = ch & num 'this joins the 3 left characters and the 3 digits to give a new StaffNumber
NewDate = rstWe![Startofweek] + 7
rstWe.AddNew 'creates a new record
DoCmd.GoToRecord , , acNewRec 'creates a new record
rstWe![WeekNo] = code 'copies the new Staff Number into StaffNumber of the new record
rstWe![Startofweek] = NewDate
rstWe.Update
rstSt.MoveFirst
Do Until rstSt.EOF
rstTi.AddNew
rstTi![StaffNo] = rstSt![StaffNo]
rstTi![WeekNo] = rstWe![WeekNo]
rstTi.Update
rstSt.MoveNext
Loop
rstTi.Close
rstWe.Close
rstSt.Close
End Sub
Cheers
Terry