Increment field in access 2007 from vb6

Question

2.50/5 (2 votes)

See more:

Dear friends.
I am Sarfaraz. I have completed my project and after installing it on target computer. They have found a problem in it.
The problem is that i have a field in access 2007 table called RECEIPT_NO which is auto increment( generates the next receipt_no after incrementing the previous with 1). The problem is that after saving 32 records in the database the auto increment field freezes to 32 or 27 and does not change after that.
The code is as under
ON the form_Load i have

VB

Private Sub Form_Load()
If rs.BOF = True Then
MsgBox " There are no Records in the database", vbInformation, "No records in Database"


Text1.Text = 1' displays receipt_No
Label6.Caption = Date
List1.Clear
Con.Close
Connection
sql = "SELECT (investigation ) As Investigations FROM investigation"
Set rs = New ADODB.Recordset
rs.Open sql, Con, adOpenDynamic, adLockOptimistic
Do While Not rs.EOF
    List1.AddItem UCase(rs!Investigations)
           rs.MoveNext
    Loop
rs.Close: Set rs = Nothing

Exit Sub
Else
rs.MoveLast


Text1.Text = rs.Fields(0) + 1
Label6.Caption = Date
List1.Clear
Connection
sql = "SELECT (investigation ) As Investigations FROM investigation"
Set rs = New ADODB.Recordset
rs.Open sql, Con, adOpenDynamic, adLockOptimistic
Do While Not rs.EOF
    List1.AddItem UCase(rs!Investigations)
  
        rs.MoveNext
    Loop
rs.Close: Set rs = Nothing
End If
End Sub

Please help me in solving this problem.
Thank you

Posted 10-Oct-12 2:06am

sarfarazbhat

Updated 12-Oct-12 5:20am

Wes Aday

v3

Add a Solution

Comments

ZurdoDev 10-Oct-12 8:17am

But it looks like you are pulling the receipt_no from a textbox, not an autoincrement?

[no name] 12-Oct-12 11:44am

*IF* you have an auto increment field in your database and *IF* it is not working correctly, you are looking in the wrong place. You should be looking at your database to see why the auto increment field is not behaving.

2 solutions

Solution 1

We can't really answer this exactly, only give you some general ideas to play with.

There are a huge number of reasons why this might be happening - you really need to look more closely using the debugger, and also try to replicate the condition under which the program is running. For example, is the database being used as single user, or multiuser?

However, one thing to look at is that if you are using the last number in the database to indicate what the next mnumber will be, you need to specify the sort order on tehrecords when you retirve them - otherwise there is no guarantee that they are returned in the order you want:

SQL

SELECT (investigation ) As Investigations FROM investigation

Can return records in any order Access feels like, which may not be the one you expect.

SQL

SELECT (investigation ) As Investigations FROM investigation ORDER BY investigation ASC

Fixes that.

Posted 12-Oct-12 6:06am

OriginalGriff

Comments

sarfarazbhat 14-Oct-12 12:49pm

I tried order by but no success.any other idea sir

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sarfarazbhat · Accepted Answer · 2012-10-16T04:25:00

Solution 2

I solve the problem by selecting the max(receipt_no) from table.

sql = "select max(Receipt_No)from patients"

Anyways, I thank all the members who suggested so many ways and finally help me to solve my problem.
Thank you

Posted 16-Oct-12 4:25am

sarfarazbhat