[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Multivalued Field Functionality in Access 2003

Posted on 2010-11-18
17
Medium Priority
?
982 Views
Last Modified: 2012-05-10
I would like to replicate the multivalued field functionaility from Access 2007 in Access 2003.
I found a similar question on the internet: http://www.accessmonster.com/Uwe/Forum.aspx/access-forms/47260/Multivalued-Field-Functionality-in-Access-2003   

However, i do not know how to implement the answer "Add a boolean field to the table and use a continuous form (showing the boolean field) as a subform, rather than listbox."

To which table do I add the boolean field? How do I link the tables? Can somebody run me through the steps, or upload a template file?
0
Comment
Question by:kittenwhisky
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +1
17 Comments
 
LVL 75
ID: 34169750
Just an FYI ... the general recommendation here on EE is to avoid Multi-Valued Fields

See this:

http://allenbrowne.com/Access2007.html#Bugs

Search that page for Multi-valued

Also see this:

http://support.microsoft.com/kb/926701

mx
0
 
LVL 4

Author Comment

by:kittenwhisky
ID: 34169841
Thanks for your advice about MV fields, DatabaseMX. Let me be more precise: I have alredy normalized my tables in my database by creating a junction table using the primary keys of 2 different tables. But I know want to display that information in a form, where I don't mind having MV fields.

I want the form to handle the information "like Access 2007 does with multi-valued fields", because the Access 2007 method seems very user-friendly with the drowpdown and the checkbox, as seen here http://www.databasedev.co.uk/image/choose_multiple_values.gif

The thread I found about this seems to give the answer, but I don't know how to implement it exactly. Would appreciate any further help.
0
 
LVL 75
ID: 34169863
Well ... I haven't use MVF's ... so, I'm probably not the person who can help you with this.

Someone may be along who can.

Sorry ...

mx
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34169871


kittenwhisky,


add the boolean field in the table that you will display in the continuous form.

just take note that you will be adding the records to the junction table using codes,
after the selecting (checking ) the items displayed in the continuous form together with the id (PK) displayed in the main form..


post back if you encounter a problem..


0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34169877
If I am reading your post correctly, this can be done quite easily without MVF's
(The same way it has been done for decades before MVF's came along)

;-)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34169883
and, sorry forgot to mentioned,
after adding the records in the junction table,
you have to run an update query to reset all the selected items (boolean field) to No.
0
 
LVL 4

Author Comment

by:kittenwhisky
ID: 34169983
thanks for your responses, it's pretty late here, but will come back tomorrow evening.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34170036
1. Did you read the link MX posted?
This feature has a "Bug" that is not yet resolved...
Still want to proceed with this...?

2. See here as well:
http://allenbrowne.com/Access2007.html#Mixed
http://www.theregister.co.uk/2006/07/18/multivalued_datatypes_access/
Quote: Ted Codd (the originator of the relational model) forbad their use....
Still want to proceed with this...?
;-)

As you will see that this "user-friendly" interface comes at a price....

3. Remember these fields were created with MS SharePoint in mind.
(Are you using SharePoint?)

4. This datatype is not backward compatible.
So if you ever need this to be accessible to anyone who has Access 2003 or older, you are out of luck.

Finally note that the dearth of simple-to-follow tutorials on this is another red flag.
(Yes, I have read some good ones, but on the whole they are a bit overwhelming for someone just starting out in MS Access.)

I leave the decision to use these fields up to you...

;-)

JeffCoachman
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34170044
;-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34170342
kittenwhisky,

Here is a *very* basic example of simply using a junction table.
While not as ascetically pleasing as the MVF's it does not have all of the associated "issues".

Any Expert her can view this and understand what was done.
Any Expert here can modify it in their own way to make it more user friendly (more compact, Concatenate the values, ...etc)

Jeff
Access-EEQ26625842DisplayJunctio.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34170344
0
 
LVL 4

Author Comment

by:kittenwhisky
ID: 34178353
hi boag2000

I am very sorry I could not award you points on this question, but I think your answers, even though elaborate, were missing the point. I was probably not expressing myself clearly, so let me try to explain again:

We have the same idea: I am using a table1 , table2 and junction table with PK1 & PK2 as a design source for my form.

My question relates to the customisation technique to make the form more user-friendly (basically copying the Access 2007 MVF display, such as the check boxes & concatenation). The underlying data will NOT be stored as an MVF in any table, it will only be displayed as an MVF on the form.

The link I provided in my question does not advocate storing the data in MVF's. The user suggests as well to use a junction table, and then briefly explains how to achieve a customisation whichncludes check boxes. I was looking for a more elaborate explanation on these customisation steps.
0
 
LVL 4

Author Closing Comment

by:kittenwhisky
ID: 34178358
I have encountered an issue when writing the code, I will be opening a new question for this tomorrow, would be great if you could help out on that one , too!
0
 
LVL 4

Author Comment

by:kittenwhisky
ID: 34178370
just to be clear: I validated the wrong comment as a solution, i meant to validate capricorn's answer #34169871, not his smiley answer.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34178371
sure...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34178437
No problem here, as long as your issue is resolved...
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question