VBA to Delete a Record with Supporting Tables - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

VBA to Delete a Record with Supporting Tables

Richard Garber

14 months ago

I'm deleting a record in my main table but there are two supporting tables associated with the main record that could have data associated with that record. I wrote this and it seems to work good enough but my concern is if its good code or not.

This is in the OnClick property of a button on a form. The three tables effected are My_Records, Data_Pics and Data_Tags.

Details

Private Sub DeleteRecBtn_Click()

Dim RecsPics As Long
Dim RecsTags As Long
Dim Response As String

DelRecID = Forms!Fm_MyRecords.Addon_ID

Response = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Delete Record")

If Response = vbYes Then    ' User chose Yes, delete the record.
    DoCmd.SetWarnings False
    RecsPics = DCount("*", "Data_Pics", "addon_ID = [Forms]![Fm_MyRecords].[Addon_ID]")
    
    If RecsPics > 0 Then
        DoCmd.OpenQuery "Qry_Delete_from_DataPics"
    End If
    
    RecsTags = DCount("*", "Data_Tags", "addon_ID = [Forms]![Fm_MyRecords].[Addon_ID]")
    
    If RecsTags > 0 Then
        DoCmd.OpenQuery "Qry_Delete_from_Tags"
    End If
    
    DoCmd.SetWarnings True
    DoCmd.RunCommand acCmdDeleteRecord
    MsgBox "Delete routine is completed", , "Delete Record Completed"
    
Else    ' User chose No, exit subroutine.
    Exit Sub
End If

End Sub

Richard Rost @Reply

14 months ago

Just one thing that I would note, everything looks fine except if you're only looking to see if there are records or not, don't use DCount. Use DLookup because if it's DCount, it's got to count all of them, it will literally run down the entire set of records. So if there's 1000 records, it'll slow things down.

So if you only care if there's one or zero, just do a DLookup. If it returns a zero, we'll obviously use NZ, and if it returns any other number that means you do have some. But aside from that, this looks pretty solid.

	Richard Garber OP @Reply 14 months ago
Gee, I hadn't thought of that, SUPER glad I posted now!! Thanks Rich!

	Richard Rost @Reply 14 months ago
Richard Garber check out Quick Queries 36.

Richard Garber OP @Reply

14 months ago

I MADE IT TO THE BIG TIMES!!! Hollywood, here I come!! Already an overnight sensation, they're gonna love me! Might sport me a tuxedo next time I post.

But wait!! You followed me up with the most horrible news! I was gonna hang that video in a nice frame in my living room but now... geez. Very sorry to hear anything like that would happen to anybody. My younger brother by only a year, was mugged in San Diego and this is like 40 years ago. They just did an MRI on him because back then he suffered a concussion. He hasn't had migraines though, or at least I don't think.

I hope things get better for ya. Hard not to be bitter from something like that.

Richard Rost @Reply

14 months ago

Bitter, yes... especially when the whole thing was caught on video, and the DA still refused to press charges. In a nutshell, I was sucker punched and didn't even see it coming. But I'm not going to let it slow me down. I've got one working eye, and that's all I need. There are some benefits. I can sleep in a bright room with my good eye on the pillow and it's still dark to me. I'm actually BETTER at the shooting range (don't have to close one eye). It did take me a while to be comfortable riding a bike, driving a car, or climbing a ladder as I have poor depth perception now. Can't catch a ball to save my life - so one of my passions of playing softball is done. But I'm still holding out for a bionic eye. Come on... science! :)

	Sami Shamma @Reply 14 months ago
That is the spirit Richard Rost

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View