473,545 Members | 1,638 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Subform showing records before choice made on Mainform

18 New Member
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.

Aug 6 '08 #1
5 2608
32,563 Recognized Expert Moderator MVP
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
18 New Member
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

Expand|Select|Wrap|Line Numbers
  2. Option Compare Database
  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
  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
  28. Private Sub Form_Load()
  29. Forms.frmStaffEntry!frmStatusReports.Enabled = False
  30. 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.

Aug 12 '08 #3
32,563 Recognized Expert Moderator MVP
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
18 New Member
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.

Oct 2 '08 #5
32,563 Recognized Expert Moderator MVP
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

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

Similar topics

by: P Mitchell | last post by:
Hello I would like someone to be able to help with the updating of data in a lookup list in a subform. In short, is their a VB instruction that is the equivalent of performing the action of going to the menu and selecting Records and then Refresh? or To explain, based on a three table system where cars participate in rallies,
by: Greatmaze | last post by:
Hi All, I want to update a column in my Users table through a subform. I have made a mainform where you can choose a Department (from Department table), then a subform pops-up showing the users in that Department. Now I want to make 2 buttons on this subform to pop-up new (sub)forms to add or delete a user to/from this Department, but this...
by: misscrf | last post by:
I have this normalized database, and I don't understand why setting up the forms is so hard for me. I have the main candidate entry form. This is to enter in candidates who apply for a job. I have a tab control with a page that shows if the candidate type is 1 of a few types ( in code). Otherwise 2 pages show. 1 is for contact...
by: phaddock4 | last post by:
Being fairly inexperienced at Access 2000, i've been reading many posts here for the last several days, and testing myself to find the best approach to do the following in A2K: SET UP: I have created a MainTable (and related form), which has an associated SubForm (popup) along with its underlying, separate Table. The tables'...
by: MLH | last post by:
I have frmVehicleEntryForm and on it is frmAddnlOwnrListSubForm. The subform is unbound and lists records in a table, allowing the user to change values in CheckMark controls on the subform as desired. I have another button that will allow user to run a query which will update the field values related to the checkmark control to False. To...
by: Macbane | last post by:
Hi, I have a 'main' form called frmIssues which has a subform control (named linkIssuesDrug) containing the subform sfrmLink_Issues_Drugs. A control button on the main form opens a pop-up form which allows me to edit the record in the subform. What I want to happen is for subform with the new edits to be updated on the main form when I...
by: Robert Jacobs | last post by:
Please advise... I currently have a Mainform (Customers) and a Subform (Service Requests) with a one to many relationship (one customer, many service requests) with a CustomerID that is unique for each customer and is autofilled on the subform. Subform contains field called TicketID (which there could be multiple instances of and is not...
by: mnms | last post by:
Hi, I'm trying to create an "overview" form. And I haven't been able to figure out how to get Access to do what I want yet. Basicly what I want, is to populate a subform based on a field selection. In other words, I have several fields like colour, shape, location etc. on my Main form. Now I want to add a subform to my unbound mainform,...
by: samdev | last post by:
I have created two combo boxes in a subform.... For example 1. Combo Box State 2. Combo Box City 3. When a state from the Combo Box State is selected, the City combo box updates to reflect the State chosen by only showing cities in that selected state. 4. When I open just the subform it works just fine but when I open the
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.