Serial Numbers Per Batch

[Huxxxy]

Im not sure if this is the right catagory im posting this in but...

I need to work out what would be the best way for a user to enter in one Serial number into a field then once submitted it would automaticly add 7 more plusing one each time as.

EG. 8 Components Per batch each having a different serial number but each number is plus one. 12321 12322 12323 etc, but I only want the user to enter in the first serial number and then the rest add automaticly..

If you can make any sence of that and can help

thanks in advance

Martin

 

[vbaInet]

This could possibly create problems in a multi-user environment. How many people will be using the db at once?

 

[Huxxxy]

The numbers are issued to the components already, before they enter the Clean Room, they have a job card with the batch ID part Number and serial Numbers with them.
So the DB is basicly just a DayBook Log of which batches to be worked.
I wanted to log the serial numbers so if a part comes back as faulty I can search the DB and find out when that batch was done and who done it.

 

[vbaInet]

Since you said the numbers exist already in that case the serial numbers you want to increment would turn out to be inconsistent.

E.g. you had a serial number 12322, but later deleted it. You started the increment from 12320 up to 7, if you got to 12322 that record will not be pointing to anything.

 

[Huxxxy]

On the Job Card Issues, it says to the Employee that this batch contains serial numbers from 12320 - 12328. I dont need to know which individual component has which number, but what I need to know is what serial number came from what batch.

So I tried to make it easy and quick when adding to the db for the user to Enter in the Batch Number and the First Serial Number Eg 12320. And the db to then add 8 records to the Serials table to say that
Batch Number: 12345678 Has Serials 12320, 12321, 12322, 12323,12324, 12325, 12326, 12327 and 12328.

 

[vbaInet]

So all that needs to be inserted into the table are just those two fields, no other fields involved?

 

[Huxxxy]

I have a Work Order Table of which you enter in the AutoID, BatchNumber, PartNumber, Quantity, Operator, Notes, Complete (Yes/No)

I have a Seperate Table Contains SerialAutoID, BatchNumber, Serial.


I have other tables but they are the main relevent ones.

 

[Huxxxy]

Here you are this is my novice start to the DB

 

[vbaInet]

I will give you the code and you can decide which table(s) the records should be inserted into. It's something like this:

Dim rst as dao.recordset, counter as integer, serId as Long

set rst = Currentdb.openrecordset("TableName")

counter = 1
serid = Val(Me.SerialNumber)

with rst
     do while counter <> 8
          .addnew
          ![BatchNumber] = Me.BatchNumber
          ![SerialNumber] = serid
          .update
          counter = counter + 1
          serid = serid + 1
     loop
end with

rst.close
set rst = nothing

 

[pbaldy]

I'd probably use a For/Next loop, but that's a matter of style. Note that if the table gets large, this can get very slow (voice of experience):

set rst = Currentdb.openrecordset("TableName")

I'd use

set rst = Currentdb.openrecordset("SELECT * FROM TableName WHERE 1=0")

which will open an empty recordset you can add records with.

 

[Huxxxy]

Code works like a charm, Thank you so much vbaInet, and pbaldy I added that line thank you.

Only thing I had to do was counter = 1 to counter = 0 so it added all 8 serial numbers.

Thanks Again.

All the Best

 

[vbaInet]

Glad we could help.

That's true Paul. I was thinking of doing something with LastModified but had a change of mind and just left it as a Do While loop.