UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sequential Numbering Of A Continuous Form, Access 2016    
 
   
InfoHound
post Jan 21 2020, 07:16 PM
Post#1



Posts: 1,217
Joined: 1-December 12



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?
This post has been edited by InfoHound: Jan 21 2020, 07:17 PM
Go to the top of the page
 
June7
post Jan 21 2020, 10:47 PM
Post#2



Posts: 1,522
Joined: 25-January 16
From: The Great Land


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


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
June7
post Jan 22 2020, 01:26 AM
Post#3



Posts: 1,522
Joined: 25-January 16
From: The Great Land


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 http://www.lebans.com/rownumber.htm


This post has been edited by June7: Jan 22 2020, 01:32 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Larry Larsen
post Jan 22 2020, 04:39 AM
Post#4


UA Editor + Utterly Certified
Posts: 24,585
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


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

CODE
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_RowNum:
    Exit Function
Err_RowNum:

    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


HTH's
thumbup.gif
Attached File(s)
Attached File  2020_01_22_09_38_10.jpg ( 8.68K )Number of downloads: 2
 

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
InfoHound
post Jan 22 2020, 08:16 AM
Post#5



Posts: 1,217
Joined: 1-December 12



Good day Larry,

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

In the Open event I've added this

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

Go to the top of the page
 
cheekybuddha
post Jan 22 2020, 08:39 AM
Post#6


UtterAccess Moderator
Posts: 13,015
Joined: 6-December 03
From: Telegraph Hill


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:
CODE
=RowNum([Form])


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Jan 22 2020, 08:40 AM
Post#7


UA Admin
Posts: 37,480
Joined: 20-June 02
From: Newcastle, WA


Right.

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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Jan 22 2020, 10:50 AM
Post#8


UtterAccess Moderator
Posts: 13,015
Joined: 6-December 03
From: Telegraph Hill


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

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
InfoHound
post Jan 22 2020, 11:16 AM
Post#9



Posts: 1,217
Joined: 1-December 12



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

CODE
=RowNum([Forms]![frmAddItems].[subfrmAddItemsDetails])


intRowNum displays "#Error"
Go to the top of the page
 
cheekybuddha
post Jan 22 2020, 11:23 AM
Post#10


UtterAccess Moderator
Posts: 13,015
Joined: 6-December 03
From: Telegraph Hill


See above Post#8

--------------------


Regards,

David Marten
Go to the top of the page
 
InfoHound
post Jan 22 2020, 01:03 PM
Post#11



Posts: 1,217
Joined: 1-December 12



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.

Thanks
This post has been edited by InfoHound: Jan 22 2020, 01:06 PM
Go to the top of the page
 
cheekybuddha
post Jan 22 2020, 01:06 PM
Post#12


UtterAccess Moderator
Posts: 13,015
Joined: 6-December 03
From: Telegraph Hill


Does that not happen when you try?

--------------------


Regards,

David Marten
Go to the top of the page
 
InfoHound
post Jan 22 2020, 02:02 PM
Post#13



Posts: 1,217
Joined: 1-December 12



After deleting the record I use a me.requery and everything works great.
Thanks
Go to the top of the page
 
Larry Larsen
post Jan 22 2020, 02:22 PM
Post#14


UA Editor + Utterly Certified
Posts: 24,585
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

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..???
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
InfoHound
post Jan 22 2020, 02:41 PM
Post#15



Posts: 1,217
Joined: 1-December 12



The only reference to the RowNum() public function is in the unbound control "intRowNum's" Control Source =RowNum([Form])
Go to the top of the page
 
Larry Larsen
post Jan 22 2020, 02:54 PM
Post#16


UA Editor + Utterly Certified
Posts: 24,585
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


uarulez2.gif thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
InfoHound
post Jan 22 2020, 03:30 PM
Post#17



Posts: 1,217
Joined: 1-December 12



Thanks Larry I appreciate your help.
Go to the top of the page
 
cheekybuddha
post Jan 22 2020, 04:56 PM
Post#18


UtterAccess Moderator
Posts: 13,015
Joined: 6-December 03
From: Telegraph Hill


[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]).

--------------------


Regards,

David Marten
Go to the top of the page
 
InfoHound
post Jan 22 2020, 07:23 PM
Post#19



Posts: 1,217
Joined: 1-December 12



Thanks David, you guys are the best.



Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    7th July 2020 - 08:05 PM