By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Very Interesting problem with records

P: n/a
I am using Access 97, yes I know Access 97 not by choice, I am adding a
new record to a table with a Primary Key set with an auto number
incremented by 1. I have the following code to find the highest
number. It works great 99.9% of the time then, it will just stop
working it doesn't find the highest numbered record. I can't replicate
the problem, but I can fix it by compacting and repairing the DB. I
would like to know why?

prime = Nz(rst.Fields!OrderNumber, " ")
num1 = Val(prime)
i = 0
rst.MoveFirst
While i < rst.RecordCount
num2 = Val(rst.Fields!OrderNumber)
If num1 < num2 Then
num1 = num2
i = 0
rst.MoveFirst
prime = Str(num1)
End If
i = i + 1
rst.MoveNext
Wend

Dec 1 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

fl*******@hotmail.com wrote:
I am using Access 97, yes I know Access 97 not by choice, I am adding a
new record to a table with a Primary Key set with an auto number
incremented by 1. I have the following code to find the highest
number. It works great 99.9% of the time then, it will just stop
working it doesn't find the highest numbered record. I can't replicate
the problem, but I can fix it by compacting and repairing the DB. I
would like to know why?

prime = Nz(rst.Fields!OrderNumber, " ")
num1 = Val(prime)
i = 0
rst.MoveFirst
While i < rst.RecordCount
num2 = Val(rst.Fields!OrderNumber)
If num1 < num2 Then
num1 = num2
i = 0
rst.MoveFirst
prime = Str(num1)
End If
i = i + 1
rst.MoveNext
Wend
You shouldn't even have to reference the autonumber field. As a matter
of fact, you shouldn't be trying to change it. The database engine
gets the next number and assigns it for you. There may be gaps in the
sequence, but each value is guaranteed to be unique (not sequential).
Or are you trying to force a sequential number? Then you could use a
table, and then open it so that nobody else can, grab the number, use
it, update it, and then release the "order numbers" table by closing
the connection to it.

Dec 1 '06 #2

P: n/a

Just use a SQL statement or DMax to get this information, cycling through a
recordset as you are doing is the slowest way you could do this.

BTW, there's nothing wrong with using A97, it's the most stable version of
Access going.

--

Terry Kreft
<fl*******@hotmail.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...
I am using Access 97, yes I know Access 97 not by choice, I am adding a
new record to a table with a Primary Key set with an auto number
incremented by 1. I have the following code to find the highest
number. It works great 99.9% of the time then, it will just stop
working it doesn't find the highest numbered record. I can't replicate
the problem, but I can fix it by compacting and repairing the DB. I
would like to know why?

prime = Nz(rst.Fields!OrderNumber, " ")
num1 = Val(prime)
i = 0
rst.MoveFirst
While i < rst.RecordCount
num2 = Val(rst.Fields!OrderNumber)
If num1 < num2 Then
num1 = num2
i = 0
rst.MoveFirst
prime = Str(num1)
End If
i = i + 1
rst.MoveNext
Wend

Dec 4 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.