Help with Code....

[bellegigi]

Hi anyone. I need help... I need to write some code and I know what I want it to do, but I can't quite figure out what it needs to be. If I write in plain english what it needs to do, can someone help me with the code? Thanks so much!

1) Button cmdDelete on frmInventory. On Click, I need it to:


a) If they have selected a null record, give them an error message that says that you can't delete a null record and exit sub
b) For the record selected, get the value of the field FTPFileName.
c) Search all records in the recordsource (qryInventoryLookup) of this form and find out if there are any others besides this one that have this same FTPFileName.

If so (there are other records with this value in FTPFileName),
1. Delete this record from tblWorkflow

If not (this is the only record with this value in FTPFileName)
1. Delete this record from tblWorkflow and
2. Delete all records with this value in field FileName
from tblErrors
3. Delete all records with this value in field FileName
from tblField Errors


Thank you so much for any help! This one has been driving me crazy!

 

[bellegigi]

This is the code that I've come up with thus far... it doesn't work! But, I thought that it may be a starting point!

Dim MyID, MyFile
Dim dbs As DAO.Database
Dim Rs As Recordset
Dim strSQL As String


Set MyID = Me.WorkflowID
Set MyFile = Me.FTPFileName
Set dbs = CurrentDb()




On Error GoTo Err_cmdDelete_Click

strSQL = ("SELECT * FROM qryInventoryLookup WHERE WorkflowID <> " & MyID & " AND FTPFileName = " & MyFile & ";")

If IsNull(strSQL) Then
'Delete from tblErrors, tblFieldErrors and tblWorkflow
DoCmd.RunSQL ("DELETE *.* FROM tblErrors WHERE FileName = " & MyFile & ";")
DoCmd.RunSQL ("DELETE *.* FROM tblFieldErrors WHERE FileName = " & MyFile & ";")
DoCmd.RunSQL ("DELETE *.* FROM tblWorkflow WHERE WorkflowID = " & MyID & ";")
Forms![frmInventory].Requery

Else

'Otherwise, delete the record they have selected from tblWorkflow
DoCmd.RunSQL ("DELETE *.* FROM tblWorkflow WHERE WorkflowID = " & MyID & ";")
Forms![frmInventory].Requery
End If



'DoCmd.RunSQL "DELETE *.* FROM tblWorkflow WHERE WorkflowID = " & MyID & ";"


Err_cmdDelete_Click:
MsgBox Err.Number & "" & Err.Description

Resume Exit_cmdDelete_Click



Exit_cmdDelete_Click:

 

[gator]

I just looked at your code quickly and noticed that your delete statements are DELETE *.* FROM - unless I'm missing something, they should be: DELETE * FROM.

-gator