Solved

Sequential record numbering within "parent" group

Posted on 2004-09-20
6
197 Views
Last Modified: 2008-03-10
Greetings all...

I'm setting up a hierarchy of three related datasets. The governing entity is a PROJECT. A PROJECT may in turn contain multiple ZONES, and each ZONE may in turn contain multiple OBJECTS.

I'm experiencing a brain cramp. Typically I would let Access assign unique ID numbers (autonumbering) for each new ZONE that is created within a PROJECT, but in this case, it will be important for the users to have a "handle" for each ZONE which they can use in referencing any ZONE. This kind of easily identifiable "handle" would not really exist (unless of course the user was asked to assign the ID for each new ZONE), because with autonumbering, the assignment of new ZONES won't have any discernible pattern: the first ZONE for any PROJECT may be assigned ID #345, the second may be assigned #962, and so on.

Maybe I'm just worrying about aesthetics, but it would be nice to have the first ZONE assigned to any PROJECT receive ID #1, the second #2, and so on. So the "Big River" PROJECT has ZONES 1 - 15, the "Little River" PROJECT has ZONES 1 - 9, and so on. My gut feeling is that the only way to gain this sort  of autonumbering would be to set the ZONE ID field as LONG, automatically create ZONE 1 when the PROJECT is created, and then fire off a manual routine each time a new ZONE is created which would determine the MAX number that has already been assigned within that PROJECT and just increment it by 1. Index the ZONE table on both PROJECT ID and ZONE ID. Then repeat the same process in the OBJECT table, indexing that table on PROJECT ID, ZONE ID and OBJECT ID.

Do I think aright? Or am I forgetting an obvious element of functionality that Access provides that would handle this kind of repetitive numbering within "Parent" groupings?

Thanks, folks. I never cease to be amazed at the breadth and depth of talent out there, and I look forward to your feedback.

...Jed
0
Comment
Question by:jofoco4
  • 3
  • 2
6 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
You could use a manual procedure to assign your zone IDs, but I wouldn't recommend it. Autonumbers should never be visible to the user and shouldn't be have any meaning - the only thing you can rely on them for is to be unique within their own sequence.

However, you could determine which zone a particular record is, within its parent. An expression like this:
DCount("*","ZONES","[ZoneID]<" & Me!txtZoneID & " AND [ProjectID]=" & Me!txtProjectID)+1

on a form that contains the zone's autonumber in txtZoneID and the projectID in txtProjectID would tell you which zone that is in relation to its project. So for example, if project 12 has zone records 345, 346 and 347, the expression would display 2 for zone 346, since there is 1 zone with a lower number than itself for projectID 12. This would only work though if zones were never deleted from that project.
0
 
LVL 1

Expert Comment

by:Clippit
Comment Utility
I have an application where entities are added with an autonumber PK but also have a sequence number which is meaninful and which will not be changed unless the user changes it. (This might be a problem with the solution above- if a zone is deleted the others would get different numbers- is this what you want?)

When the user adds a zone I assign it a new sequence number in the BeforeInsert event, by finding the next available number. (If 1,2, and 3 have been used it will be 4, but if 2 has been deleted the new one will be 2.) I do this in a small routine that reads through a recordset that contains the entities that are children of the same parent. I also provide a button on the form to "compress" the numbers, so that if an entity has been deleted leaving the numbering as 1,2,4,5 it will move 4 and 5 down to fill the gap.

I can post code samples if it would be useful.
0
 

Author Comment

by:jofoco4
Comment Utility
Thanks to you both, Shane and Clippit...

Shane, I absolutely agree with you in principle regarding the use of autonumbers. However, as you suggest, if zones are deleted (and this will inevitably happen, unfortunately) "the results might become unpredictable".

Clippit, it sounds like we're onto something here. And yes, a few code samples would defitely help me orient. The compression functionality is probably more than I'll need; I don't mind a deleted zone burning a numeric assignment as long as the results are (for example) 1, 2, 3, 5, 6 ... that would still be very workable within the environmental context.

Thanks to you both. I'll leave the question open for another day or two for Clippit and I to yak a bit more ...

...Jed
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Accepted Solution

by:
Clippit earned 250 total points
Comment Utility
Jed-

As I said, I set the "Sequence Number" in the BeforeInsert event. My code is pretty complex and probably does more than you need, so here's a simple version I just whipped up.

Option 1- DMax: Simple, but may be a performance problem:
Private Sub Form_BeforeInsert(Cancel As Integer)
  Me.Sequence_Number = DMax("[Sequence Number]", "EntityTest", BuildCriteria("ParentEntity", dbLong, Me.Parent.EntityID)) + 1
End Sub
(note the use of the seldom used function BuildCriteria to make the DMAX statement easier to build. In my scenario the form is a subform and the parent entity id is retreived from the parent form)

Option 2- slicker as it uses the form's recordset which is already open:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rsFormData As DAO.Recordset
Dim intMax As Integer
Set rsFormData = Me.RecordsetClone
rsFormData.MoveFirst
Do While Not rsFormData.EOF
  If rsFormData![Sequence Number] > intMax Then
    intMax = rsFormData![Sequence Number]
  End If
  rsFormData.MoveNext
Loop

Me.Sequence_Number = intMax + 1

End Sub


Option 3: Use a text box on the form to calculate the existing max sequence (presumable the text box should be hidden):
Create a text box in the form footer with an experssion like:
=Max([Sequence Number])
Then make the code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Sequence_Number = Nz(Me.txtMaxSequence) + 1
End Sub

All these options assume records will always be added through the form.
0
 

Author Comment

by:jofoco4
Comment Utility
Once again, many thanks for your thoughts...
0
 
LVL 1

Expert Comment

by:Clippit
Comment Utility
You're welcome- thanks for the grade.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now