By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 1,876 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Problem getting subform to refresh using default settings/option group

P: 18
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub ChooseStaff_AfterUpdate()
  2.     ‘Find record for chosen staffer
  3.     Dim rs As Recordset
  4.     Set rs=Me.RecordsetClone
  5.     rs.FindFirst “[StafferID] = “ & Str(Nz(Me![ChooseStaff],0))
  6.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  7.     rs.Close
  8.     Set rs = Nothing
  9. End Sub
  10.  
  11. Private Sub ChooseStaff_Enter()
  12.    ‘Update subform based on chosen staffer
  13.    Me![ChooseStaff].Requery
  14.    Forms!frmStaffEntry.Form!frmStatusReports.Requery
  15. End Sub
“frmStatusReports” VBA routines are:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.     ‘ Requery status reports after an entry has been made or modified.
  3.     ‘ Requery uses whichever SortOption is in effect
  4.     Me.Requery
  5. End Sub
  6.  
  7. Private Sub SortOption_AfterUpdate()
  8.    ‘ Show status reports based on sort choice made by user
  9.    Select Case SortOption.Value
  10.       Case 1
  11.          Me.RecordSource = “qrySRSortDate” 
  12.       Case 2
  13.          Me.RecordSource = “qrySRSortProject”
  14.    End Select
  15. 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
Apr 29 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. With Forms!frmStaffEntry.Form
  2.     .RecordSource = “qrySRSortDate” 
  3.     !SortOption = 1
  4. End With
Thank you for providing a very well structured question which was well set-out and thought through.

-Stewart
Apr 30 '08 #2

P: 18
Thanks, Stewart.

I've added your suggested code at the end of ChooseStaff_AfterUpdate() as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub ChooseStaff_AfterUpdate()
  2.    ' Find record for chosen staffer
  3.    Dim rs As Recordset
  4.    Set rs=Me.RecordsetClone
  5.    rs.FindFirst "[StafferID] = " & Str(Nz(Me![ChooseStaff],0))
  6.    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  7.    rs.Close
  8.    Set rs = Nothing
  9.  
  10.    ' Reset subform default sort
  11.    With Forms!frmStaffEntry.Form
  12.       .RecordSource = "qrySRSortDate"
  13.       !SortOption = 1
  14.    End With
  15.  
  16. 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:

Expand|Select|Wrap|Line Numbers
  1. With Forms!frmStaffEntry.Form
  2.     .RecordSource = “qrySRSortDate” 
  3.     !SortOption = 1
  4. End With
Thank you for providing a very well structured question which was well set-out and thought through.

-Stewart
May 1 '08 #3

Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. With Forms!frmStaffEntry!frmStatusReports.Form
  2.   .RecordSource = "qrySRSortDate"
  3.   !SortOption = 1
  4. End With
If this is being called from within the main form it can shortened by use of the me operator to

Expand|Select|Wrap|Line Numbers
  1. With me!frmStatusReports.Form
  2.   .RecordSource = "qrySRSortDate"
  3.   !SortOption = 1
  4. End With
-Stewart
May 1 '08 #4

P: 18
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

Expand|Select|Wrap|Line Numbers
  1. With Forms!frmStaffEntry!frmStatusReports.Form
  2.   .RecordSource = "qrySRSortDate"
  3.   !SortOption = 1
  4. End With
If this is being called from within the main form it can shortened by use of the me operator to

Expand|Select|Wrap|Line Numbers
  1. With me!frmStatusReports.Form
  2.   .RecordSource = "qrySRSortDate"
  3.   !SortOption = 1
  4. End With
-Stewart
May 6 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.