Full Version: MultiSelect List Box As Subform OR Command Button
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
jmcwk
Do not really know how to approach this however will attempt to explain the situation. I have a Stand Alone Multiselect List Box Form (frmAnimalLists) that works quite well Adding Item/s from the left side to the right and able to delete Items from the Right Side and Move them back to the Left. Based on the Selected Item/s they are written to a table called tblSelectedAnimals having one field AnimalID. Now that I have it working I would like the functionality to allow the user to click a button,open the list box form,select the Animal/s and write them to a subform. The Primary Key on the Mainform is ProgDetailID.
o I:
Add ProgDetailID to the tblSelectedAnimals
Add ProgDetailID to the ListBox
Create A New Form based on the SelectedAnimals Table and use that as the Subform
Go Past go and do not collect $200.00
Where do I go from here?
Thank You
Jack Cowley
John -
You have code that adds the AnimalID to the table that is the source for the right list box. To that code you can add some more code to add the same animalID to the table that is the Record Source for the subform. You will also need code to delete any entries made to that table if the user moves the data from the right list box back to the left list box.
hth,
Jack
jmcwk
Hello Jack Good to hear from you and hope things are going well!
This is the code I have to Add an Item on the Click Event do I add ProgDetailId to this?
CODE
' Enumerate through selected items.
    For Each varItem In ctlList.ItemsSelected
        ' Add to selected list.
        mysql = "INSERT INTO tblSelectedAnimals (AnimalID) VALUES (" & ctlList.ItemData(varItem) & ");"
        DoCmd.SetWarnings False
        DoCmd.RunSQL mysql
        DoCmd.SetWarnings True
Jack Cowley
John -
All is well as I am finally seeing the sun after many days of Not seeing it!!!
Create another SQL to add the data to the table that is the Record Source for the subform. You will probably need to add the FK to this table as well.
Hope this helps and that you have some sunshine as well...
Jack
merlicky
I may be reading this wrong, but why don’t you add a Boolean field to your main table with True meaning selected and False meaning not selected. The subform could then use SQL with “WHERE SelectedAnimals=True”
jmcwk
Well Yesterday was not bad here today overcast and gloomy! One day is better than none for sure.
dded this with no result:
' Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
' Add to selected list.
mysql = "INSERT INTO tblSelectedAnimals (AnimalID) VALUES (" & ctlList.ItemData(varItem) & ");"
mysql = "INSERT INTO tblSelectedAnimals (ProgDetailID) VALUES (" & ctlList.ItemData(varItem) & ");"
DoCmd.SetWarnings False
DoCmd.RunSQL mysql
DoCmd.SetWarnings True

Next varItem
ctlList.Requery
ctlList2.Requery
tblAnimalsSelected now has two fields
AnimalID
ProgDetailID
Subform Has
AnimalID
ProgDetailID (FK)
etc........
Jack Cowley
John-
ysql = "INSERT INTO NameOfTableThatIsRecordSourceForSubfrom (ProgDetailID, AnimalID) VALUES (" & Me.ProgDetailsID & ", " & ctlList.ItemData(varItem) & ");"
In regards to the other suggestion you could do that too, but you will have to be sure that the subforms table is updated before you leave the form.
hth,
Jack
jmcwk
Sorry to be such a pest Jack but it errors out on the Me.ProgDetailsID I did change it to Me.ProgDetailID
CODE
  mysql = "INSERT INTO tblAnimalProgramDetail (ProgDetailID, AnimalID) VALUES (" & Me.ProgDetailID & ", " & ctlList.ItemData(varItem) & ");"
Jack Cowley
John -
You are not a pest... Is there a control on your form called ProgDetailID and is it a number or is it text (should be a number)? Also, what is the error you get? Is tblAnimalDetail the table that is the Record Source for the subform? Does the table tblAnimalProgramDetail have a field called ProgDetailID and it is NOT the primary key for the table, but the foreign key?
Jack
jmcwk
Thanks Jack,
Is there a control on your form called ProgDetailID "yes"
and is it a number or is it text (should be a number)? "Number DataType"
Also, what is the error you get? Compile Error
Is tblAnimalDetail the table that is the Record Source for the subform? "Yes"
Does the table tblAnimalProgramDetail have a field called ProgDetailID "Yes"
and it is NOT the primary key for the table, but the foreign key? "Yes" , ProgDetailID is the PK on the Main Form

If needed I can Attach
Jack Cowley
John -
When you compile your code Access should highlight the item that it is having a problem with. Does it highlight your new SQL statement? If you remove one of the table fields and its corresponding VALUE does the code compile? If so, then switch and try the other field and VALUE and see if that compiles. This will tell you which item is giving you the problem and that may help you find a solution. If you cannot solve the problem then go ahead and post your db...
NO guarantees, as always, that I can find and/or fix the problem!
Jack
jmcwk
Jack am prety sure it is something simple I just cannot find it. The Listbox is frmAnimalLists,the Mainform is frmProgramSchedule with the subform frmProgramDetail. frmProgramDetail of course is the Form I want the values written to. There is a command button at the bottom of the form to open frmAnimalLists.
Jack Cowley
John -
I have downloaded your db and I will get back to you shortly...
Jack
jmcwk
No hurry Jack you certainly have bigger fish to fry than that
Thank You
Jack Cowley
John -
Here is the correct syntax:
CODE
mysql = "INSERT INTO tbAnimalProgramDetail (ProgDetailID, AnimalID) VALUES (" & [Forms]![frmProgramSchedule]![ProgDetailID] & ", " & ctlList.ItemData(varItem) & ");"

