Subform showing records before choice made on Mainform

[JHite]

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

[NeoPa]

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?

[JHite]

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 frmStatusReport s

Option Compare Database

Private Sub ChooseStaff_AfterUpdate()
 ' Find the record that matches the control.
    Dim rs As Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "[UserID] = " & Str(Nz(Me![ChooseStaff], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    rs.Close
    Set rs = Nothing
    Forms!frmStaffEntry!frmStatusReports.Enabled = True
    With Me!frmStatusReports.Form
       .RecordSource = "qrySRSortDate"
       !SortOption = 1
    End With
End Sub

Private Sub ChooseStaff_Enter()
If Me.Dirty Then
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  End If
Me![ChooseStaff].Requery
Forms!frmStaffEntry!frmStatusReports.Enabled = True
Forms!frmStaffEntry.Form!frmStatusReports.Requery
End Sub

Private Sub Form_Load()
Forms.frmStaffEntry!frmStatusReports.Enabled = False
End Sub

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

[NeoPa]

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.

[JHite]

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

[NeoPa]

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 ;)