Multi-Value fields

[Pat Hartman]

It's been 14 years since their addition to Access and up until two days ago, I had been 100% against their use. I was actually present when the MS team announced this "feature" for the first time to about 20 fellow Access experts from around the country and abroad who had been invited to three days of presentations in Redmond. There was almost a revolt when the MS team told us how they were going to implement the feature with a hidden table. There was not a happy person in the room and experts have been steadfastly against the feature from the moment it was announced but only because of the way MS choose to implement it. If they had found a way to do it without hiding the table, we all would have adopted the feature because the interface for it is actually great. But by hiding the table, they prevented the data from being upsized and they prevented us from using it in any case where we had existing data or intersection data.

Anyway, I was helping a poster here with an application. The problem turned out to be, it is really difficult to force a main form record to have at least one choice from one of these multi-value fields when you implement it without the crutch. I was sure I had done this in the past, It is not a frequent requirement but it isn't that rare either. I kept coding myself in knots as I tried to give the user a warning and flag the parent record as incomplete when nothing was selected but not prevent the record from being saved because you have to save the main form record to move into the subform to add a record once you normalize the structure.

After about an hour, I threw up my hands and said - self - you are not getting paid to do this, just give up and just go back to the multi-value field so you can go to bed, so I did. I'm sure I could have gotten it to work but I wasn't prepared to spend any more time on it. One of the problems became making the update visible on the main form when the update was made by the subform. For example, in the AfterUpdate event of the subform, you have to count the subform records and if there are 0 (meaning you've deleted the last one), you have to set the incomplete flag on the parent table to true and if the count is > 0 then you set it to False if it isn't already false. But for some reason, I couldn't make the change visible on the main form. In my defense, it was late and I was tired.

So, if any of you have an example that shows how to flag the mainform as incomplete until at least one subform record has been added, it would be a good addition to our code library. If no one posts a sample in a couple of days, I'll build one myself.

 

[NauticalGent]

Good evening Pat,

I'm not sure if what I attached will help, but it is the only place (not that I looked!) where I found any code on how to navigate MVF. It is an except from the 2010 Access Developers Reference but I was not able to save it in PDF format so I did a C&P into Notepad.

Hope it helps...

 

[CJ_London]

A couple of thoughts spring to mind

thought 1

have a specific save button on the main form, any potential saves through user moving off form being cancelled until the save button is clicked.
The subform control would not use linkchild/master properties, instead based on a criteria in the subform recordsource (which is good practice anyway)
The subform would be based on a disconnected ADO recordset - it could be populated with the autonumberPK from the main form if it has been assigned
The mainform button save would have a check as to whether the disconnected recordset has at least one record before allowing a save
and part of the save process would be to update the child table from the disconnected recordset. Thereby maintaining referential integrity
additional thought - main form could be a disconnected recordset as well

thought 2

use transactions and roll back the mainform insert if no subform records are added. Technique often used where user aborts for example an invoice entry

 

[isladogs]

See if my article helps Multivalued fields ...and why you really shouldn't use them.. Whilst it doesn't precisely answer your question, it does cover how to search and update the dreaded MVFs. It also shows the workings of the fully normalised deep hidden table lurking in the background and other issues

 

[Pat Hartman]

I'm not looking for a solution. I was asking for an existing sample if anyone had one. I know very well WHY MVF's are to be avoided. But I left the MVF's in the poster's app because it was the course of least resistance.

It looked to me to be a good topic for a sample. If no one has a working sample to post using the requirements I specified, then I'll build one at some time.

The problem is that most 1-m relationships are actually 0-m. But, this one required at least ONE many-side record in order for the parent to be usable and in my tired state, I was having trouble working out how to make this happen. It is a cart before the horse problem. You can't save a child record without an existing parent record but the parent record requires at least one child record. Obviously you need to save the parent first but you need to mark it some how as incomplete and then the subform has to mark the parent record as complete, once at least one item is added.

Either I made this harder than it needed to be or you've never encountered this requirement before so you haven't though about how to implement it. Maybe this is the situation that the MS team was thinking of when they thought this feature would be a good idea.

Thanks

 

[jdraw]

I don't have an example, but it "sounds like" an older issue at least conceptually.
A store has Customers and Customers may have 1 or more Orders (definition/business rule)
So how to define a Customer before he/she has made an Order?
This may just be a definition issue; or a process sequence issue.
The 0 or many resolves things.

 

[Pat Hartman]

Customer is the 1-side of order so there is no problem defining a customer before an order. If you create order first and have a new customer, you pop up the customer form and add the customer and then continue with the order. In many applications Order doesn't even need a customer. Customer is optional and is usually provided only if the order will be shipped. For this application, the many-side table has no meaning by itself so you would never create it before you create its parent.

 

[CJ_London]

Thought your issue was the need to save the parent before going to the subform - in which case if it is saved and you didn't want to do that until there was a record in the subform as you wanted to preserve relational integrity.

I was just suggesting what might work if you are going to try yourself, for that I don't have an example

if you are just wanting a textbox to display a message, You don't need to store a value to say it is incomplete, you can do that with a dlookup/dcount/dmax/subquery/exists whatever. But I'm sure you know that.

so a textbox with something like the following code in it
=IIf(DCount("*","childtable","parentFK=" & Nz([parentPK],0)),"Order can be completed","Order Incomplete")

to trigger a requery use the subform exit event to requery the control and you can also use the subform form delete/insert events to requery as well

 

[GK in the UK]

In my Invoicing application, I set a flag in the main form on the firing of Child Form After Update. Main form then dirties the main record (Me.thDate = Me.thDate). So the main form has no choice but to be saved again. The main form BeforeUpdate tests, and asks the user if they really want to save an empty invoice. I allow it, if confirmed, but it would be easy to demand that a child record is inserted first and of course it doesn't actually delete the already saved main record once it's in the table. Db could be coded to delete but by then it's acquired it's consecutive number so it has to stay, empty or not. It can be re-used with a different customer on the next order.

I should add that the user can click "Add item" which saves main record, gets invoice number, and then cancel pop-up child form so no child records

 

[Pat Hartman]

Thought your issue was the need to save the parent before going to the subform

No, as we all know, the mainform is always saved if it is dirty when you move focus to the subform.

The problem I was having was that when I modified the mainform from the subform, the mainform was NOT showing the change in status. The status was changing but the new value could not be seen until the form was closed and reopened.

 

[CJ_London]

so my suggestion in post #8 should work for for you

 

[Pat Hartman]

The reason for storing the values is to make the queries easier. Otherwise ALL queries need to incorporate the domain function in the where clause.