Note that "tbAnimalProgramDetail" does not have the 'l' in tbAnimalProgramDetail...
The code is not updating the table but I haven't gone there yet, just got the right syntax for the SQL statement.
Jack
Jack Cowley
John -
o get your code to update the table remove the Default values of 0 from all of the fields in "tbAnimalProgramDetail"
Jack
jmcwk
Jack,
Is there a reason that the Selected Animals do not move from the Left Side to the right? And do I need to do a requery on the subform aftr closing the frmAnimalLists? If you are seeing something I am not could you reattach please?
Thank You
Jack Cowley
John -
Here is the code for the Double Click event. Modify your other code as needed:
CODE
    Dim ctlList As Control
    Dim ctlList2 As Control
    Dim varItem As Variant
    Dim mysql As String
    Dim mysql1 As String
    
    ' Return Control object variable pointing to list boxes.
    Set ctlList = Me.employees
    Set ctlList2 = Me.employeesselected
    
    ' Enumerate through selected items.
    For Each varItem In ctlList.ItemsSelected
        ' Add to selected list.
        mysql = "INSERT INTO tblSelectedAnimals (AnimalID) VALUES (" & ctlList.ItemData(varItem) & ");"
        mysql1 = "INSERT INTO tbAnimalProgramDetail (ProgDetailID, AnimalID) VALUES (" & [Forms]![frmProgramSchedule]![ProgDetailID] & ", " & ctlList.ItemData(varItem) & ");"
        DoCmd.SetWarnings False
        DoCmd.RunSQL mysql
        DoCmd.RunSQL mysql1
        DoCmd.SetWarnings True
        
    Next varItem
    ctlList.Requery
    ctlList2.Requery

Note additional mysql statement, dimension and code to run them. Don't forget to delete a record from tbAnimalProgramDetail if you move an item from the left list box back to the right.
Jack
jmcwk
Aha! An additional RunSql, very clever I would have been forever getting to that point. Only a question but I am assuming if Additional Values needed added to the Detail Form that all would need to be done is to add another Sql line. Also understand the Deletion from tbAnimalProgramDetail if moved.
Thanks You for the Help As Always!
Jack Cowley
John -
You do not need another SQL if you are going to add something to "tbAnimalProgramDetails". Just add another field and value to mysql1:
mysql1 = "INSERT INTO tbAnimalProgramDetail (ProgDetailID, AnimalID, AnotherField) VALUES (" & [Forms]![frmProgramSchedule]![ProgDetailID] & ", " & ctlList.ItemData(varItem) & ", " & Me.AnotherControl & ");"
I hope that was the question you were asking, but if you were saying you wanted to append data to another table then you would need another SQL statement.
Glad to help!!!
Jack
jmcwk
No Jack that was the answer to the question, I have one more hopefully it will be the last. If I go to a new record and open the frmAnimalList should the right side be clear of items? I am not getting that result it shows the selection/s from the first record.
Jack Cowley
John -
If the list box is showing the values from the first record then it is because the data in the tables that the list boxes are bound to have not been 'reset'. Since the list boxes are just a means to update the AnimalDetails table (I think that is its only job) then you will have to reset the tables for the list boxes whenever you go to a different record.
I think I am on the right track with this, as I did not look at all that was going on with the db, I just looked at the code to to update your AnimalDetails table.
Jack
jmcwk
Jack,
can select 3 Animals to the first record close the frmAnimalLists (at this point the selected Animals are not visible) go to record 2 and back to record 1 and the 3 Selections are visible. From that I can close all forms and reopen to record 2, click the command button to open the frmAnimalLists and the 3 selected Animals from record 1 are there. Do not know why the link is not being made in regard to the ProgDetailID Master, Child Links I imagine on closing the frmAnimalLists and doing a requery of the frmProgramDetail that that will clear not seing the initial selections but I do not understand the latter.
Jack Cowley
John -
The two list boxes have a Row Source the tells them to show data based on two tables. No matter what record you are on form "frmAnimalLists" is always going to show the data from the tables ("tblSelectedAnimals" and "tblAnimals") the list boxes are 'bound' to. If you want to show the values that are in the table "tbAnimalProgramDetail" then you will need to use that table as the Row Source for the right list box.
I hope that I am making sense....
Jack
jmcwk
Jack,
Thank You for your time I guess I just complicate things more than need be from my side of the house. I will continue playing with it and hopefully I will figure it out. Use that Table for the Row Source for the Right List Box.
So, the left pulls from tblAnimals and writes to the right side and the right side needs to pull data from tblAnimalProgramData is that correct or is that what I am understanding you to say? The INSERT INTO would change from:
CODE
INSERT INTO tblSelectedAnimals (AnimalID) VALUES  etc....

