Re: Incremented Value for Subform Line Number (?)
- From: tbl <hateThatSpam@xxxxxxxxxxx>
- Date: Wed, 18 Apr 2007 12:48:42 -0700
On Thu, 13 Jul 2006 14:19:00 +0800, "Allen Browne"
<AllenBrowne@xxxxxxxxxxxxxx> wrote:
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
.
- Follow-Ups:
- Prev by Date: Re: Lost All Forms
- Next by Date: Re: How Do I make fields not appear on a form or report
- Previous by thread: Re: refreshing combo box on subform
- Next by thread: Re: Incremented Value for Subform Line Number (?)
- Index(es):
Relevant Pages
|
