Access - search across multiple base tables (SELECT with JOINs) & show results in parent form and subforms

Question

Background - there exists the following for a residential property database:

Tables in SQL Server 2017

• H_ADDRESS
• H_IMPROVEMENT

Access 2016

• Linked tables to those in SQL Server
• Parent Form - frmSales
• Sub Form - frmImprovement

So the parent form, frmSales, contains subforms e.g. frmImprovement. I'm not wanting to deviate from parent and subforms.

Requirement

Provide the user two search methods - simple and advanced.

Simple

A new form will be created that has fields for basic search parameters like city (address), year built (improvement), etc. This form will need to run a query that does SELECT with JOINs and WHERE conditions for the values of the search parameters. I know how to write the query. How do I get a button to run the query and open the parent and sub forms with the result set?

Advanced

I'd like this to work like Filter by Form -- the user views the parent and sub forms, initiates a search, enters search values, runs search (the SELECT with JOINs), and views the result set. Is it possible to have the parent and sub forms support something equivalent to Filter by Form? (doesn't have to actually be Sort & Filter > Filter by Form out of the box... vba custom code is agreeable)

Answer 1

To filter on the basis of subform values you would need to include a subquery in the string expression which filters the parent form by means of the IN operator  to those rows in which the value of the referenced table's primary key is any one of the values of the relevant foreign key in the subset of those rows in the referencing table which match the search criteria.  You'll find simple examples in FindRecord.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file two options are included for searching by a subform value, one of which filters the parent form only, the other of which filters both the parent form and subform.  In my case the unbound controls are in the bound form, but the methodology to open a bound form from an unbound form by means of the WhereCondition argument of the OpenForm method of the DoCmd object would be the same.

To combine this with criteria on values in the parent form, in addition to the subquery, the string expression would combine this by means of a Boolean AND operation with straightforward criteria to filter the parent form on columns form its own recordset.

To do this in the manner of a 'filter by form' operation, you'd use exactly the same methodology, but by means of an unbound form which simulates the parent form/subform, and which opens a bound form/subform filtered to the selected/entered criteria.  Alternatively you could open the unbound form in dialogue mode from the bound form, and filter the bound form on the basis of the values entered/selected in the unbound dialogue form.  My demo includes an example of this.

---

Ken Sheridan, Stafford, England

Answer 2

I have the simple search working. I used the approach of:

1. Search form with multiple controls
2. Search button
3. On click of the same, an SQL query is built up in code based on values of the controls
4. The button then does OpenForm with the WhereCondition of the SQL query

I'm still unclear of how to approach the advanced search - your examples are different than my ideal, which is:

• The parent and subform may be viewed for CRUD of records (bound)
• The user does something to enter search mode (may be the same as 'unbinding'??)
• The parent and subform clear the contents of all controls
• The user is allowed to enter search criteria into one/many controls
• The user initiates the search
• Results are shown in the parent and subform 

This is simple and out-of-the-box with a single form (not parent/sub) by using filter by form. What would it take to create this same experience (as described above)? And no, I don't want to build an SQL query for all values of the parent/sub - it's unwieldy. Thanks in advance!

Answer 3

This is simple and out-of-the-box with a single form (not parent/sub) by using filter by form. What would it take to create this same experience (as described above)? And no, I don't want to build an SQL query for all values of the parent/sub - it's unwieldy. Thanks in advance!

