Form with "pick box" for append query

[terrytek]

I am trying to set up a form to append class assignment records to a tblClassAssignment (Assignment ID (PK), StudentID, ClassID, AcademicYr). I have a cbo on form to choose the class, and another to choose the academic year. I want to have the list of current students drop down for the data entry person to pick from, but I would also like a text box next to that that lists the students picked (like the boxes one uses to pick fields in the Combo Box Wizard, for example; see attached photo) so that the data person can see which students they have already added to the class. (Does that type of box have an "official" name?)

(1) Is this possible?
(2) AssignmentID is an AutoNumber field, so Access will number that field without my including it in the append, correct?
(3) Should each record be appended as each student is chosen (using code behind the > button that copies the name over to the text box) , or is there a way to append the records all at once?

I am guessing I will not be able to do this in Access' query design view

Thanks!

 

[jdraw]

What tables do you have? How are they related?

These free data models may help.

 

[terrytek]

What tables do you have? How are they related?

These free data models may help.

Relevant tables/queries to the form I'm trying to make:
qryStudentsExtended (StudentID, StudentName, many other fields) on tblStudents (StudentID (PK))

tblAcademicYr (AcademicYr (PK), StartDate, EndDate)
tblClasses (ClassID (PK), Class)
tblClassAssignment (AssignmentID (PK), StudentID (FK), ClassID (FK), AcademicYr (FK))

qryStudentsExtended.StudentName is a field that concatenates the students' first and last names.

tblStudents.StudentID, tblClasses.ClassID and tblClassAssignment.AssignmentID are all Access auto-number fields.

 

[sneuberg]

For the listbox that shows the students assigned to a course I suggest you start by creating a query in the query designer of the tblClassAssignment and related tables using the combo boxes on the form as criteria. If you have a later version of Access the Intellisense will help you build your form references. Just type Forms! in the criteria and you should get a drop down of the forms. It sometimes helps if you have the form open and you certainly want the form open and something selected in the combo boxes when you test your query. Once you get that working I suggest just using the wizard to create your listbox and give it the query you just made as the row source. It usually gets it right. The listbox will have to be requeried in any event that changes its contents.

For the other listbox you will want a Row Source query that displays the students are not already assigned. This is called an unmatched query and there is a query wizard that gets it most of the time. So I'd give it a shot. Then create your listbox with that query as a Row Source. This llstbox will also need to be requeried by any event that changes the contents. You will want to change this listbox to a multiselect. I think that property is in the Other tab.

To assign the students selected in this listbox you will need to loop through the selected items and insert records into the tblClassAssignment table. This Web page has some code that shows looping through a listbox. I suggest giving it a look before looking at PBaldy's page which covers one way of inserting the records. Another way is to use the INSERT INTO statement. If you go that route you will need to know how to concatenate in the variables which is discussed here.

 

[terrytek]

For the listbox that shows the students assigned to a course I suggest you start by creating a query in the query designer of the tblClassAssignment and related tables using the combo boxes on the form as criteria. If you have a later version of Access the Intellisense will help you build your form references. Just type Forms! in the criteria and you should get a drop down of the forms. It sometimes helps if you have the form open and you certainly want the form open and something selected in the combo boxes when you test your query. Once you get that working I suggest just using the wizard to create your listbox and give it the query you just made as the row source. It usually gets it right. The listbox will have to be requeried in any event that changes its contents.

For the other listbox you will want a Row Source query that displays the students are not already assigned. This is called an unmatched query and there is a query wizard that gets it most of the time. So I'd give it a shot. Then create your listbox with that query as a Row Source. This llstbox will also need to be requeried by any event that changes the contents. You will want to change this listbox to a multiselect. I think that property is in the Other tab.

To do the "pick box" part, I came across this after I got your reply:
How to use AddItem and RemoveItem to move selections from one list box to another: https://support.microsoft.com/en-us/kb/278378
and I got it to work, miraculously (although when I tried to do the exact example that they gave on the Northwinds 2007 database, the code threw an error, even though the same code worked fine in my Student database?!?) If anyone else would like to use the code from this site, be sure that your two listboxes have the same number of columns or it will not work correctly. I had two hidden columns and forgot to account for them, and it would show the student names in the first list box and their ID numbers in the second!

To assign the students selected in this listbox you will need to loop through the selected items and insert records into the tblClassAssignment table. This Web page has some code that shows looping through a listbox. I suggest giving it a look before looking at PBaldy's page which covers one way of inserting the records. Another way is to use the INSERT INTO statement. If you go that route you will need to know how to concatenate in the variables which is discussed here.

Am going to work on this part after vacation. Thanks so much for your help so far; it really helped to point me in the right direction.

 

[sneuberg]

Where I was trying to lead you would have only done part of that system that you posted, the button that would move all selected in one direction. Had I know of the Web page you found I would have point you in that direction. Nice find. I'll take a closer look at it when I get more time.

Thanks
Steve

 

[terrytek]

Where I was trying to lead you would have only done part of that system that you posted, the button that would move all selected in one direction. Had I know of the Web page you found I would have point you in that direction. Nice find. I'll take a closer look at it when I get more time.

Thanks
Steve

Hi Steve,

I have attached a stripped-down version of the db with StudentIDs standing in for StudentNames for privacy. The VBA code from the aforementioned MS site does work, but it has a few quirks that need fixing.

1)When you try to move a single record from one box to another (in either direction) and forget to highlight a record first, you see this error:

"Run-time error '5': Invalid procedure call or argument"
and the debugger points to the line of code in bold below (I even tried putting it on two lines both starting with "Me", but that throws a different error):

Sub MoveSingleItem(strSourceControl As String, strTargetControl As String)
    
    Dim strItem As String
    Dim intColumnCount As Integer
    For intColumnCount = 0 To Me.Controls(strSourceControl).ColumnCount - 1
        strItem = strItem & Me.Controls(strSourceControl).Column(intColumnCount) & ";"
    Next
    strItem = Left(strItem, Len(strItem) - 1)

    'Check the length to make sure something is selected
    If Len(strItem) > 0 Then
        Me.Controls(strTargetControl).AddItem strItem
        [B]Me.Controls(strSourceControl).RemoveItem Me.Controls(strSourceControl).ListIndex[/B]
    Else
        MsgBox "Please select an item to move."
    End If

End Sub

2) lstUnassigned starts out with the StudentIDs in numerical order, since that's the way they're sorted in qryStudentsExtended. If you move a record from lstUnassigned to lstAssigned, and then move it back, it goes to the bottom of the list instead of its original proper numerical order. Is there a way to get lstUnassigned back to its proper numerical order? I played around with requerying but got nowhere.

(BTW, the button to append the new students to tblClassAssignments has no code behind it yet; I haven't gotten that far).

This is called "Learning VBA by lurching from error to error" :banghead:!

I will be away for about 10 days, so will not be able to reply in that time. Thanks as always for your generous help; it's very much appreciated .

Terry

 

[Galaxiom]

How to use AddItem and RemoveItem to move selections from one list box to another: https://support.microsoft.com/en-us/kb/278378 .

What a horrible technique. Using a list RowSource is absolutely crazy for something like this when listboxes have a Recordset Property that can be Set to either a DAO or ADO recordset. I expect the example code came from before the Recordset property was introduced for listboxes.

Use disconnected ADO recordsets which can even be populated with data quite independent of any tables. Then use the recordsets' Sort and Filter methods to change what is displayed in the listboxes and in what order.

 

[terrytek]

Galaxiom,

Can you point me to a reference that would show me how to populate the list box with a recordset and sort it? I am pretty new to VBA so need some guidance. Thanks.