How to create a incrementing text field tied to multiple tables?

[ThornofSouls]

Hello everyone!

I have a question for you guys.

This is a errand database where each client can have several errands and some errands are related to each one and other.


This is how i have thought about it.
Since each client can have several errands and and each errand is related to each one and other, then the client. I thought i need key which can work as a ClientErrandID, so i can tie the errands together. My idee is to use the ClientErrandID as an text auto incrementing field with a string like: (Errand-A-xxxx).
Lest say the range would be from (Errand-A-0000 to Errand-A-9999) And when the string hits "9999" at the end the "A" will switch to a "B" and the number counter would start over from "0000"

• How should the table relationship look like?
• When a user is adding a new errand to a client, how should the function work?
  
  
  
  • Should all the associated errands be created in the background and granted the same Errand_ID when the main errand is created?
  
  
• Can i create a incrementing text field with vba & queries?
  
  • How would the code structure look like?
  • Is there any examples on a incrementing text field with code available?

If you feel like i missed something, let me know and i add it

//ThornofSouls

[ranman256]

I think you are putting too much effort into ClientErrandID . Just set it to autonumber and be done. Theres no need to do Errand-A, Errand-B.
The system uses the key, it doesnt care. Humans dont need to know the key or see it, so the ERRAND-A is just more work youd have to do to get it right.
You would associate like Errands in a form and the keys would join in the background. (master-child forms)
no muss, no fuss.

[orange]

Thorn,
Access doesn't need the codification eg Errand-A-xxxx.
Access only requires a unique identifier for each record in the table.

I'm in agreement with ranman -- too much emphasis on ClientErrandID
You really need to focus on the "things" involved in your "business" and get all the relevant facts recorded and clarified. Then start to design the database. Too many people jump in trying to work with HOW before they understand WHAT. The analogy is to get a "blueprint/plan" before you start building.

Good luck with your project.

[ThornofSouls]

So i would be better of with an autonumber for each errand and tie it with the ClientID, righter den implement ClientErrandID.
Yes it would be much easier to use and each client can have multiple errands, but how would i tie the errands together?
On the other it could be i'm overthinking this. well, well.

Em this might sound odd, but i try to describe errands & clients are related in this scenario.

• tblErrand: is the main errand table. It is the actual errand.
• tblErrandBefore: contains information about the client before he/ she was documented.
• tblErrandAfter: contains information about the client after the errand ended for a short period of time.

That is way i would like to tie the records together among with the ClientID. So the user could open a "case" and see all the related errands.
So when the user opens the Preview window for a client he/ she have all the cases listed with the possibility to select and open the requested one and the open the needed errand.
ErrandID, ErrandBeforeID, ErrandAfterID the the ID fields are autonumbers.


This is how have have set up the current relations.
Attachment 21894

[orange]

I get invalid attachment when trying to open your file.
Let's get the facts understood before you start with Access.
What exactly is an Errand? Can you give a clear, concise, plain English description so that it is clear to readers?
I do not understand the Before and After. I'm sure it is clear in your mind, but not to me.

As other posts will show you, you can construct and use a custom, incremental identifier. But, because you can, doesn't necessarily mean you should. Weigh the alternatives against your needs. Do some research, then make a decision.

Good luck.