numbering subrecords

  • Thread starter Thread starter Hydra
  • Start date Start date
H

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?
 
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.
 
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?
Click to expand...

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.
 
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



John W. Vinson said:
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?
Click to expand...

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.
Click to expand...
 
I tried your code but when I substituted my table and field names it threw a
type mismatch.

Still searching for the problem.

John W. Vinson said:
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?
Click to expand...

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.
Click to expand...
 
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
Click to expand...
Click to expand...


throws a type mismatch.

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

Still searching for the problem.

John W. Vinson said:
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?
Click to expand...

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.
Click to expand...
Click to expand...
 
"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.


John W. Vinson said:
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?
Click to expand...

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.
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...

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.
 
Back
Top