Append records to table based on a changing variable 1

[JanBS]

Hi there. I need to created serial numbers for kits being shipped to a customer. They are 19 characters long with the last 4 digits starting at 0001 and ending with the total number of kits that we need to make, which is determined by the quantity in a production order.

On a form I prompt the user for the Production Pick Ticket number and display the Production Order Number, the Kit ID and quantity to make, which I have called QtyToMake, on the open form.

I append the first record into table SerialNumbers with the SerialCountNo starting with 1. I tried to repeat a macro of an append query with a repeat condition using DCount that should stop when the count of how many records in that SerialNumbers table is equal to the quantity to make. No matter how I change the syntax the macro repeats and adds thousands and thousands of records instead of the test quantity to make of 375. It keeps repeating the SerialCountNo, which uses another query to determine the max SerialCountNo and adds 1.

The quantity to make can be anywhere from 50 to 5,000. That means my SerialCountNo could be 0001-0050 or 0001-5000 in those 2 examples.

Does anyone have any suggestions on how to go about this? I sure hope so! I'm pulling my hair out and the customer is waiting!!

Thank you in advance for any help you can give.

Jan

 

[SkipVought]

Hi,

Plz post your code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

That sounds very unusual: if I order 5000 of (whatever), you will insert 5000 records in a table for that order? One record for every (whatever). Why? And who and how will update all 5000 records with (what?) information?

...confused...


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I would create a table tblNums with a single numeric field [Num] and records with values 1 through 5,000 or more. You can then add this to your append query and set the criteria under the Num field to <=[SerialCountNo].

Use the Num field to generate your serial numbers.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JanBS]

Skip,

I wiped it all out because I was frustrated. I don't even remember what I had because I must have tried 20 different ways. Now I can't get it to do any appends. I currently have Repeat Expression = DCount("*","SerialNumbers","SerialCount =" & "WhatQty") which does nothing.


Andy,

The customer has required us to create a unique serial number for every kit we send them in an order. There will be a barcode label with that serial number. The format of the serial number contains the part number, version number, vendor id, month and year of kit production and at the end the serial count, which is essentially the kit number of the total number ordered. So, today we have an order to produce kit ABC, quantity of 1000. Each kit bag must have a unique kit serial number with the Serial ID Numbers 0001 through 1000. Next month they may order 375 of that same kit. So for that order the Serial ID Numbers will be 0001-0375. Now, when we box them up, we have to scan each and every Serial Numbers that go into the box and I have to list all those serial numbers in a QR Code that gets put on the box. Depending on the size of the kits there may be 10 kits in a box or there may be 50. We never know. So this is why I have am appending all the different numbers to a table so the labels can be printed. I am probably going about this the wrong way but I was unable to figure out a different method.

 

[MajP]

So assuming you are enter place holder records into a child table. I would make a simple procedure that the form calls and passes the information for the insert. If there are other fields to insert you need to pass them in as parameters.

Public Sub CreateSerialNo(ProductionNumber As Long, Quantity As Long)
  Dim strSql As String
  Dim SerialNumber As String
  Dim i As Integer
  For i = 1 To Quantity
    SerialNumber = Format(i, "0000")
    strSql = "Insert into TblProduction_Serials (ProductionNumber_FK, SerialNumber) values (" & ProductionNumber & ", '" & SerialNumber & "')"
    CurrentDb.Execute strSql
  Next i
End Sub

This will create X amount of serials for the given Production number.

 

[dhookom]

If you were to use the method I suggested in the Northwind table with the Order Details table, you append query might look something like:

INSERT INTO tblSerialNumbers (OrderID, ProductID, SerialNumber)
SELECT [Order Details].OrderID, [Order Details].ProductID, [ProductID] & Format([Num],"0000") AS SerialNum
FROM [Order Details], tblNums
WHERE [Order Details].OrderID = 10911 AND [Order Details].ProductID=1 AND tblNums.Num<=[Quantity];

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Just a guess here...
So it looks to me that you want to keep this information in a table like this:

[pre]
part_number, version_number, vendor_id, month_year, [blue]serial_count[/blue]
789 456 123 072017 [blue] 1[/blue]
789 456 123 072017 [blue] 2[/blue]
789 456 123 072017 [blue] 3[/blue]
789 456 123 072017 [blue] 4[/blue]
... ... ... ... [blue] ...[/blue]
789 456 123 072017 [blue] 5000[/blue]
[/pre]
in order to produce this:
[tt]
789456123072017[blue]0001[/blue]
789456123072017[blue]0002[/blue]
789456123072017[blue]0003[/blue]
789456123072017[blue]0004[/blue]
...
789456123072017[blue]5000[/blue]
[/tt]

But if you do not need to keep this information, you just need it to produce your labels/bar codes, and I assume part_number, version_number, vendor_id, and month_year will be the same for one shipment, you do not need really this table, you just need to increment the serial_count and format it to, let's say 5 characters.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[JanBS]

I followed Duane/dhookom's suggestion and I am now all set! Thank you all very much for your help. It was greatly appreciated!!!
Jan

 

[Andrzejek]

Don't forget to click on [blue]Great Post![/blue] in Duanne's post to award him a star.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[JanBS]

Done! Thanks!!