Multiselect listbox how to store in table

[Trevor]

I have a muli-select listbox [Bill choice] and I would like to store the values selected in a table [VMSU-IL].Billfor. So far I have attempted to store the Items selected as a string but I haven't had much luck running update querys so thats as far as I have gotten, here is my code.

and help on how to store values selected in a table would be a great help, maybe Im going about it the wrong way, this is my first attemp at a multi select listbox, I would rather use a list box then a subform, because it is the "road less travled" and I would just like to learn how to do it


Dim db As DAO.Database
Dim varItem As Variant
Dim strCriteria As String
Set db = CurrentDb()
For Each varItem In Me![Bill List].ItemsSelected
strCriteria = strCriteria & ",'" & Me![Bill List].ItemData(varItem) & "'"
Next varItem

[Trevor]

I have pass the multi select listbox value(s) to a string and thested it by passing the string to a text box, ( yes I do get an output) and am now trying an update query with the string, and need help, here is my revised code, I am trying up update table VMSU-ILT field [BillingFor] were IDNumber = The Form IDNumber (IDnumber is a letter and # string)

Dim db As DAO.Database
Dim varItem As Variant
Dim strCriteria As String
Set db = CurrentDb()
For Each varItem In Me![Bill List].ItemsSelected
strCriteria = strCriteria & ",'" & Me![Bill List].ItemData(varItem) & "'"
Next varItem
strSql = "UPDATE VMSU-ILT SET BillingFor = strCriteria & WHERE " _
& " [IDNumber]= '" & Me.[IDNumber] & "';"

[Trevor]

I just looked at my table, and there isn't any info for it to update Ie no IDnumber untill the record is submitted, so is there away without a hidden textbox to pass a value to a field in a db for the current record beeing submitted?

[OBP]

Trevor, are you sure that you actually want to store the choices (assuming more than one choice?) in a text string.
It would be more normal to have a table with the BillID and Choice for each of the choices.
If you just wish to store the Text string that you have built in a table that is not Open as a Form then you should open a Recordset based on the Table in which you want to store the data.
Or why not Base your form where you make the Selection on the Table and just store the Text string in it's approprate field?

[Trevor]

nice suggestion, thanks,
What I have is an Invoice for state office db and it contains a TO(training Order) # witch is used nation wide for each event, and the billchoices are all the things that the state being billed can be billed for, I have since yesterday modified or added to what I want to do, I want a way to be able to multi select the billing choices, be able to store those choices , and is it possable to ie for each Itemselected create a lable on the oposite (ie; left side) of the for for each Item selected and a textbox to the right of each selected item for an amout for each Item selected. but the question then is how would I store the programicly added lables and textbox values since the multiselect list has a table/query for a row source that can grow or shink.
Trevor

[Trevor]

and haveing all the selectable items in bill choice have its own table, would be a 1 many or many to 1( Im not good and decifering 1 to many or many to 1 relationships(im learning as I go) and this has to be maintainable by end users that are not very computer savy.
and having each selected Item in bill choice have is own table I wouldn't know how to set that up mnd then pull it back up , I am using 1 screen far entry and a seperate screen for query( I removed the defalt navigation and added my own buttons to limit end users error

[Trevor]

I have decied to 86 the multiselect box and go with a sub form, thanks for everyones advice, but now I have questions on how to write code for subform textboxs, I have in my subform [bill choice] ,[ amount to pay], [Quantity] , [Total] (total I would like to be [quantity] * [Amount to pay]
but I cant write code for subform textboxes within the subform , so how would I do it?, I would like the event to take place after [Amount to pay] updates.
my subform is [Bill select choice] and all my txtboxes I have listed above are on subform
I also have a textbox to add all the amounts up in amount to pay and it doesn't seem to be working ,the textbox to display the amount in the textbox is [subformtotal] with a controle source of =(Sum([Amount To Pay])) and a text box on my main form to display that totle with a control soucre of =[bill Select Subform].Form!subformTotal , witch doesn't display an #Error or #name it just stays blank so I assume the problem is with my [subformtotal].
I need help please, this is my first subform attemp

[OBP]

Trevor, it would be much easier for us to help you if you posted a Zipped copy of your database as an attachment to your post using the "Go Advanced" and "Manage Attachments" Buttons.

[Trevor]

my DB is attached, not even though I took out the record selector it still scrolls through records w/ mouse wheel, I'll fix that part later
I've tried attaching my DB but the files size is 2.4MB unzipped and 44k zipped and that w/o graphics and no table entries except for Lookup tables it keeps failing on upload so i'll keep trying to attach it. can you stil attempt to help with the info provided already/