UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Create Multi Select Box Like 2010, Office 2003    
 
   
Dexter
post Jun 20 2012, 02:51 PM
Post #1

UtterAccess Guru
Posts: 528



I want to create a multi select list check box as close to 2010 as possible. What options do I have for creating the multi list box and saving the multiple selections in my database per record? I have been reading online and it seems that to save multiple selections from a listbox, you have to use code. The code that I found was a bit over my head. The one that I tried to implement sort of worked but not really. I am okay in VBA but not great. The multiple selections need to go on a report in list form.

Thanks
Go to the top of the page
 
+
Jeff B.
post Jun 20 2012, 04:53 PM
Post #2

UtterAccess VIP
Posts: 8,544
From: Pacific NorthWet



Are you talking about a "multi-select" list box, or a list of checkboxes, in which you can check multiple items?

The former is just a standard listbox with the "multi-select" property turned on ... and you need to iterate through what's been selected.

The latter is a construct of the new version(s) of Access. Underneath, not accessible (no pun intended) to the developer, Access is representing this as, I believe, a "many" side table. I'm sure there are folks out there who can help with identifying which checkboxes are checked in the list ... sorry, not my area.

If you're working with the former, post back for more ideas.
Go to the top of the page
 
+
theDBguy
post Jun 20 2012, 04:58 PM
Post #3

Access Wiki and Forums Moderator
Posts: 55,856
From: SoCal, USA



Hi Dexter,

If I am not mistaken, you must be talking about the new Multi-Value Field (MVF), which allows you to use a combobox or a listbox to select multiple items and "stored" in the record as a delimited value.

If so, I'm afraid you will have to use VBA to copy that functionality in 2003.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
pere_de_chipstic...
post Jun 20 2012, 05:45 PM
Post #4

UtterAccess VIP
Posts: 8,581
From: South coast, England



Hi Dexter

In A2010 the Multi Value Field is often misunderstood, it's actual implementation, behind the scenes, is a hidden table that is equivalent to a standard junction table. The MVF table field, MVF combo box and MVF list box are simply the way that Access represents that data on the user interface ie it LOOKS like a comma separated string but this is purely the way it is displayed, it is not stored as a comma separated string.

There is no equivalent to the MVF combo or MVF list box in A2003.

To simulate the function you need to create a standard join table to store the selected values. There are then various implementations you could use for the form interface,

1. use a subform bound to the join table, and select the values via combo boxes.
This is a 'standard' method of populating a join table

2. Use a multi select list box
Then use code both to write the selections to the join table or to read the table and set the selected items in the list box

3. Use a subform showing all the records in the source form with checkboxes, which are bound to virtual fields in the sub forms recordsource, shown against each record, again you would need to use code to add and delete records from the join table

Option 1 is the easiest option to implement and suggest you should use this.

MVFs are useful but complex constructs, and IMHO (and probably most developers) were very poorly implemented by MS. they need to be used with caution as they are not comaptible with earlier versions of Access nor are they compatible with any other RDBMS system should you ever wish to upsize your db.

hth

This post has been edited by pere_de_chipstick: Jun 20 2012, 05:54 PM
Go to the top of the page
 
+
Dexter
post Jun 21 2012, 11:39 AM
Post #5

UtterAccess Guru
Posts: 528



In Access 2010, if you add a combo box and give it a value list and select that the user can select multiple items on the list, it gives you a nice drop down with check boxes next to each item. That is pretty slick and if that is called a mult-select list box then yes that is what I'm referring to. I just basically want to duplicate that look and feel and still be able to store the selections per record in the database so I can later add them to a report.
Go to the top of the page
 
+
Dexter
post Jun 21 2012, 11:41 AM
Post #6

UtterAccess Guru
Posts: 528



My online searches pointed me to various VBA solutions also. The problem is that I'm okay with VBA so some of it was over my head. Can you point me to a good example of VBA that will work for what I'm trying to do that is as simple as possible?

Thanks!
Go to the top of the page
 
+
Dexter
post Jun 21 2012, 11:42 AM
Post #7

UtterAccess Guru
Posts: 528



Good to know. Thank you. This will definately help.
Go to the top of the page
 
+
pere_de_chipstic...
post Jun 21 2012, 01:09 PM
Post #8

UtterAccess VIP
Posts: 8,581
From: South coast, England



Hi Dexter

I think it important that you differentiate between a Multi Select list and a Multi Value Field List, the control you describe "if you add a combo box and ... .... it gives you a nice drop down with check boxes next to each item" is a Multi Value Field control.

There is no equivalent in A2003, or earlier versions, of either an MVF Combo box or listbox, and the data structures behind MVFs are completely different to the standard stuctures you would use in A2003.

