Access filter main form by an entry on linked subform

Question

A table "items" contains records of lesson worksheets.

On the server, it is accessed by numerous teachers, who each have their own Access front end.

Teachers need to index their personal favourite lessons, so a local table is linked to the remota data table to do this.

A form shows the data table, and a subform on it shows the local data. The box "favourite" when checked in the subform changes as records in the master are scrolled. In other words, the link works, and I can also print a a list of favourites. What I cannot do is filter the subform to see all the favourite lessons. One can of course only filter the Main form.

I want to be able to filter the subform and see the matching records in the masterform. I assume this should be easy once you know how, but no explanation I have seen could ever be described as easy and I have not undersood any of them, even though I am reasonably competent in Access.

Answer 1

Dear Ken ...

I managed to get this to work, which makes me astonished, and very pleased. I have actually done a fair amount of "programming" with Access, including designing an entire working and fairly complex application for a language school, but this filtering from a subform required coding I was of course not capable of. So a big thankyou. I feel as if I should pay you something, as this was the last piece in a database jigsaw I am preparing for another school tor teachers to share archived resources. (It is a personal project and not a commercial one - I am just a teacher interested in databases.)

One thing I would like to remark on. You and other writers - being so familiar with them of course - often refer to combo boxes as if they were easy, but in my experience they certainly aren't. For example, consultants say "use a combo box to do this or that", but in fact there are quite a few ways to set up a combo box and I find them quite confusing.

In this case, I created a new one with a VALUE LIST rather than referring to any field (there is in fact no relevant field to refer to). And I ASSUME that the box passes the value entered to the coding that you supplied.

The OTHER point is another conceptual one. The way this is working, there is a SUBFORM on the master (and obviously linked to it) with a single field into which a user can put a number to index a file on the main table,  but one ALSO needs a combo box to do the filtering.

In other words, achieving the desired result requires TWO elements embedded into the master form: A) the subform for entering data and B) a combo box for FILTERING it. In other words (again) you cannot have a SINGLE box to do BOTH things as you can on the masterform.

Does that make sense? In any case, it works, which is all I really care about! As I said, I am not sure how to thank you adequately!

Answer 2

Those of us who answer questions here do so entirely on a voluntary basis, and are more than happy to do so if it helps others gain experience, so don't even think about any need for payment.  The only thanks we need are knowing that we've helped.

As regards combo boxes these by and large are used in two situations.  Firstly to insert a value into a column (field) in a table.  This is a bound control, and is used where the values come from a defined list.  A combo box can also be unbound, and used for things like searching for records by selecting a value from the list.  You are absolutely correct in thinking that these must be separate controls; you can't use one for both entering data and searching for records.

In cases where the values are immutably fixed in the external world a value list is appropriate; days of the week or months of the year are examples.  Where the values are not fixed sets, however, a value list should not be used, even if you think the values will never change.  You should always get the values from a table.  Otherwise you would be encoding data as part of the object definition, whereas a fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

In your case, the values would be stored in a separate table named Favourites say, with a column Favourite designated as its primary key.  The combo box's RowSource property would then be:

SELECT Favourite FROM Favourites ORDER BY Favourite;

In this case the primary key of Favourites is a 'natural' key, but that assumes that the values are distinct.  A table of cities, for instance,  cannot have the city name as its primary key as city names can legitimately be duplicated; think how many Springfields there are in the USA!  In a case like this the Cities table would have a 'surrogate' numeric CityID primary key, using an autonumber, and a non-key column with the city names  The combo box is then set up to hide the CityID column and show the names.  If the same name appears more than once in the list, however, you would not know which to select.  To cater for this you could return other columns' values in the list.  You'll find an example in DatabaseBasics.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.

In this little demo file, if you go to the 'Retrieving data from the database' section you'll see that in the right hand part of the form is a combo box to search by city.  When you drop the list down it comprises not just the city name but also the region and country in which the city is located, so Paris, France can be distinguished from Paris, Texas.  The value is concatenated from three separate columns in the RowSource as follows:

SELECT Cities.CityID, City & ", " & Region & ", " & Country AS CityLocation

FROM (Countries INNER JOIN Regions ON Countries.CountryID = Regions.CountryID)

INNER JOIN Cities ON Regions.RegionID = Cities.RegionID

ORDER BY Cities.City;

Another way is to use correlated combo boxes in which you progressively select the country, region and city in three separate combo boxes, the first two being unbound.  You'll find an example of this in the 'Building the user interface' section.  

