Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Forms _ Sequential Numbering Of A Continuous Form

Posted by: InfoHound Jan 21 2020, 07:16 PM

I'd like to have a sequential numbering of records on a continuous form. The form is based on a local table that will be used to collect data for updating another table.
The form's local table will have all the records deleted when the user closes the form. Every time the user opens the table and enters a new record I'd like it to start at one again. The control that holds the sequential numbering would be unbound and the its data is not saved.

The purpose for this is; to give the user a nice and easy way to see where they are at when entering data.

I know we can do this sort of thing using a running sum on a report, but, can it be done on a continuous form?

Posted by: June7 Jan 21 2020, 10:47 PM

Requires a field in table to receive value. Set textbox DefaultValue property to 1 in form design. Then use VBA in that textbox AfterUpdate event to increment the DefaultValue:

Me.textboxname.DefaultValue = Me.textboxname + 1

Posted by: June7 Jan 22 2020, 01:26 AM

An alternative is to use an autonumber field and an INSERT action to reset the seed value followed by a DELETE action.

CurrentDb.Execute "DELETE FROM tablename"
CurrentDb.Execute "INSERT INTO tablename(ID) VALUES(0)"
CurrentDb.Execute "DELETE FROM tablename"

Also review

Posted by: Larry Larsen Jan 22 2020, 04:39 AM

My I also offer up an option method.. using an unbound text box control.

Here I'm using a very small UDF (User defined function) that can be called when the form/report is opened.

And is reset every time..

Public Function RowNum(frm As Form) As Variant

    On Error GoTo Err_RowNum
    'Purpose:   Numbering the rows on a form.
    'Usage:     UnBound Text box with ControlSource of:  =RowNum([Forms]![myForm])

    With frm.RecordsetClone
        .Bookmark = frm.Bookmark
        RowNum = .AbsolutePosition + 1
    End With

    Exit Function

    If Err.Number = 3021& Then  'Ignore "No bookmark" at new row.
        Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description
    End If

    RowNum = Null
    Resume Exit_RowNum

End Function



Posted by: InfoHound Jan 22 2020, 08:16 AM

Good day Larry,

I created a public function inside a mod using your code.

In the Open event I've added this

Private Sub Form_Open(Cancel As Integer)
           RowNum (subfrmAddItemsDetails)
End Sub

I keep getting an error.

The mainform is : frmAddItems
The subform is : subfrmAddItemsDetails
The unbound control on the form is : intLineNo

Thanks to all for your help. I'm trying all three ways as each presents on opportunity to learn and achieve different goals.

Posted by: cheekybuddha Jan 22 2020, 08:39 AM

Larry appears to have left the building.

In his absence, my guess would be to remove the code from the Open event.

Then, in the ControlSource of textbox 'intLineNo' put:




Posted by: GroverParkGeorge Jan 22 2020, 08:40 AM


Larry's code applies to a FORM.

You want to apply it to a SUBFORM, not a form.

Change the syntax so that it applies to that subform.

Posted by: cheekybuddha Jan 22 2020, 10:50 AM

Just for clarification, you should use the expression exactly as typed in my previous response - do not substitute [Form] for the actual form name.

I assume that control is on the actual subform that requires numbering and, other than the expression, is unbound



Posted by: InfoHound Jan 22 2020, 11:16 AM

I removed the Form's Open Code and have the expression below as intLineNo Control Source:


intRowNum displays "#Error"

Posted by: cheekybuddha Jan 22 2020, 11:23 AM

See above Post#8

Posted by: InfoHound Jan 22 2020, 01:03 PM

Wow Cheekybuddha it worked!
If it's not to much trouble could you tell me why? Or maybe point me in the direction where I could learn more about how this works?

Is there a way too renumber the lines if one of the lines is deleted. Say you have five lines and you delete line number three can number four and five become three and four.


Posted by: cheekybuddha Jan 22 2020, 01:06 PM

Does that not happen when you try?

Posted by: InfoHound Jan 22 2020, 02:02 PM

After deleting the record I use a me.requery and everything works great.

Posted by: Larry Larsen Jan 22 2020, 02:22 PM


Got dragged out kicking and screaming to the shops... pullhair.gif

David many thanks for the follow up's and helping out the OP..

Out of interest what was the final instruction used to call the RowNum..???

Posted by: InfoHound Jan 22 2020, 02:41 PM

The only reference to the RowNum() public function is in the unbound control "intRowNum's" Control Source =RowNum([Form])

Posted by: Larry Larsen Jan 22 2020, 02:54 PM

uarulez2.gif thumbup.gif

Posted by: InfoHound Jan 22 2020, 03:30 PM

Thanks Larry I appreciate your help.

Posted by: cheekybuddha Jan 22 2020, 04:56 PM

[Form] in an expression will return the form that the control is on, much like 'Me' in code on a form's module.

Since the control will be on the form that you need to get the recordset of (even if it's a subform) then that reference is all you need. A lot easier than trying to make an expression via the Forms collection (ie [Forms]![NameOfForm]).

Posted by: InfoHound Jan 22 2020, 07:23 PM

Thanks David, you guys are the best.