Delete Button Code needed !

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Before I can delete a client out of my SubForm I want a code to check that
it has at least 1 other client other wise you can not delete the last client
connected to that horse
Table is: tblHorseDetails
The 3 fields are OwnerID , HorseID & OwnerPercentage
What I want is when I delete OwnerID from that HorseID (I need a code to
check that, that HorseID has at least 1 OwnerID) (Never Mind about
Percentage)
What I am trying to do is to stop a Horse having no OwnerID because it
causes HAVOC later on
----------------------------------------------------------------
Current Dode:
Private Sub Command13_Click()
On Error GoTo Err_Command13_Click
Dim nRtnValue As Integer
nRtnValue = MsgBox("Are you sure you want to ((Delete)) this Client ?" &
vbCrLf & vbCrLf & "Horses must have at least one Client!", vbCritical +
vbYesNo, "Delete Client")
If nRtnValue = vbYes Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click
End If

End Sub
 
Your tblHorseDetails sounds like it's a properly designed, relational,
junction table. Thus, I assume you want to delete the entire record, not
just the OwnerID field's value, is that right?

Assuming that this is true, you could use DCount function to identify that
there are at least two records in that table with the same HorseID value
before you allow the deletion:

Private Sub Command13_Click()
On Error GoTo Err_Command13_Click
Dim nRtnValue As Integer
If DCount("*", "tblHorseDetails", "HorseID=" & Me.HorseID.Value) _
< 2 Then
MsgBox "You cannot delete this record because the horse " & _
"has just one owner!", vbExclamation, "Cannot Delete"
Else
If vbYes = MsgBox("Are you sure you want to ((Delete)) this Client ?" &
vbCrLf & vbCrLf & "Horses must have at least one Client!", vbCritical +
vbYesNo, "Delete Client") Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If
End If

Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click
End If

End Sub
 
Ken I don't mind if the horse is deleted with any amount of owners, I just
want to prevent a horse having no owner assigned to it, At the moment you
cant set up a horse without an owner but maybe later on you might delete the
only owner and forget to put in new owner.
I have this Code assigned to my close form button:
Private Sub cmdClose_Click()
If IsNull(Me.subHorseDetailsChild.Form.OwnerID) Then
If Me.Dirty Then
Me.Undo
End If
If IsNull(Me.subHorseDetailsChild.Form.OwnerID) Or _
(IsNull(tbName) And IsNull(cbFatherName)) Then

If Me.Dirty Then
Me.Undo
End If

End If
End If

DoCmd.Close acForm, Me.Name

End Sub

Thanks for any help....Bob

Ken Snell (MVP) said:
Your tblHorseDetails sounds like it's a properly designed, relational,
junction table. Thus, I assume you want to delete the entire record, not
just the OwnerID field's value, is that right?

Assuming that this is true, you could use DCount function to identify that
there are at least two records in that table with the same HorseID value
before you allow the deletion:

Private Sub Command13_Click()
On Error GoTo Err_Command13_Click
Dim nRtnValue As Integer
If DCount("*", "tblHorseDetails", "HorseID=" & Me.HorseID.Value) _
< 2 Then
MsgBox "You cannot delete this record because the horse " & _
"has just one owner!", vbExclamation, "Cannot Delete"
Else
If vbYes = MsgBox("Are you sure you want to ((Delete)) this Client ?" &
vbCrLf & vbCrLf & "Horses must have at least one Client!", vbCritical +
vbYesNo, "Delete Client") Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If
End If

Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click
End If

End Sub
Click to expand...
 
Actually this is where I have a problem with a horse that does not have a
Owner, When I go to distribute all my holding Invoices the DB jams when It
comes to a horse with no owner that has been DELETED
out...............Thanks Bob
Private Sub cmdDistributeAllInvoices_Click()
Dim nRtnValue As Integer

nRtnValue = MsgBox("Are you sure you want to Distribute All Invoices?" &
vbCrLf & vbCrLf & "If you choose Yes, all the Invoices will have Invoice
Numbers.", vbCritical + vbYesNo + vbDefaultButton2, "Distribute all
Invoices")
If nRtnValue = vbYes Then
DoCmd.Hourglass True
subSetInvoiceValues
Application.SysCmd acSysCmdSetStatus, "Process is completed."
Application.SysCmd acSysCmdClearStatus
DoCmd.Hourglass False
Me.lstModify.Requery


End If
End Sub

Bob said:
Ken I don't mind if the horse is deleted with any amount of owners, I just
want to prevent a horse having no owner assigned to it, At the moment you
cant set up a horse without an owner but maybe later on you might delete
the only owner and forget to put in new owner.
I have this Code assigned to my close form button:
Private Sub cmdClose_Click()
If IsNull(Me.subHorseDetailsChild.Form.OwnerID) Then
If Me.Dirty Then
Me.Undo
End If
If IsNull(Me.subHorseDetailsChild.Form.OwnerID) Or _
(IsNull(tbName) And IsNull(cbFatherName)) Then

If Me.Dirty Then
Me.Undo
End If

End If
End If

DoCmd.Close acForm, Me.Name

End Sub

Thanks for any help....Bob
Click to expand...
 
I need to clearly understand -- are you deleting an entire record? or are
you deleting the value in the OwnerID field of the record?

--

Ken Snell
<MS ACCESS MVP>


Bob said:
Ken I don't mind if the horse is deleted with any amount of owners, I just
want to prevent a horse having no owner assigned to it, At the moment you
cant set up a horse without an owner but maybe later on you might delete
the only owner and forget to put in new owner.
I have this Code assigned to my close form button:
Private Sub cmdClose_Click()
If IsNull(Me.subHorseDetailsChild.Form.OwnerID) Then
If Me.Dirty Then
Me.Undo
End If
If IsNull(Me.subHorseDetailsChild.Form.OwnerID) Or _
(IsNull(tbName) And IsNull(cbFatherName)) Then

If Me.Dirty Then
Me.Undo
End If

End If
End If

DoCmd.Close acForm, Me.Name

End Sub

Thanks for any help....Bob
Click to expand...
 
Thanks Ken, You are deleting a OwnerID from HorseID
this is a qry that shows how it is set up, what I want is that HorseID has
at least 1 OwnerID
Thanks Bob
SELECT qHorseNameSourceAddMode.HorseID, tblOwnerInfo.OwnerID
FROM (qHorseNameSourceAddMode INNER JOIN QryOwnerHorsePercent ON
qHorseNameSourceAddMode.HorseID=QryOwnerHorsePercent.HorseID) INNER JOIN
tblOwnerInfo ON QryOwnerHorsePercent.OwnerID=tblOwnerInfo.OwnerID;
 
I apologize, but I don't understand how you delete an OwnerID from a HorseID
field? What is the SQL statement of the qHorseNameSourceAddMode query? I'm
still not understanding if you want to delete an entire record from
tblHorseDetails table, or if you just want to delete the value that is in
the OwnerID field in a single record in the tblHorseDetails table?

--

Ken Snell
<MS ACCESS MVP>
 
Thanks Ken deleting the HorseID takes OwnerID away too, that's not really an
issue it can not effect my DB, But if HorseID does not have a least 1
ownerID it causes Havoc.
Basically its set up like this:
SELECT tblHorseDetails.OwnerID, tblHorseDetails.HorseID,
tblHorseDetails.OwnerPercent
FROM tblHorseDetails;
Thanks.Bob
 
Back
Top