How do I capture an associated Count field in a list box

I have a unbound 7 page tabbed form and within one of the page/tabs I have a list box where the user can select multiple items.  Using the ListBox Selected Item property I loop thru the listbox control to capture each  item selected and place in a store table.  

My problem is it turns out each selected item also has an associated count field.  Thus, if the user selects from the drop-down field an item called "Cars", I should also be capturing the number of Cars.  If the listbox is just that..a selection, how would I allow someone to also input a count.  

Note since the form is unbound I assume I can not use a temp table for this particular page/tab as the control source.  In addition it is very important the person see all the items selected (that is why I am not using a combo box).  Also I do not want to create separate text boxes for each item.  Currently there are 10 potential items that can be selected, but it will grow quickly.

Any suggestions ?
upobDaPlayaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eric ShermanAccountant/DeveloperCommented:
Is the number of Cars a field in your table somewhere??  If so, you should have two fields in your ListBox (Item, Qty).  When you write the Item to your table you can also write the Qty to your table.

ET
0
Kelvin SparksCommented:
When you select the item, you can also select others.

If you're using .itemselected for instance, you can try .itemselected.Column(1) which should give you the second column (the colu,mns are based on a zero based range - i.e the first column is Column(0) for instance).


Kelvin
0
Kelvin SparksCommented:
You may need to add the count is as a column that is used to populate the listbox.

Kelvin
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Eric ShermanAccountant/DeveloperCommented:
As mentioned in my earlier post ... You will need to have the Item and Qty as fields in your listbox. Then in code you would write those values to your table when selected.

This is how I do that ....

Dim varItem
Dim rstYourTbl As Recordset

Set rstYourTbl = CurrentDb.OpenRecordset("YourTableName", dbOpenDynaset)

For Each varItem In Me.ListMyListBox.ItemsSelected

    rstYourTbl.AddNew
    rstYourTbl!Item_Name = Me.ListMyListBox.Column(0, varItem)
    rstYourTbl!Item_Count = Me.ListMyListBox.Column(1, varItem)  
    rstYourTbl.Update
   
Next varItem


ET
0
upobDaPlayaAuthor Commented:
Sorry, I realize now my question may not have been clear.  The issue is not writing multiple columns to the table, but how to allow the user to input a number value for each item select.  As an example for a lisbox that displays a field called Inventory, the person could select Cars, Trucks, and RV's, but I also need to know to allow them to input the number of Cars, Trucks, and RV's.  Once I have this information I will then write it to a table...
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You cannot input values into a Listbox, but you can into a Subform. Could you instead use a Subform based on the same data that your Listbox contains, and include a field that would allow the user to enter the Count. You could then write that value back to the database.
0
Eric ShermanAccountant/DeveloperCommented:
<<<<<<As an example for a lisbox that displays a field called Inventory, the person could select Cars, Trucks, and RV's, but I also need to know to allow them to input the number of Cars, Trucks, and RV's.  Once I have this information I will then write it to a table... >>>>>

You would have to allow the user to select Cars from your list box then open a small input prompt form so the user can enter the count.  Minimize the input form and then write the Item from your list box and count from your input form to your table.

ET
0
PatHartmanCommented:
This is pretty much a no code requirement when using bound forms.  Why are you using unbound forms?  The whole point of using a RAD (rapid application development) tool such as Access is to make use of the features that make developing with it fast.  If you are not going to use the RAD features, you are better off developing in some other environment where distribution would be easier.
0
upobDaPlayaAuthor Commented:
Pat it is a tabbed form..each tab essentially represents a table..A form can only be bound to one table...I have a lot of different information that needs to be recorded..As an example on the first tab I have volunteer info such as name, address, all previous volunteer organizations they worked at and approximate hours, second tab is Grant Information, 3rd tab is Project advocacy, 4th tab is Transp/Cars/Vans, etc.

Hope this helps...I'm open to your sugeestions..
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This is where Subforms in Access really shine ...

If all that "sub data" is related to a Volunteer, then you'd have a MainForm with the Volunteer info, a Subform for PreviousOrganizations, one for Grant data, one for ProjectAdvocay data, one for Transportation data, and so on. Setting the Master/Child link of the Subform control to the correct fields allows Access to save this data automatically, with no interaction from you. Also, your requirement to allow the user to input data for the "Number of Cars" value is a no-code solution - just add the relevant field to the form's datasource, add a control, and let Access do the rest.

I'm not entirely opposed to unbound forms, but it is rare that they are used effectively - and as we've said in the past, if you must use unbound forms, then Access is probably not the right tool to be using for the job.

