help with strSQL

[rsbutterfly16]

hi guys. i hope you can help me, i am trying to create a delete button in my form. Basically what this form has to do is delete the row from my main table and then insert that row into my history table.

Main Table:
tblDetails
DistID (PK)
SalesNumber
SalesStatus (is always active)
Notes

tblHistory (History Table):
HistID (PK)
DistID (FK)
oldSalesNumber
SalesStatus (is always inactive)
OldNOtes
Date Changed

this is the code i have so far.....

Private Sub Label26_Click()
Dim dbs As DAO.Database
Dim strSQL As String
Dim strSQL2 As String

strSQL = "Delete tblDetails FROM tblDetails " & _
"values ('" & Me.cboSales.Value & "' , '" & Me.txtNotes.Value & "');"
'" WHERE DistID = (" & Me.txtDistID.Value & " );"


MsgBox strSQL
CurrentDb.Execute strSQL, dbFailOnError

strSQL2 = "Insert into tblsHistory (DistDetailsID, oldSalesNumber,OldNotes, DateChanged) " & _
"values (" & Me.txtDistID.Value & " ,'" & Me.cboSales.Value & "' , '" & Me.txtNotes.Value & "', #" & Now() & "#);"
MsgBox strSQL2
CurrentDb.Execute strSQL, dbFailOnError


but i keep getting an error "syntax error in from clause

 

[PHV]

You may try this:
strSQL = "DELETE * FROM tblDetails WHERE DistID=" & Me.txtDistID.Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

You might want to do the insert before the delete. If those are bound controls, I'm not sure those values will be correct after you delete the record from the table.

 

[rsbutterfly16]

thanks for your answer, how would the code change if i only want to delete three of my fields in the row where DistID=" & Me.txtDistID.Value without deleting the whole row?

 

[PHV]

In fact, you want an UPDATE ... SET ... WHERE ... instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

delete three of my fields

There's no such thing in an SQL DELETE statement, it's for deleting entire rows.

With individual fields, you use UPDATE to change their value, perhaps to an empty string, perhaps to Null, or maybe to zero - all depends what your definition of "deleted" is.

 

[rsbutterfly16]

thank you! i apreciate it!