Have Subform Blank by Default

[Anita Arnold]

I have a subform used for REFERENTIAL/DISPLAY purposes only -- it is NOT used for data entry.

The main form in which it is contained has a combo box for users to select a value. Once selected, the subform populates with the selection's record.

However, when the form initially opens, the subform is already displaying data (the first record in its record source).

How can I get that subform to show NOTHING until a selection is made in the main form's combo box?

I saw a post elsewhere that advised setting the OnLoad property of the subform to GoToNewRecord. That doesn't really work for me, since this form isn't being used for data entry (the subform's DataEntry property is set to No).

I'd greatly appreciate any other suggestions!

[Patrick Finucane]

Let's say the subform's recordsource is
Select Table1.* From Table1 Where Table1.ID = Forms!Form1!Combo1
What happens if you set the default value of the combo1 to 0 in the main form (assuming you have no id of 0)?

[Anita Arnold]

That might work -- if I could figure out how to make that default value update the subform. Right now, the combo box has an After Update action:

Private Sub cboSelectGroup_AfterUpdate()

If Me.cboSelectGroup & "" <> "" Then
Me.Filter = "[GroupID]= " & Me.cboSelectGroup

Else
Me.Filter = ""

End If

Me.FilterOn = True
End Sub

I set the default value of the combo to 0, but when I open the form, the subform doesn't update to reflect that 0 value.

[Patrick Finucane]

Do you have a main form and a form contained within the form? Does the subform have a recordsource? Maybe in the main form do something like
Me.Filter = "[GroupID]= 0"
Me.FilterOn = True
in the OnLoad event.

If your subform is not linked to anything in the main form, then what I've written won't work as I'm throwing mud at the wall, hoping for something to stick.

I have no idea how you are populating the subform or if the subform is populated or why it exists or what the purpose of it is. So we can play a game of guess and by-gosh until we find out if the subform is populated and what event populates it and how it is populated.

[Anita Arnold]

Yes, I have a subform inside the detail section of a main form.

The form's record source is a query:

SELECT DISTINCT tblGroups.GroupName, tblGroups.GroupID
FROM tblGroups INNER JOIN tjxGroupEmployee ON tblGroups.GroupID = tjxGroupEmployee.GroupID;

The subform's record source is also a query:

SELECT tblEmployees.EmployeeName, tjxGroupEmployee.EmployeeID, tjxGroupEmployee.GroupID, tblEmployees.Active, tblEmployees.Manager_TmLead, tblEmployees.EmpType, tblEmployees.JanAvailHrs, tblEmployees.FebAvailHrs, tblEmployees.MarAvailHrs, tblEmployees.AprAvailHrs, tblEmployees.MayAvailHrs, tblEmployees.JuneAvailHrs, tblEmployees.JulyAvailHrs, tblEmployees.AugAvailHrs, tblEmployees.SeptAvailHrs, tblEmployees.OctAvailHrs, tblEmployees.NovAvailHrs, tblEmployees.DecAvailHrs
FROM tblEmployees INNER JOIN tjxGroupEmployee ON tblEmployees.EmployeeID = tjxGroupEmployee.EmployeeID
ORDER BY tblEmployees.EmployeeName;

The subform is set in the main form to Link Master Fields AND Link Child Fields on GroupID.

The purpose is to show which employees are assigned to each group. The actual data is updated elsewhere in the database. This is meant to be a read-only way for non-savvy users to simply view information entered and maintained by someone else.

[Ron Paii]

"Anita Arnold" <honol...@yahoo.com> wrote in message
news:0903267a-5c22-4257...@googlegroups.com...

Remove the sub form's source query. Set it in the after update event of the
combo box.

[Anita Arnold]

Ron, I'm not entirely certain how to put it in the AfterUpdate - I've never "written" a query there -- I've run saved queries from here, but never actually put the query itself as code -- is that what you mean?

[Patrick Finucane]

On Monday, November 17, 2014 1:11:57 PM UTC-5, Anita Arnold wrote:
> Ron, I'm not entirely certain how to put it in the AfterUpdate - I've never "written" a query there -- I've run saved queries from here, but never actually put the query itself as code -- is that what you mean?

In your Subform's OnLoad event have something like
Me.Filter = "YourKey = 0"
Me.FilterOn = True
Now when the combo box is selected in the MainForm, in the AfterUpdate event do your code and then add something like
Forms!MySubFormName.Form.Filter = "YourKey = " & Me.Combo1
Forms!MySubFormName.Form.FilterOn = true

[Patrick Finucane]

Oops.
Forms!MyMainFormName!MySubFormName.Form.Filter = "YourKey = " & Me.Combo1
Forms!MyMainFormName!MySubFormName.Form.FilterOn = true
Currently your subform has no filter when opened. This will do so.

[Ulrich Möller]

Why just easily take the combo box control as the master link field.
There is no need for additional filters and after update event procedures.

Ulrich

[Anita Arnold]

Any of you watch that new television show, Scorpion? It's about genuises trying to "fit in" and be understood in the "real world." I feel like the answer on this newgroup are a lot like that -- you are all complete and total geniuses when it comes to access. However, I am just a "normal" human and need them to be a little more step-by-step.

To that end, Ulrich, can you explain your suggestion?

Patrick - I think I have the AfterUpdate event for the combo box set correctly. What's stumping me is how to get the 0 value so that when the form opens, the subform initially displays NO RECORDS.

[Ulrich Möller]

Have a lock at this Sample:
http://www.fmsinc.com/microsoftaccess/Forms/Subform/Master-Link-Fields.asp
Download the SubformMaster - Sample and expand the mdb. The sample
contains a form named frmSimple.
1. Open this form in design view and change the textbox into a combobox.
2. Clear the controlsource to make it unbound
3. Change RowSource to "SELECT DISTINCTROW tblMaster.MasterID,
tblMaster.Field1 FROM tblMaster; "
4. Set Columncount = 2 and ColumnWidths = 0

Try the modified sample form now and it should work as you expected. I
hope thats what you wanted to do?

Ulrich

[Anita Arnold]

This does EXACTLY what I want it to do. Now, I only need translate it into my own database.

Thank you, everyone!!!

For anyone wanting to follow Ulrich's instructions, in the first step, he says to change the text box into a combo box. Here, he's talking about changing the Master ID field.

[Phil]

Marginally off the subject, I think, for the sake of being able to see
trends, that you should not be holding the available hours in the Employee
table. If it were me, I would have a table of Years with YearID as the Key
and YearName for the actual year (Use "YearName" as "Year" is a reserved
word), Probably also a table of Months MonthID & MonthName ("Month" is a
reserved word) to hold Jan to Dec and the table of AvailableHours with
Foreign Keys of EmployeeID, MonthID and YearID. That way you can keep an eye
on how each employee's available hours changes from year to year.

Good database practice normalises tables, so that an employee table should
really only contain "fixed" data about the employee - name, address, national
insurance number sex etc.

Phil

---
This email has been checked for viruses by Avast antivirus software.
http://www.avast.com

[Anita Arnold]

Excellent point,Phil. Thanks for bringing that up. In this case, the hours are static and we aren't looking to preserve them from year to year. This database has to do with resource forecasting, so the hours stored in the employee table aren't "real-time," they are designated per employee in chunks based on certain assumptions. The only real flexibility stems from whether or not the employee is full or part-time.