subform Append Query

[trebor3900]

I have a form with a subform in dataview and wish to append
the selected record in the subform to another table when double clicked.

I have the following code in the double click event of the subform.



Private Sub Form_DblClick(Cancel As Integer)

Dim strSearchName As String

strSearchName = Str(Me.NatoStockNo)
MsgBox (strSearchName)

DoCmd.RunSQL "INSERT INTO tblManualTemp ( NatoStockNo, Description,
HeightMin, HeightMax, CollarSize, ChestMin, ChestMax, WaistMin,
WaistMax, InsideLegMin, InsideLegMax, SeatSize, HelmetSize, CapSize,
ShoeSize, GloveSize ) SELECT tblClothing.NatoStockNo,
tblClothing.Description, tblClothing.HeightMin, tblClothing.HeightMax,
tblClothing.CollarSize, tblClothing.ChestMin, tblClothing.ChestMax,
tblClothing.WaistMin, tblClothing.WaistMax, tblClothing.InsideLegMin,
tblClothing.InsideLegMax, tblClothing.SeatSize, tblClothing.HelmetSize,
tblClothing.CapSize, tblClothing.ShoeSize, tblClothing.GloveSize FROM
tblClothing WHERE (((tblClothing.NatoStockNo) = strSearchName));"

End Sub



The "msgbox" was added just to prove then value of the selected field
in the record was returned (which it was). The problem comes with the
SQL statement.An "enter parameter value" dialogue box appears.
I am a novice as far as VBA and SQL is concerned so any help would be
invaluable.

 

[MarkK]

Note that your sql is a string, but your variable name, to be evaluated correctly, must be evaluated outside this string.
The problem is here:
...tblClothing WHERE (((tblClothing.NatoStockNo) = strSearchName));"
Should be:
...tblClothing WHERE (((tblClothing.NatoStockNo) = " & strSearchName & "));"
Or, if NatoStockNo is a string:
...tblClothing WHERE (((tblClothing.NatoStockNo) = '" & strSearchName & "'));"

 

[trebor3900]

Der

Thanks for that