I have two forms, and I want a way to select names from one table, and move them to the second one. The easiest way i saw was through two lsitboxes from this example from here (http://www.access-programmers.co.uk/forums/showthread.php?t=165549&highlight=list)

However, it only seems to flag it and I don't know what to do next.

Usually a request to move records to another table can be satisfied by eliminitating the need to do so.
I would suggest you tell us more about why you need to move the records.

I think you are looking for a way to categorise your data into two categorise but you feel it's better to place them in seperate tables? Please clarify

Hm, I hope what I am trying to do is right.. but..What I am doing is using a list of people. And then from the list box form, I select people to append their name under the event's table.
I hope that makes more sense.

Hm, I hope what I am trying to do is right.. but..What I am doing is using a list of people. And then from the list box form, I select people to append their name under the event's table.
I hope that makes more sense.


So you want to be able to select a name from that list box, click a button and it adds that selection value to a seperate table and removes it from the list box?

Haha, just what I want.

Is your list box getting data from a table or query or is it unbound? Could you tell us under Format tab, the number under Column Count property. And also the Bound Column number under Data tab?

I know the left list box gets its data from the name list. The list box on the right gets its data from the event. So I guess I only need to have an append query that only appends the selected item from the list box. But i have no clue how to get that far.

oh! But i do have something very similar. It deltedes the selected item though. Any idea how to make it append to the second table?

Private Sub DeleteRecord_Click()

Dim strSql As String

strSql = "DELETE [EventName] FROM tblEvents WHERE " & _
"tblEvents.[EventName] = '" & Me![EventList] & "'"
CurrentDb.Execute strSql, dbFailOnError

Me![EventList].Requery ' requery the list

End Sub

oh! But i do have something very similar. It deltedes the selected item though. Any idea how to make it append to the second table?

Private Sub DeleteRecord_Click()

Dim strSql As String

strSql = "DELETE [EventName] FROM tblEvents WHERE " & _
"tblEvents.[EventName] = '" & Me![EventList] & "'"
CurrentDb.Execute strSql, dbFailOnError

Me![EventList].Requery ' requery the list

End Sub


Something along these lines


strSQL = "UPDATE tblSecondTable SET [Field1] = '" & Listbox1.value & "' " & _
"WHERE [secondTableID] = tableIDNumber;"
CurrentDb.Execute strSQL, dbFailOnError

' Requery both list boxes Here.

you are awesome! I will work with this later tomorrow (later here), but this seems right. I will let you know when I am done.
Going to add some rep points.

you are awesome! I will work with this later tomorrow (later here), but this seems right. I will let you know when I am done.
Going to add some rep points.


Glad to be of assistance.

well, i guess i spoke too soon. I can not find a way to make it work. Then doing some more research i found this site (http://www.blueclaw-db.com/accessquerysql/sql_insert_into.htm).
I have these two tables:
tblNames
tblEvent

and both share the
FullName

field.

How can I make the names move with the buttons according to the list box? And then remove them if they were already selected?
I hope I am not piling on more ;)

maybe this picture helps:
http://i49.tinypic.com/2dklr9x.png

well, i guess i spoke too soon. I can not find a way to make it work. Then doing some more research i found this site (http://www.blueclaw-db.com/accessquerysql/sql_insert_into.htm).
I have these two tables:
tblNames
tblEvent

and both share the
FullName

field.

How can I make the names move with the buttons according to the list box? And then remove them if they were already selected?
I hope I am not piling on more ;)


You are certainly delving into the world of VBA here, good way to learn though. What you are looking for isn't an update query. You want to be able to "move" the list box A to listbox B. Firstly you need to enable the Multi select property for both list boxes. I'm positive someone has answered a post of the same nature as yours. Do a search and if you're still stuck write back on here and I will show you how?

wow, you have got me there. I am not sure what to do after that. I mean i know how to delete a selected file. But no matter what I do I can't get thew append query to work properly. It sounds like you seem to have knowlege about VBA judging by you name however haha ;).

wow, you have goo me there. I am not sure what to do after that. I mean i know how to delete a selected file. But no matter what I do I can't get thew append query to work properly. It sounds like you seem to have knowlege about VBA judging by you name however haha ;).


Haha! It's just a random name. I only joined two days ago and it's quite interesting helping out. There are a good number of gurus and MVPs on this forum.

On the On Click event of the button with caption ">" do something like this:

Dim i as long
If Available.ListIndex > -1 Then
Attending.AddItem Available.value
Else
Msgbox "Please make a selection",,"Item not selected"
End if

For the one that reads ">>", this:
If Available.ListIndex > -1 Then
For i = 0 to Available.ListCount - 1
Attending.AddItem Available.value
Next
Else
Msgbox "Please make a selection",,"Item not selected"
End if

This is a start.

alright. I had a look at it, but the appending wasnt goign on so i looked a bit further and came across this here (http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=323&KW=list+box&PID=323#323).
I will change it around to see if it can suit my needs. I will let you know if I need any more help.
thanks so far!

alright. I had a look at it, but the appending wasnt goign on so i looked a bit further and came across this here (http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=323&KW=list+box&PID=323#323).
I will change it around to see if it can suit my needs. I will let you know if I need any more help.
thanks so far!

That code should work. Upload your db, let me have a look.

Ok, I took out this part of my database to simplify things. I am still not sure why it isnt working though. Link: http://dl.dropbox.com/u/1254531/Database1.accdb

I've just quickly bashed this out so it hasn't been properly tested. Play around with the form and you will see the new implementations. In order for you to select more than one name in the box, hold down the CTRL key and select as many as you want (whilst holding down the key).

Create a backup copyof yours before using mine just in case you find mine too complicated to understand. Or just simply rename mine and test it until you get yourself fully acquainted.

The bits you should work on now is removing the selected item(s) from the list box and the undo bit. :)

See attached.

Wow! Youre amazing at this! It works. I bet i could figure out how to make it do the reverse. But my last problem is to prevent duplicates. Just make an index?
Actually. haha, I think i am overstaying my welcome with all these questions, but, how come does it not add thme to the other table? Only to the other list.

Wow! Youre amazing at this! It works. I bet i could figure out how to make it do the reverse. But my last problem is to prevent duplicates. Just make an index?

If you remove the item from the listbox on the LHS straight after it was added to the RHS it will (in essence) prevent duplicates (because that record will no longer be there). Think about it!

Most importantly, the record that is added to Listbox2 isn't updating your table. So that's another thing to figure out. All I did was unbind listbox2 for it to work. Create an "Assign" button underneath Listbox2 which will then add all the records to the table. It's a more efficient way of doing it.