Incremented Value for Subform Line Number (?)

[tbl]

Is there a good way to offer a default, incremented
line-number in a subform, where the first subform record
would offer up a line number of "1", and each successive
subform record would have the default incremented by 1?

The number of subform records would never be more than 15,
for any given main form record.

This would differ from the common "invoice number" request,
in that each new main form record would start the subform
record (line) numbering default anew (1 thru xx).

After 3 hours of trying this and that, I've only produced
impared lucidity!

 

[Allen Browne]

This example is for entering CD albums in the main form, and the tracks of
the CD in the subform (1 ~ 15 or whatever.)

It assumes you have:
- a table for entering the albums, with an AlbumID primary key;
- a related table named tblTrack, with fields:
AlbumID relates to AlbumID in the album table;
TrackNum the number you need to set to the next available.

It checks that the main form is not at a new record (where AlbumID would be
blank), and then uses DMax() to get the highest value assigned so far for
this album, Nz() to convert that to zero if there's none, and adds one.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter or select a record in the main form first."
Else
strWhere = "[AlbumID] = " & Me.Parent![AlbumID]
Me.[TrackNum] = Nz(DMax("TrackNum", "tblTrack", strWhere),0) + 1
End If
End Sub

If you need help with the DMax(), it uses the same arguments as DLookup(),
so this should help:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[tbl]

This example is for entering CD albums in the main form, and the tracks of
the CD in the subform (1 ~ 15 or whatever.)

It assumes you have:
- a table for entering the albums, with an AlbumID primary key;
- a related table named tblTrack, with fields:
AlbumID relates to AlbumID in the album table;
TrackNum the number you need to set to the next available.

It checks that the main form is not at a new record (where AlbumID would be
blank), and then uses DMax() to get the highest value assigned so far for
this album, Nz() to convert that to zero if there's none, and adds one.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter or select a record in the main form first."
Else
strWhere = "[AlbumID] = " & Me.Parent![AlbumID]
Me.[TrackNum] = Nz(DMax("TrackNum", "tblTrack", strWhere),0) + 1
End If
End Sub

If you need help with the DMax(), it uses the same arguments as DLookup(),
so this should help:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Thanks to Allen Browne!

After a few stumbles and fumbles, I finally got that to work
(an amazing feat for someone with no coding experience or
aptitude).

The very next day, a new complexity was added.

We are inputing the data from paper forms, where the subform
has "Line Numbers" that we want to enter, much like your CD
Tracks have "Track Numbers".

The new complexity is: when the lines on one page of the
paper form are used up, the data continues on a second,
fresh page, starting with "Line 1" again (the paper has a
place for the field tech to write in "Page ___ of ___".

So I added a field, "PageNum" to the "sub" table, but...

I can't seem to sort out how to change your "where"
statement to reflect this plain-text concept:

Where [PageNum] is highest value, and [AlbumID] = &
Me.Parent![AlbumID]

Any thoughts appreciated.

 

[tbl]

This example is for entering CD albums in the main form, and the tracks of
the CD in the subform (1 ~ 15 or whatever.)

It assumes you have:
- a table for entering the albums, with an AlbumID primary key;
- a related table named tblTrack, with fields:
AlbumID relates to AlbumID in the album table;
TrackNum the number you need to set to the next available.

It checks that the main form is not at a new record (where AlbumID would be
blank), and then uses DMax() to get the highest value assigned so far for
this album, Nz() to convert that to zero if there's none, and adds one.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter or select a record in the main form first."
Else
strWhere = "[AlbumID] = " & Me.Parent![AlbumID]
Me.[TrackNum] = Nz(DMax("TrackNum", "tblTrack", strWhere),0) + 1
End If
End Sub

If you need help with the DMax(), it uses the same arguments as DLookup(),
so this should help:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Thanks to Allen Browne!

After a few stumbles and fumbles, I finally got that to work
(an amazing feat for someone with no coding experience or
aptitude).

The very next day, a new complexity was added.

We are inputing the data from paper forms, where the subform
has "Line Numbers" that we want to enter, much like your CD
Tracks have "Track Numbers".

The new complexity is: when the lines on one page of the
paper form are used up, the data continues on a second,
fresh page, starting with "Line 1" again (the paper has a
place for the field tech to write in "Page ___ of ___".

So I added a field, "PageNum" to the "sub" table, but...

I can't seem to sort out how to change your "where"
statement to reflect this plain-text concept:

Where [PageNum] is highest value, and [AlbumID] = &
Me.Parent![AlbumID]

Any thoughts appreciated.

Well, I *thought* I had it working. Now it seems to be
broken, giving this up when run :

"Runtime error 2001"
"You cancelled the previous operation."

Clicking on Help brings up a blank Help window.

The debugger takes me to the second line below "Else".

If the parent record is a new record, the code behaves.

Any thoughts, anyone?