Creating sequential numbers that change based on field value

[KyleSed]

Hello!

I have what I think should be a fairly simple issue, but I can't seem to figure out the easiest way to make it work.

I have a database that has 2 fields, one for "Shipment" and one for "ItemNumber". I want to auto-populate the ItemNumber field starting at 1 and going sequentially until there is a change in Shipment, then reset to 1. The shipment number will be input into a form by the user along with the other data.

I think a query is the correct solution to this, but I'm up for other options if there is a better way.

 

[Addyman]

On the form the users use to enter the data, do you use a button for them to click to submit the data to the table? Or is it a bound form and they enter data in which is committed straight away to the table?

Personally I would use an unbound form with a submit button so you can verify the data they have entered before writing it to the table. You can then easily calculate the next available ItemNumber using DMax function. For example, you could calculate it by:

Dim intItemNumber as Integer
intItemNumber = Nz(DMax("ItemNumber", "YourTableName","Shipment = " & Me.txtShipmentNumber),0) + 1

This will look at the Shipment Number entered by the User in the text box txtShipmentNumber (edit to match your own control name) and will then find the Max value for the ItemNumber field where the Shipment field in the table equals the value on your form and will then add 1 to it. The Nz is there so that if this is the first ItemNumber to be added (and so there is no value in the table for the DMax to find), it will use 0 instead and so this will become 1 with the '+ 1' used at the end of the formula.

Hope this helps.

 

[KyleSed]

This looks like it should be exactly what I am looking for, but I'm running into problems. This is the code I have, updated for my actual field names. Currently when I add / update records the HFNumber field remains blank. Am I missing something obvious?

ShipmentNumber is the name of the text box with a control source of the Shipment field, but changing to Me.Shipment had the same result

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim HFNumber As Integer
HFNumber = Nz(DMax("HFNumber", "Boxinfo", "Shipment = " & Me.ShipmentNumber), 0) + 1
End Sub

 

[Addyman]

With that code you will be assigning a value to the HFNumber variable, but what code are you using to actually write the data to the table?

Or, if you are using a bound form, you need to enter the value of the variable to the text box. So, if you have a text box called HFNumber, you need an additional line of code saying:

Me.HFNumber = HFNumber

(Just as an aside, I normally like to prefix my variables so that I know what type they are just by looking at the variable name and also so that I don't confuse myself by having controls and variables named the same. i.e. Dim intHFNumber as Integer)