How do I make changes retroactive?

[Guest]

I recently came up with a new number scheme that is automatically created
when the form my department uses is "dirtied". Problem is it starts from the
begining of the scheme even though I have 100s of records before it was
created. It is already part of the form but it only starts where new records
were created. Is there any way I can make it start numbering from the first
record?

Thank you in advance.

 

[Joseph Meehan]

I recently came up with a new number scheme that is automatically
created when the form my department uses is "dirtied". Problem is it
starts from the begining of the scheme even though I have 100s of
records before it was created. It is already part of the form but it
only starts where new records were created. Is there any way I can
make it start numbering from the first record?

Thank you in advance.

I suspect you are using the autonumber function for this. Is that right?

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Guest]

No, autonumber couldn't do this. The number looks like this: 05-123
05 is the current year based on system time, and the 123 is just incremental
to ensure no repeat numbers. Curently it starts at 05-000 then 05-001 but
only of new records not previously created ones.

 

[Joseph Meehan]

No, autonumber couldn't do this. The number looks like this: 05-123
05 is the current year based on system time, and the 123 is just
incremental to ensure no repeat numbers. Curently it starts at
05-000 then 05-001 but only of new records not previously created
ones.

Sorry I don't know how you are generating the numbers so I can't really
help. BTW you could do this with autonumbers. The usual way is to combine
two fields or use some sort of formatting to handle it.

 

[Guest]

Your right, but I was advised time and time again not to do that. Here is
the code I used to make this number:

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "ID Like """ & Format(Date, "yy") & "*"""
varResult = DMax("ID", "CASE", strWhere)

If IsNull(varResult) Then
Me.ID = Format(Date, "yy") & "-001"
Else
Me.ID = Left(varResult, 3) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If

 

[Joseph Meehan]

Your right, but I was advised time and time again not to do that.
Here is the code I used to make this number:

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "ID Like """ & Format(Date, "yy") & "*"""
varResult = DMax("ID", "CASE", strWhere)

If IsNull(varResult) Then
Me.ID = Format(Date, "yy") & "-001"
Else
Me.ID = Left(varResult, 3) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If

Thanks for your response. I believe I see what is going on now. I
believe that you have started on the right track, but I am not the best
person to help you convert the existing data. I am not sure why what you
have is not working for you as you would like. My VBA is .. well let's say
it is a little weak.

You may want to combine some of this information into a single message
are repost it as a new message. I fear that this tread has fallen off the
radar of many of those who are more experienced at VBA than I.

 

[SusanV]

In order to go back through and do this for existing records, you could use
an ADO recordset and step through the existing records, updating each record
as you go. Something like:



Dim cnx As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cnx = CurrentProject.Connection

rs.Open "tblPMSheet", cnx, adOpenDynamic
rs.MoveFirst
Do While Not rs.EOF

' Make change to this record


rs.MoveNext
Loop
rs.Close


Sorry, but I've lost track of exactly what it is you were trying to do...