Refering to a MVF from within a query (1 Viewer)

[MvdBergh]

I’m working in the higher education environment. I have created a database (MS Access 2016) with access control for multiple users all over different faculties. The table I use is “AfterStartup” of which I’m using a multivalued field (MVF) “School” from where access to different schools are pre-selected per user. (This is linked to a Logon screen).

I need users to run financials reports for their selected schools from which the data is obtained from:

Table: Debtors_Summary​

Field: Dr_School​

Other fields: InvNumber, DrAmnt, CrAmnt​

The idea is to have only the schools’ information queried to which the users have access to as per MVF “School”.

The problem is when building the criteria with the expression builder to retrieve the "Dr_schools" from the MVF “School” from the table “AfterStartup”, I get no results unless I manually enter a string as 1 AND 2 AND 3.

I have read many posts on this and it seems I’ll have to write a search string.

I need assistance in writing such a search string or an alternative as to how to retrieve MVF fields in a query.

 

[Ranman256]

i would not use MVF, but rather, subtables. Easier to manage.

 

[jdraw]

If you research MVF you will often be referred to this link.
For most applications MVFs are not a first choice option because of what they do behind the scenes.
Better to restructure your table and use appropriate "lookup tables" and traditional queries/sql.

 

[MvdBergh]

jdraw. Thank you. I have realised the issues the more and more I have searched for an answer and found that is not advised. I'll address future development with subtables as you have suggested. Thank you for the link provided. I'll investigate accordingly.

 

[arnelgp]

On your query add the school tsble coz this is yhe lookup table.
Still on ur query add the school.Value not the "school" field from afterstartup or whichever tabke you have to query.
School.value will automatically link to the school.id.
In your query add the school name field or whatever info you need from main school table.