DCount function that compares to a combo box on a form

[sprtrmp]

lemme see if I can explain this

I have a table (tblLockerInfo) that contains some information about lockers (locker numbers, combinations, etc).there is a field in that table called "LockerNo" with numbers 1-60 that correspond to the 60 lockers I have available. easy enough.

there is another table (tblCrew) that contains information about employees, one of those things is the locker # that is assigned to them called "LockerAssigned"

I have a form (frmNewCrew) that is used to enter information about employees: as a new employee comes in, that person gets assigned a locker.
on my form, there is a combo box (cmbLockerNo) with a list of locker numbers 1-60 that the user would use to select a locker number to assign to that new employee.
in the real world, each locker can only be assigned once!!

I am trying to use a DCount function in the "Beforeupdate" event of cmbLockerNo that would run through the "LockerAssigned" field on the "tblCrew" table, and count the number of times (preferably none) that the locker number entered in cmbLockerNo appears in that field...

if the user selects "7" in the cmbLockerNo control, I want the dcount to count the number of times "7" appears in the "LockerAssigned" field on the "tblCrew" table, and if that number is greater than "0", I want to generate a message box (but, that part, I can handle)

just for the purposes of information: the "LockerAssigned" field on the 'tblCrew" table is indexed, (no duplicates allowed), and the data types for the locker number fields (on the respective tables) all all defined as numbers/long integers, if anybody needs to know that.

[ssanfu]

I would use an unmatched query to find the unassigned locker numbers.

A table with all of the locker numbers.
One query ("qryCrewLockers") to get the assigned lockers.
Another query ("qryNotAssigned")to join the table and query.

The query "qryNotAssigned" is the row source for the combo box.
The combo box afterupdate event has 1 line of code -> " Me.Recalc"

My example only uses 15 lockers, but you can use any number of lockers.....

[sprtrmp]

AAAHH, ok. I see where that is going, but it doesn't meet my needs, exactly...

one reason is that this is a new database, and the user will need to input lockers for employees that have already been assigned. in other words, there is still the possibility that the user could accidentally enter the same locker number twice while feeding the existing info into the new database.

trying to avoid a situation similar to that

[ssanfu]

and the user will need to input lockers for employees that have already been assigned

Don't understand that... input locker numbers for employees that have already been assigned a locker?????

Are you importing data into the tables?


Care to post your dB? It would be a lot easier to write a VBA solution.
Only need a few records..... change any sensitive data, do a "Compact & Repair", then Zip it.

[sprtrmp]

ALSE2.zipSeveral of the lockers have already been assigned, but so far, they haven't really been tracked. Once this db Is built, someone will have to input all of the numbers along with existing employee info. I am trying to make THAT process as idiot-proof as possible (we have a lot of those around here).

I am posting a stripped-down version of the db
thanks for the help

[ssanfu]

I changed the structure of the table "tblLockerInfo" - deleted the autonumber field and set the LockerNo field as the PK.
Changed the field type for "tblCrew.Locker_FK" to Long Integer.
Set up relationships - see the Relationships widow.

Created two queries like in my example dB.
Changed the combo box "cmbLockerNo" Row Source...

In code you have two lines

        DoCmd.Close acForm, "frmNewCrew", acSaveNo
        DoCmd.Close acForm, "frmNewCrew", acSaveYes

These parameters do not do what you think. They DO NOT save the data, they save changes to the form.
The syntax is
expression.Close(ObjectType, ObjectName, Save)

All you need is

        DoCmd.Close acForm, "frmNewCrew"
        DoCmd.Close acForm, "frmNewCrew"

From Help:

Save

Optional

AcCloseSave

A AcCloseSave constant that specifies whether or not to save changes to the object.

Does this meet your needs?