First then you would have to change your data tables and replace the hidden MVF table with a join table, 'Normally' you would use a subform to populate this table, and this could be designed to look like a list box with checkboxes, UAs Leigh Purvis has a demonstration on his website here under 'list selections'. This is the closest that you will get to an MVF list box on a form.

Adding an MVF combo is a further step, you would have to effectively unhide/hide a subform (as described above) to simulate the drop down part of a combo box, you would also need an unbound text box to overlay the combo box to display the selected items in a Comma Separated Variable type string, you would need to use code to create and edit this string whenever you need to refresh the subform and when any entries are checked or unchecked on the sub form.

This is not a task to undertake lightly. You should also be aware that while MVF are 'cool' looking, they do have their limitations, not simply because of their complexity but also MVFs in A2007/A2010 are not compatible with any other RDBMS database, if in the future you have to upsize your db to use (say) mySQL, SQL Server or whatever then you will need to a major redesign of your database, effecting tables, queries, forms, reports, macros and code, which would be inevitably a major undertaking.

hth
Go to the top of the page
 
+
theDBguy
post Jun 21 2012, 01:11 PM
Post #9

Access Wiki and Forums Moderator
Posts: 55,856
From: SoCal, USA



Hi Dexter,

It is a good idea to address the person or quote their post so we can determine who you are replying to.

QUOTE (Dexter @ Jun 21 2012, 09:39 AM) *
...I just basically want to duplicate that look and feel and still be able to store the selections per record in the database so I can later add them to a report.

I think we need to understand what you mean by using the selections later on in a report. Your purpose for wanting to duplicate the MVF functionality might affect the solution we could recommend to you.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
pere_de_chipstic...
post Jun 25 2012, 11:50 AM
Post #10

UtterAccess VIP
Posts: 8,581
From: South coast, England



Hi Dexter

I've justed posted a demo version of an MVF combo box equivalent that will work in A2003, A2007 or A2010, it works using a standard Join table not the hidden A2007/2010 MVF field. It will need some redsign of your db to replace the MVF implementation you have at the moment.

See here in the UA Code Archive

hth
Go to the top of the page
 
+
Dexter
post Jul 12 2012, 10:03 AM
Post #11

UtterAccess Guru
Posts: 528



Thank you pere de chipstick! Your posting was really helpful. I did look at your sample databases and this is very close to what I'm trying to do and yes I am looking to create a multi value field. I am looking at the last database and the only difference between this and what I am trying to do is that I want the selections to save to the current record on the form rather than selecting a value (company name) to save it to. Your VBA code is a bit beyond me. I think I understand what is happening but not sure what I would change to make this happen.

Thanks again! I really appreciate your help.
Go to the top of the page
 
+
Dexter
post Jul 12 2012, 12:09 PM
Post #12

UtterAccess Guru
Posts: 528



I'm actually pretty close to getting this to work. I created a test form similar to how my original was set up but when I open the form, all the check boxes for the regions are greyed out. Once I select all or clear all, they are open. I can select individual boxes but when I click okay, I get an error saying, "Application-defined or object-defined error".
Go to the top of the page
 
+
jbred
post Dec 4 2013, 04:09 PM
Post #13

New Member
Posts: 1



Pere-de-chipstick,

Thanks very much for your sample files. The MVF_listbox example provides the exact functionality I need (recording the designated purpose for donations received and recorded on an individual reciept). Often donors make more than one designation on a single donation. For years I've been creating a new field in my receipts table/form for each new fund campaign. I new this was the wrong method, and it requires manual changes to the form, the table and various reports, but I didn't have a workable method for creating a related "designation" table linked by receipt number until I found your solution (this application started as a flat file mailing list in a spreadsheet program, symphony, in the 80's, and I migrated to Access in the mid 90s and added relational features such as a table of receipts). I'm sure it would make you cringe, but it still works well.

I do have a question on the manner in which you create the data source for the subform. I understand the purpose of most of the procedures and feel reasonable comfortable adapting them, but the one you apprently use to create the dynaset: Private Sub Form_Current(), is way outside of my rudimentary knowledge of VBA. Is there a reason why you didn't base the subform on a listbox drawn from a query of the table tblRegions? Will the INSERT and DELETE SQL statements work with this type of list? Did I even properly understand the purpose of this procedure?

On a more prosaic note, I see that the properties sheet for the subform does not have an entry for the record source. All it designates is that the record set type is "dynaset." How does the form know the source of the data? Is it this command: Set Me.SubfrmCoRegions.Form.Recordset = rst2?

Please accept my apologies if these are not appropriate questions or if they should have been posted in a different way. I am a newbie.

I am working in Acess 2003 and have no training as a programmer (I keep trying stuff until it works).

Thanks
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 16th April 2014 - 06:02 AM