Auto numbering data field in Access form

That did it for me. Thank You!

I do have one more question. The method works excellent when the form is loaded with several records. I can click a button and the items are numbered just like I want them to be. But in the case the form is loaded one row at the time via a user selection method. How could I use your numbering method to sequencelly number the rows as they are entered? top is 1, next is 2 and so on. I tried adding your command to the ON Update function of the selection control but it does not increment the number. Every new row/record added gets a value of 1

What is the RecordSource of your form?

Usually, if you want to add a sequential number to a field, you can use the Form_BeforeUpdate event to determine the maximum number that already exists in that column, usually relating to some other value, and then add one to it.

Private Sub Form_BeforeUpdate(Cancel as Integer)

    'Define the criteria to determine the largest value in your table relating to some other field
    dim strCriteria as string
    strCriteria = "SomeID = " & me.txt_ID
    me.txt_FieldName = NZ(DMAX("SomeField", "yourTable", strCriteria), 0) + 1

End Sub

Select allOpen in new window

 
 HTH
Dale

My record source is a query based off a table. I do not want to get the max value of the field in the tables as I will have multiples instances of 1 2 3 4 5 in the able the sequence is set per form load. Every time I load my form with 6 user selected datasets I want them numbered 1 2 3 4 5 6 . The next time I load the form with different data from the same table the numbering sequence starts all over.

Then you could probably use:

Private Sub Form_BeforeUpdate(Cancel as Integer)

    me.txt_FieldName = NZ(DMAX("FieldName", "formQueryName"), 0) + 1

End Sub

Select allOpen in new window

This would be at the form level - which only has several rows loaded from the record source dataset. Looking for the max number in the source would not work. I want the max for only those records loaded on the form now.

Well you said you have a query that populates the form with the right number of records, and you can then add records to that, right?

If so, when the form loads with X# of records, does it automatically renumber those records, or do you click the button to do so?

When you add a record, are you starting over from 1, or are you adding to the number of records the query returned?

You may need to take a couple of screen shots or put together a demo database to make it easier to understand what you are trying to do.

This is my record source for the form
SELECT ProjectLines.ProjectID, ProjectLines.[Mark#], ProjectLines.UniqueID, ProjectLines.ElementID, ProjectLines.PieceUID, ProjectLines.[ElementLenght(ft)], ProjectLines.[ElementWidth(in)], ProjectLines.WorkOrder, ProjectLines.CastDate, ProjectLines.StripDate, ProjectLines.CastLocation, ProjectLines.BedID, ProjectLines.LineID, (Val(Eval(Replace(Left([ElementLenght(ft)],6),"'","*12+"))))/12 AS eLenght, Val(Eval(Replace(Left(Nz([ElementLenght(ft)],"0"),6),"'","*12+")))/12 AS dcmLenght FROM ProjectLines WHERE (((ProjectLines.ElementID)=Forms!WorkOrderEntry!formElement) And ((ProjectLines.WorkOrder) Is Null) And ((inparam([LineID],Forms!WorkOrderEntry!ListformUIDlist))=True));

Several records are selected from an existing record set in a table

These records come with CastLocation blank
I want the records that are loaded in this form to be numbered 1 2 3 4 5 6 from top to bottom

The button you first suggested works well but I would like to get away from a button

As the form is being populated (via user selection process) I want the CastLocation field to be populated with 1 for the first record added 2 for the next and so on.

There are no new records added in this form. Only existing records have empty fields populated.