Multiselect Listbox add records

[samonwalkabout]

I have a form with 2 listboxes. i highlight a name in the 1st listbox and click add, a record is added to a many-many link table and the 2nd list box is requeried and reflects this change. All works well but i want to use a multi select box and a select all/add all button. I have read many posts on using multi boxes to pass info to sql but they seem to be realted to searching/ printing reports. Heres the code im using to add the new records.

The table is a simple link table with ref id, eventid, trimid, schid

TrimID links one to many to a table for trimID with staff names

EventID links one to many to a table for EventID with event details.

The code i am using to add one record to this table based on the listbox value is

Dim rst As DAO.Recordset

If Not IsNull(Me.List0) Then
Set rst = CurrentDb.OpenRecordset("tblschedule", dbOpenDynaset)


rst.AddNew
rst.Fields("TrimID") = Me.List0
rst.Fields("EventID") = Me.event
rst.Update


Me.List0.SetFocus
Me.List2.Requery
Else
MsgBox "Please enter a name", vbCritical
Me.List0.SetFocus
End If

ME.Event is a combobox of events

Any ideas on the code needed to add a mutliselection of TrimID's.

Thanks

 

[dcx693]

This post: Multi Select List Box --> Query has sample code on how to use a loop to get at all the selected items in a multiselect list box.

 

[samonwalkabout]

Thanks, i was looking at that earlyer today, there is a mixture of methods there,

Just to clarify the solution -- i will be taking the multislection into a sql qry and then using an update qry bases on this to add data to the record set, then deleting the qry??

 

[dcx693]

There's no need to get into SQL, unless you want to. Since the code to go through the multiselect items is a loop, you just need to put that loop "around" your existing code to add items to "tblschedule".

For moving large amounts of data, it's best to use some type of update query, but if that isn't the case, just use your existing code and see if the speed is acceptable.

 

[samonwalkabout]

I thought it must be simplier than that, but it still seems to be passing null value into the code. He's what ui have.


Dim rst As DAO.Recordset
Dim vItm As Variant

For Each vItm In Me!List0.ItemsSelected

If Not IsNull(Me.List0) Then
Set rst = CurrentDb.OpenRecordset("tblschedule", dbOpenDynaset)


rst.AddNew
rst.Fields("TrimID") = Me.List0
rst.Fields("EventID") = Me.event
rst.Update


Me.List0.SetFocus
Me.List2.Requery
Else
MsgBox "Null values selected", vbCritical
Me.List0.SetFocus

End If
Next

End Sub

But it does give me the msgbox the right number of time (i.e 3 times if 3 values selected) so it is looping properly

 

[dcx693]

The loop looks fine. You are not referring to each item within the loop properly.

You've got this statement
Dim vItm As Variant
and you've got
For Each vItm In Me!List0.ItemsSelected
but you never use each vItem in your code.

Instead, you are still referring to:
Me.List0

You need something in there that refers to:
Me.List0.ItemData(vItem)

 

[samonwalkabout]

Fantastic, works great! I guess i can delete records in the same way too! Is there an easy way of checking for duplicate entrys without disrupting the loop. ie it will add all the items except the duplicates (the code wont error just skip offending selections)

thanks very much

 

[dcx693]

Since your recordset type is dynamic, you can use the rst.FindFirst command to check for duplicates. Check it out in the Access help system.

 

[samonwalkabout]

All i seem to be able to get is errors


Have tried load of combinations but think like before im missing one key statement or line. this code gives data type mismatch and i dont think that it is looking at the values in the 2 listboxes but not sure. Any ideas

Dim rst As DAO.Recordset
Dim vItm As Variant
Set rst = CurrentDb.OpenRecordset("tblschedule", dbOpenDynaset)



For Each vItm In Me!List0.ItemsSelected


If Not Me.List0.ItemData(vItm) Then

rst.FindFirst "[TrimID]='" & Me.List2 & "'"

If Not rst.NoMatch Then

rst.AddNew
rst.Fields("TrimID") = Me.List0.ItemData(vItm)


rst.Fields("EventID") = Me.event
rst.Update


Me.List0.SetFocus
Me.List2.Requery

Else

MsgBox "Null values selected", vbCritical
Me.List0.SetFocus

End If
Else
MsgBox "Matching Record Already exists"
End If


Next vItm

End Sub

 

[dcx693]

I'll give you a hint. Take a look at this line.
rst.FindFirst "[TrimID]='" & Me.List2 & "'"

It's the same issue that you had before when you said you were coming up with only Nulls.

 

[Pat Hartman]

May I suggest an alternative solution. Since most of the time the user will type a valid key, it is inefficient to look for that key value for every record since most of the time, a duplicate will not be found. It is better to add the record and then trap the error for the odd case when there IS an error. Here is a function (it is a function because I use it from several places and pass in the control as an argument) that adds a row for each item selected in the listbox. In my case, I choose to ignore the dups since they are not added to the table anyway. You would need to add code in the case statement to display a message box or whatever.

