Solved

MultiValue Field Reference

Posted on 2014-02-10
7
315 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
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 48

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: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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

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: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 48

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

710 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