Hi (I don't see your name in this thread on this moment),

If I understand you well, you want a system where the user can define the values of one or more fields. After pressing the "Search" buttonan sql_string is composed of the filled controls, the resulting recordset populates the main form, and details of the current record as displayed in the subform. Right?

You mention "filter by form". Is that when you fill the Where-argument in the OpenForm method? I never use it, but that is out of scope of the discussion.

I conclude that after user entry a new Where-string is constructed. Did you also try to assign this string directly to the Filter property of the form. I expect it to give you a new set of records according the new criteria.

In my case(s) I construct always the complete sql-string including Where's and Order By's, and assign that directly to the RecordSource of the form.

The userform for the entry of criteria is quite versatile. All fields in the table/query can be adressed, and depending on the type of the field nuancated. See the below example:

Imb.

Answer 4

Hi Imb,

"I don't want to build an SQL query for all values of the parent/sub - it's unwieldy."

In my database, there are in fact 7 tables used to populate 1 parent and 6 subforms. I wrote the question as 1 subform for simplicity. Creating a WHERE string for all fields (200+) for these 7 tables seems to be a very time intensive way to solve this problem. It could be done, but I hope there is a more efficient way!

Below is an example for 1 of the fields, as I wrote it in my simple search. Each AND gets concatenated to the WHERE clause...

'5 BSMT SF
    Dim sBSMTSF As String
    If Nz(Me.BSMTSF_min, 0) <> 0 Then
        sBSMTSF = "AND dbo_H_IMPROVEMENT.BasementTotalSqFt > " & Me.BSMTSF_min
    End If
    If Nz(Me.BSMTSF_max, 0) <> 0 Then
        sBSMTSF = sBSMTSF & " AND dbo_H_IMPROVEMENT.BasementTotalSqFt < " & Me.BSMTSF_max
    End If

Answer 5

In my database, there are in fact 7 tables used to populate 1 parent and 6 subforms. I wrote the question as 1 subform for simplicity. Creating a WHERE string for all fields (200+) for these 7 tables seems to be a very time intensive way to solve this problem. It could be done, but I hope there is a more efficient way!

Hi Kevin,

If you want that the users can select from all 200+ fields, well, then you have to think about a way to supply all these fields.

With a little fantasy you can construct a couple of different scenario's. But independant of what your goal is, it is not standard Access, so you must be willing to write it using VBA. On the other hand, if you use VBA, you can reach that goal.

One scenario could be that there is a first selection of with table the user wants to select on, further specified with on which fields. It all can be done using generalized forms, but you must be a programming freak to realize that. In the systematics that I developped, it would not be a great problem.

But isn't easier to restrict your search to the fields of one table at a time? You even could gather the id-values of the retrieved records in a string, and use that string in a next query using the IN clause. Is it on the edge of building complex dynamic query builder?

Imb

Answer 6

"I don't want to build an SQL query for all values of the parent/sub - it's unwieldy."

It is not a question of 'building an SQL query', but of a set of subqueries, which are incorporated into the string expression assigned to the parent form's Filter property.  The key issue is that you wish to search on the basis of values in subforms.  The implications of this are twofold:

1.  Because a subform displays a subset of arbitrary size you cannot simply 'clear the controls'.  How you approach this depends on whether you wish to apply a single set of criteria to the subform's recordset, i.e. no more than one value for each column in the recordset; or to apply multiple criteria to one or more columns.  If the former, then you simply need a set of unbound text box or combo box controls, each reflecting one column in the recordset.   If the latter, then you would have two alternatives: (i)  a set of unbound text boxes in each of which you enter a value list which can then be parsed into its individual values; (ii) a set of multiselect list boxes whose ItemsSelected collections can then be iterated.

2.  Both the parent form and the subforms will need to be filtered on the basis of the values entered into the unbound controls.  Consequently  the string expression assigned to the parent form's Filter property needs to filter the parent form to those values of its primary key which have one or more matches in the foreign keys in rows which meet the criteria in the referencing tables on which the subforms are based.  This means that the string expression must include subqueries to which the IN operator is applied, as my demo illustrates in the context of a single column in a single subform's recordset.  Scaling the methodology up to multiple columns in multiple subforms is a far from trivial task, requiring a good knowledge of both VBA and SQL.

The other issue to be considered here is that of performance.  Much will depend on the total number of criteria applied to columns across the seven subforms.  If only a few are entered/selected at any one time then the string expression will be proportionately less complex than if many criteria are applied simultaneously.  As complexity increases performance will inevitably suffer, possibly to the point where the filter becomes too complex to be applied at all.

---

Ken Sheridan, Stafford, England

Answer 7

It could be done, but I hope there is a more efficient way!

Hi Kevin,

The solution of many of these kind of problems, is to approach it in a systematic way.

The form that I showed is an unbound form, with a couple of columns (subforms if you want), with controls that have systematic names,e.g. Veld1, Veld2, Veld3, … and Waarde1, Waarde2, Waarde3, …

You also need to know what the Type is of the field: Text, Date, Real or else, for type-dependant formatting of the value

Your loop would then look like:

For x = 1 to nr_fields
  tmp_waarde = cur_form("Waarde" & x)
  If (tmp_waarde > "") Then
    Select Case fieldtype(x)
    Case "D": tmp_waarde = As_date(tmp_waarde)
    Case "T": tmp_waarde = As_text(tmp_waarde)
    Case "R": tmp_waarde = As_real(tmp_waarde)
    End Select

    where_str = where_str & " AND " & cur_form("Veld" & x) & " = " & tmp_waarde
  EndIf
Next

At the end the first " AND " is dropped for use as a form filter, or replaced by " WHERE " for use in an sql-string.

In the above example all comparisons are done with " = ", but it is possible to use any condition, as in the above form-example, column "Condition".

Imb.