Supress Enter Parameter Value Dialogue Box

[oxicottin]

Hello, I have a simple delete button that deletes the main record and records pertaining to it and it works except I get this Enter Parameter Value Dialogue Box that gives the error below:

Enter Parameter Value

Forms!frm_ShiftDay!frm_ShiftMachinesSubform.Form!frm_MachineOutputSubform.Form!cboProductID

Now I found that formula on my [frm_MachineOutputSubform] which it has to do with a cascading combo box using a query [qry_DependantLengthToProduct] that the cboProductID uses and for some reason when I delete the main record it gives that error BUT still deletes the record as expected. I think it has to do with its deleting some of the info that the combo relies on and then deletes all the info after.

Here is the delete button, what needs added to supress the Dialogue? Thanks!

Private Sub cmdDeleteShift_Click()
'--------------------------------------------------------------------------------------------------
'  Deletes entire current shift and its related records.
'--------------------------------------------------------------------------------------------------
    If IsNull(Me.ShiftDayID) Then
        MsgBox "There is no shift to delete!", vbInformation, "No Shift"
        Exit Sub
    End If

    If MsgBox("Are you sure you want to delete this entire Shift?" & vbCrLf _
            & "There is no way to recover these records if you say 'Yes'.", vbCritical + vbYesNo, "Confirm Deletion Of Shift") = vbYes Then

        DoCmd.SetWarnings False
        'Delete the record
        DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True

        'On Error Resume Next    'Ignore error if first record

        'Return to the previous record
        'DoCmd.GoToRecord , , acPrevious
    Else
    End If
End Sub

Thanks,
SoggyCashew.....

 

[lameid]

I think your issue is you have related records in a subform that you are orphaning... All timing. Just delete the related records first in code then the record in the main from. I'd use a query to delete the related records. You might also have to requery the subform after the delete to completely clean it up.

 

[oxicottin]

here's another weird thing if its the first main record then it will delete without error box but if its the second and so on then I get the Error box. I can't figure this puppy out....

Thanks,
SoggyCashew.....

 

[dhookom]

I agree with lameid. I typically create an SQL statement and execute it rather than the DoCmd stuff.

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Ok, I tried the following SQL and im getting a Run Time Error '3061' Too few parameters expected 1.

Dim strSql as String

strSql = "DELETE tblShiftDay.ShiftDayID " & vbCrLf & _
         "FROM tblShiftDay " & vbCrLf & _
         "WHERE (((tblShiftDay.ShiftDayID)=[Forms]![frm_ShiftDay].[ShiftDayID]));"

CurrentDb.Execute strSql

Thanks,
SoggyCashew.....

 

[PHV]

Assuming ShiftDayID is numeric:
strSql = "DELETE * FROM tblShiftDay WHERE ShiftDayID=" & Forms!frm_ShiftDay!ShiftDayID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[oxicottin]

Thanks it worked...

'--------------------------------------------------------------------------------------------------
'  Deletes entire current shift and its related records.
'--------------------------------------------------------------------------------------------------
Dim strSql As String

strSql = "DELETE * FROM tblShiftDay WHERE ShiftDayID=" & Forms!frm_ShiftDay!ShiftDayID

    If IsNull(Me.ShiftDayID) Then
        MsgBox "There is no shift to delete!", vbInformation, "No Shift"
        Exit Sub
    End If

    If MsgBox("Are you sure you want to delete this entire Shift?" & vbCrLf _
            & "There is no way to recover these records if you say 'Yes'.", vbCritical + vbYesNo, "Confirm Deletion Of Shift") = vbYes Then

      CurrentDb.Execute strSql
      Me.Requery
    Else
    End If

Thanks,
SoggyCashew.....

 

[oxicottin]

I just dont get why this works by using a sql than Docmd, Thoughts?

Thanks!

Thanks,
SoggyCashew.....

 

[lameid]

When you delete the record via the form on the main form, it apparently is clearing out the the Master Key field or changing it to null causing problems when it trys to cascade update the child form whose record is being deleted. When you delete via SQL all the data is changed and your form display updates when the change is detected which is not instantaneously.

My hunch is that you are deleting the record via SQL from the main form recordset since you are only performing one delete and relying on a cascade delete for the related records. I suggest you explicitly delete the child form records and then the main form to ensure avoidance of any sort of race condition (extremely rare if it is possible).

 

[TheAceMan1]

How are ya oxicottin . . .

[blue] ... here's another weird thing if its the [purple]first main record[/purple] then [purple]it will delete without error box[/purple] but if its the second and so on then I get the Error box.[/blue]

For databases I've designed, and for most ... I've always wound up using cascade delete ...

So . . . . . . as far as your concerned ... are you using [blue]cascade delete[/blue] in your relationships? ... this matters ...BIG!

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

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!