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

Subform showing records before choice made on Mainform

P: 18
Iím using Access 2003 on Windows XP.

I have a Mainform (Staff Entry) which displays an unbound combo box named ChooseStaff. The Row Source for this combo box is a SELECT of the UserIDs and Staffer Names from a table (Staffers) which contains staffersí names and other info.

When the user actually selects a Staffer Name from the dropdown combo box, the VBA routines for the combo boxís On Enter and After Update events take care of finding and displaying the appropriate records in the Subform (Report Entry) from a table (Status Reports) which contains reporting information Ė where UserID in Staffers table matches UserID in Status Reports table. This works fine.

The problem arises when the Mainform is first displayed. The combo box is shown as empty but the Subform displays the records for the first UserID in the underlying table Staffers. If the user goes directly to the Subform and starts entering/modifying data, the Status Report records for the staffer that matches the first UserID are the ones that get modified, even though the user hasnít even selected a staffer from the combo box.

What I want to happen when first entering the Mainform is to show a blank/inaccessible Subform until the user has selected a staffer from the combo box and the VBA routines have displayed the matching info in the Subform (i.e., force the user to make a selection first). Iím guessing some code should be written for the Mainformís On Open event, but Iím not very experienced in Access and am at a loss.

Appreciate any help with this. Many thanks.

Janice
Aug 6 '08 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,186
I would consider setting the subform's .Enabled property to False in the design, and setting it to True whenever the operator actually selects an item.

Does this sound like it may be a suitable solution?
Aug 6 '08 #2

P: 18
I added lines 11, 23 and 28 based on your suggestion (see code below) but that did not change a thing. The subform is still "active" and shows info for the first UserID without the user having entered/selected anything in the combobox.

name of combobox is ChooseStaff
name of main form is frmStaffEntry
name of subform is frmStatusReports

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Private Sub ChooseStaff_AfterUpdate()
  5.  ' Find the record that matches the control.
  6.     Dim rs As Recordset
  7.     Set rs = Me.RecordsetClone
  8.     rs.FindFirst "[UserID] = " & Str(Nz(Me![ChooseStaff], 0))
  9.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  10.     rs.Close
  11.     Set rs = Nothing
  12.     Forms!frmStaffEntry!frmStatusReports.Enabled = True
  13.     With Me!frmStatusReports.Form
  14.        .RecordSource = "qrySRSortDate"
  15.        !SortOption = 1
  16.     End With
  17. End Sub
  18.  
  19. Private Sub ChooseStaff_Enter()
  20. If Me.Dirty Then
  21.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  22.   End If
  23. Me![ChooseStaff].Requery
  24. Forms!frmStaffEntry!frmStatusReports.Enabled = True
  25. Forms!frmStaffEntry.Form!frmStatusReports.Requery
  26. End Sub
  27.  
  28. Private Sub Form_Load()
  29. Forms.frmStaffEntry!frmStatusReports.Enabled = False
  30. End Sub
  31.  
  32.  
I also tried the same code on Form_Open and got the same results.

Do you have any other suggestions or did I misinterpret what you thought might work???

Thanks again for your help.

Janice
Aug 12 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
Firstly, I think you probably have a problem with the idea of a SubForm control. Please read Referring to Items on a Sub-Form for a better understanding (Saves me repeating it all in here ;)).

Also, I wonder if you changed the property first in the design of the form?

You will also need to reset it in the form's Current event (Private Sub Form_Current()). If ChooseStaff is a bound ComboBox then this will need to be reset depending on the data in the underlying field.

Please feel free to ask questions relating to this after you've read through it.
Aug 12 '08 #4

P: 18
Firstly, I think you probably have a problem with the idea of a SubForm control. Please read Referring to Items on a Sub-Form for a better understanding (Saves me repeating it all in here ;)).

Also, I wonder if you changed the property first in the design of the form?

You will also need to reset it in the form's Current event (Private Sub Form_Current()). If ChooseStaff is a bound ComboBox then this will need to be reset depending on the data in the underlying field.

Please feel free to ask questions relating to this after you've read through it.
Sorry it's taken me so long to respond. I had to turn my attention to another issue and have just now come back to working on this Access database.

I now understand what you meant in the first place. When I set the subform's Enabled property to No in the design, it disabled the ability to access the subform but still showed the subform information for the first person (UserID = 1). So I used the subform's Visible property instead - setting it to No in the design and then setting it to True in the combobox's after_update.

Works great! Thanks again for your help.

Janice
Oct 2 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
It sounds like you came up with a better solution then :)

Well done Janice.

PS. No worries about the delay. We have plenty to keep us busy ;)
Oct 3 '08 #6

Post your reply

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