Subform filters not being applied

[andrewsmith]

Hello,

I have an access 2010 db with several forms with multiple subforms. The subforms are based on the same query and are filtered by one of the fields to show subsets of this query data. Basically the query holds 4 fields, a Parent Record ID, attribute ID, attribute type and attribute description.

The main form holds the parent record and the sub forms are linked using this and then filtered to show attributes of one type per sub form.

The issue is that when I open the main form from a button on another form with the record filtered the sub forms don't apply their filters properly. The button was generated automatically by access and the macro has the following where condition on the openform action

="[ClientContact_ID]=" & [ClientContact_ID]

Attached are three screen grabs in a zip as I am not permitted to upload images. The first shows some example query results, the next the filters on the subforms working correctly and the final incorrectly. The subforms both show the same record on the same form but the correct one is opened directly and the incorrect from a button on another form.

Its leaving a placeholder for the 'filtered' records, I assume its empty as the combobox source doesn't match.

This happens on several forms I have created with similar subforms.

Any help greatly appreciated.

Andrew

 

[andrewsmith]

Anyone?

This is really doing my head in!

 

[vbaInet]

Convert the macro to code and show us the code.

 

[andrewsmith]

Unfortunately as its an autogenerated macro I can't convert. The macro is as follows

RunMenuCommand

Command: SaveRecord

​

OpenForm

Form Name: frm_ClientContacts
View: Form
Filter Name:
Where Condition=: ="[ClientContact_ID]=" & [ClientContact_ID]
Data Mode:
Window Mode: Normal


​

 

[andrewsmith]

OK - I have just written some code which opens a new form with similar issues. The code is as follows

Private Sub cmd_CandidateSearch_Click()

    Dim strSQL As String
    
    DoCmd.SetWarnings False
    
    'clear existing searches
    strSQL = "DELETE * FROM tbl_Searches WHERE tbl_Searches.Job_ID=[forms]![frm_Jobs]![Job_ID] and tbl_Searches.Search_Name='Please Enter Name to Save';"
    DoCmd.RunSQL strSQL
    
    'Add job search criteria
    strSQL = "INSERT INTO tbl_Searches ( Search_Name,Search_Type, Job_ID, Search_MinVal, Search_MaxVal, Search_DateCreated ) " & _
             "VALUES ('Please Enter Name to Save','Candidates', [forms]![frm_Jobs]![Job_ID], [forms]![frm_Jobs]![job_salary_min], [forms]![frm_Jobs]![job_salary_max], Now());"
    DoCmd.RunSQL strSQL
    
    'Add job attributes to search attributes
    strSQL = "INSERT INTO tbl_Search_Attributes ( Search_ID, Attribute_ID ) " & _
             "SELECT tbl_Searches.Search_ID, tbl_Job_Attributes.Attribute_ID " & _
             "FROM tbl_Job_Attributes INNER JOIN tbl_Searches ON tbl_Job_Attributes.Job_ID = tbl_Searches.Job_ID " & _
             "WHERE tbl_Job_Attributes.Job_ID=[forms]![frm_Jobs]![Job_ID] AND search_name='Please Enter Name to Save';"
    
    With DoCmd
        .RunSQL strSQL
        .SetWarnings True
        [B].OpenForm "frm_SearchCandidates", , , "[Job_ID]=" & [Job_ID] & " AND search_name='Please Enter Name to Save'"[/B]
    End With

End Sub

I assume its the Where condition causing the issue.

 

[vbaInet]

So what are the data types of ClientContact_ID and Job_ID?

 

[andrewsmith]

Both are autonumber fields. as are all _ID fields in my DB.

For clarification if I open the form frm_SearchCandidates - it shows the correct search ie it is for the correct Job_ID and search_name. The three subforms on frm_SearchCandidates are on a tabbed control and show records for the correct Search howvere they are not filtered correctly.

 

[vbaInet]

Ok, when you say "they are not filtered correctly", what do you mean?

 

[andrewsmith]

