[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-19
21
Medium Priority
?
259 Views
Last Modified: 2014-09-02
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 ?
0
Comment
Question by:upobDaPlaya
  • 8
  • 4
  • 4
  • +2
21 Comments
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 400 total points
ID: 40271546
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
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 200 total points
ID: 40271548
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40271549
You may need to add the count is as a column that is used to populate the listbox.

Kelvin
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40271574
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
 

Author Comment

by:upobDaPlaya
ID: 40271705
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
 
LVL 85
ID: 40272249
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
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 40272328
<<<<<<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
 
LVL 40

Expert Comment

by:PatHartman
ID: 40274255
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
 

Author Comment

by:upobDaPlaya
ID: 40275522
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 700 total points
ID: 40275926
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
 
LVL 40

Expert Comment

by:PatHartman
ID: 40276333
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
 

Author Comment

by:upobDaPlaya
ID: 40282288
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
 
LVL 85
ID: 40282711
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
 

Author Comment

by:upobDaPlaya
ID: 40282865
So scott i like the idea of a main form and sub forms.   Would i place the subforms within each tab/page ?
0
 

Author Comment

by:upobDaPlaya
ID: 40282889
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
 
LVL 40

Assisted Solution

by:PatHartman
PatHartman earned 700 total points
ID: 40282979
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
 

Author Comment

by:upobDaPlaya
ID: 40283266
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
 
LVL 40

Expert Comment

by:PatHartman
ID: 40283668
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
 

Author Comment

by:upobDaPlaya
ID: 40294032
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
 
LVL 85
ID: 40294312
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
 

Author Closing Comment

by:upobDaPlaya
ID: 40299447
Thanks all this was a great lesson on sub-forms and development considerations for MS Access.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

867 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