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 “tblStatusReports” 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, “frmStatusReports,” 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 “frmStatusReports”

The user can choose how to sort/show the status report entries via an Option Group on “frmStatusReports” 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 tblStatusReports by reverse Date order (first) then by alphabetical Project order (second). (The Record Source for “frmStatusReports” is “qrySRSortDate”)

The other sort choice is “qrySRSortProject” which sorts tblStatusReports 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

“frmStatusReports” 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 (“frmStatusReports”) refreshes but does not revert to the default “DateOption” (“qrySRSortDate”).
I thought the “frmStaffEntry” subroutine: ChooseStaff_Enter() 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_Enter() 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:

With Forms!frmStaffEntry.Form
    .RecordSource = “qrySRSortDate” 
    !SortOption = 1
End With

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_AfterUpdate() 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 "frmStatusRecords." 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:

With Forms!frmStaffEntry.Form
    .RecordSource = “qrySRSortDate” 
    !SortOption = 1
End With

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 frmStatusRecords in your last post and frmStatusReports in the first post. I'll stick with frmStatusReports for the code below. Try revising the code to

With Forms!frmStaffEntry!frmStatusReports.Form
  .RecordSource = "qrySRSortDate"
  !SortOption = 1
End With

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

With me!frmStatusReports.Form
  .RecordSource = "qrySRSortDate"
  !SortOption = 1
End With

-Stewart

[JHite]

The subform name is "frmStatusReports" and the routine is being called from the main form "frmStaffEntry" 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 frmStatusRecords in your last post and frmStatusReports in the first post. I'll stick with frmStatusReports for the code below. Try revising the code to

With Forms!frmStaffEntry!frmStatusReports.Form
  .RecordSource = "qrySRSortDate"
  !SortOption = 1
End With

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

With me!frmStatusReports.Form
  .RecordSource = "qrySRSortDate"
  !SortOption = 1
End With

-Stewart