Deleting or not deleting record 1

[cneill]

Hi Everyone,
I am trying to do the following but can't seam to get it right, part of it works but not all, any guidance would be greatly received.

On the Subform which is in Datasheet view
in the OnDelete event, I have the following code
Private Sub Form_Delete(Cancel As Integer)

If DLookup("[CostsID]", "QryFinanceRecordNotes") > 0 Then
MsgBox ("There have been Notes added to this record, please delete the notes first before Deleting the Record"), vbInformation, "Hi Just to let you know"
Exit Sub
End If
End Sub

This is fine, but it still triggers the prompt to delete the record, so I added

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

Cancel = True
DoCmd.CancelEvent
End Sub

This stops the record from being deleted.
My problems is as follows

If I want to check that there are no Notes added to this record
I want to be able to delete the record
Could someone guide me on how to modify the code to check that there are no Notes so that I can Delete the record?

Thanks

CNEILL

 

[TheAceMan1]

How are ya cneill . . .

In the following line ...

[blue]If DLookup("[CostsID]", "QryFinanceRecordNotes") > 0 Then[/blue]

... your not checking if notes have been added. Your checking if [blue]QryFinanceRecordNotes[/blue] returns any records at all. Your check should be something like ...

[blue]If Trim(Me.[purple][b]YourNotesFieldName[/b][/purple] & "") <> "" Then[/blue]

If your going to do this, better would be ...

[blue]If Trim(Me.[purple][b]YourNotesFieldName[/b][/purple] & "") <> "" Then
   MsgBox "There have been Notes added to this record, " & _
          "please delete the notes first before Deleting the Record", _
          vbInformation, _
          "Hi Just to let you know"
    Cancel = True
 End If[/blue]

However the question arises:

[blue]If your going to delete a record with a note ... why delete the note first?[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[cneill]

Hi TheAceMan1

I am well thanks
Understand you suggestion about deleting the note first, which I could do, but giving them a message first, allows them to double check that they have selected the right record to delete.

Okay I presume you put the Code in the "On Delete" event
The Notes Field Name is on a Sub Form of the main form, so tried
If Trim(Forms![FrmSchemes]![FrmFinanceRecordNotes].Forms.WNotes & "") <> "" Then
MsgBox "There have been Notes added to this record, " & _
"Please delete the notes first before Deleting the Record", _
vbInformation, _
"Hi Just to let you know"
Cancel = True
End If

and

If Trim(Forms![FrmSchemes]![FrmFinanceRecordNotes].WNotes & "") <> "" Then
MsgBox "There have been Notes added to this record, " & _
"Please delete the notes first before Deleting the Record", _
vbInformation, _
"Hi Just to let you know"
Cancel = True
End If

I am getting an error message "Object doesn't support this property or method, There is a error in the If Line

Any thoughts

Thanks

CNEILL

 

[TheAceMan1]

[blue]On the Subform which is in Datasheet view
in the OnDelete event, ...[/blue]

If your talking the [blue]On Delete[/blue] event of the subform it should be:

[blue]If Trim(Me.WNotes & "") <> "" Then[/blue]

I'm not sure why you show code as if it were running from the mainform ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[cneill]

Hi TheAceMan1

I think I am confusing you and me
Form Structure
Main Form - FrmSchemes
SubForm - FrmSchemesDataSubForm - This were the records are that may need to be deleted

Second SubForm - FrmFinanceRecordNotes on Main Form - FrmSchemes - this is where the notes are held which are connected to the FrmSchemesDataSubForm
So when you select a record on the FrmSchemesDataSubForm, if there are any notes they will be shown on the FrmFinanceRecordNotes

So when I want to delete a record from the FrmSchemesDataSubForm I what it to check there are no notes on the FrmFinanceRecordNotes

So I have put the "on Delete" code in the FrmSchemesDataSubForm

This is why I am trying to reference the WNotes field on the FrmFinanceRecordNotes
Hope this makes sense now, so that you have a clearer picture.

Any Thoughts

CNEILL

 

[cneill]

Hi TheAceMan1

I have fixed the referenceing problem, the working code looks like this

If Trim(Forms!FrmSchemes!FrmFinanceRecordNotes.Form!WNotes & "") <> "" Then
MsgBox "There have been Notes added to this record, " & _
"Please delete the notes first before Deleting the Record", _
vbInformation, _
"Hi Just to let you know"
Cancel = True
End If

Now not all the records on the FrmSchemesDataSubForm have Notes on the FrmFinanceRecordNotes
So if there are no Notes. I what to be able to delete the record

How do I modify the code to allow this option

Thanks

CNeill

 

[TheAceMan1]

cneill . . .

Your post [blue]5 Sep 11 14:49[/blue] should've been your post origination! ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[cneill]

Hi TheAceMan1,

Just to let you know I fixed the code
Code as follows:-
Private Sub Form_Delete(Cancel As Integer)

Dim rs As DAO.Recordset
Dim sMsg As String
Dim lRecordCount As Long

Set rs = Forms!FrmSchemes!FrmFinanceRecordNotes.Form.RecordsetClone
If Not (rs.EOF) Then
rs.MoveLast
End If

lRecordCount = rs.RecordCount
If lRecordCount >= 1 Then
MsgBox "There have been Notes added to this record, " & _
"Please delete the notes first before Deleting the Record", _
vbInformation, _
"Hi Just to let you know"
Cancel = True
End If
End Sub

So when If lRecordCount (is not) >= 1 Then
the Cancel = True is bypassed and the record is deleted.

Thanks for all your help.

CNEILL

 

[TheAceMan1]

cneill . . .

I could let the thread go, but I'm along way from agreement in your final code (not trying to rain on your parade). I don't think you really have it. Granted ... it may appear to be so ... but thats the issue!

[blue]Your talking about deleting a specific record ... not the last record in a recordsetClone![/blue]

I couldn't rely on the last record of a clone being the last entered ... at least not logically ... as the user could select a previously saved record ... yes/no?

My point is ... you should be specifically looking at the record selected for deletion instead of the entire recordsource of the form (as in the final code you presented). I expect you'll be back for future problems with this.

If your satisified then no return post is required. Otherwise I need to know the [blue]relationships[/blue] between the tables as a start.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[cneill]

TheAceMan1

I agree with you thoughts and yes the user could select any of the saved records.
All I need to do is simply to check if there is a record in the FrmFinanceRecordNotes, this would be linked to the record selected on the FrmSchemesDataSubForm (by the user), if there is, to stop the delete and give the user a message, if no record then allow the delete.

I am not an expert with code, but have tested it with as many senarios as I can think of, and it apears to do the job.

Yes agree it would be better to match the actural record selected using the CostID field (which is the linked field name between the two forms.
Would welcome you thoughts on how to do this.
I am not on my home PC today (where the Database is at present), so when I get home I will give you some details of the relationships and see if we can come up with a better solution.
I realy appreciate you time with this, as I would like to get it right so that I don't have any future problems.
Talk soon

CNEILL