Creating sequential numbers in subform

[CCH]

I'm creating my first Access form and am trying to figure out how to have a
field within a subform generate sequential numbers. The main table contains
Master_ID as its PK and the table the subform is based off of has the
following fields
Master_ID
Step_ID(PK)
Step_Number
Step_Instructions

The Master_ID's are linked in a one-to-many relationship.

The user will be entering unique instructions for each Master_ID. When the
subform opens, I would like the first Step_Number to start with 1, the second
step be 2, etc.. Individual Master_ID's could have one or many steps. Every
time a new Master_ID is created, the Step_Number should start over with 1 and
increment with each entry for that particular Master_ID.

I'd also like to be able to insert a row, so if a user enters steps 1-5, and
then realizes they have forgotten a step, they can insert it in the
appropriate place. An alternative to this would be to add it at the end but
change the number from 6 to 2 and then clicking on a command button to
reorder the steps (similar to the Netflix site queue, if you are familiar
with that)

Thanks for any help you can give!

 

[Arvin Meyer [MVP]]

This code in the subform's Current event will give you line numbers,
assuming that you save them in the underlying table:

Private Sub Form_Current()
On Error Resume Next
Dim x As Integer
x = DMax("Line", "TableName", "ID =" & Forms!MainFormName!ID)

If IsNull(x) Then
Me!Line.DefaultValue = 1
Else
Me!Line.DefaultValue = x + 1
End If

End Sub

So that gives you the default value for the line, which you can then change
manually.

 

[CCH]

I'm saving the line numbers in the Step_Number column of the subform table,
so would I replace " Line" with Step_Number?

Private Sub Form_Current()

On Error Resume Next
Dim x As Integer
x = DMax("Step_Number", "Tbl_Subform_Table", "Master_ID =" &
Forms!MasterTable_Form!ID)

If IsNull(x) Then
Me!Step_Number.DefaultValue = 1
Else
Me!Step_Number.DefaultValue = x + 1
End If

End Sub

The subform is a continuous form, and it is still only showing 1 in each
step_number text box

 

[Krzysztof Naworyta]

Hi, Arvin

Very interesting approach...

I've tried to simplify this with:

1.
writing other "if":

if Me.NewRecord then

to avoid calling DMax() in every "current" event

2.
using textbox in footer:
=Max(Line)
instead of DMax(), but have got some hazards...

But what do you think about this:

Private Sub Form_BeforeInsert(...)
Dim x As Integer

With Me.RecordsetClone
'.MoveLast
x = .RecordCount
End With
Me.line = x + 1
End sub

--
KN


Juzer Arvin Meyer [MVP] <[email protected]> napisa³
| This code in the subform's Current event will give you line numbers,
| assuming that you save them in the underlying table:
|
| Private Sub Form_Current()
| On Error Resume Next
| Dim x As Integer
| x = DMax("Line", "TableName", "ID =" & Forms!MainFormName!ID)
|
| If IsNull(x) Then
| Me!Line.DefaultValue = 1
| Else
| Me!Line.DefaultValue = x + 1
| End If
|
| End Sub
|
| So that gives you the default value for the line, which you can then
| change manually.
|
| || I'm creating my first Access form and am trying to figure out how to
|| have a
|| field within a subform generate sequential numbers. The main table
|| contains
|| Master_ID as its PK and the table the subform is based off of has the
|| following fields
|| Master_ID
|| Step_ID(PK)
|| Step_Number
|| Step_Instructions
||
|| The Master_ID's are linked in a one-to-many relationship.
||
|| The user will be entering unique instructions for each Master_ID. When
|| the
|| subform opens, I would like the first Step_Number to start with 1, the
|| second
|| step be 2, etc.. Individual Master_ID's could have one or many steps.
|| Every
|| time a new Master_ID is created, the Step_Number should start over
|| with 1 and
|| increment with each entry for that particular Master_ID.
||
|| I'd also like to be able to insert a row, so if a user enters steps
|| 1-5, and
|| then realizes they have forgotten a step, they can insert it in the
|| appropriate place. An alternative to this would be to add it at the
|| end but
|| change the number from 6 to 2 and then clicking on a command button to
|| reorder the steps (similar to the Netflix site queue, if you are
|| familiar with that)
||
|| Thanks for any help you can give!

--
KN

archiwum grupy:
http://groups.google.pl/advanced_search
(grupa: pl*msaccess)

 

[CCH]

Am I just supposed to try the last last section? I'm getting an error on the
'.MoveLast. Is there supposed to be a column name between the ' and the .

 

[Arvin Meyer [MVP]]

Yes
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

Hi, Arvin

Very interesting approach...

I've tried to simplify this with:

1.
writing other "if":

if Me.NewRecord then

to avoid calling DMax() in every "current" event

That would probably work, but I prefer that the new line at the end of the
recordset have the default next number. Unless there are tens of thousands
of records or more in the subform table, the DMax() function will run in
milliseconds.

2.
using textbox in footer:
=Max(Line)
instead of DMax(), but have got some hazards...

But what do you think about this:

Private Sub Form_BeforeInsert(...)
Dim x As Integer

With Me.RecordsetClone
'.MoveLast
x = .RecordCount
End With
Me.line = x + 1
End sub

Try it.

 

[Arvin Meyer [MVP]]

Usually, to get an accurate recordcount you need to Move to the end of the
recordset. He has .Move Last commented out.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com