Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

multiple values - list box save-edit

Posted on 2013-12-30
6
Medium Priority
?
824 Views
Last Modified: 2014-01-15
Hi Experts,
I am working on an access form to save multiple values(id's)  from a list box to the database field. I have to use those values in another form(may be in the same form) to edit... So my question is, do we have any other better options to do this type of functions(adding multiple value to db and retrieve it update later) in Vba and Access forms..Please help..
Thanks
0
Comment
Question by:gtmathewDallas
  • 3
  • 2
6 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39746455
I would recommend against using multi-valued fields.  Personally I believe they are a mistake and should be avoided.

What are you trying to do with the values from the list?  Normally, if you are going to use a list that allows you to select multiple items from the list, you are going to need a table that stores those in a table with a one-to-many relationship to the main record that the form is tied to.  

Unfortunately, that also means that the list will not be bound to the main record and you will have to write those selected items to a table when you save the record, and if you come back to that record (Current Event), you will need to read from your one-to-many table and highlight (SELECT) those items in the list again.

To write the values to a table, you might use code similar to:

Dim varItem as variant

for each varItem in me.lstYourListName.itemsselected

    strSQL = "INSERT INTO someTable (ID, ListID) " _
                & "Values (" & me.txt_ID & ", " & me.listYourListName.column(0, varItem) & ")"
    currentdb.execute strsql, dbfailonerror

Next

written on my iPad, so there might be some syntax errors.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 39746912
I also do not use multi-value fields.  They are really more trouble than they are worth.  Create a normal 1-many relationship.  Use a subform for the many-side data.  Using a subform is essentially a no-code solution.  You can make the subform innocuous so it blends in with the main form if you wanted more of a list effect.

The only benefit of using a multi-value field is the nice display options.  Everything else is a problem.
0
 

Author Comment

by:gtmathewDallas
ID: 39754378
Thanks  fyed  and PatHartman,
My requirement is to assign multiple members(users) to different rooms. I have a form and table for room details and from the same form i have to assign the members to it. The form is user defined and not linked to any data/table records.
I was thinking to add two list boxes to the same form - one to select the users and other to show the selected users and then to submit. I am not sure this method is right or wrong but from the user end I think it will be easy for them.
I am not sure how the other method (subform) works.. to save/edit each emplooyee shall we need to go through each subforms? Please Help.
Thanks
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 40

Expert Comment

by:PatHartman
ID: 39754770
The form is user defined and not linked to any data/table records.
Are you saying that you didn't create the form but the users are allowed to create their own forms in a shared database?  -- Big problem waiting to happen.

If this is a one to many relationship, the room is actually stored in the member record.  If this is a many-to-many relationship, it needs a third table to implement.

I've attached an example of a many-to-many relationship and how to manipulate it from either side.
ManyToManyAXP.mdb
0
 

Author Comment

by:gtmathewDallas
ID: 39765808
Sorry for the dealy - I was off from work.. If i use listbox or combobox - whats the best way to store the data to the table of the selected items from it? save multiple selected items in to a field or any other way to store? May be the answer of this question will give me the solution. Please help.
Thanks
0
 
LVL 40

Accepted Solution

by:
PatHartman earned 1500 total points
ID: 39766240
The example I posted stores the data directly to the underlying table.  There is no code involved.  Using a multi-select listbox will require code to loop through the selected items and add them to a table.  But it's not that simple because first you have to delete the previous items since there is no way to tell what was there before unless you go item by item.  It is simply easer to delete the previous values and insert the new ones.  You also have to populate the list box in the current event of each record.  So the choice is yours - code (listbox) or no code (subform).  The combo is not an option for this task.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

927 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