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?
 
Dale FyeCommented:
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.Edit
        rs!FieldName = lngCounter
        rs.Update
        rs.MoveNext
   Wend
   rs.close
   set rs = nothing
   me.refresh

End Sub

Open in new window

1
 
Matthew WaldnerAuthor Commented:
That did it for me. Thank You!
0
 
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Dale FyeCommented:
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

 
 HTH
Dale
0
 
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.
0
 
Dale FyeCommented:
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

0
 
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.
0
 
Dale FyeCommented:
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.
0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.