Auto Generate Purchase Order Number for each new set of records

[KaedeM]

Hello,



I've been viewing this forum for several months now searching for answers to my database questions.
I have found many helpful articles and threads on here. Now I have a question that even Google can't help me with.

I was tasked by my boss to create a Purchase Order Database for my department. I haven't been able to create a functioning database on my own but this time I'm close.

Right now my main two problems are:
1. Setting up the Purchase order number to auto generate from a formula.
The Purchase Order numbers need to follow the same format - 16-XXXX12-1, 16-XXXX12-2, 16-XXXX12-2
16 is the Year. XXXX is the department identifier. 12 is the employee ID number.
2. Keeping the Purchase order number the same for multiple records on the same form.

I have a few other smaller issues but I figure I'll get those eventually.

I have a bunch of tables and forms already but I'm not sure where to go from here.

What can I provide to help you make suggestions?

Thank you,
Kaede

[June7]

1. generating custom unique identifier is a common topic, start with https://www.accessforums.net/forms/a...ing-23329.html

2. use form/subform arrangement - main form bound to Orders table and subform bound to OrderDetails table - Orders table primary key will automatically save to the OrderDetails records as foreign key

Now you have to decide whether or not the purchase order number will be designated as the PK/FK or to use autonumber as PK.

[orange]

As June says custom unique numbering is a common subject, but relational database works very well using the concept of 1 fact 1 field.
You could have an EmployeeNumber, DepartmentNumber, PurchaseOrderDate and concatenate (combine) these or pieces of each for displaying your PurchaseOrderNumber. (your format)
How you store/record info in a database is not always the way you display info. The autonumber PK suggested by June is the most common method of identifying records to the database management system.

If your database/application will involve any aspect of Stock Control, I would recommend this 26 minute youtube video to get some ideas about a complete application with lots of tips and examples. In fact it may be the best 26 minutes spent just to confirm any plans/ideas/thoughts you may have for your database.


Good luck with your project.

[KaedeM]

OK, I looked at the topic you recommended and I have a question about that.
'I have never tried to increment an alpha. Think will have to use Asc and Chr functions. Asc will return the ASCII code of character, increment the code, then convert to letter with Chr.
Example: Chr(Asc("A")+1)'
Is Asc and Chr Ascend and Chronological?
I am extremely new to code and don't always know what simple things mean.

I don't have a difference between the 'Orders' and 'OrderDetails' tables. I just have all the PO information in one table. Is the way I have it a more difficult way to store the data?
I thought I did the subform but after some experimental clicking I used the multiple items form. I am unable to locate the subform button
When there's a new record added to the PO it doesn't keep the PO number as it should. This is probably because I used to wrong form, right?



^This is how it appears with the first record added. The PO number is clearly filled out at the top


^This is how it appears when the second record is added - the PO number is gone from the text box.

[June7]

You need to 'normalize' data structure. If each purchase order can have multiple products then yes, should be two tables.

IMO, a purchase order number should not be dependent on data like the department and employee IDs, well, definitely not the employee ID.

Why would you need to increment alpha?

[orange]

Kaede,
Since you do not have an OrderDetails table, you should do some research. Here is a link with several videos related to building a Customer Order database.

You really need to learn/experience some database concepts before getting too deeply into Access.