?
Solved

MultiValue Field Reference

Posted on 2014-02-10
7
Medium Priority
?
320 Views
Last Modified: 2014-02-11
Can somebody tell me how to reference the data in a multivalue field? I am attempting to call Outlook from Access and have the message body display the values in a subform. Everything works except for the data in the multivalue field and I get a data mismatch error. Attached is the code I am using that works with the multivalue field removed.
 strBodyText = "Hi" & vbCrLf & vbCrLf & _
    "Case Number: " & Me.subfrmDetail.Form![PropertyAddress] & vbCrLf & vbCrLf & _
    "Cause of Action: " & Me.subfrmDetail.Form![CauseofAction] & vbCrLf & vbCrLf & _
    "Opposing Counsel: " & Me.subfrmDetail.Form![OpposingCounsel] & vbCrLf & vbCrLf & _
    "Important Dates: " & Me.subfrmDetail.Form![ImportantDates/Deadlines] & vbCrLf & vbCrLf & _
    "Property Address: " & Me.subfrmDetail.Form![PropertyAddress] & " " & Me.subfrmDetail.Form![PropertyCity] & ", " & Me.subfrmDetail.Form![PropertyState] & " " & Me.subfrmDetail.Form![PropertyPostalCode] & vbCrLf & vbCrLf & _
    "Project Description: " & Me.Project_Description & vbCrLf & vbCrLf & _
    "Thanks!"

Open in new window


Also, do I need to keep referencing the subform when concatinating the address?
0
Comment
Question by:Harry Batt
  • 3
  • 2
  • 2
7 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39848429
Personal opinion.  Multi-valued fields were a big mistake, designed to work with SharePoints multi-valued field, which are somewhat necessary because normal users have no way of manipulating the lists to create a true one-to-many or many-to-many relationships.

In my experience, they are more of a headache than a "value"!

I assume that the [ImportantDates/Deadlines] field is the multi-value field?  Or is it one of the others?
0
 
LVL 21
ID: 39848570
I agree with fyed that Multi-valued fields were a big mistake. I avoid them for all Desktop databases. They are a great source of frustration and headaches for many.

A  Multi-valued is basically a sub-table. When working with a Multi-value fields you are basically  working with recordset.  In your case it will be best to use a query to retrieve the values. AFAIK, you can;t use a form reference to retrieve all the values.

See this office.microsoft.com artivle: Using multivalued fields in queries
0
 

Author Comment

by:Harry Batt
ID: 39848583
I am always hesitant to use MVFs because of the many issues that they cause, and I may go back to using a text field. In this case, I have omitted the multivalue field because of the error. The field is called "Possible Defense" and there are many options. Originally it looked like this:
"Possible Defense: " & Me.subfrmDetail.Form![PossibleDefense]
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39848597
When you retrieve the values in a query, only those that are selected will show up, separated by a comma, if I remember correctly.  So you cannot do a query of:

SELECT * FROM myTable WHERE [Possible Defense] = "Some Value"

Instead, you have to use:

SELECT * FROM myTable WHERE instr([Possible Defense], "SomeValue") > 0

and even then you might not get what you expect.
0
 

Author Closing Comment

by:Harry Batt
ID: 39848772
I went back to using a text field as the most prudent course of action.
0
 
LVL 21
ID: 39848900
hbatt,  I think that is a wise decision. Good luck with your project.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39849760
Instead of a multi-valued field, the general alternative is to create your own using either a listbox or a small subform.  The challenge is that you have to build the table to store your responses in, and the code to store the responses and to populate your list based on what your users selected.  

If you want to pursue that, post back and I will try to walk you thru it.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Are you looking to start a business? Do you own and operate a small company? If so, here are some courses you need to take before you hire a full-time IT staff.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

594 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