Multi-select List Box (A97)

Topic

New Reply

I am needing to append records to a linking table from a listbox by double-clicking on the row in the listbox. It seems that this should be simple, but I can’t seem to figure it out.

Details: I have a class registration form that I can specify a class, which in turn lists all the individuals who have been registered for that particular class (We’ll call this listbox A). Another listbox ( lists all individuals who can be registered into a class.

The source for (A) comes from a table (tblClassRegistrations) linking a person’s ID to a class ID. I want to be able to double-click on a row in ( and have that ID and the classID appended to the tblClassRegistrations table, and thereby showing up in listbox (A).

Reply | Quote

Replies

I would have created a registration form based on the class table (tblClass?) and a subform based on tblClassRegistrations. In the subform (or even in the tabledefinition for tblClassRegistrations) select the individual to register via a combobox. No VB coding needed.

If for some reason you don’t want to do that, something like this should work:

Assumptions:
The bound column in lstBoxB has the StudentID.
StudentID is defined in the tblClassRegistrations table.
The primary key in tblClassRegistrations is an autonumber (e.g. RegistrationID).
The name of the ClassID control on the form is txtClassID.

Create a DoubleClick event in listbox B having the following code:

Private Sub lstBoxB_DblClick(Cancel As Integer)
on Error goto ErrHdl
dim rst as recordset
set rst=currentdb.openrecordset(“tblClassRegistrations”)
rst.add
rst!ClassID=txtClassID
rst!StudentID=lstBoxB ‘ Assume Student is the bound column
rst.update
rst.close
lstBoxA.Requery ‘ Update the list of reg’ed students
Xit:
exit sub
ErrHdl:
msgbox Err.description
resume Xit
End Sub

Reply | Quote

Here

Reply | Quote

Thanks to Claus and Tom for yourhelp. Modifying the suggestions to fit my application, here is what I came up with:

Private Sub lbxMembers_DblClick(Cancel As Integer)
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(“TblActivitiesReg”)
rst.AddNew
rst!ClassID = “SELECT ClassID FROM qryClasses WHERE ClassDate = ‘” & Me!lstClassDate & _
“‘ AND ClassName = ‘” & Me!cboClassName & “‘;”
rst!ID = lbxMembers
rst.Update
rst.Close
Me!lstParticipants.Requery

The problem is that I am getting a Data Type Conversion error at the SELECT statement above. The fields of both tables (originating and receiving) are Long data types. I attempted to move the SELECT statement to a variable and then place the variable, but that didn’t work either. I tried changing the variable data type to Variant, and got a Data Type Mismatch error (as opposed to the Data Type Conversion error I got earlier).

Any thoughts??

Reply | Quote

Hi Randall,

It seems you are trying to assign a string (the SELECT stmt) to a Long (ClassID).

I had some spare time. So, to help you, I’ve prepared and attached a small sample database for you. It shows you how you can control a class/student registration job using a combobox for class selection and two listboxes displaying students. One listbox has students selected for the class, the other one has students you may select for inclusion in the first list.
Select a class and the listbox at your right hand can be used for adding students to a class and the listbox at your left hand will display students already selected. I’ve included a button to remove students selected, in case you need that.
The sample database has 3 tables (tblClass, tblStudent, and tblClassRegistration) with selfexplaning names. Take a look at the relations.
There are 2 queries both of which depend on the form frmClassRegistration being open. One query has students selected for the class selected on the form, and the other one has students not selected for the class on the form.
I am using multiselect lists.
To select one student: doubleclick the student.
To do multisection of students: 1. press left mouse button while selecting multiple consecutive rows or 2. click ctrl and left mouse button on several individual rows. And then click the register students button.

I hope you will find my sample application (Office 97) helpfull.

Reply | Quote

Randall,

I haven’t checked out Claus’ solution, but I think you’re pretty close with yours.

As Claus points out, you are assigning your SQL string to rst!ClassID. What you want to do is evaluate the SQL query and assign the result of the query to rst!ClassID. To do this you can used CreateQueryDef():

rst!ClassID = CurrentDb().CreateQueryDef(“”,””).OpenRecordset!ClassID

Alternatively, you could use the DLookup function:

rst!ClassID = DLookup(“ClassID”, “qryClasses”, “ClassDate = ‘” & Me!lstClassDate & “‘ AND ClassName = ‘” & Me!cboClassName & “‘”)

The three arguments of DLookup() are the SELECT, FROM, and WHERE clauses (without the keywords and closing semicolon), respectively.

Hope this helps.

Reply | Quote

Sorry to disagree Tom,

There is no reason to look up the ClassID. It should be the bound column of the Class combobox.. Take a look at my sample db, and I’am sure you’ll agree.

Reply | Quote

You’re right, Claus. As long as the form has a control (combo box or list box) where the class is selected (and ClassID is the bound value), as is the case with your sample db. (Randall, it seems like you need to have this class-selection control on your form, right?)

I was giving Randall a possible patch to his existing code.

Reply | Quote