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:

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

puppydogbuddy

try this syntax:

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

AdrianGawrys

Quote:

Originally Posted by puppydogbuddy

try this syntax:

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

Thanks but unfortuantely this doesn't help.

puppydogbuddy

Quote:

Originally Posted by AdrianGawrys

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

Quote:

Originally Posted by puppydogbuddy

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

Quote:

Originally Posted by AdrianGawrys

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

Quote:

Originally Posted by puppydogbuddy

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

Quote:

Originally Posted by AdrianGawrys

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

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

End If

AdrianGawrys

Quote:

Originally Posted by 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

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  '<<<<<<<<<<<<<<<<<<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.

Subform requery problem

re: Subform requery problem

re: Subform requery problem

re: Subform requery problem

re: Subform requery problem

re: Subform requery problem

re: Subform requery problem

re: Subform requery problem

re: Subform requery problem

re: Subform requery problem

re: Subform requery problem

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about