• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • Last Modified:

Getting the values from a multi value list in Access 2007

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
BobHavertyComh
Asked:
BobHavertyComh
  • 9
  • 6
  • 4
1 Solution
 
rockiroadsCommented:
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
 
BobHavertyComhAuthor Commented:
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
 
rockiroadsCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
BobHavertyComhAuthor Commented:
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
 
rockiroadsCommented:
Not sure how multivalue fields are setup. Looking into it now, but it might be more than just a simple split
0
 
BobHavertyComhAuthor Commented:
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
 
rockiroadsCommented:
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
 
BobHavertyComhAuthor Commented:
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
 
BobHavertyComhAuthor Commented:
Actually, the multi value field has numbers so i can't use the split function
0
 
Rey Obrero (Capricorn1)Commented:
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
 
rockiroadsCommented:
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
 
BobHavertyComhAuthor Commented:
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
 
BobHavertyComhAuthor Commented:
rockiroads, I don't know exactly what you mean, do you want the whole database file or something?
0
 
rockiroadsCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
BobHavertyComhAuthor Commented:
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
 
BobHavertyComhAuthor Commented:
What is recordsetclone for?
0
 
Rey Obrero (Capricorn1)Commented:
sorry, i missed that part { On current } , actually didn't read it all, just read the title of the q. ;-)
0
 
Rey Obrero (Capricorn1)Commented:
What is recordsetclone for?  it is copy of the form's recordset
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 9
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now