Public Function CreateAttendanceRecords(ctlRef As ListBox) As String
On Error GoTo Err_CreateAttendanceRecords

    Dim i As Variant
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qd As DAO.QueryDef
    
    Set dbs = CurrentDb
    Set qd = dbs.QueryDefs!qAttendance
    Set rst = qd.OpenRecordset

    For Each i In ctlRef.ItemsSelected
        rst.AddNew
        rst!MailingListID = ctlRef.ItemData(i)
        rst!AttendanceDate = Me.txtToday
        rst.Update
    Next i
    Set rst = Nothing
    Set qd = Nothing
    CreateAttendanceRecords = "Records Created"
    
Exit_CreateAttendanceRecords:
    Exit Function

Err_CreateAttendanceRecords:
    Select Case Err.Number
        Case 3022     'ignore duplicate keys
            Resume Next
        Case Else
            MsgBox Err.Number & "-" & Err.Description
            Resume Exit_CreateAttendanceRecords
    End Select
    
End Function

 

[samonwalkabout]

Thanks that seems to work, but i think the problem im tring to overcome is not vaildation or duplicate key values but a duplicate TrimID (a staff member) being added to an eventID. The same person cant be add twice. The more i think about if i could get the names to dissaper from the left listbox as they were added over to the right aswell as checking for duplicates it would be good.

Do i have to build the frist box on a qry based on part of the second?

 

[dcx693]

When designing forms with two listboxes where you "move" some from one to the other, generally I would only design them so that they are exclusive (meaning that an item is in one list or the other).

 

[samonwalkabout]

Do you mean you would design them to look and function as they were exclusive or that you DB structer would accomplish this?

Is there anyway i can get them to look like data is "moving" between the 2 listboxes that you know of?

 

[dcx693]

Having two listboxes where items move between them is a "classic" UI set up. Access doesn't have a particularly simple built-in way to handle it.

There are a bunch of ways to handle it, some very simple if your database is single-user as opposed to multi-user.

Most of my databases are single user, so I use temporary tables and update queries for it. It works like this:

1. let's say I'm working with employees
2. if I want to select some employees from a list, I copy the list of employees to a temp table with a yes/no field
3. all of those employees are non-selected and get put into the "left" listbox
4. as employees are selected, they get "moved" over to the "right" listbox. What's really happening behind the scenes is that they're being flipped from "yes" to "no" in the temp table and the listboxes are just being requeried.
5. when the selecting is finished, I can now run a query using a WHERE clause that only shows employees based on whether they were "yes" or "no" in the temp table.
6. the original data tables are untouched. The temp tables can be design to either be blanked out or retain their values in between times when the form is opened. It's up to you.
   [/list=1]

 

[samonwalkabout]

It will be a small user base and probably on;y one at a time will be working in the DB so i think im going to give it a go. Couple of questions.

-- Im currantly copying the record directly into another table, can i continue doing this or is it better practice to create another temp and once the user has finished adding append to the orginal table.


-- Should i "make table" a new temp table every time or just "update" the old temp one so all the records filter visable again?

-- would the other method "rst.FindFirst " look at the contents of the destination list box to compare or the contents of the table?

Thanks

 

[dcx693]

If you need to "store" the selections for use later on, then you can do either. I use temp tables because the selections themselves have nothing to do with the original data. It's just a user selection.

I just update the old temp table. I figure it's faster.

On the rst.FindFirst, it really depends where you need to look.

 

[samonwalkabout]

Well the 2nd listbox (right hand) has a source of another link table, combining trimID's (people) with eventID (events) the listbox is filtered on the event, so i would like to ensure there are no duplicate TRIMID on a specfic EventID but not look for duplicate TRIMID in the whole link table record set. so

rst.FindFirst "[TrimID]='" & Me.List2 & "'" was meant to do that.

Im think the above compares trimID in question (the varitant im tring to "move" against exisitng records in the List2(the right hand List box) but alas it seems to think it dealing with null values.

Any clues??

 

[dcx693]

If this is a single-select listbox, you'll get a Null value if no line of the list box is selected.

If this is a multi-select listbox, you'll get a Null value if you try to refer to the listbox itself like:
Me.listbox0
instead of referring to a particular line of the listbox like this:
Me.listbox0.ItemData(2)

 

[samonwalkabout]

I must be getting close but i still get an error that vtlm isnt regonised but if i put a value in its place it gives a error on the findfirst line itself. "unknown function"


i have

Dim rst As DAO.Recordset
Dim vtlm As Variant
Set rst = CurrentDb.OpenRecordset("tblschedule", dbOpenDynaset)

For Each vtlm In Me!List0.ItemsSelected

If Not Me.List0.ItemData(vtlm) Then

Criteria = "Me.List0.ItemData(vtlm) = '" & Me.List2.ItemData(vtlm) & "'"

''''''Criteria = "Me.List0.ItemData(vtlm) = '" & Me.List2.ItemData(1) & "'" 'doesnt work

'''''Criteria = "Me.List0.ItemData(1) = '" & Me.List2.ItemData(1) & "'" 'doesnt work


rst.FindFirst Criteria


If Not rst.NoMatch Then

rst.addnew
rst.Fields("TrimID") = Me.List0.ItemData(vtlm)


rst.Fields("EventID") = Me.event
rst.Update


Me.List0.SetFocus
Me.List2.Requery

Else

MsgBox "Null values selected", vbCritical
Me.List0.SetFocus

End If
Else
MsgBox "Matching Record Already exists"
End If