MultiValue Field Reference

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?
Harry BattDirector of DevelopmentAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
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
 
Dale FyeCommented:
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
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Harry BattDirector of DevelopmentAuthor Commented:
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
 
Harry BattDirector of DevelopmentAuthor Commented:
I went back to using a text field as the most prudent course of action.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
hbatt,  I think that is a wise decision. Good luck with your project.
0
 
Dale FyeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.