VBA assistance

[Kwbrown]

I am using a db that was created by someone else and there is a problem with a command button in that it allows you to delete records that should not be deleted. I have an idea of how to fix the problem, but I don't know the syntax etc to use. Hopefully the following makes sense:


Private Sub cmdADelete_Click()

MY COMMENT: After the button is clicked I need the code to check the following query where Agreements.agreement_id = “gAgreement”

SELECT Agreements.agreement_id, Projects.project_ID, Projects.invoice_number FROM Agreements INNER JOIN Projects ON Agreements.agreement_id = Projects.Agreement_ID WHERE (((Projects.invoice_number) Is Not Null));

MY COMMENT: If the query returns a value (is true = is not empty) then MsgBox (“You cannot delete an Agreement that has been invoiced!”)- close msgbox & stop code Otherwise (next step)

MY COMMENT: Check next query where Agreements.agreement_id = “gAgreement”

SELECT Agreements.agreement_id, Projects.project_ID FROM Agreements LEFT JOIN Projects ON Agreements.agreement_id = Projects.Agreement_ID WHERE (((Projects.project_ID) Is Not Null));

MY COMMENT: If the query returns a value (is true = is not empty) then MsgBox (“You must delete all projects associated with this Agreement before you can delete the Agreement!”)- close msgbox = stop code
Otherwise (next step is to run the rest of the code)

MY COMMENT: this is the rest of the prior code which will then allow the agreement to be deleted

On Error GoTo Err_Handler
If MsgBox("Are you sure you want to delete agreement #" & gAgreement & "?", vbYesNo, "Confirm Deletion") = vbYes Then
CurrentDb.Execute ("DELETE * FROM agreements WHERE agreement_id=" & gAgreement & ";"), dbFailOnError
CurrentDb.Execute ("DELETE * FROM liagreementcontacts WHERE agreement_id=" & gAgreement & ";"), dbFailOnError
gAgreement = 0
lstAgreements.Requery
End If
Exit_Handler:


Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Sub

[June7]

What is the query name? Something like:

If Not IsNull(DLookup("Agreement_id", "query name", "Agreement_id=" & gAgreement) Then
'message box here
Else
'delete code here
End If

[robrich22]

Can you paste the actual code?

[June7]

You already have all the pieces.

Use your query name in place of 'query name'.

You want a message box then use the one you described in your post. Only don't use the parentheses.

Where it says 'delete code here, do that. Put your delete code there.


MsgBox can take two forms. One as a function and one as just a notification to user. Use parentheses for the function form, no parentheses for the notification form.


DELETE does not need * because the entire record is deleted. No need for any reference to fields. Also, the semi-colon at end is not required. Save yourself some typing. Only SELECT sql needs semi-colon.