Subform requery problem

[AdrianGawrys]

Hi guys,
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:

 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
 

 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
 

 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
 

 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
 

[puppydogbuddy]

try this syntax:

Me!sfrmRota.Form!Item(2).Requery

[AdrianGawrys]

try this syntax:

Me!sfrmRota.Form!Item(2).Requery

Thanks but unfortuantely this doesn't help.

[puppydogbuddy]

Thanks but unfortuantely this doesn't help.

Please provide some details.......I am not sitting at your computer....what happens.....what is the error message ?

[AdrianGawrys]

Please provide some details.......I am not sitting at your computer....what happens.....what is the error message ?

Like I wrote in my first post - Error message is
"You entered an expression that has an invalid reference to the property Form/Report." and it comes up under line 9 of PopulateListsBoxes() function but only when condition from line 14 (If rs!Rcount >= 1 Then) of "Private Sub Calendar1_Click()" is False - meaning: there is no rota in tblRotas with date (rDate) specified by user (value of Calendar1) if condition from line 14 is met then PopulateListsBoxes doesn't fail - no error whatsoever.
Any ideas? I can't find a workaround to this problem.

[puppydogbuddy]

Like I wrote in my first post - Error message is
"You entered an expression that has an invalid reference to the property Form/Report." and it comes up under line 9 of PopulateListsBoxes() function but only when condition from line 14 (If rs!Rcount >= 1 Then) of "Private Sub Calendar1_Click()" is False - meaning: there is no rota in tblRotas with date (rDate) specified by user (value of Calendar1) if condition from line 14 is met then PopulateListsBoxes doesn't fail - no error whatsoever.
Any ideas? I can't find a workaround to this problem.

Well, if you want to fire the code below when you have an existing record:
=================================================
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

Why can't you do it like this:
========================
If Not Me.NewRecord then
Me.sfrmRota.Controls.Item(2).Requery
Else
' what you want program to do if you have a new record
End If

[AdrianGawrys]

Well, if you want to fire the code below when you have an existing record:
=================================================
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

Why can't you do it like this:
========================
If Not Me.NewRecord then
Me.sfrmRota.Controls.Item(2).Requery
Else
' what you want program to do if you have a new record
End If

Thanks a bunch, I feel like I am slowly moving in a good direction but the problem is that Me.NewRecord is set to 0 at this point for some reason. @puppydogbuddy would you be interested in looking into the database file itself? I can share them somewhere on public location and send you link if you are interested.

[puppydogbuddy]

Thanks a bunch, I feel like I am slowly moving in a good direction but the problem is that Me.NewRecord is set to 0 at this point for some reason. @puppydogbuddy would you be interested in looking into the database file itself? I can share them somewhere on public location and send you link if you are interested.

Adrian,
The code I gave you was : If <<<Not>>> Me.NewRecord then.

If you need me to look at the file, you can zip and email to the address on my VCard (with my profile), In order for me to look at your file, it has to be Access version 2000 or 1997(can convert to 2000).

[puppydogbuddy]

Adrian,
Just noticed this. Could line 7 be your problem? it looks to me like line 7 is backwards. I think it should be: Me.rDate = Me.Calendar1

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  '<<<<<<<<<<<<<<<<<<Problem???
End If

[AdrianGawrys]

Adrian,
Just noticed this. Could line 7 be your problem? it looks to me like line 7 is backwards. I think it should be: Me.rDate = Me.Calendar1

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  '<<<<<<<<<<<<<<<<<<Problem???
End If

No it's not a problem actually - Calendar is not bound to rDate so if you open form with rota I need Calendar to show what day is the rota for. It's under Form_Current because before I had "move to next" and "move to previous" record selectors so also needed to show what date is the rota for.

[AdrianGawrys]

Sorry for wasting your time but I manage to find a solution to my problem - I just removed the line where I execute PopulateListBoxes() function after creating new record and put it under AfterInsert() Sub of the form. That seem to fixed my problem - for some reason running my function right after creating new record didnt allow me to address subform's controls - like they haven't been created yet. Now everything seems to be ok.