Multi Select List Box - Update Table

[Guest]

I have a form with list box = ListEmployees based on a query (qryCrew).

I want the user to be able to select multiple employees, which it does, but
then continue selecting on the form from 2 other Combo boxes (ComboPCDate ;
date field, and ComboTopic ; text choice of topics). I then want the choices
to update the tblPersonalContact table = fields Employee, PCDate and Topic,
so that if 3 employees were selected for a particular date and topic, then 3
new records would be created.

Thanks in advance!

Patty

 

[Guest]

As to using the Multi Select List Box, you will need to look in VBA Help and
read up on the ItemsSelected property. There is a good example there on how
to loop through it.

As to creating the new records, the question is whether it is the table or
query your form is bound to. How you create the new records will depend on
whether it is the bound recordset.

 

[Guest]

Presently the form is bound to tblPersonalContact, but I'll change it to
whatever I need to. Thanks.

 

[Guest]

Since you are wanting to add records to the bound recordset, the easiest way
to do that would be to automate the process that a user would have to do by
hand to enter the new records. Basically, you would use the ItemsSelected
property, and the example is shows to loop throught the collection, and add a
record for each selected new employee. This example assumes, per your post,
you are adding new employees. If you want to update records for existing
employees, there would be additional steps. You will need to have 3 text
boxes bound to the fields you want to update rather than using the list box
and combo boxes as bound controls. That is because as soon as yo go to a new
record, they will loose their current values. The text boxes do not have to
be visible. I would suggest you put this code in the Click event of a
command button.

Dim ctl As Control
Dim varItm As Variant

Set ctl = Me.ListEmployees
For Each varItm In ctl.ItemsSelected
DoCmd.GoToRecord acNewRec
Me.txtEmployee = ctl.ItemData(varItm)
Me.txtPCDate = Me.ComboPCDate
Me.txtTopic = Me.ComboTopic
Next varItm
Set ctl = Nothing

 

[Guest]

I followed your example and all the text boxes update on the form except for
the txtEmployee - the one based on the Multi Select List Box. Also, how
would I go about updating the table? Below is the code as used:

Private Sub Command56_Click()
Dim ctl As Control
Dim varItm As Variant

Set ctl = Me.ListEmployees
For Each varItm In ctl.ItemsSelected

DoCmd.GoToRecord , , acNewRec
Me.txtEmployee = ctl.ItemData(varItm)
Me.txtPCDate = Me.comboPCDate
Me.txtTopics = Me.ComboTopic
Me.txtDuration = Me.ComboDuration
Me.txtComments = Me.Comments
Me.txtEmployee = Me.Employee
'Debug.Print ctl.ItemData(varItm)

Next varItm
Set ctl = Nothing

End Sub

 

[Guest]

Why the employee is not updating is a puzzlement. Aren't the values in the
List Box the value you want in txtEmployee?
I would put a breakpoint on this line:
Me.txtEmployee = ctl.ItemData(varItm)
and see what the value in ctl.ItemDate(varItm) is

As to updating your table, if the controls in your code are bound to the
fields in the table, then it should update automatically. If you are using a
bound form, every time you move off the current record, it is updated in the
table. That is happening at this line:
DoCmd.GoToRecord , , acNewRec

If you are using an unbound form, let me know, and I can show you the code
to update directly to a table.

 

[Guest]

OK - I changed the Multi Select to Simple; it was on Extended, and now all
records update EXCEPT for the employee field. If 3 employees were chosen, 3
records are updated with the same date, topic, duration and comments, but the
Employee field is blank. The breakpoint displayed no results. Any other
ideas? So far your help has been wonderful.

 

[Guest]

Thanks for all your help!! I had the column on ListEmployees set to the
wrong number. It works great now.

 

[Guest]

Oh Good. Had me thinking I had lost my mind!
But then, I thought, well had I lost my mind, I would not be able to think
I'd lost my mind