Sorry about the empty reply.
Tables are related by having a foreign key column (field) in one (The
Transactions table in your case) reference the primary key column in the
other (Master in your case). Client ID is the primary key of Master, so you
also need A Client ID column in Transactions, in the latter case indexed
non-uniquely (duplicates allowed). This should be separate from the
Transaction Number column which can be numbered from 1 upwards for each
Client ID. You should therefore also create a unique index on the ClientID
and Transaction Number columns, jointly on both columns that is, not
individually.
Rather than text it would be better to make these columns number data types
as this makes it easier to generate the numbers automatically as described
below.
A relationship between the tables should be created on the Client ID column
and referential integrity enforced. Cascade Updates should also be enforced
so that if for any reason a Client ID value in Master should be changed the
matching rows in Transactions would be updated automatically.
To generate the Client ID numbers in master automatically when a new
customer is added this can be done in the data entry form bound to the Master
table by putting the following code in the form's BeforeInsert event
procedure. This assumes that the data type has been changed to a number in
table design:
Me.[Client ID] = Nz(DMax("[Client ID]", "[Master]"),0) + 1
This looks up the highest existing value of Client ID and adds 1. If the
table is empty, the DMax method returns Null, so the Nz function converts
this to a zero, and the number for the first row thus becomes 1.
To automatically assign the correct Client ID value to a new row in
Transactions the usual approach would be to include a subform based on the
Transactions table in the form based on the Master table, linking them on
Client ID by making this the LinkMasterFields and LinkChildFields properties
of the subform control.
To assign the next sequential number per Client ID to the Transaction Number
column in Transactions when entering a new transaction record would be
similar to generating the Client ID for a new customer, but in this case the
DMax function needs a criterion limiting it to the transactions for the
current Client ID, so the the code for the subform's BeforeInsert event
procedure would be:
Dim strCriteria As String
strCriteria = "[Client ID] = " & Me.[Client ID]
Me.[Transaction Number] = _
Nz(DMax("[Transaction Number]", "[Transactions]", strCriteria),0) + 1
Using the DMax method like this is fine in a single-user environment, but
can give rise to conflicts in a multi-user environment on a network if two or
more users are adding new customer or transaction records simultaneously.
You'll find a simple solution to this by Roger Carlson at:
http://www.rogersaccesslibrary.com/...?TID=395&SID=83z7c11zc7b721d2a1e51989c53d7ffb
or a more complex one by me, which also allows the next number to be used to
be reset, at:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps
Although storing the Client ID and Transaction Number in two separate
columns in the Transactions table, you can of course always return them as a
single value in a computed column in a query or a compute control in a form
or report using the expression [Client ID] & "-" & [Transaction Number]
It goes without saying that before you start amending your tables in this
way its imperative to back up the database.
Ken Sheridan
Stafford, England
