Solved Reset numbering on subform when new master record is added (1 Viewer)

Joemon

New member
Local time
Today, 11:26
Joined
May 12, 2022
Messages
4
I'm a novice user hoping for some quick help.

I have created a master form with two subforms.

The master form (ProjDataForm) contains an autonumber ID field (ID). It houses the primary key.

The subforms (FeatDataForm and FutuireFeatDataForm) are linked to the primary key via dummy (non-visible) number fields. Each subform also contains an autonumber field that numbers each subrecord as it is entered.

The forms seem to work well together, with both subforms capturing as many records as needed and tying them to their appropriate master record. I would like to have the subforms restart numbering at 1 each time a new master record is opened. I've done some research and tried applying different expressions using the DMax function, but I'm not sure what event to apply it to or how to differentiate between forms.

Again, all help is appreciated. Thanks in advance.
 

plog

Banishment Pending
Local time
Today, 10:26
Joined
May 11, 2011
Messages
11,613
First, why? How are you going to use this number outside of the database?

Second, yes it will involve a DMax. You would make the field that holds this number a long text (not an autonumber) and then have to write code to generate your number and then stick it on an event in the form when a new record is created.

Again, why? What purpose/problem does this serve?
 

Joemon

New member
Local time
Today, 11:26
Joined
May 12, 2022
Messages
4
The database is (ironically) intended to track new version features and additions for software development. The concept is to enter project data in the master form, with numbered listings for multiple new features and future features contained in the subforms. So when a new master record is opened, it would be ideal for the newly added features and future features in the subforms to be numbered individually, but restarting at 1 each time, and linked only to their new master record.

Hope that makes sense.
 

bob fitz

AWF VIP
Local time
Today, 15:26
Joined
May 23, 2011
Messages
4,717
The database is (ironically) intended to track new version features and additions for software development. The concept is to enter project data in the master form, with numbered listings for multiple new features and future features contained in the subforms. So when a new master record is opened, it would be ideal for the newly added features and future features in the subforms to be numbered individually, but restarting at 1 each time, and linked only to their new master record.

Hope that makes sense.
Can you post a copy of the db with a few dummy records
 

Joemon

New member
Local time
Today, 11:26
Joined
May 12, 2022
Messages
4
Hi Bob; please see the attached. The form to look at is ProjDataForm. Note the subform numbering (for instance, in Test2, the subforms are numbered 3, 4, etc. - would want them to restart at 1).
 

Attachments

  • Feature Requirements v0.11_TestDev.accdb
    748 KB · Views: 172

bob fitz

AWF VIP
Local time
Today, 15:26
Joined
May 23, 2011
Messages
4,717
Try the attached db.
I changed the AutoNumber field called FeatureNumber to a Number type field
I added the following line of code to the Before Update event of the sub form
Code:
    Me.FeatureNumber = Nz(DMax("FeatureNumber", "FeatDataTbl", "[Key]=" & Me.[Key]), 0) + 1
Post back if you have any questions
 

Attachments

  • Bob01Feature Requirements v0.11_TestDev.zip
    149.9 KB · Views: 200

Joemon

New member
Local time
Today, 11:26
Joined
May 12, 2022
Messages
4
Thanks, but now the FeatureNumber field is only showing zero. No consecutive numbering when records are added.

Edit: I compacted the database and started with new records. It IS working; thanks again!
 
Last edited:

bob fitz

AWF VIP
Local time
Today, 15:26
Joined
May 23, 2011
Messages
4,717
Thanks, but now the FeatureNumber field is only showing zero. No consecutive numbering when records are added.

Edit: I compacted the database and started with new records. It IS working; thanks again!
Then could you please mark this thread as Solved (Click the Solved button at the top)
 

Users who are viewing this thread

Top Bottom