Update multi value listbox fields from subform

[Stephenson]

Hello all,

I have a form JobServices that has a multi value listbox, lboServiceChoice. On this form I also have a subform, JobServicesSub that is there to add new services if they are not already present in the multi value listbox. In JobServiceSub I have a textbox, tboAddService and a button, cmdAddService. I would like to be able to put a new service name into tboAddService and press cmdAddService and have that new service pop up in the list of services in lboServiceChoice already checked.



I would like it to show inside the form checked so the user knows there choice has been added. They possibly could add more than just the one service while using the form.

I have tried using,

Private Sub cmdNewService_Click()    
    DoCmd.GoToRecord , , acNext
    Forms![JobServices].Requery
End Sub

This is not doing the trick. There must be some way to actually get lboServiceChoice to requery that I'm not aware of.

Please let me know if you have any suggestions for me to try.

[June7]

Me.lboServiceChoice.Requery

Not understanding data structure and form/subform setup. What table is JobServices form bound to? What table is the RowSource for lboServiceChoice?

[Stephenson]

Thanks June7.

I was able to get my form, JobServices to update using my button in JobServicesSub with,

Private Sub cmdNewService_Click()    
    DoCmd.GoToRecord , , acNext
    Forms![JobServices]![lboServiceChoice].Requery
End Sub

This adds the new value from the subforms textbox to the parents multi value listbox. It is however unchecked. If there isn't a way to make it come up automatically checked I can live with it. I just hate for the user to have to select something twice.

The form JobService is bound to my JobBudget table using

SELECT JobBudget.*, ServiceChoice.Value FROM JobBudget;

My subform JobServiceSub has table Service as it's record source. I've used the following for lboServiceChoice

SELECT [Service].[ID], [Service].[Service] FROM Service ORDER BY [ID];

This is what I'm slowly attempting to do. The user will use this to setup bids for new jobs and print proposals.

1. First is form, JobNamer. This lets the user name the new job or choose a job that they've already been working on. I have form JobNamer connected to table Jobs.

2. Form JobService will have the user select all the services the new job will include in the bid. The title of the form shows the new job name from form JobNamer so they know they're in the right place.

3. Next will be form BudgetBuilder. Here the Services will show one at a time in the order they are in lboServiceChoice. The user will select the pieces of equipment and labor type that will be used and the duration they will be needed as well as any outside resources. There will be subtotals to let them know where the budget stands. The user will be able to press a button to bring up the next service to fill in its details. This last form is still all in my head, but I'm excited to try to get it to work. I'm not sure where or how to record this to a table.

My concern is table layout. I know I'll need to make adjustments to get this to flow.

[June7]

I NEVER use multi-value fields. For the item to show checked, have to save the value to the multi-value field. Programmatically saving value to multi-value field is a bit complicated. Requires VBA to open and manipulate recordsets.