Want to Assign an Auto Number when there is No Bldg #

[Huddle]

I have a form where the Building number is the primary key. Sometimes I have to enter data before a building number is assigned and I want to have the field AutoNumber until I get the building number. I don't want to have to manually enter the "no bldg number" because I don't want to keep track of the last "no bldg number" that was entered.



Example

Bldg 1 is # 349752 the field would read "349752"
Bldg 2 has no # the field would read "NN1"
Bldg 3 is #4445 the field would read "4445"
Bldg 4 has no # the field would read "NN2"

Is there a way to do this?

[ConneXionLost]

This might be an indication that the building number is not an ideal choice as a primary key in your table; especially if you intend to come back at a later date to change the number. You could use auto-number as the primary key, and set the building number as an attribute (or even a candidate key).

Cheers,