Form / SubForm <Delete Record> "Enter Parameter Value"

[Randomblink]

Ok...

I have a BANK form...
it holds bank data (bank name, address, etc.)

It has a Branch Sub Form...
This holds Branch location and contact information...

"IT" has an Employee Sub Form...
This holds Employee listing for that Bank...

Each Form / Subform has a List Box.

The Employee Subform List box Shows ALL employees for that BRANCH of that BANK...

The Branch Subform List Box Shows ALL Branches for that BANK...

The Bank Form List Box Shows ALL Banks...

The Problem I am having is...
If I create a Bank... whether I create any Branches for it... or even any employees for those branches...

IF I DELETE that Bank... I get "Enter Parameter Values" from the SQL code the SubForm Listboxes use as RowSources...

I get TWO "Enter Parameter Values" since I refresh each Listbox on each Form / Subform in the OnCurrent event for ALL of the forms...

What can I do to stop this? If you can help, it would be appreciated... Thanks...

 

[SforSoftware]

Hi,

I'd think that you first have to delete all employees and Branches (with a delete-query), before you can delete the Bank?

 

[Randomblink]

They are Linked Tables...

The Tables they are built from are all linked together...

Table: tbl_Bank
<primary key>: bnk_ID
<link field>: -none-

Table: tbl_Bank_Branch
<primary key>: brnch_ID
<link field>: bnk_ID

Table: tbl_Bank_Employees
<primary key>: bnk_empl_ID
<link field>: brnch_ID

These are their linked fields...
They are One-to-Many links...
Cascade Updates / Deletes...

Help if you can... thanks...

 

[SforSoftware]

Hi,

Create a New Query and change to SQL-mode. Delete everyting and then copy-paste this:

DELETE tbl_Bank.*, tbl_Bank_Branch.*, tbl_Bank_Employees.*, tbl_Bank.bnk_ID
FROM tbl_Bank INNER JOIN (tbl_Bank_Branch INNER JOIN tbl_Bank_Employees ON tbl_Bank_Branch.brnch_ID = tbl_Bank_Employees.brnch_ID) ON tbl_Bank.bnk_ID = tbl_Bank_Branch.bnk_ID
WHERE (((tbl_Bank.bnk_ID)=[Forms]![frmBank]![txtBank_ID]));

You'll only have to change "[Forms]![frmBank]![txtBank_ID]" in your names for Form and for the control that contains the Bank_ID on that form. Then save your query. On the form you can put a button that runs this query. It will delete the Bank, all related Branches and all related Employees.

Important!! Try this first in a copy of the original database!!!

 

[Randomblink]

Ok... I should prolly preface this...

I use a custom button manager I am in the middle of designing for all the databases I build... So... when I press the delete button, here is the function I call...

Public Function btnBasics(curForm As Form, curButton As Label, Optional rqstdObj As Object)
Dim recNew As Boolean, deleteQuestion, btnOption As String, frmTarget As Form
Dim frmScope As String, strTarget As String, btnName As Integer, rCount As Integer
Dim testDebug As String
On Error GoTo Err_btnBasics

recNew = curForm.NewRecord

GoSub CheckTheButton

btnOption = Mid(curButton.Name, 5, 9)
Select Case btnOption
    Case "DelCurRec": GoSub DelCurRec
    Case "AddNewRec": GoSub AddNewRec
    Case "PrtCurRec": GoSub PrtCurRec
    Case "SavCurRec": GoSub SavCurRec
    Case "GotFrsRec": GoSub GotFrsRec
    Case "GotNxtRec": GoSub GotNxtRec
    Case "GotPrvRec": GoSub GotPrvRec
    Case "ClsCurFrm": GoSub ClsCurFrm
    Case "ClsPopFrm": GoSub ClsPopFrm
    Case "OpnRegFrm": GoSub OpnRegFrm
    Case "OpnPopFrm": GoSub OpnPopFrm
    Case "OpnLnkFrm": GoSub OpnLnkFrm
End Select

Exit_btnBasics:
Exit Function

OpnRegFrm:
    DoCmd.OpenForm curButton.Tag
    Set frmOpener = curForm
    Return

OpnLnkFrm:
    Dim stLinkCriteria As String

    stLinkCriteria = "[" & CStr(curForm![mnIdentifier].ControlSource) & "]=" & curForm![mnIdentifier]
    DoCmd.OpenForm frmScope, , , stLinkCriteria, , , curForm![mnIdentifier]
    Set frmOpener = curForm
    Return

