Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi-select List Box (A97)

    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 ([img]/forums/images/smilies/cool.gif[/img] 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 ([img]/forums/images/smilies/cool.gif[/img] and have that ID and the classID appended to the tblClassRegistrations table, and thereby showing up in listbox (A).

  2. #2
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-select List Box (A97)

    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

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-select List Box (A97)

    Here

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating class list (A97)

    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??

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating class list (A97)

    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.
    Attached Files Attached Files

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating class list (A97)

    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():

    <font face="Georgia">rst!ClassID = CurrentDb().CreateQueryDef("","<your SQL code>").OpenRecordset!ClassID</font face=georgia>

    Alternatively, you could use the DLookup function:

    <font face="Georgia">rst!ClassID = DLookup("ClassID", "qryClasses", "ClassDate = '" & Me!lstClassDate & "' AND ClassName = '" & Me!cboClassName & "'")</font face=georgia>

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

    Hope this helps.

  7. #7
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating class list (A97)

    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.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating class list (A97)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •