Solved

Getting the values from a multi value list in Access 2007

Posted on 2009-05-13
19
573 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Binding recordsets to a form 6 27
Access Delete Query Not Working 13 33
Modify report 8 11
AddNew causes duplicate key in VBA Access 12 19
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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 …
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…

831 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