OK - I'll try and explain this better as reading through my posts I haven't done a very good job. The database is a contact management system for a recruitment consultant.

My DB holds an attributes table. This holds an Attribute_ID(autonumber), Attribute_Type(text, limited to 3 options), Attribute_Desc(text). Attribute_Type can be Job Function, Location or Area of specialism

Attributes are linked to several other entities within my database Clients, Client_Contacts, Jobs and Candidates. Each is a many to many relationship so I use a linking table holding the 2 ids (Entity ie Candidate_ID and Attribute_ID)

Each main form shows 3 subforms (usually on a tabbed control) showing the 3 types of attribute. These are created from a single query joining the linking table to the attribute table. The subform then uses the "Where" property to filter the attributes to show only the appropriate type.

All this works when you open the form from the Navigation pane. However when the form is opened from a button on another form the subforms show all the attributes for the appropriate entity on each subform regardless of the where property on the subform. The rows for the incorrect data types are blank as its a combo box displaying the data and this is filtered correctly.

If you look at the screen grabs on the first post they show the issue. I cancut out any sensative data and post the database if this may help.

And THANKYOU for trying to help because this is getting me really perplexed.

 

[vbaInet]

Why are you needing to insert into a temporary search table then?

And when you open your form, why the boldened part? Is that supposed to be a search criteria or a pop-up?

        .OpenForm "frm_SearchCandidates", , , "[Job_ID]=" & [Job_ID] & " AND search_name=[B]'Please Enter Name to Save'[/B]"

Because that is just a search criteria and not a pop-up.

 

[andrewsmith]

The database allows a user to create searches that can be saved. The search criteria basically build a query on the fly. They can be a combination of matched attributes, matched search words, salary ranges etc... all of which can be and/or ie it could be a job which is

• in liverpool or manchester
• between £50K and £60K
• for a web designer or web developer
• with keywords asp.net and vb.net and XML.

A user may or may not decide to save the search definition so I store it in a table regardless and delete on form close unless they choose to save.

The bold bit just opens a new form displaying the Search form with some values such as the salary range and attributes prefilled.

The idea being that a candidates attributes are compared to the jobs attributes to find potential matches. We might not need all attributes to match hence the 4 different And/Or options which are for keywords and the attribute types.

 

[andrewsmith]

I have just taken a couple of screen grabs to help describe whats going on.

The first form is pouplated with job details (no 1 in zip file). On here the Roles subform displays correctly.

When the user clicks "Candidate Search" the code posted above is run and the frm_SearchCandidates (no 2 in attached zip file) is opened. Some fields are prefilled. Namely the min and max salary and the attributes sub forms (roles, discplines and regions)

Everything can be editted by the user. They can change salary ranges, add or remove keywords. Add, Remove or Change the required attributes. They can set the search terms to be and/or, the roles to be and/or, Disciplines to be and/or and regions to be and/or.

The red sections need to be completed by the user on first load.

The issue is with the sub forms for the attributes. If you look on the frm_jobs screen grab you will see 2 Role attributes. On the tabbed sub forms beneath there are 2 disciplines and 1 region set. If you look at the 2nd screen grab of frm_SearchCandidates you will see 3 blank rows. These correspond to the Discipline and region attributes. On the disciplines tab you will see the 2 attributes set and 3 blank rows representing the 2 role attributes and the region...

 

[andrewsmith]

Just for clarification - if I open the frm_searchCandidates form for the same search record but from the navigation pane and not the form the erroneous subform rows are not there.

 

[andrewsmith]

Just sorted it!

Turns out all I needed to do was change the subform data source to a query with the filter in its where clause and hey presto.

 

[vbaInet]

Just sorted it!

Turns out all I needed to do was change the subform data source to a query with the filter in its where clause and hey presto.

Good lesson learned - always bind your form to a query

 

[andrewsmith]

It was bound to a query but I needed to query the query!

Thanks for your help!!

 

[vbaInet]

I see. You needed to query the Query and access the Access

Happy developing!