TO
CODE
INSERT INTO tblAnimalDetailProgram (AnimalID) VALUES  etc.....
Jack Cowley
John -
You have two SQL statements in your form "frmAnimalLists", and the one you want is the second one, 'mysql1'. What you need to do is set the Row Source of the Right List Box to the table "tbAnimalDetailProgram" not the table that it is now getting its data from. You do not need to change any code in your form with the list boxes, except to remark out the first mysql and to change the Row Source of the second list box to a query that gets its data from table "tbAnimalDetailProgram". Be sure the query is filtered by the ProgDetailID (I think that is the right filter) so that the list box does not show all the items that will eventually be in the table "tbAnimalDetailProgram".
I hope you were able to follow my convolutions...
Jack
jmcwk
Jack, I thiink I understand???? I am going to eat some dinner and come back to it, I am sure it is not as difficult As I am Making it. two hours ago it seemed so simple frown.gif Famous Last Words!
Thank You for Your Time AND Patience!
Jack Cowley
John -
You are very welcome and I am glad that I am able to help...at least a little help and then a little confusion to keep things lively..
Enjoy your dinner and good luck with the db when you get back to it....
Jack
jmcwk
Can not pull myself away !
ysql1 now reads:
CODE
mysql1 = "INSERT INTO tbAnimalDetailProgram (ProgDetailID, AnimalID) VALUES (" & [Forms]![frmProgramSchedule]![ProgDetailID] & ", " & ctlList.ItemData(varItem) & ");"

Generates a Run Time Error 3192 Could Not Find output table tbAnimalDetailProgram crazy.gif
Jack Cowley
Hmmm. This was working before. Are you running this somewhere else? Does the code compile without error? Is your mysql1 properly dimensioned? Have you done a compact and repair lately? Did you rename the table to add the missing L?

Jack
Edited by: Jack Cowley on Wed Apr 26 19:52:58 EDT 2006.
jmcwk
Jack,
Are you running this somewhere else? Not that I know of there is two mysql1 lines within the code for the frmAnimalLists I changed bot to reflect the tbAnimalProgramDetail
Does the code compile without error? I did a compact and repair of the DB and when the Error is genrated and I click Debug It Takes me to " DoCmd.RunSQL mysql1" within the code window
Is your mysql1 properly dimensioned? ????? If you mean spacing ALL I did was replace the old table name to the new
Have you done a compact and repair lately?
I did you rename the table to add the missing L? No
Jack Cowley
John -
I am not sure what is going on as the mysql1 code was working, and as far as I know, is still working for me. This code is what appends the data to the table "tbAnimalProgramDetail". Did you remove the default zeros from the fields in that table?
Would you like me to send the db I have as it is adding data to the table "tbAnimalProgramDetail" when I add a record to the right list box.
Jack
PS. Here is the code I am using in the double click event of the left list box:
CODE
    Dim ctlList As Control
    Dim ctlList2 As Control
    Dim varItem As Variant
    Dim mysql As String
    Dim mysql1 As String
    
    ' Return Control object variable pointing to list boxes.
    Set ctlList = Me.employees
    Set ctlList2 = Me.employeesselected
    
    ' Enumerate through selected items.
    For Each varItem In ctlList.ItemsSelected
        ' Add to selected list.
        mysql = "INSERT INTO tblSelectedAnimals (AnimalID) VALUES (" & ctlList.ItemData(varItem) & ");"
        mysql1 = "INSERT INTO tbAnimalProgramDetail (ProgDetailID, AnimalID) VALUES (" & [Forms]![frmProgramSchedule]![ProgDetailID] & ", " & ctlList.ItemData(varItem) & ");"
        DoCmd.SetWarnings False
        DoCmd.RunSQL mysql
        DoCmd.RunSQL mysql1
        DoCmd.SetWarnings True
        
    Next varItem
    ctlList.Requery
    ctlList2.Requery
jmcwk
Yes Jack I would appreciate that very much if you could attach
Thank You
Jack Cowley
John -
o guarantees on the attached as I just zipped it up and here it is... I do know that if you double click in the left list box that the record will be added to the correct table...
Jack
jmcwk
Works Jack!
Thank You only thing remaining is to only show the selections in right side of the Form according to the ProgDetailID.
Jack Cowley
John -
Glad to hear it works!
HAs for the list box on the right side you want to use the tbAnimalProgramDetails as its Row Source and you should be good to go. It may take a bit of fiddling to filter it properly, but it shouldn't be hard as you have ProgDetailID handy...
Jack
jmcwk
Thank Again Jack!
Jack Cowley
John -
You are welcome and good luck with this. I think you are almost home free....
Jack
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.