The form has a 'View Code' button which enables you to take a look at the code in the form's module.  I would not expect you to understand how the code works at first sight, of course.  As Richard Feynman is saying to his students in the quote below my signature, you have to understand how things work before you can make them work.  My little demo is designed to give a brief introduction, but it really is a question of doing the homework.  There are plenty of resources freely available online, but I would still recommend getting hold of a good general purpose book on Access, one which comes with its own demo files which you can work through methodically at your own pace.

Answer 3

Thanks - obviously a lot of homework to do ....... this stuff really does go deep.

As I said, I got this to work thanks to your help, but I have ONE last problem as explained below:

To summarize:

A) I have a table “items” with the following fields viewed by a form (I have greatly simplified the number of fields)

1.    PIC = a link using the web browser control (primary key) to look in the folder where the actual worksheets lie.

2.    TITLE = a textfield describing the worksheet

I want to find a way for multiple users to enter a number from 1 to 5 in ANOTHER table (on the same form as for "items" to indicate that a particular file in “items” is a favourite.

So, there is table

B) called “links” which has these fields:

PIC = as above in A)

ID = a user ID

FAV = a number field where a user can put from 1 to 5 to indicate mark a particular file in “items “ as a favourite (the 1 to 5 rating is an extra complication - it would perhaps be easier to leave it at 1 (=yes). Or a checkbox would be more elegant, but I am not sure how this would work with a combo box)

The "links" table would more elegantly be placed on the server, not on the local front end.

I need to be able to filter the FAV field in “links” and see ALL the records in “items” which the particular user for which the user has placed a number in FAV in “links”.

Using this procedure you posted, I managed to make this work, but only for ONE user. I need to find a way to have the user of the front end enter into table B (via the form combining "items" and "links") BOTH his or her ID AND the “favourite” rating so that even using a single copy of the front end the favourites are individualised.  I assume this is possible somehow.

To summarize, assuming we have several front end copies of Access, then each individual user needs to be able to input his or her own favourites. At the moment, I can mark a file as “favourite”, but it is favourite for EVERYONE.

You could of course have multiple copies of the front end, each “belonging” to a particular user (and each user on a network would se his or her personal copy on the desktop), but I imagine a programmer would shudder at such a clumsy arrangement!

Here is the code you posted and which works as described above, but not for individual users:

Private Sub Text2093_AfterUpdate()

    Const MESSAGETEXT = "No matching records found."

    Dim ctrl As Control

    Dim strFilter As String

    Set ctrl = Me.ActiveControl

    strFilter = "PIC IN(SELECT PIC " & _

        "FROM links WHERE FAV = " & ctrl & ")"

    If Nz(ctrl, 0) = 0 Then

        ' turn off filter

        Me.FilterOn = False

    Else

        If Not IsNull(DLookup("FAV", "links", "FAV = " & ctrl)) Then

            ' filter form to name selected in combo box

            Me.Filter = strFilter

            Me.FilterOn = True

        Else

            ' inform user if no matching records found and show all records

            MsgBox MESSAGETEXT, vbInformation, "Warning"

            Me.FilterOn = False

            Me.Requery

        End If

    End If       

End Sub

Answer 4

First a few general points:

1. I would not recommend that the PIC column be the primary key of Items.  It is a candidate key, and as such must be uniquely indexed, but a 'surrogate' autonumber ItemID primary key would be more efficient

2.  Do not use the generic ID as a column name.  I know you'll find it done in some of the Access templates, it is not good practice due to the ambiguity which it creates.  Always use more specifically descriptive column names which describe the attribute represented by the column, in this case UserID.  Use the same name for the primary key of the referenced table and any corresponding foreign keys in referencing tables.

3.  The use of upper case for column names will make any SQL statements confusing as the usual convention is to use upper case for SQL key words (Access defaults to this).  For table and column names I always use CamelCase, or you can represent a space by an underscore character.

What you have is a many-to-many relationship type between users and items.  This is represented by a table which resolves it into two one-to-many relationship types, i.e. your Links table:

Links

….ItemID  (FK)

….UserID  (FK)

….Fav

This table is 'all key', i.e. its primary key is a composite one of the three columns.  In addition to referencing Items it is referencing a Users table.

To return a result table of all of each user's favourite items is merely a matter of joining the tables, e.g.

SELECT Users.UserID, FirstName, LastName, Pic, Title

FROM (Users INNER JOIN Links ON Users.UserID= Links.UserID)

INNER JOIN Items ON Links.ItemID = Items.ItemID

ORDER BY LastName, FirstName, Title;

