numbering subrecords

[Hydra]

How can I autoatically number a subrecord sequentially?

I have a three level form and I want the record nmber to be the same as the
number on the record selector bar.

Can I pick up the largest record number plus one when I enter the new record
row?
or just make the record equal to the number in the record selector?

 

[Hydra]

I shold make it clear that I am using this number as the key for the table:
third level subrecord has a three part key, second level record has a two
part key.

 

[John W. Vinson]

How can I autoatically number a subrecord sequentially?

I have a three level form and I want the record nmber to be the same as the
number on the record selector bar.

Can I pick up the largest record number plus one when I enter the new record
row?
or just make the record equal to the number in the record selector?

The record selector would be a dreadfully bad idea, since it will change
whenever you resort or filter the data!

What you can do is use code in your sub-subform's Beforeinsert event to
calculate the next ID. Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

This assumes that the first and second level ID's are available in the
sub-subform, you might need to use Parent! rather than Me! to pull a field
from the parent form if not.

 

[Hydra]

That is pretty much what I am trying to do. What is the NZ for?

What i have is

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim Key1 As Single
Dim key2 As Single
Dim NewRecord As Single

Key1 = Me.[Major Event Key]
key2 = Me.SA_Key

NewRecord = (DMax("[Criteria #]", "[Criteria Table]")) , "[ID] =" & Key1 And
"[SAKey] =" & key2))
NewRecord = NewRecord + 1
Me.Criteria_Number = NewRecord

End sub

But when I do this it throws and error saying I canceld the operation.
If I change [Critieria Table] to a Query on the Critera Table , then it runs
provided I don't use the keyw and key2 as criteria. Why does one throw the
cancel error and the other not?

Obviously, if it runs against the [Criteria Query] I always get the same
answer if i don't use the criteria, but when I put in the critera (key1 and
key2) then it throws a type mismatch, which I can't resolve.


I'll try your code next.

Thanks

How can I autoatically number a subrecord sequentially?

I have a three level form and I want the record nmber to be the same as the
number on the record selector bar.

Can I pick up the largest record number plus one when I enter the new record
row?
or just make the record equal to the number in the record selector?

The record selector would be a dreadfully bad idea, since it will change
whenever you resort or filter the data!

What you can do is use code in your sub-subform's Beforeinsert event to
calculate the next ID. Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

This assumes that the first and second level ID's are available in the
sub-subform, you might need to use Parent! rather than Me! to pull a field
from the parent form if not.

 

[Hydra]

I tried your code but when I substituted my table and field names it threw a
type mismatch.

Still searching for the problem.

How can I autoatically number a subrecord sequentially?

I have a three level form and I want the record nmber to be the same as the
number on the record selector bar.

Can I pick up the largest record number plus one when I enter the new record
row?
or just make the record equal to the number in the record selector?

The record selector would be a dreadfully bad idea, since it will change
whenever you resort or filter the data!

What you can do is use code in your sub-subform's Beforeinsert event to
calculate the next ID. Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

This assumes that the first and second level ID's are available in the
sub-subform, you might need to use Parent! rather than Me! to pull a field
from the parent form if not.

 

[Hydra]

So I stuck in msgBoxes to check all the values.

msgbox (Me!Level3ID)
msgbox (NZ(DMax("[Level3ID]", "[Table3]")
msgbox (Me![Level1ID])
msgbox (Me!Level2ID)

all return the correct values, but the final code:

Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _

"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1

throws a type mismatch.

I tried your code but when I substituted my table and field names it threw a
type mismatch.

Still searching for the problem.

How can I autoatically number a subrecord sequentially?

I have a three level form and I want the record nmber to be the same as the
number on the record selector bar.

Can I pick up the largest record number plus one when I enter the new record
row?
or just make the record equal to the number in the record selector?

The record selector would be a dreadfully bad idea, since it will change
whenever you resort or filter the data!

What you can do is use code in your sub-subform's Beforeinsert event to
calculate the next ID. Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

This assumes that the first and second level ID's are available in the
sub-subform, you might need to use Parent! rather than Me! to pull a field
from the parent form if not.

 

[Hydra]

"The record selector would be a dreadfully bad idea, since it will change
whenever you resort or filter the data!"

Actually, no. It would work fine. All I need is the highest number whichis
what the record selector shows when it is on the new entry line. After I
wetie that into the table it becomes permanent, and I don't care about sorts
or flters subsequently.

I just don;t know how to read the record selector, or even if it is possible.

How can I autoatically number a subrecord sequentially?

I have a three level form and I want the record nmber to be the same as the
number on the record selector bar.

Can I pick up the largest record number plus one when I enter the new record
row?
or just make the record equal to the number in the record selector?

The record selector would be a dreadfully bad idea, since it will change
whenever you resort or filter the data!

What you can do is use code in your sub-subform's Beforeinsert event to
calculate the next ID. Air code, needs to be adapted to your tablenames and
fieldnames:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1
End Sub

This assumes that the first and second level ID's are available in the
sub-subform, you might need to use Parent! rather than Me! to pull a field
from the parent form if not.

 

[John W. Vinson]

So I stuck in msgBoxes to check all the values.

msgbox (Me!Level3ID)
msgbox (NZ(DMax("[Level3ID]", "[Table3]")
msgbox (Me![Level1ID])
msgbox (Me!Level2ID)

all return the correct values, but the final code:

Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _

"[Level1ID] = " & Me![Level1ID] & _
& " AND Me![Level2ID] = " & Me!Level2ID)) + 1

Please copy and paste the actual code you're using. Also... what are the
datatypes of the ID fields? If they're Text then you need the syntactically
required quotemarks:

Me!Level3ID = NZ(DMax("[Level3ID]", "[Table3]", _
"[Level1ID] = '" & Me![Level1ID] & _
& "' AND Me![Level2ID] = '" & Me!Level2ID & "'")) + 1

I'm using ' marks inside the " strings so that the third argument to DMax will
be

[Level2ID] = 'xyz' AND [Level3ID] = '123'

once the strings get concatenated.