SQL in Add form wont delete cancelled record

  • Thread starter Thread starter Penny
  • Start date Start date
P

Penny

Hi all,

I have built an Access application in which I use custom pop-up forms for
the user to add new records rather than the Access 'end of recordset' add
method( I have removed the navigation buttons and standard 'add new record'
button). I was using a code incremented primary key but recently changed it
to an Autonumber field. The code I have pasted below comes from the
forms Cancel Button click event.

A Company has a one to many relationship(RI enforced) with JobSpecs and
Industries assigned to it therefore I have two subforms on the Add Company
form. If the user cancels out of the Add form having added rows in either of
the two subforms or not the last SQL delete statement seems to run but
doesn't actually delete that record from tblCompanies. That is, the line:

DoCmd.RunSQL "DELETE * FROM tblCompanies WHERE CompanyKeyNum= " &
intCompanyKeyNum

runs when I view it in the debug window but doesn't delete the empty record
from the table.

Any ideas on what may(or may not) be happening?

Regards,

Penny.

Private Sub lblCancel_Click()

On Error GoTo HandleErrors

' remove focus from subform
CompanyTitle.SetFocus

Dim intCompanyKeyNum As Integer
intCompanyKeyNum = Me.CompanyKeyNum

' if any JobSpecs or Industries were added in sub forms before cancel
their records must be removed
' from tblCompanyJobSpecs or tblCompanyIndustries repectively.
' suppress confirm dialog.
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblCompanyJobSpecs WHERE CompanyKeyNum= " &
intCompanyKeyNum
DoCmd.RunSQL "DELETE * FROM tblCompanyIndustries WHERE CompanyKeyNum= "
& intCompanyKeyNum

' empty the fields to prevent the record being stored
' [CompanyKeyNum] = Null
[CompanyTitle] = Null
[CompanyRegistrationDate] = Null
[CompanyStreetAddress] = Null
[CompanySuburb] = Null
[CompanyState] = Null
[CompanyPostcode] = Null


' if a subform was even clicked in then it seems a Company record will
have been created in tblCompanies.
' delete the candidate record stored then re-instate confirm dialogs.
DoCmd.Save
DoCmd.SetWarnings True
DoCmd.RunSQL "DELETE * FROM tblCompanies WHERE CompanyKeyNum= " &
intCompanyKeyNum


DoCmd.Close acForm, "frmCompanyAdd"
[Forms]![frmMAINMENU]![cboSelect].Requery

ExitHere:
Exit Sub

HandleErrors:
MsgBox Err.Description
Resume ExitHere

End Sub
 
Penny said:
Hi all,

I have built an Access application in which I use custom pop-up forms for
the user to add new records rather than the Access 'end of recordset' add
method( I have removed the navigation buttons and standard 'add new record'
button). I was using a code incremented primary key but recently changed it
to an Autonumber field. The code I have pasted below comes from the
forms Cancel Button click event.

A Company has a one to many relationship(RI enforced) with JobSpecs and
Industries assigned to it therefore I have two subforms on the Add Company
form. If the user cancels out of the Add form having added rows in either of
the two subforms or not the last SQL delete statement seems to run but
doesn't actually delete that record from tblCompanies. That is, the line:

DoCmd.RunSQL "DELETE * FROM tblCompanies WHERE CompanyKeyNum= " &
intCompanyKeyNum

runs when I view it in the debug window but doesn't delete the empty record
from the table.

Any ideas on what may(or may not) be happening?

Regards,

Penny.



Private Sub lblCancel_Click()

On Error GoTo HandleErrors

' remove focus from subform
CompanyTitle.SetFocus

Dim intCompanyKeyNum As Integer
intCompanyKeyNum = Me.CompanyKeyNum

' if any JobSpecs or Industries were added in sub forms before cancel
their records must be removed
' from tblCompanyJobSpecs or tblCompanyIndustries repectively.
' suppress confirm dialog.
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblCompanyJobSpecs WHERE CompanyKeyNum= " &
intCompanyKeyNum
DoCmd.RunSQL "DELETE * FROM tblCompanyIndustries WHERE CompanyKeyNum= "
& intCompanyKeyNum

' empty the fields to prevent the record being stored
' [CompanyKeyNum] = Null
[CompanyTitle] = Null
[CompanyRegistrationDate] = Null
[CompanyStreetAddress] = Null
[CompanySuburb] = Null
[CompanyState] = Null
[CompanyPostcode] = Null


' if a subform was even clicked in then it seems a Company record will
have been created in tblCompanies.
' delete the candidate record stored then re-instate confirm dialogs.
DoCmd.Save
DoCmd.SetWarnings True
DoCmd.RunSQL "DELETE * FROM tblCompanies WHERE CompanyKeyNum= " &
intCompanyKeyNum


DoCmd.Close acForm, "frmCompanyAdd"
[Forms]![frmMAINMENU]![cboSelect].Requery

ExitHere:
Exit Sub

HandleErrors:
MsgBox Err.Description
Resume ExitHere

End Sub
Click to expand...

This is just a guess, but it seems like the form is in edit mode.

DoCmd.Save doesn't save the record, it saves an Access Object - in this case it
is saving the form.

Try changing the code to:



' if a subform was even clicked in then it seems a Company record will
have been created in tblCompanies.
' delete the candidate record stored then re-instate confirm dialogs.

'for debugging '<<<<<< ADDED
MsgBox "dirty = " & Me.Dirty '<<<<<< ADDED

'DoCmd.Save '<<<< COMMENT OUT/delete

'saves record '<<<<<< ADDED
If Me.Dirty then '<<<<<< ADDED
Me.Dirty = Not Me.Dirty '<<<<<< ADDED
End If '<<<<<< ADDED

'for debugging '<<<<<< ADDED
MsgBox "dirty = " & Me.Dirty '<<<<<< ADDED

DoCmd.SetWarnings True
DoCmd.RunSQL "DELETE * FROM tblCompanies WHERE CompanyKeyNum= " &
intCompanyKeyNum




HTH
 
Back
Top