Prevent Delete of Donor Profile Record If SubForm Has Donation Records

[Bill6868]

I’m developing a database for a fundraising event.

I have a table for a profile of the donor (tblProfile) and another table for multiple donations this donor may give (tblDonations). One-to-many relationship.

My edit the profile form (frmEditDonorProfile) shows the donor (with all the profile data) and has a subform at the bottom which list all the donations made by this donor (subfrmDonations).

I have a delete command button at the top of frmEditDonorProfile, but I don’t want this delete code to execute if there are donation records in the subform.

How would I do this with a MsgBox coming up giving directions to the user? Of course if there are no records in the subform I’d like the user to be able to delete the donor record.

Any suggestions would be appreciated.

 

[Andrzejek]

If you have anything in the "list all the donations made by this donor " - dis-able Delete command button ([tt]cmdDelete.Enable = False[/tt])

If the list is ampty, no donation, [tt]cmdDelete.Enable = True[/tt]

Have fun.

---- Andy

 

[Bill6868]

Thanks Andy. How do I get the Main form to tell if there are any records in the subform? If there are no records in the subform then yes, Delete this profiole record, if there are records in the subform than no, do not delete this profile record...MsgBox "Do not delete this donor because donation records for the donor exist.

 

[Andrzejek]

I am a 'recordset guy', so what I would do would be something like: (code not tested)

Private Sub cmdDelete_Click()
Dim rst As Recordset

If vbYes = MsgBox("Are you sure you want to Delete this Donor?", vbYesNo + vbQuestion) Then

  rst.Open "Select * From tblDonations Where DonorID = " & intDonorID

  If rst.RecordCount = 0 then[green]
     'It is OK to Delete[/green]
     "Delete From tblDonors Where DonorID = " & intDonorID
  Else[green]
     'We have some donation for that Donor[/green]
     Msgbox("You cannot Delete this Donor, he/she gives us Money")
  End If

  rst.Close
End If

End Sub

I am sure there are many other ways to do it.

Have fun.

---- Andy

 

[Bill6868]

Thanks Andy. My syntax and VBA skills are not that great. Forget about the deleting part. My first step is how to determine if there are any records in the subform. Is there an easy way to write this? I tried your suggestion but just could not get it to work.


Main form is named frmEditDonorProfile and its table is tblProfiles
subform is named subfrmDonations and its table is tblDonation

If the subform has records then my delete code executes, if not then my delete code does not execute.

 

[MazeWorX]

This works because the default property of a subform control is its form property and the default collection of a form is its controls collection. It returns the value of a control located on the subform

If IsNull(Me.YourSubFormControlName("txtAcontrolwithavalue")) Then

'Delete code here

Else

MsgBox "Cannot delete this donor"
Exit Sub
End If

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Bill6868]

Thank you MazeWorX. This is getting close for me. I understand the If IsNull(Me.YourSubFormControlName refers to my sub form's name, but I can't figure out what ("txtAcontrolwithavalue")) refers to. Between the quotation marks I've tried inserting the sub form Rec ID number and other fields on the sub form, but no luck. If there are no donations associated with this donor the sub form is just blank with no records to display.

Would you be able to share further light on this. If I can get it to work, I will be able to use this in other projects.

bill6868

 

[MazeWorX]

how about
If Not IsNull(Me.YourSubFormControlName("Name of one of your controls")) Then

'Dont delete

else

'Delete

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

and it refers to the sub form control which holds the subform

In other words if i have a frmOrders which has a sub form named frmOrderDetails. On my main form i place a sub form control to house my frmOrderDetails and call it subOrders and on My frmOrderDetails i have a Textbox named txtID it would be

Me.subOrders("txtID")

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Bill6868]

MazeWorX - thanks. Still having problems. For now I'm just keeping it simple with the follow code:

Private Sub Command165_Click()

If Not IsNull(Me.subfrmDonCont("Text150")) Then
MsgBox "Not OK to Delete"
Else
MsgBox "Ok to delete"
Exit Sub
End If
End Sub

The name of the sub form control is subfrmDonCont (which is also the name of the sub form itself). Text150 refers to a text box I put in the header of the subform, it equals Count(*)

When I pull up the main form the count(*) number is there. If this donor has two donations then Text150 reads 2 and when I execute the code I get my MsgBox "Not Ok to delete".

However when I pull up a record where the donor has no donations, Text150 will be blank (or null) and there will be no records on the sub form. But I still get MsgBox "Not OK to delete"

Is there something I'm missing in the above code?

bill6868

 

[MazeWorX]

Private Sub Command165_Click()

    If Not IsNull(Me.subfrmDonCont("Text150")) Or Not Me.subfrmDonCont("Text150") = "" Then
        MsgBox "Not OK to Delete"
    Else
        MsgBox "Ok to delete"
        Exit Sub
    End If

End Sub

When validating data in a Text Box I as a rule always check for both an empty string and Null


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[TheAceMan1]

How are ya Bill6868 . . .

Try the following in your button (no need to look at a subform control):

[blue]   If Me.[[purple][B][I]yoursubformname[/I][/B][/purple]].Form.Recordset.RecordCount > 0 Then
      MsgBox "Can't Delete!"
   Else
      [green]'Your delete code here[/green]
   End If[/blue]

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

See Ya! . . . . . .

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

 

[Bill6868]

Thank you AceMan1 - this was the solution I was looking for:

If Me.[yoursubformname].Form.Recordset.RecordCount > 0 Then
MsgBox "Can't Delete!"

Else
'Your delete code here
End If

Worked perfectly!

Thank you Andrzejek and MazeWorX for getting in on the conversation. Much appreciated.

Fundraiser is for a good cause - a children's learning center. I'm donating my time to this project and I apprciate all the feedback. My Access learning curve just went up a notch.

bill6868

 

[Andrzejek]

Nice, full circle around, and pretty much back to my original suggestion, but I like TheAceMan1's idea better since you do not have to re-create a recordset that you already have

To MazeWorX's code - both Null and empty in one line

    If Me.subfrmDonCont("Text150")[blue] & "" [/blue]<> "" Then
        MsgBox "Not OK to Delete"
    Else
        MsgBox "Ok to delete"
        Exit Sub
    End If

Have fun.

---- Andy