Delete record? Yes or No Message box

[andy_dyer]

Hi

I've found an old thread called this same thing but it didn't quite work for me still and didn't want to post onto a 2 year old thread... so figured I'd start a new one... hope that is ok and I am staying the right side of the site admin's...

I have a cmd button on a form which if clicked i want it to check that i'm sure and if i clcik yes then delete the current record, then close the form returning me to another open form behind which has a summary datasheet which then should auto refresh to delete the line...

Private Sub cmdDeleteCurrentRecord_Click()
On Error GoTo Err_cmdDeleteCurrentRecord_Click
Dim Answer As Integer

Answer = MsgBox("Are you sure you wish to delete this record?", vbYesNo + vbExclamation + vbDefaultButton2, "Delete Confirmation")
If Answer = vbYes Then
DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.Close
DoCmd.SetWarnings True

Else

End If

Exit_cmdDeleteCurrentRecord_Click:
    Exit Sub

Err_cmdDeleteCurrentRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdDeleteCurrentRecord_Click
    
End Sub

Then this on close

Private Sub Form_Close()
Forms![frmprojectfinanceyear].SetFocus
Forms![frmprojectfinanceyear].[frmFinanceYear].Requery
Forms![frmprojectfinanceyear].[frmProjectSummaryData].Requery
End Sub

It is deleting the detail in the record but leaving the Primary Key (FinanceYearID) and the Foreign Key (ProjectID) hence when the datasheet requeries it leaves blank lines behind which look messy...

How can I get this to delete the whole record not just the data??

I'm using Access 2010 btw in case that makes a difference...

 

[Trevor G]

If this going across 2 tables then look to create a delete query and take all the fields you want to show (not really necessary, but worth doing just to check) and set it to the id field criteria to match whats on the form, it will then give you the correct option and you run the query behind your button or you can then look at the SQL code and take that into your command button.

 

[andy_dyer]

If this going across 2 tables then look to create a delete query and take all the fields you want to show (not really necessary, but worth doing just to check) and set it to the id field criteria to match whats on the form, it will then give you the correct option and you run the query behind your button or you can then look at the SQL code and take that into your command button.

Thanks Trevor for the reply - but no the only record I want to delete is the single line in tblFinanceYear which just links to tblProject by ProjectID but I don't want to delete anything in tblProject just this one line in the Record Source table for the form.

The only thing I can think of is that I have three fields populated on the Current of the form two of these are unbound but the third is ProjectID and is bound... maybe that is why it won't delete?

I think I need the ProjectID bound tho to enable the link between the tables as a Foreign Key?

 

[Trevor G]

Ok then just do the query on the tblFinanceYear based on the projectid that should clear the record from the Finance Year table.

 

[andy_dyer]

Ok then just do the query on the tblFinanceYear based on the projectid that should clear the record from the Finance Year table.

Sorry I don't understand... what query?

And I did just try and make the ProjectID textbox unbound which did enable the record to be deleted but then also stopped any new records being attached to a project even though the project ID was displayed in the box still...

So I'll need to bound that again but I don't understand what you are referring to...

 

[Trevor G]

Andy,

Create a Delete Query for the table Finance Year and set the ID Field to match what you have on the form, when you run the delete query it will only delete the record in the Finance Table.

You then run the delete query as part of your code or convert the SQL code into the code behind the button.

 

[andy_dyer]

Thanks Trevor i've got the delete query working

 

[Trevor G]

Excellent news, thanks for letting me know.