I recently rewrote onClick procedure for Calendar on my form (frmRota). It opens record from tblRotas where field rDate is equal the one selected on Calendar. If such a record doesnt exist I want it to be created. Before only way to create new record was to click on NewRecord button that I created using Access2003 button wizard. Calendar is not bound to any field in recordsource but on this form I have text box rDate that is bound to tblRota.rDate field which I update at the end of onClick procedure. Everything is ok when record exists - but when I try to create new record by clicking on Date that doesnt have corresponding record in my table (tblRotas) I get an error when populating combobox on subform attached to the frmRota form. Error is : "You entered an expression that has an invalid reference to the property Form/Report."
I distinguished the line that produces the error.
I know that my explanation might be a bit confusing I am pretty much novice in Access DB programming so please excuse me not the best vb code. Here is all the code attached to frmRota:
Expand|Select|Wrap|Line Numbers
- Private Sub Calendar1_Click()
- GBL_CurrRotaDate = Me.Calendar1
- GBL_CurrRotaDay = Get_DayFromDate(CStr(GBL_CurrRotaDate))
- Me.rWeekNo = Format(GBL_CurrRotaDate, "ww") - 8
- If Me.rWeekNo <= 0 Then
- Me.rWeekNo = Me.rWeekNo + 55
- End If
- STRSQL = "Select count(rDate) as Rcount from tblRotas where rDate = CDATE('" & GBL_CurrRotaDate & "')"
- Set Db = CurrentDb
- Set rs = Db.OpenRecordset(STRSQL)
- If Not rs.EOF Then
- 'Check if Rota for that date already exists
- If rs!Rcount >= 1 Then ' Rota already exists
- Me.rDate.SetFocus
- DoCmd.FindRecord Me.Calendar1, acEntire
- rs.Close
- Db.Close
- Exit Sub
- Else 'rota doesnt exist so create new record
- rs.Close
- Db.Close
- Me.Form.SetFocus
- DoCmd.GoToRecord , , acNewRec
- If Not Me.Calendar1 Then
- Me.rDate = Me.Calendar1 'calendar not bound to tblRotas.rDate so need to update rDate box which is bound to rDate field in tblRotas table
- GBL_CurrRotaDate = Me.Calendar1 'setting global rota date
- GBL_CurrRotaDay = Get_DayFromDate(CStr(GBL_CurrRotaDate))
- Me.rDate = Me.Calendar1
- Me.rWeekNo = Format(GBL_CurrRotaDate, "ww") - 8 ' where I leave financial year starts from March thats why -8
- If Me.rWeekNo <= 0 Then 'if current data between January and March update week by adding 55 so week is not negative
- Me.rWeekNo = Me.rWeekNo + 55
- End If
- End If
- PopulateListsBoxes 'function at the end of frmRota module - within which I receive error when creating new record
- End If
- End If
- End Sub
Expand|Select|Wrap|Line Numbers
- Private Sub Form_Current()
- Dim rs As Recordset
- Dim Db As Database
- Dim STRSQL As String
- If Not Me.rDate Then
- Me.Calendar1 = Me.rDate
- End If
- If Not Me.CurrentRecord Then
- Me.Caption = "Create Rota No. " & Me.CurrentRecord
- End If
- If Not Len(Me.rDate) = 0 Then
- GBL_CurrRotaDate = Me.rDate
- GBL_CurrRotaDay = Get_DayFromDate(CStr(GBL_CurrRotaDate))
- PopulateListsBoxes
- End If
- End Sub
Expand|Select|Wrap|Line Numbers
- Private Sub sfrmRota_Enter()
- If Me.rDate = 0 Then 'rDate is what bounds frmRota with sfrmRota so it cant be NULL or 0
- MsgBox "Please Select Date First"
- Me.Calendar1.SetFocus
- End If
- If IsNull(Me.rDate) Then 'rDate is what bounds frmRota with sfrmRota so it cant be NULL or 0
- MsgBox "Please Select Date First"
- Me.Calendar1.SetFocus
- Exit Sub
- End If
- End Sub
Expand|Select|Wrap|Line Numbers
- Public Function PopulateListsBoxes()
- Set Db = CurrentDb
- CurrentDb.Execute ("Delete from tblAvailableEmployees")
- STRSQL = "Insert into tblAvailableEmployees (EmployeeID) Select employeeID from tblDaysOff where " & GBL_CurrRotaDay & _
- "=0" 'Selecting employees that are contracted on selected day
- CurrentDb.Execute (STRSQL)
- STRSQL = "Insert into tblAvailableEmployees (EmployeeID) Select employeeID from tblOT where otDate = #" & GBL_CurrRotaDate & "#;" 'Adding employees that agreed to do Overtime
- CurrentDb.Execute (STRSQL)
- Me.sfrmRota.Controls.Item(2).Requery 'Thats the part that errors when creating new record. I am using this for combobox( item(2) on subform ) to requery tblAvailableEmployees for new data
- End Function