Subform Sequential Numbering - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Subform Sequential Numbering

Tim Gilbert

4 years ago

How can I create sequential numbering for subform entries?

The database tracks various activities that are sub-divided into component parts.  The data common to all the parts for a particular entry (e.g. date, location, client, etc) are in the one table and reflected on the main form.  But each activity can have multiple sequential (not concurrent) parts, the data for these items  (e.g. sequence, duration, speaker, volume, etc.) is stored in a related table that is displayed on a subform (continuous form).

Currently, I have an entry of on the main form for the number of "steps" in the activity, and each subform entry has an input for what step it is in the sequence.  Is there some way to (1) have the subform entries labled with a sequence number starting with 1 and (2) only ley the subform display the number of "rows" corresponding to the total number of steps (must be at least 1) in the main form?

Thanks
Tim

	Adam Schwanz @Reply 4 years ago
I think this is what you're looking for Custom Sequential Number

	Adam Schwanz @Reply 4 years ago
With being a subform, you might need Value From a Form too

Tim Gilbert OP @Reply

4 years ago

Thanks - I watched the Sequential Numbering video before posting. If I understand correctly, the method in the video seems to increment the custom number for each entry in the table. What I am hoping to do is subtly different. I want to restart numbering for each new activity.

For example, tracking conferences & seminars we track presentations. One table that contains all the data for the conference with a related table for each session in the conference. The conference table has data such as location, organizer, number of sessions, etc. The related session table has speaker, session sequence (1,2,3..). My goal is to have the subform show 1, 2, 3...for each entry of session sequence. Currently, the session sequence is entered manually by the user - this works but there's no check or control against entering bad data (1, 4, 2, 6; when the conference data says there are 3 sessions). Also, I want to restart at 1 for the next conference.

Thanks again for your time

	Adam Schwanz @Reply 4 years ago
You might be able to just set a default value then on the ID field inside the session sequence. Using DCount to count the number of sessions under that specific sequence (using the where section for criteria to get that exact session) and add +1. Then use NZ to make the first record #1.

	Kevin Robertson @Reply 4 years ago
I did this with a DMax in an AfterInsert event. I added 1 to this value. I also added a `Me.Refresh` in the AfterUpdate event of the first field to trigger the AfterInsert event.

	Richard Rost @Reply 4 years ago
Whatever your other criteria is, just add that to the DMax statement. For example: `X = DMax("RecordNumber","SomeTable","CustomerID=" & ID & " AND SomeOtherID=" & ID2)`

Tim Gilbert OP @Reply

4 years ago

Thanks for the replies and apologies for the delayed follow-up. After thinking more about this, re-watching the videos Adam recommended (and this one: https://www.youtube.com/watch?v=2yj6x5E4Z_0 ), I realized what I was asking for was essentially a calculated table field - which I know from Richard's training is an EVIL thing (https://www.599cd.com/blog/display-article.asp?ID=1718).

Instead I am planning to use conditional formatting to highlight any entries that are problematic.

Thanks again

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View