Link to home
Create AccountLog in
Avatar of Matthew Waldner
Matthew Waldner

asked on

Auto numbering data field in Access form

I have a continuous form that is populated by the records from a table. The records have 1 field that is blank when loaded. I want this field filled in on this form level. I am trying to sequentially number the records from top to bottom in the form. How would I do this at the form level? I have tried assigning default values to the field in the form with no success.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Matthew Waldner
Matthew Waldner


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

Open in new window

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

Open 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.