Custom Counter

[DS]

How do you make a field add or go up one number everytime a new record
is created? If Line ID is 1 and I go to a new record I want Line ID to
=2, so on and so forth.
Thanks
DS

 

[Allen Browne]

In the BeforeInsert event of the form, use DMax() to get the highest value
so far entered for the foreign key.

This example assumes a main form where you enter your CD albums, and a
subform where you enter the tracks. A soon as you start adding a track name,
it fills in the next available TrackId for the album in the main form:

Private Sub Form_BeforeInsert(Cancel As Integer)
With Me.Parent.AlbumId
If IsNull(.Value) Then
Cancel = True
MsgBox "Enter the main for record first."
Else
Me.[TrackId] = Nz(DMax("TrackId", "tblAlbum", "AlbumId = " &
..Value),0) + 1
End If
End With
End Sub

 

[DS]

In the BeforeInsert event of the form, use DMax() to get the highest value
so far entered for the foreign key.

This example assumes a main form where you enter your CD albums, and a
subform where you enter the tracks. A soon as you start adding a track name,
it fills in the next available TrackId for the album in the main form:

Private Sub Form_BeforeInsert(Cancel As Integer)
With Me.Parent.AlbumId
If IsNull(.Value) Then
Cancel = True
MsgBox "Enter the main for record first."
Else
Me.[TrackId] = Nz(DMax("TrackId", "tblAlbum", "AlbumId = " &
.Value),0) + 1
End If
End With
End Sub

Thanks Allen. I'm trying it now.
DS

 

[DS]

In the BeforeInsert event of the form, use DMax() to get the highest
value so far entered for the foreign key.

This example assumes a main form where you enter your CD albums, and a
subform where you enter the tracks. A soon as you start adding a track
name, it fills in the next available TrackId for the album in the main
form:

Private Sub Form_BeforeInsert(Cancel As Integer)
With Me.Parent.AlbumId
If IsNull(.Value) Then
Cancel = True
MsgBox "Enter the main for record first."
Else
Me.[TrackId] = Nz(DMax("TrackId", "tblAlbum", "AlbumId = "
& .Value),0) + 1
End If
End With
End Sub

Thanks Allen. I'm trying it now.
DS

Allen, this works fine except I need to attach it elsewhere. I have one
Sub-Form "SalesDetails". I have 2 Listboxes. When you click Listbox1 I
want to increase the ID number, when you click Listbox2 I want to copy
it to the next record. I would imagine I would have to put it on the
"On Click" property of the Listboxes, but how would I reference it?
Thank You
Sincerely
DS

 

[Allen Browne]

Use the AfterUpdate event of the list box (assuming it is not a
multi-select) to set the value of the field in the subform, or to AddNew to
the RecordsetClone of the subform is you want to create a new record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In the BeforeInsert event of the form, use DMax() to get the highest
value so far entered for the foreign key.

This example assumes a main form where you enter your CD albums, and a
subform where you enter the tracks. A soon as you start adding a track
name, it fills in the next available TrackId for the album in the main
form:

Private Sub Form_BeforeInsert(Cancel As Integer)
With Me.Parent.AlbumId
If IsNull(.Value) Then
Cancel = True
MsgBox "Enter the main for record first."
Else
Me.[TrackId] = Nz(DMax("TrackId", "tblAlbum", "AlbumId = " &
.Value),0) + 1
End If
End With
End Sub

Thanks Allen. I'm trying it now.
DS

Allen, this works fine except I need to attach it elsewhere. I have one
Sub-Form "SalesDetails". I have 2 Listboxes. When you click Listbox1 I
want to increase the ID number, when you click Listbox2 I want to copy it
to the next record. I would imagine I would have to put it on the "On
Click" property of the Listboxes, but how would I reference it?
Thank You
Sincerely
DS

 

[Huey]

How about using the autonumber datatype.