Solved

MultiValue Field Reference

Posted on 2014-02-10
7
312 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

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)

Question has a verified solution.

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

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

776 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