Hi, I have a button on a form which I use to delete records. The code behind the button is as follows:
'Turn off system messages
DoCmd.SetWarnings False
'The response from the Yes/No message box
Dim Response As Integer
'My customised deletion messgae
Response = msgBox("You are about to delete this record and the associated Pole Attributes and Work Instructions" & Chr(13) & "Are you sure you wish to do this?", vbYesNo, "Warning! Deletion in Progress")
'If the response from the message box was Yes(6) delete the record and close the form, otherwise just close the form
If Response = 6 Then
'mainTbl - the main table, poleAttr - table with related records
'workCompl - another table with related records
Dim mainTbl, poleAttr, workCompl As String
'syntax for a delete query to delete it from the pole attributes table
poleAttr = "DELETE [NETWORK REFURBISHMENT-POLE_ATTRIBUTES].*, [NETWORK REFURBISHMENT-POLE_ATTRIBUTES].Segment, [NETWORK REFURBISHMENT-POLE_ATTRIBUTES].[Pole No]FROM [NETWORK REFURBISHMENT-POLE_ATTRIBUTES]WHERE ((([NETWORK REFURBISHMENT-POLE_ATTRIBUTES].Segment)=[Forms]![Network Refurbishment 20kV-Delete a Record]![Segment]) AND (([NETWORK REFURBISHMENT-POLE_ATTRIBUTES].[Pole No])=[Forms]![Network Refurbishment 20kV-Delete a Record]![Pole No]))"
'delete related records from poleAttr
DoCmd.RunSQL poleAttr
'syntax for a delete query to delete it from the work completed table
workCompl = "DELETE [NETWORK REFURBISHMENT-WORK_COMPLETED].*, [NETWORK REFURBISHMENT-WORK_COMPLETED].Segment, [NETWORK REFURBISHMENT-WORK_COMPLETED].[Pole No]FROM [NETWORK REFURBISHMENT-WORK_COMPLETED]WHERE ((([NETWORK REFURBISHMENT-WORK_COMPLETED].Segment)=[Forms]![Network Refurbishment 20kV-Delete a Record]![Segment]) AND (([NETWORK REFURBISHMENT-WORK_COMPLETED].[Pole No])=[Forms]![Network Refurbishment 20kV-Delete a Record]![Pole No]))"
'delete related records from WorkCompl
DoCmd.RunSQL workCompl
'syntax for a delete query to delete it from the main table
mainTbl = "DELETE [NETWORK REFURBISHMENT-MAIN].*, [NETWORK REFURBISHMENT-MAIN].Segment, [NETWORK REFURBISHMENT-MAIN].[Pole No]FROM [NETWORK REFURBISHMENT-MAIN]WHERE ((([NETWORK REFURBISHMENT-MAIN].Segment)=[Forms]![Network Refurbishment 20kV-Delete a Record]![Segment]) AND (([NETWORK REFURBISHMENT-MAIN].[Pole No])=[Forms]![Network Refurbishment 20kV-Delete a Record]![Pole No]))"
'delete the record
DoCmd.RunSQL mainTbl
'Closes the form
DoCmd.Close
Else
'close the form anyway
DoCmd.Close
End If
'Turn back on system messages
DoCmd.SetWarnings True
Now, this all works perfectly fine. The problem I have with it is that sometimes I have to hit the button twice before it executes properly. Even when I do this it does execute properly. Sometimes I have to hit the button again and rerun the whole lot and sometimes I just hit the button once and it executes.
Why the hell is it doing this???? It doesn't cause any problems, as I said either way the code executes correctly but it does look rather sloppy. It doesn't seem to matter whether there are a lot of related records in the other tables either. Any ideas????
Thanks,
Liam
'Turn off system messages
DoCmd.SetWarnings False
'The response from the Yes/No message box
Dim Response As Integer
'My customised deletion messgae
Response = msgBox("You are about to delete this record and the associated Pole Attributes and Work Instructions" & Chr(13) & "Are you sure you wish to do this?", vbYesNo, "Warning! Deletion in Progress")
'If the response from the message box was Yes(6) delete the record and close the form, otherwise just close the form
If Response = 6 Then
'mainTbl - the main table, poleAttr - table with related records
'workCompl - another table with related records
Dim mainTbl, poleAttr, workCompl As String
'syntax for a delete query to delete it from the pole attributes table
poleAttr = "DELETE [NETWORK REFURBISHMENT-POLE_ATTRIBUTES].*, [NETWORK REFURBISHMENT-POLE_ATTRIBUTES].Segment, [NETWORK REFURBISHMENT-POLE_ATTRIBUTES].[Pole No]FROM [NETWORK REFURBISHMENT-POLE_ATTRIBUTES]WHERE ((([NETWORK REFURBISHMENT-POLE_ATTRIBUTES].Segment)=[Forms]![Network Refurbishment 20kV-Delete a Record]![Segment]) AND (([NETWORK REFURBISHMENT-POLE_ATTRIBUTES].[Pole No])=[Forms]![Network Refurbishment 20kV-Delete a Record]![Pole No]))"
'delete related records from poleAttr
DoCmd.RunSQL poleAttr
'syntax for a delete query to delete it from the work completed table
workCompl = "DELETE [NETWORK REFURBISHMENT-WORK_COMPLETED].*, [NETWORK REFURBISHMENT-WORK_COMPLETED].Segment, [NETWORK REFURBISHMENT-WORK_COMPLETED].[Pole No]FROM [NETWORK REFURBISHMENT-WORK_COMPLETED]WHERE ((([NETWORK REFURBISHMENT-WORK_COMPLETED].Segment)=[Forms]![Network Refurbishment 20kV-Delete a Record]![Segment]) AND (([NETWORK REFURBISHMENT-WORK_COMPLETED].[Pole No])=[Forms]![Network Refurbishment 20kV-Delete a Record]![Pole No]))"
'delete related records from WorkCompl
DoCmd.RunSQL workCompl
'syntax for a delete query to delete it from the main table
mainTbl = "DELETE [NETWORK REFURBISHMENT-MAIN].*, [NETWORK REFURBISHMENT-MAIN].Segment, [NETWORK REFURBISHMENT-MAIN].[Pole No]FROM [NETWORK REFURBISHMENT-MAIN]WHERE ((([NETWORK REFURBISHMENT-MAIN].Segment)=[Forms]![Network Refurbishment 20kV-Delete a Record]![Segment]) AND (([NETWORK REFURBISHMENT-MAIN].[Pole No])=[Forms]![Network Refurbishment 20kV-Delete a Record]![Pole No]))"
'delete the record
DoCmd.RunSQL mainTbl
'Closes the form
DoCmd.Close
Else
'close the form anyway
DoCmd.Close
End If
'Turn back on system messages
DoCmd.SetWarnings True
Now, this all works perfectly fine. The problem I have with it is that sometimes I have to hit the button twice before it executes properly. Even when I do this it does execute properly. Sometimes I have to hit the button again and rerun the whole lot and sometimes I just hit the button once and it executes.
Why the hell is it doing this???? It doesn't cause any problems, as I said either way the code executes correctly but it does look rather sloppy. It doesn't seem to matter whether there are a lot of related records in the other tables either. Any ideas????
Thanks,
Liam
