Solved

MultiValue Field Reference

Posted on 2014-02-10
7
314 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:hbatt
  • 3
  • 2
  • 2
7 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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:hbatt
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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:hbatt
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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

820 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