Data Entry Rules

[Barbara]

I am working on a database to record inwards and outwards mail and work in
progress for my workplace. The database has been in existance for a couple
of years but I want to make some changes.

My question is in regards to the Clients table. The fields are:

Client No: (An autonumber primary field).
Client Code: This is a code allocated by my workplace. The same code may be
used several times eg. for an individual, their business name, their spouses
name.
Last Name: (This is also used for a business name)
First Name: (Only if an individual)
Name Type: (A lookup field - Main or Sub)

Basically what I want to happen is that records could look like this:

1 ABC123 BLOGS, Joe Main
2 ABC123 BLOGS & Co Pty Ltd Sub
3 ABC123 BLOGS, Josephine Sub

I want there to have to be a "Name Type" Main for any one client code, but
only one. Any additional "Name Types" for that client code have to be Sub.
I would also like error messages to pop up telling the user "You must have a
main client for this code", or similar, or "This client already has a Main
Client, you must choose Sub", or similar.

Hope this makes sense. Can anyone help with how to make it happen?????

Thanks

 

[tina]

well, i don't think you can do it at the table level. you'd have to do it at
the form level, during data entry. and i'd recommend setting the NameType
values automatically with VBA, but leaving them open to be changed by the
user. yes, that does allow the user to potentially screw it up - but if you
don't, you'll wind up with the following scenario:

user enters

ABC123 BLOGS & Co Pty Ltd Main
ABC123 BLOGS, Joe Sub

then realizes that Joe Blogs should be the Main. but now the system won't
let the user change Main to Sub, because a Main record is required. and the
user can't change Sub to Main, because only one Main record is allowed.

leaving the values open to user change side-steps that problem - and you
can, at the very least, create a report that checks for record "groups" that
don't meet the business rules, so they can be fixed.

to set the NameType field in a data entry form, try adding the following
code to the ClientCode control's AfterUpdate event procedure, as

If DCount(1, "Clients", "ClientCode = '" & Me!ClientCode & "'") > 0 Then
Me!NameType = "Sub"
Else
Me!NameType = "Main"
End If

you can also set the NameType control's TabStop property to No. that way the
user won't tab into the control and accidentally change the pre-assigned
value.

the above solution is based on the assumption that when entering new
records, the Main record normally will be entered first.

hth

 

[peregenem]

well, i don't think you can do it at the table level.

It surely is possible by using two tables i.e. a FOREIGN KEY
relationship e.g.

CREATE TABLE MainClients (
client_num CHAR(6) NOT NULL PRIMARY KEY,
last_name VARCHAR(35) NOT NULL,
first_name VARCHAR(35) DEFAULT '{{NA}}' NOT NULL,
sex_code INTEGER DEFAULT 0 NOT NULL,
CHECK (sex_code IN (0, 1, 2, 9))
)
;
CREATE TABLE SubClients (
ID INTEGER IDENTITY NOT NULL UNIQUE,
client_num CHAR(6) NOT NULL
REFERENCES MainClients (client_num)
ON DELETE CASCADE
ON UPDATE CASCADE,
last_name VARCHAR(35) NOT NULL,
first_name VARCHAR(35) DEFAULT '{{NA}}' NOT NULL,
sex_code INTEGER DEFAULT 0 NOT NULL,
CHECK (sex_code IN (0, 1, 2, 9)),
UNIQUE (client_num, last_name, first_name, sex_code),
PRIMARY KEY (last_name, first_name, ID)
);

The OP mentioned last_name could apply to a person or an individual so
I've included a column for the the ISO standard sex codes where 0 =
'unknown' and 9 = 'legal person' e.g. a company. However, I suggest the
OP investigate whether 'subclassing' would be appropriate e.g. a
Persons table and a LegalPersons or Companies table, the keys to which
could be used in their MainClients and SubClients tables.