Nicely consecutive IDs within a subform

[David Portwood]

On a main form I have an ID field, call it MainID. On a subform I have an ID
field, call it SubID. The forms are linked 1:M. I'd like SubID to renumber
from 1 for each master record. Right now SubID is Autonumber so of course it
doesn't do that. Is there any way I can make this happen?

 

[John W. Vinson]

On a main form I have an ID field, call it MainID. On a subform I have an ID
field, call it SubID. The forms are linked 1:M. I'd like SubID to renumber
from 1 for each master record. Right now SubID is Autonumber so of course it
doesn't do that. Is there any way I can make this happen?

Change the datatype from Autonumber to Long Integer. In the Subform's
Beforeinsert event put code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.SubID = NZ(DMax("[SubID]", "tablename", "[MainID] = " & Me.MainID)) + 1
End Sub

This will look up the largest existing SubID for the currently selected
MainID; if there is no such record, NZ() will return a 0. The +1 will
increment to the next unused SubID.

John W. Vinson [MVP]

 

[David Portwood]

I'll give it a try in the morning. Thanks.

On a main form I have an ID field, call it MainID. On a subform I have an
ID
field, call it SubID. The forms are linked 1:M. I'd like SubID to renumber
from 1 for each master record. Right now SubID is Autonumber so of course
it
doesn't do that. Is there any way I can make this happen?

Change the datatype from Autonumber to Long Integer. In the Subform's
Beforeinsert event put code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.SubID = NZ(DMax("[SubID]", "tablename", "[MainID] = " & Me.MainID)) + 1
End Sub

This will look up the largest existing SubID for the currently selected
MainID; if there is no such record, NZ() will return a 0. The +1 will
increment to the next unused SubID.

John W. Vinson [MVP]

 

[Guest]

Hi All

I use this to increment a subform record number

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.PayItemID = Nz(DMax("[PayItemID]", "tblPaymentItems", "[PayID] = " &
Me.PayID)) + 1
End Sub

So you get
MainRecord 1
LinkedRecord 1.0, 1.1, 1.2, 1.3, 1.4 and 1.5 etc This works fine.

Just a thought to make it look a little better. Would it be possible to
increment "letters" instead of numbers
- there are never more than 15 linked records.
So you may get
MainRecord 1
LinkedRecord 1A, 1B, 1C, 1D and 1E etc

MainRecord 2
LinkedRecord 2A, 2B, 2C, 2D and 2E etc

The actual records would show A B C etc (the 2A, 2B etc are concenated)




--
Wayne
Manchester, England.

On a main form I have an ID field, call it MainID. On a subform I have an ID
field, call it SubID. The forms are linked 1:M. I'd like SubID to renumber
from 1 for each master record. Right now SubID is Autonumber so of course it
doesn't do that. Is there any way I can make this happen?

Change the datatype from Autonumber to Long Integer. In the Subform's
Beforeinsert event put code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.SubID = NZ(DMax("[SubID]", "tablename", "[MainID] = " & Me.MainID)) + 1
End Sub

This will look up the largest existing SubID for the currently selected
MainID; if there is no such record, NZ() will return a 0. The +1 will
increment to the next unused SubID.

John W. Vinson [MVP]

 

[John W. Vinson]

Hi All

I use this to increment a subform record number

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.PayItemID = Nz(DMax("[PayItemID]", "tblPaymentItems", "[PayID] = " &
Me.PayID)) + 1
End Sub

So you get
MainRecord 1
LinkedRecord 1.0, 1.1, 1.2, 1.3, 1.4 and 1.5 etc This works fine.

Just a thought to make it look a little better. Would it be possible to
increment "letters" instead of numbers
- there are never more than 15 linked records.
So you may get
MainRecord 1
LinkedRecord 1A, 1B, 1C, 1D and 1E etc

MainRecord 2
LinkedRecord 2A, 2B, 2C, 2D and 2E etc

The actual records would show A B C etc (the 2A, 2B etc are concenated)

Good move... <concatenating separate fields that is>

Try

Me.PayItemID = Chr(NZ(Asc(DMax("[PayItemID]", "tblPaymentItems", "PayID = " &
Me.PayID), 64) + 1))

This will return A (Chr(65)) for the first PayItemID, and the next letter
alphabetically for existing letters.

A trap for Z would be useful... "never" is a long time, and you could someday
end up with LinkedRecord values 312[, 312\, 312] and 312^ (on beyond zebra!)

John W. Vinson [MVP]

 

[Guest]

Hi John,

I am trying to do the same thing and have followed your advice. Whenever I
create a new subform record, however, the SubID is blank. Any ideas on what
could be causing this?

Thanks

On a main form I have an ID field, call it MainID. On a subform I have an ID
field, call it SubID. The forms are linked 1:M. I'd like SubID to renumber
from 1 for each master record. Right now SubID is Autonumber so of course it
doesn't do that. Is there any way I can make this happen?

Change the datatype from Autonumber to Long Integer. In the Subform's
Beforeinsert event put code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.SubID = NZ(DMax("[SubID]", "tablename", "[MainID] = " & Me.MainID)) + 1
End Sub

This will look up the largest existing SubID for the currently selected
MainID; if there is no such record, NZ() will return a 0. The +1 will
increment to the next unused SubID.

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John,

I am trying to do the same thing and have followed your advice. Whenever I
create a new subform record, however, the SubID is blank. Any ideas on what
could be causing this?

If you're using the BeforeInsert event, the SubID will be blank until you
actually enter data in some other field on the form. Is that not the case?

John W. Vinson [MVP]

 

[Guest]

Of course I realized that just before I read your response.

However, when I do attempt to enter data it gives me the following 3075
run-time error:

Syntax error (missing operator) in query expression "[MainID] = "

Do you know what could be causing that? I entered the code exactly as you
had provided.

 

[John W. Vinson]

Of course I realized that just before I read your response.

However, when I do attempt to enter data it gives me the following 3075
run-time error:

Syntax error (missing operator) in query expression "[MainID] = "

Do you know what could be causing that? I entered the code exactly as you
had provided.

Do you have a control on your form named MainID? The code assumes that you do;
that it's the Child Link Field of the subform, so that it's populated with a
foreign key value to the main table record; and that there is at least one
record in the mainform's table.

John W. Vinson [MVP]

 

[David Portwood]

I decided not to go this route because of performance problems caused by the
DMax() function. If you are pulling data across a network in a multiuser
environment, this kind of function is going to be very slow. So I've left my
subform ID as Autonumber and even though it doesn't look quite so pretty, it
works just fine.

I would be willing to look at this again if someone knows of another way to
achieve the objective without using a domain aggregate function.

Hi John,

I am trying to do the same thing and have followed your advice. Whenever
I
create a new subform record, however, the SubID is blank. Any ideas on
what
could be causing this?

Thanks

On a main form I have an ID field, call it MainID. On a subform I have
an ID
field, call it SubID. The forms are linked 1:M. I'd like SubID to
renumber
from 1 for each master record. Right now SubID is Autonumber so of
course it
doesn't do that. Is there any way I can make this happen?

Change the datatype from Autonumber to Long Integer. In the Subform's
Beforeinsert event put code like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.SubID = NZ(DMax("[SubID]", "tablename", "[MainID] = " & Me.MainID)) +
1
End Sub

This will look up the largest existing SubID for the currently selected
MainID; if there is no such record, NZ() will return a 0. The +1 will
increment to the next unused SubID.

John W. Vinson [MVP]