incrementing number in a subform

[asandiego]

Hey guys, this is my first post here but have been checking this site a lot for anything I need.
I hope someone can lead me to what I should do or just an idea to what can be done.

What I'm trying to do:
I have a Form that has a subform. My primary form has a field that autonumber and this field has a one to many relationship to a field in my subform.
The subform has another field that I want to AutoNumber or increment number BUT has to go back to 1 again once a new record in my Primary form is created.

I tried using autonumber on that field BUT if I do that, I will not be able to put that field back to 1automatically (I know you can revert the autonumber to start from 1 but you can only to do it manually) once a new record from the primary form is created.

I thought of a macro or a script that would be able to revert the autonumber but just dont know how I would be able to do that without an interaction from the user. Same as with the incrementing number(using DMAX).

I would really appreciate it if someone can lead me to the light... Thanks in advance.

[ADezii]

Hey guys, this is my first post here but have been checking this site a lot for anything I need.
I hope someone can lead me to what I should do or just an idea to what can be done.

What I'm trying to do:
I have a Form that has a subform. My primary form has a field that autonumber and this field has a one to many relationship to a field in my subform.
The subform has another field that I want to AutoNumber or increment number BUT has to go back to 1 again once a new record in my Primary form is created.

I tried using autonumber on that field BUT if I do that, I will not be able to put that field back to 1automatically (I know you can revert the autonumber to start from 1 but you can only to do it manually) once a new record from the primary form is created.

I thought of a macro or a script that would be able to revert the autonumber but just don't know how I would be able to do that without an interaction from the user. Same as with the incrementing number(using DMAX).

I would really appreciate it if someone can lead me to the light... Thanks in advance.

This is straight off the top of my head, and would probably not be the most efficient solution, but I'll throw it out there anyway:

1. Declare a Form Level Variable: (LONG)
   [CODE=vb]Private lngLastID As Long[/CODE]
2. In the Form Open() Event, capture the value of the last entry in your Primary Key Field of the Record Source for your Form, and assign it to lngLastID:
   [CODE=vb]lngLastID = DLast("[PrimaryKey]", "<Table Name>")[/CODE]
3. In the BeforeUpdate() Event of the Sub-Form, see if this value has changed. If it has a New Record was added, if not the existing Record was only edited or left untouched. If the values are different (New Record), write 1 to the AutoNumber Field of the Sub-Form but if they are the same, find the last value of the AutoNumber Field in the Sub-Form and increment it by 1:
   [CODE=vb]
   If lngLastID <> DLast("[PrimaryKey]", "<Table Name>") Then 'New Record
   Me![<AutoNumber Field of Sub-Form>] = 1
   Else
   Me![<AutoNumber Field of Sub-Form>] = DLast("[<AutoNumber Field>]", "<Table Name>") + 1
   End If[/CODE]
4. As previously stated, this approach wasn't well thought out, and was basically meant to be pseudo code as to how the situation may be solved.