Click here to Skip to main content
16,017,231 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
i have an employee table which has a field named department.i want the users to receive a message that no more employees can be assigned to this department because a certain limit has reached.the users will be using forms in ms access to insert the details.is it possible to do so using vba?

What I have tried:

i have created a query which calculates the current no of employees in department.the limit lets say currently is 5,more than 5 employees in a department is not allowed.i tried validation criteria on table level but no luck there as the expression is wrong.
Posted
Updated 12-Oct-20 5:22am
Comments
[no name] 1-Oct-20 10:33am    
You run the Query from the Form before allowing the employee to "insert".
muneermohd9690 1-Oct-20 11:14am    
how can that be done?if required i can share the access database with you.can you guide me please.
[no name] 1-Oct-20 12:13pm    
Just show the code where you do the "insert" from the form.
muneermohd9690 1-Oct-20 13:46pm    
Option Compare Database
Option Explicit
Private msaved As Boolean



Private Sub btnsearch_Click()

End Sub

Private Sub Command198_Click()
msaved = True
MsgBox "new record added"
Me.Requery
End Sub

Private Sub Command199_Click()
Me.Undo
DoCmd.BrowseTo acBrowseToForm, "empdetailsform", , , acFormAdd
End Sub


Private Sub browse_Click()
Dim diag As Office.FileDialog
Dim item As Variant
Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.Title = "Please select an excel sheet"
diag.Filters.Clear
diag.Filters.Add "excel spreadsheet", "*.xls,*.xlsx"
If diag.Show Then
For Each item In diag.SelectedItems
Me.filename = item
Next
End If
End Sub



Private Sub Form_BeforeUpdate(cancel As Integer)
If msaved = False Then
cancel = True
Me.Undo
cancel = False
End If

End Sub

Private Sub Form_Current()
msaved = False

End Sub

Private Sub import_Click()
Dim FSO As New FileSystemObject
If Nz(Me.filename, "") = "" Then
MsgBox "Please select a file."
Exit Sub
End If

If FSO.FileExists(Nz(Me.filename, "")) Then
importexcelsheet.importexcelspreadsheet Me.filename, "empdetails"
Else
MsgBox "File not found."
End If
filename.Value = ""
End Sub

1 solution

thanks i have received a solution for this which is as below.i have created a global module which contains the below function to calculate the current number of employees

Option Compare Database
Option Explicit
Global hrcount As Integer
Global itcount As Integer

Public Function GetCount(department As String) As Integer

hrcount = DCount("[employee id]", "empdetails", "department='HR'")
itcount = DCount("[employee id]", "empdetails", "department='IT'")
End Function

on the form where you wan to restrict the selection or update or new record addition you use the below code depending on your criteria for department.for me the maximum number of allowed employees is 7

Private Sub Combo309_BeforeUpdate(cancel As Integer)

If Combo309.Value = "HR" Then
Call GetCount("HR")
If hrcount = 7 Then
MsgBox "you have already reached the limit, choose another department or will be assigned"
cancel = True
Me.Combo309.Undo
End If
End If
If Combo309.Value = "IT" Then
Call GetCount("IT")
If itcount = 10 Then
MsgBox "you have already reached the limit, choose another department or will be assigned"
cancel = True
Me.Combo309.Undo
End If
End If
End Sub
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900