?
Solved

Getting the values from a multi value list in Access 2007

Posted on 2009-05-13
19
Medium Priority
?
598 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
[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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

777 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