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.
Matthew WaldnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Your form can be sorted by many fields, and the order it will display in your form, initially is unknown unless you have a specific order by clause in your the query which is the recordsource of the form.

One way to do this would be to include a button on the form which you could use to run some code that looks like:

Private Sub cmd_UpdateOrder_Click

    dim rs as DAO.Recordset
    Dim lngCounter as long

    set rs = me.recordsetclone
    While not rs.eof
        lngCounter = lngCounter + 1
        rs!FieldName = lngCounter
   set rs = nothing

End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matthew WaldnerAuthor Commented:
That did it for me. Thank You!
Matthew WaldnerAuthor Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Dale FyeOwner, Developing Solutions LLCCommented:
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

Matthew WaldnerAuthor Commented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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

Matthew WaldnerAuthor Commented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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.
Matthew WaldnerAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.