To return a result table of each user's favourite items ranked as 1 for instance would merely require the query to be restricted on the Fav column:

SELECT Users.UserID, FirstName, LastName, Pic, Title

FROM (Users INNER JOIN Links ON Users.UserID= Links.UserID)

INNER JOIN Items ON Links.ItemID = Items.ItemID

WHERE Fav = 1

ORDER BY LastName, FirstName, Title;

You can of course reference a parameter in place of the literal 1 value.

To return a result table of all of a specific user's favourite items the query would be restricted on the UserID column:

PARAMETERS Forms!YourFormName!cboUser LONG;

SELECT Users.UserID, FirstName, LastName, Pic, Title

FROM (Users INNER JOIN Links ON Users.UserID= Links.UserID)

INNER JOIN Items ON Links.ItemID = Items.ItemID

WHERE Users.UserID = Forms!YourFormName!cboUser

ORDER BY LastName, FirstName, Title;

Where cboUser is an unbound combo box set up as follows:

RowSource:     SELECT UserID, FirstName & " " & LastName FROM Users ORDER BY LastName, FirstName;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

Alternatively, you can concatenate the names so that the last name is first, which would be better with a large list of names as it allows the user to enter the initial characters of the last name and progressively go to the first match as each character is entered:

RowSource:     SELECT UserID, LastName & ", " & FirstName FROM Users ORDER BY LastName, FirstName;

By binding a form to an amended version of the above query:

PARAMETERS Forms!YourFormName!cboUser LONG;

SELECT Pic, Title

FROM Links INNER JOIN Items ON Links.ItemID = Items.ItemID

WHERE Links.UserID = Forms!YourFormName!cboUser

ORDER BY Title;

And placing the cboUser control in the form header, you then merely need to requery the form in the control's AfterUpdate event procedure with:

Me.Requery

An alternative approach, and the one I'd recommend, would be to use a bound parent form, in single form view, based on Users and within it a bound subform, in continuous forms view, based on a query which joins Links and Items, the subform being linked to the parent form on UserID.  The subform would contain a combo box bound to ItemID from Links, set up to hide the bound column and show either the Pic or Title values, whichever you prefer to select an item by.  Whichever you use the other can be shown in a bound text box in the subform, with its Locked property set to True (Yes) and its Enabled property to False  (No) to make the control read-only.  A form/subform like this would be the obvious set-up for entering users and their favourites.

By placing the cboUser control in the form header you can then navigate to a user with code in its AfterUpdate event procedure:

    Const MESSAGETEXT = "No matching record"

    Dim ctrl As Control

    Set ctrl = Me.ActiveControl

    If Not IsNull(ctrl) Then

        With Me.RecordsetClone

            .FindFirst "UserID = " & ctrl

            If Not .NoMatch Then

                ' go to record by synchronizing bookmarks

                Me.Bookmark = .Bookmark

            Else

                MsgBox MESSAGETEXT, vbInformation, "Warning"

            End If

        End With

    End If

This will take you to the selected user, whose favourites will be shown in the subform.

If you wished to filter such a form to those users who have selected a particular item as one of their favourites you would do as in my demo, but the string expression to filter the form would be as follows:

strFilter = "UserID IN(SELECT UserID " & _

        "FROM links WHERE ItemID = " & ctrl & ")"

The control in this case would be an unbound combo box with a RowSource such as:

SELECT ItemID, Title FROM Items ORDER BY Title;

All tables should be on the server.  Each user can enter their own favourites simply by navigating to their own record in the form.  Separate copies of single front end can be used by each user, without the need to tailor each to the individual user.  If you do not wish users to see other users' records then the form should be based on a query which restricts its result table to the current user's record only.  This can be done by giving each user a password, which is stored in a column in Users.  In the form's query this column can then reference as a parameter a control in an unbound log-in form in which the user enters their password.  This form should be hidden, not closed, when the bound form is opened.

Be clear, however, that a set-up like this does not constitute 'security'; it merely controls the normal day-to-day operation of the database.  Any half-competent user could easily access other users' records if they so wished.  If you anything like real security a lot more would be required, but even then Access is not a suitable product where true security is needed.

Answer 5

Dear Ken

That is a phenomenal post; possibly the most informative, thorough and helpful information I have ever seen in a forum like this. Lots to think about, but I will attempt to redesign my application along the lines you mention - and it has inspired me to try to follow your Feynman philosophy a bit better. One can do a great deal in Access without writing much code, but of course a lot more WITH it, and that is my ambition.

Once again, many thanks

CS.