?
Solved

Getting the values from a multi value list in Access 2007

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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