Move selection from one list box to another

[NewfieGolfer]

I have a list box in which a user selects a record and I need that record to be copied to a list box on the same page when a button is pressed. Then, that record has to be deleted from the first list box. And vice versa, the user can select a record in the second list box and move it back to the first by pressing another button.

I have a majority of the code written for this process, the only problem I'm having is trying to delete the selection from the first list after it is selected and the button is pressed. For some reason it deletes a record which is several lines below and not the selected record.

For each of the list box I have a table. The table for the first list box has records and the second table is blank (the copy table).

Suggestions appreciated,


NG

 

[JohnYingling]

With SourceList
    .RemoveItem .listindex
End With

http://WWW.VBCompare.Com

 

[Dodgey]

Hi NG.

On idea is...

add a field to the listbox1 table called
NAME TYPE DEFAULT
Transferred Yes/No No

Then in the listbox1 rowsource, use a query getting all records where Transferred = no.

Then in listbox2 rowsource, use a query that gets all records where Transferred = yes.

Then when you run the code that moves the record, simply change the value of the Transferred field to from false to True or vice versa to send it back.

Then requery both listboxes.

This might cut down on the overheads of adding extra tables to your database.

If this don't help, please say so.

 

[NewfieGolfer]

Dodgey,

I tried the above and had trouble trying to change the value of the Transferred field. Still not having a problem transferring the selection just changing the value of the field in the first selection box. There's probably an easy way to do this, but I only started programming a couple of weeks ago, so my skills are not that great. I was thinking that I could run a query that deletes the selection from the first selection box if it equals the second selection box. Any Ideas, I know the code will look something like this:

DoCmd.RunSQL "DELETE * FROM 1stBoxTable WHERE 1stBoxTable_
= 2ndBoxTable"

I know this is wrong but I'm not sure how to make it work when you have multiple tables.

Any thoughts,

NG

 

[Dodgey]

For changing the transferred field try this...

In both listboxes add the record's unique identifier to the the listbox datasource. This is called a primary key (Datatype LONG INTEGER) within the underlying table. For example, we'll make the first column the primary key.

Make the column invisible to the eye by setting it's column width to zero.

Now in your code, for transferring, try this

...
Dim lPrimaryKey As Long

lPrimaryKey = me.listbox1.column(0)

docmd.runsql "UPDATE tablename SET tablename.Transferred = true (or false) WHERE (tablename.PrimaryKey = " & lPrimaryKey & ""

me.listbox1.requery
me.listbox2.requery
...

Now if you are determined or must base listbox1 on table1 and listbox2 on table2 then I gather you are essentially coping records from one table to another via the listbox interface. Now the code to do that is the same however the small change is the sequel statements:


...
Dim lPrimaryKey As Long

lPrimaryKey = me.listbox1.column(0)

'You might want to turn the warnings off here
docmd.setwarnings false

docmd.runsql "INSERT INTO tablename2 (fieldname1, fieldname2,...) SELECT fieldname1, fieldname2 FROM tablename1 WHERE (tablename1.PrimaryKey = " & lPrimaryKey & ""

docmd.runsql "DELETE * FROM tablename WHERE (tablename.PrimaryKey = " & lPrimaryKey & ""

'turn warnings back on
docmd.setwarnings true

me.listbox1.requery
me.listbox2.requery
...

If you have problems adding the unique identifier to the listbox, please holler.

All the best.

 

[NewfieGolfer]

Dodgey,

I can't get the SQL statement to work. It will not delete the row from the table. It says there are 0 row(s) to change (I haven't turned off warnings yet).

This is what the delete SQL I am using looks like:

lPrimaryKey = Me.List1.Column(0)

DoCmd.RunSQL "DELETE * FROM Bridges WHERE Bridges.IDNumber = " & lPrimaryKey & "


I already had a primary key set up on the tables. This key is visible on the selection list (a requirement) of both tables.

Also, I'm using Access 2000. I'm not sure if this stuff changes from version to version.

The appropriate values are going into the lPrimaryKey statement. BTW, I had to change the lPrimaryKey from Long to String to suit my Primary key setup.

Ideas?

NG

 

[Dodgey]

NG,

To paraphrase,

your delete statement doesn't work
your insert statemnet does
your lprimarykey variable is obtaining the correct unique identifier and
you your primarykey was a string and therefore lPrimaryKey should now be called sPrimaryKey.

If you have given me your SQL statement verbatim then your problem is obvious.

Debug.print your SQL statement. You'll get

WHERE Bridges.IDNumber = BridgeID1

Because the original example I gave you was written to use a numeric data type the value did not need to be enclosed in quotes. Since your primarykey is a string data type you need to encapsulate the variable within a string

DoCmd.RunSQL "DELETE * FROM Bridges WHERE Bridges.IDNumber = '" & sPrimaryKey & "';"


Better luck this time.

 

[NewfieGolfer]

Dodgey,

Thanks a bunch. This helped me a great deal. It actually made me try different things and learn more about the programming aspect of Access.
BTW, the event is working perfectly.

Thanks again,


The Newfie Golfer
(Chris)