Assigning a Lot number to table of information

[mmirandola]

I have a database setup to generate information on textbooks. I run an operation that takes used books, sorts them, and sends to a company that rebinds the books. My database is setup so that I scan the book ISBN and access looks up the isbn on a table in my database. If access finds the isbn it then copies the entire row of data (author, title, isbn, copyright, edition, etc) to another table which when complete I use as a packing list and send to the bindery with the books. If the database doesn't find the isbn after being scanned the book is of no value to me and is donated.

My question is.... How do i setup in access so that when i first begin the process and run my macro I can scan a "Lot Number or shipment number" and have access attach this number to my output table for every book that i scan. i don't want to have to scan the lot number each time I scan a book. i would like to scan it once at the beginning and have access know that each time I scan a book it will attach the lot number.

i will need to assign multiple lot numbers to multiple shipments within the same day being that i ship out multiple shipments to different companies daily. I currently use a popup window and a macro to enter the book isbn and have it look in a table for the book information and then append it to another table if it finds the isbn.



How do I use something similar (popup window) to scan a lot number 1 time at the beginning of the process, and have it attach to every book i scan after the lot number is entered?

I added a scree shot of what my database looks like in hopes someone can help.

[orange]

You mention lot Number or Shipment Number, but you didn't tell us about your Table structure.
Also, your screen parameter is CA. I'm guessing this means something to you that you haven't told us about.

Is all of the Lot number/shipping number and "book scanning" done from one computer?
Could you show us a jpg of your tables and relationships?
And a sample "packing list"?

[mmirandola]

Here are some pictures of what I have setup in my database. The first table is what holds all my book information (isbn,author, title, etc). The second table (Scan Table) is the table that my information is outputted to from the macro I have setup (Create book List). The picture of the query shows how I have the tables setup and linked together. The macro is setup giveing me the opportunity to scan what we call the "CA" which is also my primary key linking my table of book information (adopted Titles) to my scan table. A "CA" is called computer assigned number, its the same as a sku. Basically my companies assigned unique number to a product that is held in our computer systems. I use that as my primary key for many reasons that aren't relevant to what I am trying to do so I will save you the boring explanation.

What I would like to do is setup, somehow with my macro, another window at the beginning of the process where I can enter a lot number to assign to each shipment. I want the lot number to be attached to the list of books I create as my shipment to other companies.

Hope this is what you were looking for.

[orange]

A few questions, but first - I have 2003 not 2007 - so there may be some differences in some terminology.
Also I don't use macros. I'd like to check on your database structure.

Do you have a data model or picture of your database in the Relationships window? If you sent that as a jpg, it might help.
Are you using Lot and Shipment interchangeably? Or is a Lot number assigned to a Shipment of Books? Could there be more than 1 Lot number with a Shipment? Or more than 1 Shipment with the same Lot number?

Are all of your Shipments for rebinding purposes? Or do you ship some to other Sellers etc?

It sounds to me, and I don't have any knowledge of the book business, that

You have Books (only Textbooks?) that you compare to some Master List(ISBN).
If a Book is on your List, it is likely to be Shipped to the Bind Company.
The Book will be part of a Shipment to a Company for Rebinding.
The Shipment will contain 1 or more Books.
The Shipment will be assigned a Lot number.
Each Shipment contains a Packing List of Books in the Shipment.

You didn't mention Returns from the Bind Company, which is probably one use of your Packing List. To make sure you got back what you sent.


If a Book is NOT on your List, you donate it. Are Donated books shipped to some other Company? Are Shipments/Lots involved?

You specifically mentioned Textbooks - are there others that you deal with?

As for your specific question re Lot Number.

You could have a Table with a Lot Number.
You could take the latest Lot Number and hold it in a variable.
You could use the variable's value (Lot Number) for each Book to be in the Lot.

You could have a button on a Form to Get A New Lot Number.
The process behind the button would be to Add 1 to the Lot Number and save it.
Then assign the latest Lot Number to the variable
Then use this Lot number for each Book in the Lot.
And so on.

[mmirandola]

Sorry I am not sure how to get the picture in the relationships window. I found a button that says relationships but its blank when I click it.

Lot and shipment are not interchangeable. There could be more than 1 lot within a shipment. For example Lot A could be all science books, Lot B could be all math books, Lot C could be all literature books however they are all on the same shipment. I would like to use that lot number as a way to enter a unique number and attach to certain books within the shipment.

All shipments are for rebinding purposes. We do not ship to other sellers.

You have Books (only Textbooks?) that you compare to some Master List(ISBN). YES
If a Book is on your List, it is likely to be Shipped to the Bind Company. YES
The Book will be part of a Shipment to a Company for Rebinding. YES
The Shipment will contain 1 or more Books. YES, Normally several thousand at a time.
The Shipment will be assigned a Lot number. Mutliple lot numbers per shipment, each lot number will represent a category of books (Math, Science, Etc)
Each Shipment contains a Packing List of Books in the Shipment. Yes, normally a cross tab query showing ISBN, qty, and lot numbers.


Yes, if the book is not in the list it gets donated. That is a totally different process that I don't even want to embark upon until this process is successful.
No, this is only textbooks.

Could you assist me in the following:
I would like to setup a popup window that will prompt me as follows "Enter Lot Number"
I would then enter a lot number or scan a lot number into the popup window
I would then like another popup window to prompt me as follows "Enter CA"
I would then enter a book CA
The databse then looks up the CA on my book list table and if its found it enters all fields I have specified into an output table I have specified (This I have created already)
For all the CA numbers that are found on my book list and relative fields transfered to my output table can access also enter that lot number I previously scanned/entered. I would like this to continously enter into a field until I tell it to stop and want to enter a different lot number. Below is an example of what my output table would ideally look like

Lot Number CA Author Title Copyright Edition
A 123456P Johnson Calclus 2006 11th
A 234567P James Trigonometry 2012 1st
B 345678X Pearson Literature 1999 7th
B 567891X Matthews Grammar 2001 3rd
C 456789P Diaz Spanish 2 2008 6th
C 789123X Gonzalez Buenos Dias 2012 4th
D 891234C Snyder Biology 2010 5th
D 678912C Williams Chemistry 1991 1st

Hope that helps explain what I want to do. I have everything figured out except how to get a Lot number entered.