Solved

Getting the values from a multi value list in Access 2007

Posted on 2009-05-13
19
581 Views
Last Modified: 2013-11-27
During the on current event, if it's not a new record, I want to get the values from a multi value field and put them into an array. How do I do that?
0
Comment
Question by:BobHavertyComh
  • 9
  • 6
  • 4
19 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24374056
A multi value field? I take it you mean the list

Considering values are delimited by a semi colon, you can use the Split command

eg

Dim sWords() as string

sWords = split(Me.mylist,";")

Now its held in a string array called sWords

to ensure its not a new record, use NewRecord flag

if Me.NewRecord = false then
 ,,,,
0
 
LVL 9

Author Comment

by:BobHavertyComh
ID: 24374102
A multi valued field is a specific feature in Access 2007 which allows you to enter in multiple values into one field. Normally, to get the value inside of a field, I would merely say variable = fieldname. So in the case of an Access 2007 multi value field, what would i do? array(0) = fieldname.value1, array(1) = fieldname.value2  ???  What is the lingo for talking to a multi value field in Access?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24374130
How are the values delimitd in this multi value field?

To place in an array, easiest option would be to use the split command.

Split(someword, somedelimitr)  returns a string array

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 9

Author Comment

by:BobHavertyComh
ID: 24374149
They are delimited by a comma, but my question isn't so much about what to do with the values after I have gotten them, it's about how to get the values in the first place. How do i talk to a multi value field and get it to cough up it's values?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24374166
Not sure how multivalue fields are setup. Looking into it now, but it might be more than just a simple split
0
 
LVL 9

Author Comment

by:BobHavertyComh
ID: 24374185
A split doesn't tell me the values in that field, it only splits them after I already have them. I need to get the values first before I bother with anything like split.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24374286
Sorry, I dont have A2007. If this field is on the form, how is it presented to you, is it shown as a comma delimited field?
Or are you wanting to read this using a recordset?

I would say just reference that field
0
 
LVL 9

Author Comment

by:BobHavertyComh
ID: 24374514
It's shown as comma delimited and the values are selected via a drop down of checkboxes. I'm going to try to try what you said and guess that array = split(fieldname.value, ",") will return one long string that I can split.
0
 
LVL 9

Author Comment

by:BobHavertyComh
ID: 24374572
Actually, the multi value field has numbers so i can't use the split function
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24374640
assuming the name of the multivalued field is MVFld  

diim rs As dao.recordset
dim rs2 As dao.recordset

set rs = currentdb.openrecordset("select * from TableX")
set rs2 = rs.fields("MVFld").Value

do until rs2.EOF
    debug.print rs2.Fields(0).Value
    rs2.MoveNext
loop
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24374904
can u upload a small sample db showing your combo as a multi value field. I can get hold of portable access 2007 so will give it a quick try. I have an idea of what is needed,
0
 
LVL 9

Author Comment

by:BobHavertyComh
ID: 24374939
capricorn, why do I have to query the table as opposed to something like me.fieldname? I am only trying to get the values currently stored in one particular MVF. Normally, if i want the value in a field of the current record, i merely reference the field name. Ex. Variable = fieldname.
0
 
LVL 9

Author Comment

by:BobHavertyComh
ID: 24374953
rockiroads, I don't know exactly what you mean, do you want the whole database file or something?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24374960
na, just create a small sample which has a form that has a multi value combo box, dont need real data, just dummy data.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 24375022
BobHavertyComh,

 didn't know you had a form open

with me.recordsetclone

set rs2=.fields("MVFld")

do until rs2.EOF
    debug.print rs2.Fields(0).Value
    rs2.MoveNext
loop

end with


0
 
LVL 9

Author Closing Comment

by:BobHavertyComh
ID: 31580926
Yes, I meant the form was open when
i mentioned the current form event in the beginning. This looks like what I've been asking for. thanks.
0
 
LVL 9

Author Comment

by:BobHavertyComh
ID: 24375088
What is recordsetclone for?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24375115
sorry, i missed that part { On current } , actually didn't read it all, just read the title of the q. ;-)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24375130
What is recordsetclone for?  it is copy of the form's recordset
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

821 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