insert into sql not working...

[doran_doran]

Private Sub chkDM_Click()

Dim varDM As Variant
varDM = Me.txtDLDMemail

If Me.chkDM.Value = -1 Then

'original code not working
'DoCmd.RunSQL "INSERT INTO tbltmpRecipients ( TotalRecipients ) SELECT " & varDM & " AS txtRecipient;"

'New code not working either
DoCmd.RunSQL "INSERT INTO tbltmpRecipients (TotalRecipients) values (" & varDM & ");"

ElseIf Me.chkDM.Value = 0 Then
DoCmd.RunSQL "DELETE tbltmpRecipients.TotalRecipients FROM dummy WHERE (((tbltmpRecipients.totalRecipients)=" & varDM & "));"
End If

Me.tblTmpRecipientsSubform.Requery

End Sub

 

[giedrius]

I would use:

CurrentDb.Execute "INSERT INTO tbltmpRecipients " & _
"(TotalRecipients) SELECT " & varDM & " FROM xxxx", dbFailOnError

you should specify table (xxxx) where from you want your values to be selected.

Rgds,
giedrius

 

[doran_doran]

It's now working like a charm....

'New Working Version of the Code
Dim varDM As Variant
varDM = Me.txtDLDMemail

If Me.chkDM.Value = -1 Then
DoCmd.RunSQL "INSERT INTO tblTmpRecipients ( TotalRecipients ) SELECT " & " """ & varDM & " "" AS txtRecipient ;"
ElseIf Me.chkDM.Value = 0 Then
DoCmd.RunSQL "DELETE tbltmpRecipients.TotalRecipients FROM tbltmpRecipients WHERE (((tbltmpRecipients.totalRecipients)=" & " """ & varDM & """));"
End If

Me.tblTmpRecipientsSubform.Requery