asked on

Delete Subform Record

Main Form = frmManageBids
Sub Form = sfrmBidInProcess (Datasheet)
Sub Form Row Source = qryBidInProcess
qryBidInProcess Row Source = tblBidLog (PK = BidNum)

Goal:
1. User selects record (Bid Number) on Sub Form
2. User clicks "Delete Bid" command button (currently located on the subform)
3. Selected Bid Number is deleted from tblBidLog
4. Requery subform results

The current code results in prompt asking if I am sure I want to delete a row from the requested table. This prompt appears regardless of which answer (yes or no) I choose in the "delete confirmation" msgbox.

Any suggestions.

Private Sub cmdDeleteInProcess_Click()
Dim iCount As Integer
iCount = DCount("*", "qryBidInProcess")
If iCount = 0 Then
    MsgBox "There are currently no bids in process."
Else
If IsNull(Me.sfrmBidInProcess.Form.BidNumber) Then
    MsgBox "Please select a bid number."
Else        
    MsgBox "Are you sure you want to DELETE Bid # " &    Me.sfrmBidInProcess.Form.BidNumber & "?", vbYesNo, "Delete Confirmation"
    
    If vbYes Then DoCmd.RunSQL ("Delete * from tblBidLog where BidNum = " & Me.sfrmBidInProcess.Form.BidNumber & ";")
    
    
        
End If
End If
 
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

omgang

If vbYes Then
DoCmd.SetWarnings = False
DoCmd.RunSQL ("Delete * from tblBidLog where BidNum = " & Me.sfrmBidInProcess.Form.BidNumber & ";")
DoCmd.SetWarnings = True
End If

omgang

....story of my life......

alliedtech

ASKER

Thanks Capricorn...and omgang (even though you were late haha).

SetWarning worked, but i have a new problem which should be simple to fix....

The record is deleted regardless of if I click Yes or No.

Rey Obrero (Capricorn1)

<The record is deleted regardless of if I click Yes or No.>

did you use the codes that i posted?

alliedtech

ASKER

capricorn....I had a slight error in the code. Your code worked to perfections.

Thanks!