Need to check another table first

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A form allows the user to delete a manager from tblManager.
This causes problems if the managerID exists in tblPeople (that is, the manager is still managing people)
I'd like to use a select statement (please help with format) to
select peoNameFull from tblPeople where (tblPeople.mgrID = cboSelectManager.value)
or something of that ilk.
If anyone is retrieved, I need to cancel the delete, so I just need a yes/no or null/not null from the select.
If there is a better way to do it, I'm open to suggestions.
Thanks.
 
Almost there! In the test case, iMgr = 2, which is correct, and should fail the delete, as he is managing 1 person.
Unfortunately i = 0 and goes down to the error exit, not through the if statement.
Therefore the DCount statement is not executing correctly. I have looked it up, and it looks like it will be quite handy once I get it going.

Private Sub cmdDeleteRecord_Click()
On Error GoTo Err_cmdDeleteRecord_Click
' from Howard Brody on the Microsoft Access Forms coding help page
' Declare variables
Dim iMgr As Integer 'holds manager ID
Dim i As Integer 'holds count of people for that manager

iMgr = Form_frmManagers.cboSelectMgrName.Value
' Count how many people the manager still has assigned to them
i = DCount("[peoPeopleID]", "tblPeople", "[mgrManagersID] = '" & iMgr & "'")

' If no people assigned, delete the manager
If i = 0 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.Close 'frmManager
DoCmd.OpenForm "Switchboard"
' otherwise, display an error message and end the sub
Else
MsgBox "There are still people assigned to this manager. Please " _
& "unassign them and then try again.", , "Oops!"
'<< Here is where you can automatically go to the UnassignPeople screen >>
End If

Exit_cmdDeleteRecord_Click:
Exit Sub

Err_cmdDeleteRecord_Click:
MsgBox Err.Description
Resume Exit_cmdDeleteRecord_Click
End Sub
 
Back
Top