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

alliedtechAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
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
    If (MsgBox("Are you sure you want to DELETE Bid # " & Me.sfrmBidInProcess.Form.BidNumber & "?", vbYesNo, "Delete Confirmation")) = vbYes Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("Delete * from tblBidLog where BidNum = " & Me.sfrmBidInProcess.Form.BidNumber & ";")
    DoCmd.SetWarnings True
    End If
   
       
End If
End If

End sub
0
 
omgangCommented:

If vbYes Then
    DoCmd.SetWarnings = False
    DoCmd.RunSQL ("Delete * from tblBidLog where BidNum = " & Me.sfrmBidInProcess.Form.BidNumber & ";")
    DoCmd.SetWarnings = True
End If
0
 
omgangCommented:
....story of my life......
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
alliedtechAuthor Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
<The record is deleted regardless of if I click Yes or No.>

did you use the codes that i posted?
0
 
alliedtechAuthor Commented:
capricorn....I had a slight error in the code.   Your code worked to perfections.

Thanks!
0
All Courses

From novice to tech pro — start learning today.