Reset a number field

[rcorbett]

I have three fields on a form titled Sequence Number, Run Number and Unit Number. There will be many run numbers for each sequence number - I am using the following formula to automatically increment the run number (=DMax("RunNumber","tblsequencetesting&quot+1). Approximately each new day I need to put a new sequnce number in and as such reset the run number back to 1. Problem is that I can change the run number back to 1 but when you go to a new record it picks up from say 89 and returns 90 instead of 2. I would appreciate any suggestions that you all may have.

 

[hymn]

The code below is what i use on the after update event on the docindent field


"Document" is the name of the table
"Number & docindent" are fields
"F_document" is a form

Set db = CurrentDb()
Set rds = db.OpenRecordset("SELECT DISTINCTROW Max(Document.Number)AS MaxOfNumber FROM Document WHERE
((Document.DocIdent = '" & [Forms]![F_Document]![DocIdent] & "')", DB_OPEN_DYNASET)


Me![Number] = IIf(IsNull(rds![MaxOfNumber]), 1, rds![MaxOfNumber] + 1)
Me![Number].Requery

Hope this helps
Hymn

 

[rcorbett]

Thank you...trying to modify your code but I'm not getting the RunNumber to incriment. I'll keep playing around.

 

[hymn]

If you remove all private information and email it to me and sort and send it back

bill12bill@msn.com

Hope this helps
Hymn

 

[klewis10367]

I am trying to solve this same issue, but I am not using forms,I am using an Update query. I want the number field to reset back to 0 when a prticular field changes. Any help would be greatly appreciated.

 

[hymn]

i gave you the wrong Email address it should of been
w.waite@virgin.net

Hope this helps
Hymn