code a command button to move a selected record in a list box to a table

[cleteh]

I have a form with a list box called lstCheckPayments which is populated by a query. Id like to code a command button cmdAddCheck to add the selected record in the list box to a table named tmpCheckQueue. This is access 97. Ive tried a few things and im not having any luck.

[jonh]

CurrentDb.Execute "insert into tmpCheckQueue (FieldWhatever) values ('" & lstCheckPayments & "')"

[cleteh]

Thanks jonh.... let's say I have 2 fields in each row of the list box (I actually have around 12 fields in each row of the list box I want transferred) called CheckNo and Amount. Would this be the correct way to code this... and this will only the transfer the selected record in the list box? Thanks, I won't be home to try it out until 8 hours from now.

CurrentDb.Execute "insert into tmpCheckQueue (CheckNo, Amount) values ('" & lstCheckPayments & "')"

[jonh]

Listboxes don't hold records.
Simply using lstCheckPayments would copy a single value (from the 'bound column') into the sql string.
For each field you need to specify the value

CurrentDb.Execute "insert into tmpCheckQueue (CheckNo, Amount) values ('" & lstCheckPayments.column(0) & "','" & lstCheckPayments.column(1) & "')"

[cleteh]

It adds the record but it's only adding the CheckNo if I use CurrentDb.Execute "insert into tmpCheckQueue (CheckNo, Amount) values ('" & lstCheckPayments.column(0) & "','" & lstCheckPayments.column(1) & "')"

Date is actually the second field im trying to add but if I use this line of code CurrentDb.Execute "insert into tmpCheckQueue (CheckNo, Date) values ('" & lstCheckPayments.column(0) & "','" & lstCheckPayments.column(1) & "')"

I get an error saying Syntax Error in INSERT INTO statement

[cleteh]

In Debug when I hover over ('" & lstCheckPayments.column(0) & "','" & lstCheckPayments.column(1) & "')" it shows the correct values im trying to insert in the tmpCheckQueue table

[jonh]

For dates, replace the single quote with #

[jonh]

You might need to put square brackets around Date as well to tell it it's a field instead of a reserved word.

[donaldsims]

You solved your problem? if Yes then how?