And finally - while the concept of "put all the data on one form" seems like a good idea, in many (if not most) cases this turns into an overwhelming mass of data, that ends up confusing users, which has the exact opposite effect intended. In most cases, a single form for a specific "entity" is the better solution. Of course, this does not apply to all situations, and it may not be valid for your own needs.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Also, having too many controls and subforms on one form will make it "heavy" and possibly slow to load.  An alternative is to give the illusion of a tabbed form but not actually load the subform until a particular tab is selected.  Since the user can't see all the subforms in a tab control, they aren't aware that they are not actually populated until you click on a tab.
0
upobDaPlayaAuthor Commented:
All of the above points are well made, but if I use 7 separate Bound main forms/subforms and abandon the 7 tabbed/pages within 1 Un bound form won't I run into locking issues if 2 or 3 people or more are inputting at the same time...
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access is fairly intelligent in regard to locks. Multiple users can enter records concurrently with no troubles. Locks come into play when users EDIT record. For example, if 2 users try to edit the SAME record you'll run into lock issues. That's really no different than with unbound forms - except the first person to save their changes "loses", since the second person's data will overwrite the first.

If you think record locks are going to be an issue, then you can implement a system that "tags" a record when a user begins to edit. Then include code on each form that checks for the "tag" before you allow users to edit a record. I've done this in the past, and it works okay. There are some pitfalls, of course - for example, if a user begins edits and then abruptly quits the system (i.e. powers down, loses network connection, etc) then you're left with a hanging tag, and you'd have to include some method to clear those. You could do it directly, of course, by running a query against the table, but you don't really want users doing that unnecessarily.
0
upobDaPlayaAuthor Commented:
So scott i like the idea of a main form and sub forms.   Would i place the subforms within each tab/page ?
0
upobDaPlayaAuthor Commented:
Or would i have 7 separate main forms with subforms.  Once they got done with lets say inputting prior organations they would click on next to get to the next main form/subform.  I suppose i could have 7 subforms within 1 main form ?  Not sure what would considered best practices.
0
PatHartmanCommented:
This isn't a best practices issue.  It is a what will work best for the situation and that statement encompasses how the client will interact with the form(s) and how "heavy" the form will be since you don't want performance issues.  You can use one main form with a tab control and on each tab have a separate subform.  This works well if you only have a few forms.  With more than about 4 subforms, you may run into sluggishness.  The solution to that is to switch to popup forms or to change the ControlSource of the subform control to be a different subform.  You can still make the main form look like a tabbed interface the only difference is that clicking on a tab opens a separate form instead of making a subform visible.  With popups, you will need code in the BeforeInsert event to populate the foreign key since you won't have a master/child link to do that for you.
0
upobDaPlayaAuthor Commented:
i like the main form tabbed interface approach.  If i go this route of clicking on a tab and a main form opens how do i keep the tabbed form easily visible....
0
PatHartmanCommented:
I actually hide the "calling" form (me.Visible = False immediately after the DoCmd.OpenForm ...) unless there is something on the main form that I think needs to be visible.  I also don't like the "new" tabbed documents format that is the standard for A2007 and newer.  I still choose the Overlapping windows option because when I allow multiple open windows, it is because I want the user to see them all.

Access remembers where the window was when you saved it or you can hard code coordinates to move it to a specific place when you open it.  So, unless I want to position a pop up to a specific location, I set the AutoCenter and AutoResize both to Yes.
0
upobDaPlayaAuthor Commented:
Note the most rows that would be called in total for all subforms across the multiple tabs is 300 rows and no subform would have more than 2 fields with a field length max of 50 and some fields are numeric.

Thus, I assume I should not be concerned about sluggishness ?  However, I would like to know how to as Pat mentioned above is not to load the subform until a tab is selected.  How do I do that..

I am almost done and I agree this is a much more efficient approach and more importantly manageable for future changes...
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
300 rows is a very minimal amount of data, but much of the concept of performance depends on other things like Indexing, Recordsource, your network, etc etc. In general, I wouldn't think you'd have much trouble with 300 or so rows, however.
However, I would like to know how to as Pat mentioned above is not to load the subform until a tab is selected.
Don't set a SourceObject for the SubformControl, then set that SourceObject when the user clicks onto the tab:

Me.SubformControl.SourceObject = "fMyFormName"

I always "unset" the SourceObjects when the form is closed:

Me.SuformControlSourceObject = ""

I've had issues in the past where Access "remembered" the SourceObject after I closed the object, so I always make it a habit to explicitly clear that value.
0
upobDaPlayaAuthor Commented:
Thanks all this was a great lesson on sub-forms and development considerations for MS Access.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.