Create auto-generated serial number by batch (1 Viewer)

[jason1225]

I have very little knowledge of Access, but I do have some intermediate knowledge of SQL. I am trying to construct a system in access where I can have a user type in a part number, revision of a part, job number, serial number quantity, and generate a serial number based on the quantity that they need.

I am getting hung up on even how to construct the tables effectively to accomplish this, let alone the forms, queries, etc.

The numbers have to be in the following form. XXXX-XXXX

For an example:

I have part ABC and I need 5 serial numbers.
I need to be able to create a batch of serial numbers automagically that reads like:
9001-0001
9001-0002
9001-0003
9001-0004
9001-0005

Then for the next part XYZ I need 3 serial numbers.
It would be constructed like this:
9002-0001
9002-0002
9002-0003

With the goal of eventually being able to search a part number and revision, or a job number and return all serial numbers assigned to that.

Is this possible in MS Access?

I'm not sure I have the correct forum for this, so my apologies if this is posted in the wrong area.

 

[StarGrabber]

Hi, Jason, welcome to the forum.

To start with your last question: yes, this is possible in MS Access.

First of all, I want to know what happens with the serial numbers once they are created. Will be there further processing in your system? And if so, how?

Please explain the meaning of "user type in a part number". What confounds me here is the word "user".

You will find a first attempt attached. Please note, the format "XXXX-XXXX" is not stored in a table but generated in the query.

Your task isn't an easy one for someone with "very little knowledge". What about your VBA skills? If you want to create serial numbers "automagically", some lines of code would be necessary.

 

[jason1225]

StarGrabber, thanks for helping with this.

To answer your questions I want to go a little more in depth with what we are trying to accomplish.

We currently use an ERP system that does not have very good serial number management. The whole goal of building this database is to make sure we do not duplicate serial numbers. Our current ERP system allows us to duplicate serial numbers and we cannot have that.

In our system we have a part number. This part number is a product that we make. We then have Job Orders. A job order is a specific job that exists to create a specific part number. (For Example, we have a part "ABC", we need to create 5 of these for a customer. We would create job 4321 to make 5 "ABC" Parts. This job would eventually need 5 serial numbers for each part created.)

First of all, I want to know what happens with the serial numbers once they are created. Will be there further processing in your system? And if so, how?
Once the serial number is in the database, the only thing we will use it for is to look up what serial number has been assigned to a job number.

Please explain the meaning of "user type in a part number". What confounds me here is the word "user".
When I say User, we will have at the most 3 people having access to add information to this database. This "user" will type in the Job Number, quantity of parts that were made (This is also how we know how many serial numbers need to be assigned.), Part Number, Revision of Part. My goal is after this information is input, they can click a button and it will assign serial numbers based on how many parts were made on that job.

When it is all said and done the users would be able to search for all serial numbers assigned to a specific job, or part number and revision. (For example, Part ABC was made 5 times in job 4321. Part ABC was also made 6 times in job 4322. If I search for the job number 4321 it would show me 5 serial numbers. If I search for Part ABC, it would show me 11 serial numbers and the job numbers they were assigned under.)

I hope this helps paint a better picture.

Edit: I neglected to mention, I do have a very small amount of VBA knowledge, to answer your question, but probably not enough to help me with the programming of this.

 

[StarGrabber]

OK, no problem. To give you a first impression, have a look at the file attached.

Currently the possibility to search the way you described is still missing but... further development in progress!

 

[StarGrabber]

And here is the second attempt. This time with search functionality.

Please note, I've used an ActiveX listview control with a customized font. That is why two additional VBA references have to be set:

1. Microsoft Windows Common Controls 6.0 (SP6) (File MSCOMCTL.OCX) and
2. Microsoft Forms 2.0 Object Library (File FM20.dll)

What is still missing is a form to enter new jobs. But I think you will achieve this. Use the wizard.