Data validation question

[mok44]

I want to make sure users flag one and only one address as home address. I am using "1" in AddressType field of tblAddress to indicate that the current record is a home address. If on a second record for the same person the user enters "1" in field AddressType I want to pop up an error message and put the focus back on the AddressType field for correction.
Thanks for help/directions.

 

[alr0]

Hi Mok,

This isn't too tough. Before adding a new address record or saving and edit you need a SQL statement to count the number of records for that individual with "1" in AddressType.

Hoping this helps get you started,

alr

 

[mok44]

Hi alr
I figured that much; do you mind helping with the sql statement, I tried Dcount function but did not get very far.
Thanks.

 

[alr0]

Hi Mok,

The following statement will count the number of records where the customerID=467 and the AddressType=1. Of course, you can make this a parameter query to substitute a variable for the number 467 used below.

SELECT tblAddress.CustomerID, tblAddress.AddressType, Count(tblAddress.CustomerID) AS CountOfCustomerID
FROM tblAddress
GROUP BY tblAddress.CustomerID, tblAddress.AddressType
HAVING (((tblAddress.CustomerID)=467) AND ((tblAddress.AddressType)="1&quot);

This can all be generated with an Access query and then copy the generated code anywhere you like. You need to click on Totals in the view menu or the sigma sign on the tool bar. Put AddressType once and CustomerID twice in the query grid. The Totals row should read "Group By" for all columns except one of the CustomerID columns.

Hoping this helps,

alr