Getting no where

[Guest]

I'm getting now where with the code below, due to the fact that I don't know
what I'm doing. This code is in the DblClick event of listBox (lstRNnotesLU).
The list is on form frmRNnotes which is connected to my frmVisit by
fldVisitNo. I need to get the list item that is being dblclk'd into the
tblRNnotes with the fldVisitNo in as the child.

The list is on the frmRNnotes along with a continuos form contrlol that is
based a query of the table tblRNnotes. Pls help if you can. Thanks, Rob


Dim db As DAO.Database
Dim strSQL As String
Dim varItm As Variant

Set db = CurrentDb()
With Forms!frmRNnotes!lstRNnotesLU
For Each varItm In .ItemsSelected
strSQL = "INSERT INTO tblRNnotes () " & _
"VALUES('" & .ItemData(varItm) & "'"
db.Execute strSQL, dbFailOnError
Next varItm
End With

 

[Damon Heron]

Try this (look at parentheses and end statement):

Dim db As DAO.Database
Dim strSQL As String
Dim varItm As Variant

Set db = CurrentDb()
With Forms!frmRNnotes!lstRNnotesLU
For Each varItm In .ItemsSelected
strSQL = "INSERT INTO tblRNnotes " & _
"VALUES('" & .ItemData(varItm) & "')"
db.Execute strSQL, dbFailOnError
Next varItm
End With

or even simpler:

Dim strSQL As String
Dim varItm As Variant

With Forms!frmRNnotes!lstRNnotesLU
For Each varItm In .ItemsSelected
strSQL = "INSERT INTO tblRNnotes " & _
"VALUES('" & .ItemData(varItm) & "')"
DoCmd.RunSQL strSQL
Next varItm
End With

HTH
Damon