Data Validation check

[Lynn]

I have a continuous subform with 2 fields: Company and
CompanyType which relates back to the client.

The CompanyType can either be TypeA or TypeB. A client can
have multiple Companies listed but only one company can be
designated as TypeA. All other companies choosen will
either be CompanyType IsNull or CompanyType = TypeB.

How do I check to see if the user has already designated
one of the companies in the subform as TypeA. For example,
if a user enters in the following:

Record 1: Company = ABC and CompanyType = TypeB
Record 2: Company = Access and CompanyType = TypeA
Record 3: Company = KU and CompanyType = TypeA

The user should get a msgbox on entering Record 3 stating
only one company can be TypeA and then it should set the
focus back to the CompanyType field deleting TypeA to
allow them to either leave the type blank or to choose
TypeB.

Thanks, hope this made sense.

 

[Jonathan]

-----Original Message-----
I have a continuous subform with 2 fields: Company and
CompanyType which relates back to the client.

The CompanyType can either be TypeA or TypeB. A client can
have multiple Companies listed but only one company can be
designated as TypeA. All other companies choosen will
either be CompanyType IsNull or CompanyType = TypeB.

How do I check to see if the user has already designated
one of the companies in the subform as TypeA. For example,
if a user enters in the following:

Record 1: Company = ABC and CompanyType = TypeB
Record 2: Company = Access and CompanyType = TypeA
Record 3: Company = KU and CompanyType = TypeA

The user should get a msgbox on entering Record 3 stating
only one company can be TypeA and then it should set the
focus back to the CompanyType field deleting TypeA to
allow them to either leave the type blank or to choose
TypeB.

Thanks, hope this made sense.

Hi Lynn,

there is many ways to acheive this, here's one. In the
CompanyType_BeforeUpdate(Cancel) event check if the value
entered is type a. If 'true' then use either a dcount
function (use online help for an example) or sql and
recordset to return the count of existing records with
type a. If return count>0 then set cancel = true.

Luck
Jonathan