Using DMax within a Subform

[bigroo]

Hi All,



I have a main form and below it, I have a subform all linked to the autonumberID on the main form. The subform is used for tracking history of the record in the main form. Since I dont have dates for all of the history items I enter, I have field called row and in this field 1 have numbers starting with 01 and as I add a row of history I go 02 and so forth. I also want to say here I use this row number for sorting the rows in chronological order as well.

How would like to know if using DMax to add the next number when I add a new row to this subform for this record only and when I visit another record enter in the next incrementing number for that recordset?

I did try this =NZ(Dmax("row","tblfachistory")) + 1 in the field control but all it did was take the Max number of all the records in this table which was 66 and enter in 67, so as you can see one record has 66 rows of history.

James
Austin, Texas

[June7]

Can't use autonumber datatype field?

[bigroo]

Hi June7,

I tried making this field an autonumber earlier in my design and it did not give me the results I wanted, all it did was move to the next number. Then when you open up another record and add another record to the history table it did not start at 01 or 02 or 03 it displayed how many records were in the history table.

Thanks

James
Austin, Texas

[June7]

What is the purpose of 01, 02, 03 - a unique ID?

[bigroo]

Hi June7,

Thanks for the reply.

What is the purpose of 01, 02, 03 - a unique ID?

Not a unique ID. Since I dont have dates for all of the history items I enter, I have field called row and in this field I have numbers starting with 01 and as I add a row of history I go 02 and so forth. I also want to say here I use this row number for sorting the rows in chronological order in descending order so the latest entry is at the top of the list.

James
Austin, Texas

[June7]

This sounds exactly like what an autonumber field would accomplish.

The leading 0 makes me think you are entering these as text. What happens when you hit 100, then 1000, etc? Numbers saved as text are subject to alpha sort. 10 will sort before 9.

Why not a date field? It could have a default of Now() to date/time stamp the record when it is created.

[bigroo]

Hi June7,

This sounds exactly like what an autonumber field would accomplish.

Then if I use an autonumber how do I make this work on different main records with a subform?

Why not a date field? It could have a default of Now() to date/time stamp the record when it is created.

I have this now at the end of each new record created Date/Time.
Events that have happened in the past, within the field I have labelled date Started, I have this as text for now, simply because if while doing my research I find a year only of the histroy event I enter in xx/xx/2001 in the date field because the month and day is not known at this time. This is where the row field comes into play and I sort the entered items this way.

Can you help me?

James
Austin, Texas

[June7]

I would just pick 1/1/ as month/day when not specified and no way to narrow the date value. For instance, if know it would be impossible for any events related to the main record prior to July 2001, then pick 7/1.

You are attempting to track a sequential number series for each main record? Why? If purpose of field is just sort criteria, doesn't matter if the sequence has gaps. Sort first by the main ID then by the sequence number or date. Autonumber or data/time stamp will allow that.

[bigroo]

Hi June7,

You are attempting to track a sequential number series for each main record?

Yes, you are correct! I want to use the system I have now in place.
What is needed for the next step to acheive what I wish?

Thanks

[June7]

Review this recent thread on same topic https://www.accessforums.net/access/...ber-20506.html

This is a common topic. You might find more with forum or Google search.

You can try your DMax expression again but with criteria in the WHERE argument.

NZ(Dmax("row","tblfachistory", "table fieldname='" & form field name & "'")) + 1

Again, if the purpose of this value is for sorting, saving as text will not accomplish that unless the length is always the same. Like: 0001, 0027, 0315, 8426.

If the field really is number type, eliminate the apostrophe delimiters.

[bigroo]

June7,

Thanks for your thoughts, help and recommendations to my solution. I ended up taking your suggestion and use the date field as my sort, I cleanned up 107,000 records. I also removed the row field, after that I just reduced a step in my database process.

Thanks so much.