?
Solved

How Do I Get / Set The Value of  MultiValued Field / ComboBox in Visual Basic for Applications Under Access 2007?

Posted on 2009-06-29
9
Medium Priority
?
782 Views
Last Modified: 2013-11-28
Before updating the text field to a multi-value field / combo box, I was using:

 Me.<Textbox Name>.Value = <Value> to set the value

and
Me.<Textbox Name>.Value  to retrieve the value

After updating this field to a multi-value field, the above code no longer works. I've looked at all of the properties and consulted the help menu.  The help menu mentions using DAO to retrieve the parent recordset and then setting a childrecordset to the multivalue field in the parent recordset to browse the selected value.  I've been successful in doing that, but how do I set with vba which values I want selected in the child recordset and how would I pass this back in the parent recordset for submission to the database?
Here is how I would browse the value of the field and this works:
Dim db As DAO.Database
Dim rs As Recordset
Dim childRS As Recordset
 
Set db = CurrentDb
 
' Get Primary RecordSet Note
Set rs = db.OpenRecordset("SELECT * FROM Notes WHERE Note_ID = " & Me.Note_ID)
 
rs.MoveFirst
Set childRS = rs.Fields("Code").Value
 
childRS.MoveFirst
 
Do Until childRS.EOF
MsgBox (childRS.Fields("Value").Value)
childRS.MoveNext
Loop

Open in new window

0
Comment
Question by:davysouthernboy
[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
  • 3
  • 3
  • 3
9 Comments
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 1400 total points
ID: 24738539
Look at the property ItemsSelected.  When you selected multiple rows the row numbers are stored in a zero based array called Items Selected.  Help has some good examples in 2003 for multi-select list boxes - I imagine similar for these new Combo boxes in A2007.
0
 

Author Comment

by:davysouthernboy
ID: 24739142
GRayL,  I looked at the ItemSelected property and wrote this code:

Dim CodeCount As Integer
For CodeCount = 0 To Code.ItemsSelected.Count - 1
MsgBox ("Item: " & Code.ItemsSelected.Item(CodeCount))
MsgBox ("Value: " & Code.ItemData(Code.ItemsSelected.Item(CodeCount)))
Next CodeCount

The Code.ItemSelected.Item(CodeCount) line correctly returns the row of the selected item.

However, the line Code.ItemData(Code.ItemsSelected.Item(CodeCount))) returns a blank / empty string everytime.

What am I doing wrong?
0
 
LVL 44

Accepted Solution

by:
GRayL earned 1400 total points
ID: 24740982
The format for this is:

forms!frmName!cboName.column(0,forms!frmName!cboName.ItemsSelected(CodeCount))

If you want the second column of the combo, set the 0 to 1.  If you are calling the routine from within the code module of the form, you can replace 'forms!frmName!' with 'Me!'
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Author Comment

by:davysouthernboy
ID: 24741226
GrayL, Thank you!  That worked like a charm.  So now I know how to get the values of the selected items in VBA.  Could you please tell me how I could programatically set which values in the combobox are selected in VBA?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 600 total points
ID: 24743718
If it's anything like listboxes, you will have to select items by the "Row" number.

Something like this:
Me.List0.Selected(2) = True
Me.List0.Selected(5) = True

JeffCoachman
0
 

Author Comment

by:davysouthernboy
ID: 24745246
I put both

Me.Code.Selected(2) = True

and

Me!Code.Selected(2) = True

but it did not work.
Any other ideas?  What's the difference between putting an exclamation mark and a period by the way?
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 600 total points
ID: 24746114
< What's the difference between putting an exclamation mark and a period by the way?>

You could debate this for days.
Basically the Exclamation point (Bang) is used to list the "Members" of an object.
The Period (Dot) is used to list properties.
Many times they are interchangeable.

Is this a listbox, Combobox, or some sort hybrid that is only available in Access 2007?
Be very careful when jumping to use these new Multi-Valued-Fields:
http://allenbrowne.com/Access2007.html#Mixed
(Please remember that database have been designed for decades without the use of this "MS Access olny" feature.)
And that they mask and or limit your abilty to design versitile database designs.
(just be carefull)

JeffCoachman
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 1400 total points
ID: 24746319
! - something you made
. - something M$ made.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24752567
Thanks Ray, I had forgotten about your little poem.

;-)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
Suggested Courses

770 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