Data Validation Check

[Lynn_AccessUser]

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.

 

[pono1]

Use a combo box rather than a textbox in your subform and bind it to the company type field.

Create two simple tables: TblTypeAB, TblTypeB. In TblTypeAB create two records: Type A, Type B. In TblTypeB create one record: Type B.

In the got focus event of the combo write code that checks to see if Type A is already chosen for the current record. If it has been chosen, set the rowsource of the combo to TblTypeB -- if it hasn't, set the rowsource of the combo to TblTypeAB.

Roughly, something like this...

Dim intcount As integer
Dim lngFKID As Long

lngFKID = Me.FKID 'foreign key

intCount = DCount("CompanyID", "TblPurchases", _
	   "CompanyiD = 'Type A' And FKID =" & lngFKID)

If intCount = 1 Then
    Me.ComboName.Rowsource = "TblTypeB"
Else
    Me.ComboName.Rowsource = "TblTypeAB"
End If

Regards,
Tim

 

[Lynn_AccessUser]

What table and field should FKID refer to.

 

[Lynn_AccessUser]

I think I am now using the correct field for FKID. However, I keep getting the following error:

Run-time error 2001
You canceled the previous operation.

 

[Lynn_AccessUser]

I finally got it to work. I am using the PK and FK values so Type = 1 as opposed to 'Type A'

Thanks for your help!!