Unique number identifier for each report printed

[Mxited]

Greetings,



I am an end user with a little experience using and creating Access databases, forms, reports, etc...

My dilemma: I print tags that go on a pallet. Each tag has information that comes from various tables, queries, and forms depending on input from the user. I have a new customer who wants each pallet to have a unique identifier such as pallet #1, pallet #2, etc... These numbers must never repeat and should increment every time a pallet tag is generated. I am not concerned with skipping a number if a pallet tag gets damaged.

I need help figuring out how to ensure that each time the 'tag report' is created, there is a new number that will be printed in a text box. It cannot be a random number, it must follow a simple 1,2,3... sequence without ever repeating. I can also start it at 1 or at 100. There is already an [event procedure] on the 'on click' setting for the button that creates the report. It simply brings up the report for a 'print preview' so I can review it before I print it.

Your help is greatly appreciated.

[nigelbloomy]

If the tag you are printing is coming from a set of tables and queries then one option may be to put an "AutoNumber" field into one of those tables. I assume one of the tables tracks each customer order or has a separate line for each time something is shipped.

If you can go to that table, and look at it in design view, you can click on the top row and select insert a row. Give it a name and then under the "Data Type" Column select AutoNumber.
There may already be a field in the table that does this.

Once the table has the field, you can look at your report in design view. Select "Add Existing Fields" on the top right. The AutoNumber field should be there and you can add it onto your report.

Hopefully that helps a little.

[Robeen]

I had a similar thought about adding the AutoNumber field.

If you'd rather not do that - you can create a separate Table - Pallet_Number_Table - and just put one field in it - Pallet_Number [data type Number].
Put a number into the Table. When I tested the code - I put the number 10000 in.

Create a Query to be the data source for your report - pull in all the fields you need on your Pallet report and then add a field to the query like this:

New_Pallet_Number: DMax("Pallet_Number", "Pallet_Number_Table") + 1

This will give you a number that is one higher than the number currently in your Pallet_Number_Table.

Then - behind your command button - you could have VBA Code like this to increment the number in your Table after you have printed your pallet report - so that the next time - the pallet number report will be one higher:

Dim db As Object
Dim rst As Object
Dim intPallet_Number As Long

intPallet_Number = DMax("Pallet_Number", "Pallet_Number_Table") + 1

   Set db = CurrentDb
   Set rst = db.OpenRecordset("Pallet_Number_Table")
   rst.Edit
   rst("Pallet_Number").Value = intPallet_Number
   rst.Update

After this code Runs - the Number in your Pallet_Number_Table is the same as the number on your last Pallet Report - so that it is ready to be incremented for the next Pallet_Number.

I hope this helps.

[Mxited]

Thanks to both of you for your response. Since all the tag information is either coming from existing tables or user input that is unbound, modifying an existing table with an autonumber field is not as clear cut as if I was writing something to a table when I generate the tag. However, the second solution seems better suited for my application. I will give it a try because in theory, it will give me the exact results I am wanting.

Again, thanks to both of you and I will post back as soon as I can implement the solution and give you feedback. Hopefully, I will not need any help interpreting the solution, but with me, you never know.

Thanks so much,
Mxited

[Mxited]

@Robeen, your solution worked perfectly. Thanks so much. @nigelbloomy-thanks for giving this a shot. I'm sure if I would have been writing to a table, your solution would have also worked. It is amazing how many different ways databases are structured. Again, I appreciate both of you and I will mark this thread as solved.

Mxited

[Robeen]

Happy to help!!