Custom AutoNumber (Access 2003)

Topic

New Reply

I have 15000 records that I am putting in an Access database that already have an established numbering system. I have a variety of products which are already numbered with a job and a subjob. I might have a job number 1274 with subjob numbers 1, 2, 3, 4, etc., for each product. I want to add a new job for “widget” and automatically generate a job new number (say, the last number for widget was 1274, I need the new number to be 1275). Or, If I want to add a new “gadget,” I need the next number for gadget. OR, I might want a new subjob for Widget Job number 235. I need to automatically add the next subjob number.

I don’t expect this to be easy enough to answer here, but I wonder if anyone could tell me how to look this up somewhere. I have 3 Access 2003 books (Bible, Complete Reference, and Using). I think if I knew what to look for, I could do this. I just don’t know where to look! Thanks!

Reply | Quote

Replies

You can use the DMax function to retrieve the maximum job number or subjob number satisfying a specific condition and add 1 to get the next number. Something like:

Dim lngNewJobNumber As Long
Dim lngProductID As Long
lngProductID = Me.ProductID
lngNewJobNumber = Nz(DMax(“JobNumber”, “tblSomething”, “Product = ” & lngProductID), 0) + 1

The exact implementation depends on the actual names and on the data type of the relevant fields. If you need more assistance, please provide specific details.

Reply | Quote

I have one table with Job, Subjob, Product fields that are combined to make a composite primary key field.
Problem 1 — I have widget with job #123 (automatically generates a subjob #1). I need to create a new widget # (this would be widget number 124). This would automatically create a subjob #1 for Job 124.
Problem 2 — I also have a whatsit #1701 (with subjob #1). I need to create a new whatsit (this would be whatsit #1702, with a new subjob #1)
Problem 3 — I have a widget #123, with a subjob #1, but I want to give add a subjob #2.
When the user goes to add a new widget, I want to automatically create a new job number and a new subjob number. When the user goes to add a new whatsit, I need a new job number for the whatsit with a new subjob #1. And on and on. So, the user will have to filter for the Product and the jub number before they add a new subjob OR filter for the product name before they add a new job number.

In the example you gave me, I’m not sure where to add the information.

Reply | Quote

You must use a form based on the table to do this. You need two command buttons on the form: one for a new job, and one for a new subjob. How exactly do you want to add a new job?

a) The user clicks in a record, then clicks the “New Job” button. The code behind the button creates a new record with the next Job number, Subjob = 1 and the same Product as the current record.
– or –
The user selects a product from an unbound combo box, then clicks the “New Job” button. The code behind the button creates a new record with the next Job number, Subjob = 1 and the Product selected in the combo box.

Reply | Quote

Okay, we’re getting there (I think). I need to go change all my job and subjob fields to numbers, but that’s okay. Only they have to be 4 digits with leading zeros (i.e., number 14 has to show up as 0014). Can I do that with a number field? If so, how?

Reply | Quote

You can set the Format property of the text boxes on forms and reports that display a job number or subjob number to 0000. This forces the number to be displayed with 4 digits with leading zeros.

Reply | Quote

Yea!! That worked! I cannot, however, understand how to do the DMAX function. In your instructins, you said lngNewJobNumber as Long. Is this supposed to be the JobNumber field that I already have or is this for a new field? I’m sorry, I’m really having a hard time with this. Thanks so much for your help!

Reply | Quote

The idea is that you create a new record in code, and assign the value of lngNewJobNumber to the JobNumber field in the new record.

Reply | Quote

You could do this a couple of ways.

1. Introduce a new table that contains a couple of fields, a field that holds the description (eg. Widget) and another field that holds the latest number for that description. Everytime you wish to add a record to your table, just lookup the new table based upon the description and update the latest number used by one and write the record back to that table, and use the number you updated to use in your main table, or,

2. Use the Max or Dmax function to read the highest number for say a widget, then use that number plus one.

Reply | Quote