Paired Listbox not behaving

[Guest]

I have a form with paired listboxes that adds a record to the destination
lstVisitStaff when an item in the source lstStaff is double clicked on.
However, when I dblclick on another item in lstStaff it adds it to the list
but deletes the previous selection. Also, the line in the code for
CurrentDb.Execute sSQL gives me an error msg, but when I comment it out of
the code the code works with the exception of the above mentioned problem.
Heres the code below. Thanks for your help


Private Sub lstStaff_DblClick(Cancel As Integer)
Dim sSQL As String

sSQL = "Append tblVisitStaff WHERE fldStaffID = " & Me.lstStaff.Column(0)
CurrentDb.Execute sSQL
Me.Refresh

End Sub

 

[strive4peace]

Hi Rob,

your SQL statement does not specify what to add...

~~~~~~~~~~~ APPEND ~~~~~~~~~~~

An Append Query is a select query preceded by

INSERT INTO Tablename (field1, field2, etc )

'~~~~~~~~~~~~~~~~~ for instance (and this does not use a table as it
supplies actual values)

dim strSQL as string

strSQL = "INSERT INTO Tablename " _
& " (TextField, NumField, DateField ) " _
& " SELECT '" & strValue & "', " _
& numValue & ", " _
& "#" & datValue & "#" _
& ";"

currentdb.execute strSQL, dbFailOnError
currentdb.tabledefs.refresh

'~~~~~~~~~~~~~~~~~

~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used

UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.keyfield

Then identifies what to change

SET table1.fieldtochange = expression

Then, if you have criteria...

WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#

'~~~~~~~~~~~~~~~~~

how many users do you have in your database? If there is only one, I
like to use a temporary Flag (yes/no) field to decide which list items
should be listed in.

How are you determining what to show in each list?


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*