[RESOLVED] MS Access Dadatbase Contestant ID Number sometimes hangs.

[nitroboatr]

I am not a programmer I am only the designer of the program however I have lost touch with my coder and I am having a problem I need repaired ASAP.

The problem is that the database "Contestant ID" number hangs on a number sometimes and everyone added after that is given the same number unless I manipulate the data which can be a pain sometimes. When I first saw the problem I opened the database in Access and looked at the designer for that table seeing the Number was set to Long Interger, Indexed, Yes Duplicates OK. At that point I thought by making it Indexed Yes, No Duplicates would fix the problem however I found that it still gets stuck on a number and I have to manipulate data to try and repair it. I have also tried Auto Number however it is not an updateable field and when I click on the Update button in my program it states that the Contestant ID is not updateable and then crashes the program.

Can anyone tell me why this number hangs or how to repair the problem. I can post some code if needed.

Thanks!

[dglienna]

ID #'s should be assigned ONCE, and not ever changed. If you violate that rule, strange things can happen (and DO)

[nitroboatr]

ID #'s should be assigned ONCE, and not ever changed. If you violate that rule, strange things can happen (and DO)

They are being assigned once by the program but sometimes it will hang on a number, not any particular number as random as can be. Once it hangs I have to delete the last few Contestants added to get the number to change and then add them back in and most times it will increment on past the number it decided to hang on. In this latest database I have been working with it has hung on me 3 different times on different numbers each time I have to delete Contestants and then add them back in to correct it.

I need a fix for this if there is one.

Thanks!

[dglienna]

Well, deleting an autonumber can cause all kinds of problems down the line. Sounds like you need one sequence number/auto along with another ID# that is separate

[nitroboatr]

Well, deleting an autonumber can cause all kinds of problems down the line. Sounds like you need one sequence number/auto along with another ID# that is separate

Hummm, you dont seem to understand at all!

I am not using an Auto Number as stated in my message above the Auto Number will not work as it is not updateable as in when I update info for a contestant with an Auto Number it will crash the program. The Number hangs without deleting anything, I only have to delete some entries after it hangs.

[dglienna]

I understand what I can SEE, and you've posted NO CODE.

Take a look at this. You can Add/Delete/Change anything you want without CRASHING the program.

Included source code in this article

[DataMiser]

Sounds like the program is assigning an ID number and is not incrementing it properly writing the same number over and over again. Hangs is a poor description as this implies that your program is locking up but I doubt that is what you mean.

You also said that you set allow duplicates to false and the problem persisted, this however could not be the case as it would throw a database error and not allow the record to be written if set this way. More likely the status of the index was never changed.

Your program may be getting this id number from anothe rlocation in the database and failing to update the table where it is getting it with the new number causing the same number to be used again and again but without code there is nothing anyone can really do to help you here.

[nitroboatr]

Ok as stated I am not a coder and can only guess at what code would be helpful. Yes you are correct that the Auto Number will crash when the Number duplicates itself and not add the record which is how I know imediately when it duplicates. I am including some code snipits and a jpg of my database in Access Design View.

Thanks in advance for your help!


'This is from modfrmClient :

Public Sub PopulateContestantID()

sqlString = "SELECT tblContestant.[Contestant ID] FROM" _
            & " tblContestant"
            
MyDB.OpenTable sqlString

If MyDB.rs.RecordCount = 0 Then
    frmClient.txtID = 1
    Exit Sub
End If

MyDB.rs.MoveLast

Dim TheLastNumber As Integer

TheLastNumber = MyDB.rs![Contestant ID]

frmClient.txtID.Text = TheLastNumber + 1

Exit Sub

End Sub 



This is from modPublicVariables :

Public intCounter As Integer            ' Placeholders for RecordCount
Public intCounter2 As Integer           ' Placeholders for RecordCount



These are from frmMain

Private Sub txtID_Change()

End Sub

Private Sub txtID_Validate(Cancel As Boolean)

End Sub

Hope this will help and thanks again!

[DataMiser]

Well not a lot to go on there but it could be simply that you need to add a sort to the select statement.

As it is your code grabs all the contestant ids and jumps to the last one assuming that this is the highest value but what if the table is not sorted by contestant id? The result could be that you get an id that is not the most recent and start duping ids.

The solution if this is the case would be to sort the data in the select query something like this.

sqlString = "SELECT tblContestant.[Contestant ID] FROM" _
& " tblContestant order by [Contestant ID]"

You could also use a reverse sort and no longer need the code to move to the last record as the first record would have the highest number.

[nitroboatr]

I have added the sort command as you stated and will be testing it for a while to see if that repairs it.

My code now reads: sqlString = "SELECT tblContestant.[Contestant ID] FROM" _
& " tblContestant order by [Contestant ID]"

Can you explain or show code of this reverse sort?

Thanks!

[dglienna]

ASC is assumed...

sqlString = "SELECT tblContestant.[Contestant ID] FROM" _
& " tblContestant order by [Contestant ID] DESC"

This will move the highest key to record [0], so you'd use THAT

[nitroboatr]

Thanks for the help you two! I have decided to go with the "order order by [Contestant ID".
I found some old databases that were hung on the contestant ID and everyone of them with the old program would not increment up without manipulation while with the added code as stated above, the databases would continue on to the next number as it should. I have also tested the ability to delete data to see if the number would increment down and then add new data to see that it increments back up, so far so good! I will have to continue testing to be sure there are no adverse affects.

[DataMiser]

Good to hear. Most likely what had happened assuming of course this was working before is that someone opened tha database in access and sorted the data in table view and then when prompted saved the changes causing the default sort order to change.

It is always a good idea to use the "order by" in your select statements where more than one row is returned and a given order is expected as this will overcome the senario where someone has changed the sort order in the database via access.