Acc97: Combining unique's

[noodnutt]

G'day ppl,

Here's my dilemma, the db is for a boarding kennel.

I am trying to combine the owners uniqueID with each of their animals e.g

Client1 has 2 dogs, so in order to identify their dogs, I need something
like 1:1, 1:2 etc

Client69 has 12 dogs, eg
69:1 thru to 69:12

this way each animal will be tied directly to it's owner in a round-about
way, question is, how can I automate it so that when I come to entering the
animals details, this number will generate.

P.S. I already pass the owners ID automatically when the animal form opens

TIA

Mark

 

[Tim Ferguson]

I am trying to combine the owners uniqueID with each of their animals e.g

Client1 has 2 dogs, so in order to identify their dogs, I need something
like 1:1, 1:2 etc

Try googling for Access Custom Autonumbers.

The method is to have two separate fields called, say, OwnerID and
DogNumber. You can find the next vacant dognumber by calling

DMax("DogNumber", "Dogs", "OwnerID=27")

This will return the largest-so-far for this particular owner, or else a
Null if there are no dogs registered to him or her. You can add 1 to the
number in order to get the next one. You have restrict all data entry to a
form, because there is no way to get code to run if a dog is added using
VBA, Excel, querydef, etc.

I would be a little suspicious of the method, though. What happens when Reg
passes or sells his dog to Wendy - does the dog change from 23:2 to 17:12?
What happens to all the dependent tables with Foreign Key values pointed at
the old dog number? Still, you know your business rules!

All the best


Tim F

 

[noodnutt]

Thx heaps for your thoughts Tim.

With regard to changing ownership, that won't be an issue, as this DB only
relates to clients boarding their animals whilst they are away eg holidays
etc... the DB is still a work in progress, and will take a while as I am
nursing a broken arm.

best wishes for the upcoming holiday season.

regards

Mark.

 

[Tim Ferguson]

the DB is still a work in progress, and will take a while as I am
nursing a broken arm.

Nah.. only takes one hand to type!

Hope you get better soon. Have a good Christmas.


Tim F