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

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

Kelvin

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

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...

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.

<<<<<<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

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.

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..

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.

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...

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.

So scott i like the idea of a main form and sub forms.   Would i place the subforms within each tab/page ?

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.

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....

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.

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...

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.

Thanks all this was a great lesson on sub-forms and development considerations for MS Access.