Append from a multi select list box 2

[JR2913]

How can I append data from a multi select list box (where the multi select parameter is set to Expanded) to a table? I have tried using an append query but this doesn't work.

Scenario is as follows - 3 tables

tblServices
tblAdultData
tblServiceUse

On a form I have a combo box to select a Service from tblServices. There is also a multi select list box of users which enables me to select a number of adult users - but I then need to add the Service ID and the IDs of Users to the tblServiceUse. I want to add the multiple users, each linked with the service ID from the combo box, to the existing table that identifies service use. How is this achieved - a query or VBscript?

Any advice gratefully received!

 

[wemeier]

You would need VBA coding to accomplish this:

Dim db as Database
Dim rst as Recordset
Dim varItem as Variant

Set db = CurrentDB()
Set rst = db.OpenRecordset("tblServiceUse",dbAppendOnly)

For Each varItem in Me.listboxname.SELECTED
rst.AddNew
rst!SelectID = Me.comboboxname
rst!User = Me.listboxname(varItem)
rst.Update
Next varItem

rst.Close
Set db = Nothing

What this does is programatically open the Service Use table for appending data. Then, it walks through all of the rows that have been selected from the listbox (using the Selected collection that automatically comes with each listbox). For each selected user, it adds a record containing the selected user and service ID.

 

[JR2913]

Thanks Wemeier for getting me started.

However, the SELECTED parameter generates a compile error. Removing it allows the loop to be entered, but then I have a problem with recognising the data.

Although the actual service names appear in the combobox and the user names appear in the multi select listbox, I actually want the service and adult IDs to go into the table as the appended data, together with a date which is also selected on the form in a text box.

Can you help to get me any further down the route I want to go?

 

[wemeier]

If you make the RowSource of the listbox the user name AND adult ID (number of columns = 2, you can always hide the ID column by making its width 0&quot you can refer to the ID using the COLUMN method when walking the selected rows.

 

[jebry]

Hi!

Use this loop instead of the one above and you should be fine:

For Each varItem in Me!listboxname.ItemsSelected
rst.AddNew
rst!SelectID = Me!comboboxname
rst!User = Me!listboxname.Column(0, varItem)
rst.Update
Next varItem

The 0 the column method refers to the first column of the list box. If the information you want is in the second column you use 1 etc. Also, I replaced the . with a ! in some places. Conventionally you use the ! to refer to user defined objects and the . to refer to intrinsic objects. In some instances it is programmatically necessary also.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[JR2913]

Jeff/Wemeier

Have tried your solution but keep getting type mismatch - it's obviously not picking up the right data item.

Both combobox and listbox have two columns - the first is the ID which is an autonumber.

For example

The combobox of services has the serviceID and service_name as the data source in the data parameters, but the serviceID column width is 0.
Similarly the listbox for multiselect items.

Both serviceID and AdultID (the autonumbers) should be entered into fields SERVICEID and ADULTID in the table tblServiceUse, both defined as Long Integers. Is it because I am trying to enter autonumbers into fields defined as numbers that the type mismatch is occurring?

The current state of the procedure is:

Private Sub cmdAddUsers_Click()
On Error GoTo Err_cmdAddUsers_Click

Dim db As Database
Dim rst As Recordset
Dim varItem As Variant


Set db = CurrentDb()
Set rst = db.OpenRecordset("tblServiceUse", dbAppendOnly)

For Each varItem In Me.AdultList.ItemsSelected
rst.AddNew
rst!ServiceID = Me.Service
rst!AdultID = Me.AdultList.Column(0, varItem)
rst!Date_started_service = Me.DateServiceStart
rst.Update
Next varItem

rst.Close
Set db = Nothing

Exit_cmdAddUsers_Click:
Exit Sub

Err_cmdAddUsers_Click:
MsgBox Err.Description
Resume Exit_cmdAddUsers_Click

End Sub


Changing the . to ! or vice-versa seems to make no difference. The date item is a field containing the date the user started the service and is obtained from a text box on the form.


Thinking about it, the autonumber can't be the problem as I have set up another form for entering individual adults for specific services using the same principles and that works fine - it must be to do with the .Column setting, I think.

Would be most grateful for some help to get me out of this problem, which has taken hours to get this far!!

 

[JR2913]

Solved this annoying problem by replacing

Dim rst As Recordset

with

Dim rst As dao.Recordset


Works like a dream now!

Thanks guys for your help and advice.

 

[JR2913]

OK guys.......

This worked fine until I split the database into a front end and back end database, where the tables are stored. Now it falls over with "Invalid Operation" when I try to apply the procedure. I guess this has got something to do with where the table is sitting now, so how do I make sure the link is made correctly? Presumably the front end is linking correctly elsewhere as everything else is working fine, so why isn't the program finding the right place to put the data?

This is getting urgent now as implementation is imminent!

Any advice gratefully received!

JR

 

[JR2913]

Solved it now - changed the first few lines to:

Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim varItem As Variant
Dim ans As String

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblServiceuse&quot

Now works, but not sure why!