A long time ago, in a galaxy far away (1 Viewer)

[cyberman55]

Someone used a table-level lookup so multiple records could be selected for a single record in the main table. In this case, the main table is composed of tasks and the related table contains users. I put an active/inactive boolean in the user table and it's easy enough to prevent the selection of inactive users. But that blocks the visibility of inactive users. So now, old task records that have inactive users show their ID numbers rather than initials. Below, perhaps the images explain the problem well. I'm looking for some way to have my cake and eat it too here. The client doesn't want to scroll through inactive users when making selections, but does want to see all past picks active or not. Incidentally, a friend who was present at the 2007 developer's conference in Redmond where Microsoft showed off this new "feature" ended up with screaming from those audience members who grasped that this was breaking relationship rules. I guess the protestors were right as this thing ends up a mess that's difficult to unwind.

 

[plog]

Forms and Reports.

Use forms to enter data, use reports to get data out. Your form can make it so that you can only enter active people, the report can be made to show everyone. Tables should be behind the scenes for users and accomodate all data.

As for the multi-select, its not more than an hour to fix, it's only 1 field. Set up the correct junction table, then write some queries to move the data from the existing field to that new table.

 

[Josef P.]

A multi-valued field does not create a violation of the normalization rules. It is just a 'hidden' n:m table, which may also hide the rule implementation a bit.

I would have preferred it if a 'normal' table could be defined as an n:m table for an MVF control.
If you use an active DBMS such as SQL-Server, these multi-value-fields do not exist at all.
(I never use a MVF in my data structure - only for client features)

With a query you can show this n:m table data:

SELECT idTask, AssignedTo.Value AS UserID
FROM tTasks
WHERE AssignedTo.Value>0

Attached is a variant that displays the selected inactive users, but does not allow selection of other inactive users.

 

[CJ_London]

The client doesn't want to scroll through inactive users when making selections, but does want to see all past picks active or not.

you can achieve this by including in your rowsource the active/inactive flag (it doesn't need to be displayed or even brought through) then sorting on that before the names

Assuming ticked means active and unticked means inactive then the sql would be something like

SELECT ID, firstname,, lastname
FROM tblUsers
ORDER BY active, firstname,, lastname

checkboxes are booleans - true/Yes=-1 and false/no=0 so -1 will sort before 0

to prevent an inactive record being selected, you would need to bring the flag through then do a test in the before update event to reject the selection if false

 

[cyberman55]

A multi-valued field does not create a violation of the normalization rules. It is just a 'hidden' n:m table, which may also hide the rule implementation a bit.
View attachment 106607

I would have preferred it if a 'normal' table could be defined as an n:m table for an MVF control.
If you use an active DBMS such as SQL-Server, these multi-value-fields do not exist at all.
(I never use a MVF in my data structure - only for client features)

With a query you can show this n:m table data:

SELECT idTask, AssignedTo.Value AS UserID
FROM tTasks
WHERE AssignedTo.Value>0

Attached is a variant that displays the selected inactive users, but does not allow selection of other inactive users.

Josef: That's brilliant. Thank You. I was puzzled a bit until I saw the event - very good trick. I was thinking I'd have to extract the data and make some type of detail table, but that probably wouldn't give me the ability to have that MVF control.

 

[isladogs]

For more info on how MVFs work, the good/bad & the ugly, see my article

Multivalued Fields

Multivalued fields (MVFs) were introduced with Access 2007. This article explains what MVFs are and how they can be used. However, most experienced developers avoid them completely. This article explains why this feature is really not a good idea

www.isladogs.co.uk

Spoiler: there really isn't anything good about MVFs

 

[cyberman55]

For more info on how MVFs work, the good/bad & the ugly, see my article

Multivalued Fields

Multivalued fields (MVFs) were introduced with Access 2007. This article explains what MVFs are and how they can be used. However, most experienced developers avoid them completely. This article explains why this feature is really not a good idea

www.isladogs.co.uk

Spoiler: there really isn't anything good about MVFs

Thanks Colin - you're always helpful. Sent you a few bucks beer money. And, I agree, there is nothing good about the MVF's except possibly the ability to display the data more compactly then, say, a listbox control.

 

[cyberman55]

Funny, the dbguy's sample db for moving attachments looks similar to mine here: Data mover sample database moves attachments.

He did it more elegantly, but mine is prettier and I think I did it first, back in 2013 or so.

 

[isladogs]

Thanks Colin - you're always helpful. Sent you a few bucks beer money. And, I agree, there is nothing good about the MVF's except possibly the ability to display the data more compactly then, say, a listbox control.

Thank you very much for the beer money
You can of course can the same result without using MVFs by contatenating data from different records

 

[Josef P.]

My opinion: The controls for using the MVF are a good idea. The possibility to select multiple entries in a dropdown can result in good usability for the user in some situations.
I don't like the implementation in the data structure. It is not technically flawed, but inflexible - not usable for SQL Server, etc.

Why did they "invent" this hidden table? Maybe because from the point of view of the office marketing department (MSFT) it is not reasonable for Access users (developers?) to set this n:m source as a property in the MVF combo box?
... for the Access web app (SharePoint) it was sufficient.

 

[Pat Hartman]

a friend who was present at the 2007 developer's conference in Redmond where Microsoft showed off this new "feature" ended up with screaming from those audience members who grasped that this was breaking relationship rules.

I was there, I remember it well. Half the room raised out of their seats at that part of the presentation. No one was happy, even when the hidden table was explained.

A multi-valued field does not create a violation of the normalization rules. It is just a 'hidden' n:m table, which may also hide the rule implementation a bit.

All of the abomination fields introduced in A2007 are technically "normalized" BUT because the important part is hidden, they are a PITA for developers to control and require non-standard SQL. The abomination fields were developed to make Access more SharePoint like. We all know how that marriage worked out. But we are left with these abominations that every newcomer seems to glom onto and use and then we get the questions about why their code doesn't work. Why the query doesn't work, etc.

I understand why MS felt the need to hide the many-side tables because having us muck with them left open a host of bug issues for the fancy new controls. But by hiding the many-side tables, they forced us into new awkward SQL and lots of other issues and so professionals just use the old methods which are exactly what the abomination fields use and use subforms with checkboxes instead of the fancy multi-value control.