Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Getting the values from a multi value list in Access 2007

Posted on 2009-05-13
19
Medium Priority
?
603 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
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 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

Technology Partners: 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!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

636 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