MessageBox Based on Recordset Count

[Two Gun]

I have a form that, among other data, includes a text field called "position number" that identifies each of many positions in our organization.



Most of our positions have only one person assigned to it, however, there are a few that have multiple people assigned. When the form is opened (based on the position number), if there is more than one person assigned to the position number, I'd like to give my users a quick warning message: "There is more than one person assigned to this position", or something similar. Just to give them a heads up.

Can something like this be done with a macro or only with VBA? With my limited background, I'm not sure how the "count" a recordset behind a form.

[aytee111]

Many ways. One way, although quite a slow command, is to use Dlookup.

Such as
Dim CountP AS Integer
CountP=DLookUp("Count(people)","tablename","Positi on=" & positionnumber)
MsgBox "There are " & CountP & "People for this position"

[June7]

How can multiple bodies occupy the same position? Job sharing? I understand many people can have same job description but a position should have only one person.

You can use DCount function in a textbox or in VBA code or in a query.

In textbox ControlSource have expression like:
=DCount("[position number]", "[tablename]", "[position number]='" & [position number] & "'"

Is [position number] a text datatype? If it is not, remove the apostrophe delimiters.

[Two Gun]

Thanks guys. Both suggestions work great! Yes, some positions sharing common characteristics use the same position description, and thus, the same number. I wanted my users to be reminded of those conditions when they apply. Thanks again.