INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Append records to table based on a changing variable

Append records to table based on a changing variable

(OP)
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

RE: Append records to table based on a changing variable

Hi,

Plz post your code.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Append records to table based on a changing variable

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.

RE: Append records to table based on a changing variable

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

RE: Append records to table based on a changing variable

(OP)
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.

RE: Append records to table based on a changing variable

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.

CODE

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.

RE: Append records to table based on a changing variable

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:

CODE --> sql

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

RE: Append records to table based on a changing variable

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

part_number, version_number, vendor_id, month_year, serial_count
   789            456            123      072017            1
   789            456            123      072017            2
   789            456            123      072017            3
   789            456            123      072017            4
   ...            ...            ...      ...             ...
   789            456            123      072017         5000
 
in order to produce this:

7894561230720170001
7894561230720170002
7894561230720170003
7894561230720170004
...
7894561230720175000


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.

RE: Append records to table based on a changing variable

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

RE: Append records to table based on a changing variable

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Append records to table based on a changing variable

(OP)
Done! Thanks!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close