I have a sub form where I enter records. It has a number field where I number
each record. Usually starting of 1 and then in ascending order. Is there a
way of defaulting this number?
If the field is null or 0, then the first number defaulted would be 1 and
then the next number would default for the next record. However if I started
with say 6 then 7 would default next. So the default value would always be
one more than the last entry

I hope someone can help

Nick

Instead of Default Value, use the BeforeInsert (or BeforeUpdate) event of
the form to supply the next number.

This example assumes you have your CD album in the main form, the tracks in
the subform, and you want to automatically insert the next track number for
the album:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter an album in the main form first."
Else
strWhere = "AlbumID = " & Me.Parent!AlbumID
Me.TrackID = Nz(DMax("TrackID", "tblTrack", strWhere), 0) + 1
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nick hfrupn" <(E-Mail Removed)> wrote in message
news:E1977267-AF67-48F0-8572-(E-Mail Removed)...
>I have a sub form where I enter records. It has a number field where I
>number
> each record. Usually starting of 1 and then in ascending order. Is there a
> way of defaulting this number?
> If the field is null or 0, then the first number defaulted would be 1 and
> then the next number would default for the next record. However if I
> started
> with say 6 then 7 would default next. So the default value would always be
> one more than the last entry
>
> I hope someone can help
>
> Nick

The approach will work for a main form.

You can use DMax() to get the highest value used so far, and add 1.

You still use Form_BeforeInsert.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nick hfrupn" <(E-Mail Removed)> wrote in message
news:4F0AA01A-45E5-457D-A77A-(E-Mail Removed)...
> Allen,
> Thanks for your reply. I mistakenly stated sub form when I only use a
> single
> form.
> I have an ID field which is an auto number, an assemble number, assembly
> description, item number, item description and manufacturer field. Some of
> the items of an assembly may not be included, hens the logic in numbers
> not
> necessarily being in sequence as per my original post.
> Your suggestion I think is on the right track but I can't seem to modify
> it
> to work for me. Do you have any other suggestions?
>
> Regards
> Nick
>
>
> "Allen Browne" wrote:
>
>> Instead of Default Value, use the BeforeInsert (or BeforeUpdate) event of
>> the form to supply the next number.
>>
>> This example assumes you have your CD album in the main form, the tracks
>> in
>> the subform, and you want to automatically insert the next track number
>> for
>> the album:
>>
>> Private Sub Form_BeforeInsert(Cancel As Integer)
>> Dim strWhere As String
>> If Me.Parent.NewRecord Then
>> Cancel = True
>> MsgBox "Enter an album in the main form first."
>> Else
>> strWhere = "AlbumID = " & Me.Parent!AlbumID
>> Me.TrackID = Nz(DMax("TrackID", "tblTrack", strWhere), 0) + 1
>> End If
>> End Sub
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Nick hfrupn" <(E-Mail Removed)> wrote in message
>> news:E1977267-AF67-48F0-8572-(E-Mail Removed)...
>> >I have a sub form where I enter records. It has a number field where I
>> >number
>> > each record. Usually starting of 1 and then in ascending order. Is
>> > there a
>> > way of defaulting this number?
>> > If the field is null or 0, then the first number defaulted would be 1
>> > and
>> > then the next number would default for the next record. However if I
>> > started
>> > with say 6 then 7 would default next. So the default value would always
>> > be
>> > one more than the last entry
>> >
>> > I hope someone can help
>> >
>> > Nick
>>
>>
>>