Moving Records? Moves all instead of selected

[ghost807]

this is my code for on double click of the list box. i'm populating it using the row soucre. SELECT DISTINCT UnDecided.ID, UnDecided.Hardware FROM UnDecided ORDER BY UnDecided.ID DESC;

when i double click on the list box it will open the right record but when i check the table it was supposed to copy into i see it has copied all the rows instead of the selected one.
any thoughts as to what i may be doing wrong?

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim D As DAO.Database, R As DAO.Recordset, Q As DAO.QueryDef
Set D = CurrentDb
Set R = D.OpenRecordset("Undecided&quot
Set Q = D.CreateQueryDef("&quot
Q.SQL = "INSERT INTO AddUndecided SELECT * FROM Undecided WHERE ID=[ID];"
Q.Execute
DoCmd.SetWarnings True

 

[RoyVidar]

Hi!

Try to "strongtype" which ID equal which ID.

i e WHERE Undecided.ID = AddUndecided.ID
WHERE Undecided.ID = " & forms!yourform!yourIDcontrol

HTH Roy-Vidar

 

[dbelsey]

ghost807, seems to me you haven't *told* the code the value of [ID]. How about something like this...

Dim strSQL as String
Dim lngID as Long
Dim ctlSource As Control
Dim intCurrentRow As Integer
Set ctlSource = frm!lstSource

For intCurrentRow = 0 To ctlSource.Listcount - 1
If ctlSource.Selected(intCurrentRow) Then
lngID = ctlSource.Column(0,intCurrentRow)
Exit For
End If
Next
Set ctlSource = Nothing

strSQL = "INSERT INTO AddUndecided SELECT * FROM Undecided WHERE ID = " & CStr(intID)"
DoCmd.SetWarnings False
CurrentDB.Execute strSQL
DoCmd.SetWarnings True

Note the lngID = bit assumes the ID is in the first column of the list box. Also I'm not sure the Exit For is great programming practice, but it does eliminate needless looping.