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.

Do 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

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

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?

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

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”

Well Yesterday was not bad here today overcast and gloomy! One day is better than none for sure.

Added 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........

John-

mysql = "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

Sorry to be such a pest Jack but it errors out on the Me.ProgDetailsID I did change it to Me.ProgDetailID

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

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

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

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.

John -

I have downloaded your db and I will get back to you shortly...

Jack

No hurry Jack you certainly have bigger fish to fry than that

Thank You

John -

Here is the correct syntax:



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

John -

To get your code to update the table remove the Default values of 0 from all of the fields in "tbAnimalProgramDetail"

Jack

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

John -

Here is the code for the Double Click event. Modify your other code as needed:



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

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!

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