Subform display is not showing appropriate main form records

[Kathy Earley]

I have search filter form that when I click the search button is passing a value in OpenArgs to the Main Form on open event. The OpenArgs is changing the recordsource of the subform on the main form appropriately. However, the main form is referencing all of the records, not just the ones that are int eh subform recordsource. My link master and child fields are correct. There is a 1:N relationship between my mainform table and subform table.

For example, my my subform, the recordsource is diplaying 2 records where the Status = BLUE, however, the mainform (Projects) is showing ALL records even where the status isn't blue. So when I scroll through the mainform records, if the project isn't status = blue, the status subform shows a blank record. I only want the Projects where the status is blue.

Thanks for any help.

[June7]

Sounds like the criteria is passing to the wrong form. Do you want to provide project for analysis?

[Kathy Earley]

Sure. Database is attached.

The form is frmProjects and subform is frmProjectStatusSubform. I am using frmSearchProjects to search on the Status. Since there can be multiple statuses for a project, ideally what I'd like to do is have the subform query display the most recent status that matches the status in the search form, or at a minimum, have all statuses appear if ANY of the statuses for that project match the status in the search box.

I've tried both but ALL of the Projects are displaying in the main form, not just those that are linked to the status subform.

Thank you!!
ps. I am using Access 2003

[June7]

StatusID field of Status subform errors, "No such field in the field list".

Not understanding the RecordSource for Status subform. Why do you need to change the RecordSource with code? qProjectStatus_SubForm_Search is a join of qProjectStatus_SubForm and ProjectStatus. qProjectStatus_SubForm is a join of Projects and ProjectStatus. Why not just use qProjectStatus_SubForm with filtering?

I set the subform's RecordSource to qProjectsStatus_Subform, removed code setting recordsource. All seems to work.

You need to run Debug>Compile. Syntax error on Me.[Project Status].Form.

Why don't you have a combobox for Project Name?

[Kathy Earley]

Thank you!
If the Status isn't selected as a search field, then I wanted the recordsource to be something else. I was having issues with the source query for the subform only returning the specific value that was searched for. If the search was on BLUE, I wanted all of the statuses attached to that Project Id to display, including BLUE.

I will do what you suggested and let you know.
Thanks again!

[Kathy Earley]

The reason I don't have combo box for Project Name is that eventually there will be many projects in the database. If a person doesn't know the name of the project, it might become cumbersome to scroll through all of the projects.

[Kathy Earley]

I made the same modifications as you. In my Search form, I select the status of RED. I click Search and all 9 records appear in the Projects form when only 2 Projects have had a status of Red at some point (Projects 3 and 4).

I really want the records to display ONLY if their CURRENT status is RED but at this point, I will take it if ANY of their Statuses are currently RED or at any point were RED.

Hope this makes sense.
Thanks.

[June7]

Scroll through combobox not required with Auto Expand property.

Guess I was distracted from the main issue by the errors I ran into.

A project has multiple status records, presuming this documents a progression. If I understand what you want: Blue is selected in the combobox. Project 3 has status from White to Blue, this project should show. Project 7 has status from Yellow to Green, this project should not show.

A subform does not control records displayed on main form (children don't command the parents). The Status value is not in the main form RecordSource, therefore those records cannot be filtered by Status. You need the main form RecordSource to have a calculated field to search for the given status color. Then this constructed field can have criteria to filter the data. An expression with DLookup should work. Something like:

Status: DLookup("Status","ProjectStatus","Status='" & Forms!frmSearchProjects!cboStatus & "' And [Project ID]=" & [Project Id]).

Fix the code that builds the filter string, and should not need the vSearch variable.

The above calc will search for the status color in all status records related to the project, even if not the most recent. Is it only the most recent status record for each project that should provide status value? That is trickier. If this is preferred, try:

query to get the latest Status
SELECT ProjectStatus.ProjectID, Last(ProjectStatus.Status) AS LastOfStatus
FROM ProjectStatus
GROUP BY ProjectStatus.ProjectID
ORDER BY Max(ProjectStatus.StatusDate) DESC;

Include this query in the main form RecordSource by join on ProjectID. This gives a Status field to filter with.

[Kathy Earley]

Ideally I would like the most recent status to display for the project. I will try both of your suggestions and let you know how it goes.
Thanks.

[Kathy Earley]

I'm going the route of finding the most recent status. Your query works great. Thank you. Using the query the way you provided allowed me to get rid of other compound queries.

If the user does not want to Search on Status, I'm thinking I will need to change the recordsource of my main and subforms so they show all statuses, not just the last.

In addition, if the status is selected as a search item, I'm adding a filter to both the main and subforms.

I think I'm almost there but I'm stuck once again. I'll zip up the database so you can review my current version.
Thanks so much for your help.

[June7]

What is the specific issue you are stuck on?

[Kathy Earley]

Sorry. I'm sending opening frmProjects using strWhere from the Search form. I'm also sending an OpenArgs value. In the Open Event of frmProjects, I'm setting the recordsource of both the main and subform based on the value of OpenArgs. The forms were opening fine, however the status in strwhere was not being used. It was lost during the setting of recordsource, I believe. So I tried setting the Filter Main and SubForm to the strWhere value. This is not working. Looking for suggestions as to why I'm unable to open the forms with the value of strWhere (status).

[June7]

I suggest not modifying the RecordSource of either. Filtering should be adequate.

StatusID textbox in Status subform still has error. The Status Max query makes this subform not editable. This subform is then superfluous. The current status can be shown in the main form with the RecordSource I suggested. Change the jointype to 'show all records from Projects', just in case a Project can exist without a related status record.

Ooops! Including the Status Max query in the RecordSource makes the recordset uneditable. Use a DLookup expression that queries the Status Max query instead. Status: DLookUp("MaxOfStatusID","qMaxProjectIDStatus","Pro jectID=" & [ProjectID])

[Kathy Earley]

I'm working on the DLookup.

I have looked in numerous places and am not seeing any issues with the StatusId in the Status Subform. I'm not sure where this may be coming from. Is there an easy way for me to find where?

I'll keep working with the filter rather than changing the recordsource. Seems a heck of a lot easier.

Thanks.

[June7]

In the posted file, the StatusID textbox has a little green triangle in the uppper left corner. Click it and view the message in the dropdown. StatusID was not included in the query selected fields so the textbox can't find it.