Multivalue field in a continuous form 1

[waubain]

I was asked to create a continous form with Research Members and their highest acedemic and professional degrees. I had not used a multivalued field before but it seemed this was a good choice. I created a table(tblDegree) with single field of 10 different degrees. I added the multivalued field(Degrees) to tblMember. In the continuous form frmMember bound to tblMember the control was populated as expected. Each degree was automatically separated by a comma.

What I did not like is the alphabetical order in which they appeared, typically the professional degree comes first after the name and then the highest acedemic degree. I created a sortorder field in tblDegree and changed it to the primary key(bound column 2) so professional degree numbers were lower than acedemic degrees. This corrected the arrangement in tblMember datasheet (1", 0"), but in the form frmMember I can only see the bound column sort numbers in the textbox control. The numbers do appear in the correct order for whatever that was worth.

Without getting into discussion on the bads of multivalued fields, is there way to change the continuous form textbox control to populate with correct colunm?
Thanks

You don't know what you don't know...

 

[PHV]

Is your table linked to a SharePoint's list ?
If not then, sorry, MultiValued field is NOT a good choice.

I'd use a classical junction table between the tblMember table and a tblDegree table having 10 records.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[waubain]

Thanks PHV for the reply. No this is not linked to a SharePoint list.

I guess I have 2 choices, live with the multivalued misordering or create the junctional table which I typically do. Thougtht I would try something new that seemed appropriate for the multivalued field. One thing I have not done is loop through the juntional table so the in the textbox the records appear with a comma between records on the continuous form.

txtFirstLastName txtDegree
John Doe, MD, PhD

You don't know what you don't know...

 

[PHV]

Have a look here:
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

You are correct that is a limitation of the MV field. So you can sort the presentation of the list and displayed columns (just like a combo), but the resulting display sorts on the bound column. I am almost positive there is not any way to work around. If you absolutely had to you could fake it. There might be some reason like you have a Sharepoint list. But basically you would concatenate the MV records yourself using similar code to what PHV suggests. When you use a MV field it creates a hidden system table similar to what you would do with a junction table. You can read those values from the recordset of the MV field. Then you could concatenate and display in the order you want. Although this is not overly complicated it is pretty obscure. It may be worth an academic exercise or if required by Sharepoint. If I get time I will demonstrate that it can technically be done, although not necessarily feasible. The problem with the MV fields is that there is a layer of abstraction that you have very little control of. So either the out of the box works for you or you are stuck with what you get.

 

[waubain]

Thanks PHV and MajP.

MajP: I have already began the creation of the junction table, so use you time wisely for any demonstration purposes.

As a last ditch effort in futility, I went back to my original single column design in tblDegree an renamed the degrees with a number in front so they would appear in the order I wanted.

11MD
12DO
...
20PhD

I then tried, unsuccessfully, to Trim off the number before it was displayed.

...Waubain

You don't know what you don't know...

 

[waubain]

To anyone else reading this thread the option I finished with was to abandoned the junction table altogether. I created 2 combo boxes tied to PDegree and ADegree in tblMember. In the query tied to the continuous form frmMember I concatenated the two fields and place the control source of txtDegree to:

ConcatDegree: IIf(IsNull([ADegree]),[PDegree],IIF(IsNull([PDegree]),[ADegree],[PDegree] & ", "&[ADegree]))

For my purpose was adequate. This would not work if the form had to list all achieved degrees rather than pick one from each category.

...Waubain

You don't know what you don't know...