OpnPopFrm:
    DoCmd.OpenForm frmScope, , , , , , curForm![mnIdentifier]
    Set frmOpener = curForm
    Return

UndAllAct:
    Dim ctlC As Control
    ' For each control.
    For Each ctlC In curForm.Controls
        If ctlC.ControlType = acTextBox Then
        ' Restore Old Value.
            ctlC.Value = ctlC.OldValue
        End If
    Next ctlC
    Return

ClsPopFrm:
    
    If curForm.Dirty Then DoCmd.RunCommand acCmdSaveRecord
        
    Select Case frmOpener.Name
        Case Is = "": GoSub ClsCurFrm
        Case Is <> "":
            With frmOpener
                .Requery
                .Repaint
            End With
            GoSub ClsCurFrm
    End Select
    Return

ClsCurFrm:
    DoCmd.Close acForm, CStr(curForm.Name), acSaveYes
    If Forms.Count = 0 Then Application.Quit acQuitSaveAll
    Set frmOpener = Nothing
    Return

SavCurRec:
    DoCmd.RunCommand acCmdSaveRecord
    curForm.Repaint
    Return

PrtCurRec:
    DoCmd.RunCommand acCmdSaveRecord
    Select Case rqstdObj.Name
        Case "Invoice": DoCmd.OpenReport "rptInvoiceRoutingSlip", , , "[Pprwrk_ID] =" ' &  Form_subfrm_Paperwork.Pprwrk_ID  from a Different db
        Case "RFA": DoCmd.OpenReport "rptRFATrackingSlip", , , "[Pprwrk_ID] =" ' & Form_subfrm_Paperwork.Pprwrk_ID          from a Different db
    End Select
    Return

DelCurRec:
    deleteQuestion = MsgBox("Are you sure you want to delete the current " & curForm.Tag & "?", vbYesNo, "Delete " & curForm.Tag)
    Select Case deleteQuestion
        Case vbYes
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.GoToRecord , , acFirst
        Case vbNo
            MsgBox "This Record was NOT deleted.", vbOKOnly, "Canceled Delete"
    End Select
    deleteQuestion = Null
    Return

AddNewRec:
    recNew = curForm.NewRecord
    Select Case recNew
        Case Is = True:
            Select Case curForm.Dirty
                Case True:
                    DoCmd.RunCommand acCmdSaveRecord
                    DoCmd.GoToRecord , , acNewRec
                Case False:
                    MsgBox "You are currently in a new record already."
            End Select
        Case Is = False: DoCmd.GoToRecord , , acNewRec
    End Select
    Return

GotFrsRec:
    If recNew = True Then Return
    DoCmd.GoToRecord , , acFirst
    Return

GotPrvRec:
    If recNew = True Then Return
    DoCmd.GoToRecord , , acPrevious
    Return

GotNxtRec:
    If recNew = True Then Return
    DoCmd.GoToRecord , , acNext
    Return

CheckTheButton:
    btnName = Len(curButton.Name) ' First we check the calling button for it's length
    rCount = (btnName - 14)
    Select Case rCount
        Case Is <= 0: Return
        Case Is >= 7
            frmScope = Right(curButton.Name, rCount)
    End Select
Return

Err_btnBasics:
    errMessage Err
    Resume Exit_btnBasics

End Function

I have a label...
It is called: btn_DelCurRec
So, when I click on this label, it runs the code above, because I call it like so:

btnbasics Me, btn_DelCurRec

I may just use what you offer for that ONE single button... But I would rather find a universal way to make this work...

My goal being to eradicate the stupid wizards for each button and run all buttons from one function... Then, when this is fixed, I can port it to a VB Project I wanna do... In the meantime, this is what I am working from...

So, I am sure you can see, for doing what I want, how I am wanting to do it... Your suggestion doesn't make that happen...

I do understand how to build the SQL commands, however, for modularity's sake, I cannot use your option... I REALLY appreciate the attempt... I am looking for a route that utilizes what I am working with... Thank you tho...

Everyone else... If you can help, please let me know...

 

[SforSoftware]

OK, (it would be fine if you'd told in your first post that it had to be a general function)

It can be discussed to work in this general way. Me personally it's going further than might be useful, but don't mind.

I'm thinking about two options:
1. Create an extra Optional argument for the function btnBasics, in which you can specify some Options (like the SQL-statement, or the name of the query to run).
2. When you use the right options for Referential Integrity within the two links between the three tables, all related records are deleted when you delete that from the first level (that's what you actually want to do, because you want to delete the Bank and all related records?). Maybe you got a msgbox to confirm that you want to delete all records. This can be ignored by using the SetWarnings:
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True