Problem getting subform to refresh using default settings/option group

[JHite]

I am using Access 2003 on Windows XP.

This is a simple database that contains “tblStaffers” containing names of the office staffers, “tblProjects” containing names of the office projects, and “tblStatusRepor ts” containing the “StafferID” (link to Staffer table), the “ProjectID” (link to Projects table), a “Date” field, and a “Notes” field.

There is a main form, “frmStaffEntry, ” with a subform, “frmStatusRepor ts,” for a user to add/delete/modify status reports for the projects the staffers are working on.

“frmStaffEntry” contains unbound Combo Box “ChooseStaff” where the user chooses the name of the staffer (from “tblStaffers”) whose entries are then shown in “frmStatusRepor ts”

The user can choose how to sort/show the status report entries via an Option Group on “frmStatusRepor ts” named “SortOption” with Default Value = 1. “SortOption” contains 2 Option Buttons: “DateOption” has value set to 1; “ProjectOption” has value set to 2.

The default sort is “qrySRSortDate” - which sorts tblStatusReport s by reverse Date order (first) then by alphabetical Project order (second). (The Record Source for “frmStatusRepor ts” is “qrySRSortDate” )

The other sort choice is “qrySRSortProje ct” which sorts tblStatusReport s by alphabetical Project order (first) then by reverse Date order (second).

“frmStaffEntry” VBA routines are:

Private Sub ChooseStaff_AfterUpdate()
    ‘Find record for chosen staffer
    Dim rs As Recordset
    Set rs=Me.RecordsetClone
    rs.FindFirst “[StafferID] = “ & Str(Nz(Me![ChooseStaff],0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    rs.Close
    Set rs = Nothing
End Sub

Private Sub ChooseStaff_Enter()
   ‘Update subform based on chosen staffer
   Me![ChooseStaff].Requery
   Forms!frmStaffEntry.Form!frmStatusReports.Requery
End Sub

“frmStatusRepor ts” VBA routines are:

Private Sub Form_AfterUpdate()
    ‘ Requery status reports after an entry has been made or modified.
    ‘ Requery uses whichever SortOption is in effect
    Me.Requery
End Sub

Private Sub SortOption_AfterUpdate()
   ‘ Show status reports based on sort choice made by user
   Select Case SortOption.Value
      Case 1
         Me.RecordSource = “qrySRSortDate” 
      Case 2
         Me.RecordSource = “qrySRSortProject”
   End Select
End Sub

This all works great – the user can choose a staffer and switch between the sorts to their hearts content. My problem comes when the user has left the sort on “ProjectOption” then moves to the main form (“frmStaffEntry ”) and chooses another staffer. The sub form (“frmStatusRepo rts”) refreshes but does not revert to the default “DateOption” (“qrySRSortDate ”).
I thought the “frmStaffEntry” subroutine: ChooseStaff_Ent er() shown above would take care of the problem, since the subform record source is “qrySRSortDate” and the “SortOption” default value is set to one.

I've researched on the web but can't find example which uses an Option Group like I have. I’ve tried various other things, such as making RecordSource = “qrySRSortDate” in the ChooseStaff_Ent er() subroutine, but have gotten weird results. My gut feel is that what I’ve got is almost right but I can’t figure out that little gotcha I’ve overlooked.

Any help would be appreciated. Thanks

Janice

[Stewart Ross]

Hi. The default value for your option group no longer applies after an option choice has been made. If the sort order chosen is the non-default project order then until this is changed manually or by code the option group will remain just as it was. Default values cannot reset anything - that is not their purpose.

If the sort order must revert back to date order when another member of staff is selected you can always set the sortoption value back to 1 from the after update event of the staff selection combo. You would also need to set the subform's recordset to the date order query again before you requery. Sample code for this shown below:

[code=vb]With Forms!frmStaffE ntry.Form
.RecordSource = “qrySRSortDate”
!SortOption = 1
End With[/code]
Thank you for providing a very well structured question which was well set-out and thought through.

-Stewart

[JHite]

Thanks, Stewart.

I've added your suggested code at the end of ChooseStaff_Aft erUpdate() as follows:

Private Sub ChooseStaff_AfterUpdate()
   ' Find record for chosen staffer
   Dim rs As Recordset
   Set rs=Me.RecordsetClone
   rs.FindFirst "[StafferID] = " & Str(Nz(Me![ChooseStaff],0))
   If Not rs.EOF Then Me.Bookmark = rs.Bookmark
   rs.Close
   Set rs = Nothing
   
   ' Reset subform default sort
   With Forms!frmStaffEntry.Form
      .RecordSource = "qrySRSortDate"
      !SortOption = 1
   End With

End Sub

but I get a "Run-time error '2465'; Microsoft Access can't find the field 'SortOption' referred to in your expression."
(referring to line 13 !SortOption=1)

"SortOption " is not the name of a field, but is the name of the Option Group in subform "frmStatusRecor ds." Does this make a difference as to how it's referenced in the code you provided????

Janice

Hi. The default value for your option group no longer applies after an option choice has been made. If the sort order chosen is the non-default project order then until this is changed manually or by code the option group will remain just as it was. Default values cannot reset anything - that is not their purpose.

If the sort order must revert back to date order when another member of staff is selected you can always set the sortoption value back to 1 from the after update event of the staff selection combo. You would also need to set the subform's recordset to the date order query again before you requery. Sample code for this shown below:

[code=vb]With Forms!frmStaffE ntry.Form
.RecordSource = “qrySRSortDate”
!SortOption = 1
End With[/code]
Thank you for providing a very well structured question which was well set-out and thought through.

-Stewart

[Stewart Ross]

Sorry, I got the name of the form wrong - I was referring to the main form and not the subform. Not sure of its name - you are referring to frmStatusRecord s in your last post and frmStatusReport s in the first post. I'll stick with frmStatusReport s for the code below. Try revising the code to

[code=vb]With Forms!frmStaffE ntry!frmStatusR eports.Form
.RecordSource = "qrySRSortD ate"
!SortOption = 1
End With[/code]

If this is being called from within the main form it can shortened by use of the me operator to

[code=vb]With me!frmStatusRep orts.Form
.RecordSource = "qrySRSortD ate"
!SortOption = 1
End With[/code]
-Stewart

[JHite]

The subform name is "frmStatusRepor ts" and the routine is being called from the main form "frmStaffEn try" so I used the second set of code you provided.

It did just what I wanted!

Thanks again.

Janice

Sorry, I got the name of the form wrong - I was referring to the main form and not the subform. Not sure of its name - you are referring to frmStatusRecord s in your last post and frmStatusReport s in the first post. I'll stick with frmStatusReport s for the code below. Try revising the code to

[code=vb]With Forms!frmStaffE ntry!frmStatusR eports.Form
.RecordSource = "qrySRSortD ate"
!SortOption = 1
End With[/code]

If this is being called from within the main form it can shortened by use of the me operator to

[code=vb]With me!frmStatusRep orts.Form
.RecordSource = "qrySRSortD ate"
!SortOption = 1
End With[/code]
-Stewart