Help me out

[Unicon]

I have an employee Database with me with so many fields where there is one field name by sites containing more than 50 sites (drop down list) and I have a approved manpower numbers for each sites like eg. DIFC sites 50 manpower. I am feeding each employee record in database and allocating them in different sites. Is there any query or something which can help me like if I am feeding the information in Database like "ID no. 01234, Site DIFC, ID no. 02314, Site DIFC" when I am allocating the staffs for DIFC that time even I don't know how many manpower I am allocating So is there anything If I allocated more than 50 staffs it will automatically remind or warn me like "You cant accommodate more than 50 staffs in DIFC"

Looking for your favourable response.

 

[vbaInet]

Investigate the DCount function or have a look at recordsets. Once you get the total numbers after every new record, you can use an if statement to check if it's above 50 and display a message box accordingly.

 

[Unicon]

Thanks for the quick response. I know the Dcount function but there are more than 80 sites so how to put an If statement for all 80 sites.

Please advise.

 

[vbaInet]

Something like this maybe:

Dim allocationsCount as long

allocationsCount = DCount("AllocationsID", "NameOfTable_Or_Query", "[DIFC_Site] = '" & ControlValueToCheck & "'")

You could put in the BeforeUpdate event with a message box after:

If allocationsCount >= 50 then
    if Msgbox(allocationsCount & " allocations." & vbcrlf & vbcrlf & "Do you still want to save record?", vbYesNo + vbExclamation, "Save prompt") = vbNo Then
    Cancel = True
End if

Is this what you're after?

 

[Unicon]

Thanks for the response but I am having more than 80 sites so how to put those whole sites in if statement.

Please advise.

 

[vbaInet]

I explained that above. Before an employee is saved, it will filter the record for the current DIFC and current employee, then count how many employee IDs it finds.

Amend to my code:

Dim allocationsCount as long

allocationsCount = DCount("AllocationsID", "NameOfTable_Or_Query", "[DIFC_Site] = '" & ControlValueToCheck & "' AND [EmployeeID] = " & ControlValueToCheck)

Lookup DCount() function.

 

[Unicon]

Hi thank you very much for the response. I got it but I have more than 80 different sites like DIFC. As per the statment it will filter DIFC only. So what about other sites which is in my sites list? how to put if statement for all sites. I want to check for all sites whatever I have in my database.

Looking for favourable response as always.