Access creating its own IDs.

[AccessBeetle]

I have an access database which has a table called Class.It has a column called ID, which is Text and it is an Automatic entry of an ID number to uniquely identify the line of data. The field size is 12, for ex: 11-136,11-137...
It is acting weired lately and changing the IDs of existing records.
I have attached a file which shows the problem.
Thanks!!

[patjones]

I can't get your image to show any larger than thumbnail size. Can you try re-posting it at a larger size? I'd be happy to take a look at it if I can view it.

Pat

[AccessBeetle]

Please try to save it first and then open it in Paint. May be that opens a larger view.
Let me know if you are able to view it.
Thanks

[AccessBeetle]

Here is the another image.

[AccessBeetle]

Here is the another image

[patjones]

Paint is what I tried first but it is showing as a thumbnail...

[patjones]

When a new record is created, where does the ID come from? Also, does this happen to random records or does it happen only to records that have been edited?

Pat

[AccessBeetle]

When a new record is created, where does the ID come from? Also, does this happen to random records or does it happen only to records that have been edited?

Pat

Were you be able to see the image?
There is a chunk of code written to increment that ID number.

 Me![ID] = Me![COUNTY] & "-" & (mx + 1)

This started to happen suddenly. I mean it was working fine untill somebody pulled a report and tried to compare it with old one, this glitch was found.
This happens randomly. No specific number of records, No certain range. I also tried to compact and repair but still doing it.

[missinglinq]

This still doesn't tell us much! What event is the code in? Where does mx come from?

Linq ;0)>

[patjones]

I guess what I'm trying to determine here is exactly what action causes these changes to occur. You know quite specifically that 11-176 used to be 11-140...so what happened in between? Was it running the report that caused the change?

And as missinglinq points out, we'd need a little more code to really analyze how you are putting these ID's together.

Pat

[AccessBeetle]

I guess what I'm trying to determine here is exactly what action causes these changes to occur. You know quite specifically that 11-176 used to be 11-140...so what happened in between? Was it running the report that caused the change?

And as missinglinq points out, we'd need a little more code to really analyze how you are putting these ID's together.

Pat

It is the whole code. It is little bit long. I didn't know where to cut it from so

Private Sub COUNTY_LostFocus()

Dim db As DAO.Database
Dim mx As Integer
Dim rsCo As DAO.Recordset
Dim rsVal As String
Dim rsCoVal As Integer
Dim strSQL As String

Set db = CurrentDb()

rsCoVal = Me![COUNTY]

strSQL = "Select Class.ID from Class where Class.County = " & rsCoVal
Set rsCo = db.OpenRecordset(strSQL)

If rsCoVal <= 9 Then
        If rsCo.RecordCount > 0 Then
            rsVal = rsCo.Fields("[ID]").Value
            ' Set mx equal to the numeric portion of the field.
            ' Change the below number of 0 to strip the first couple
            ' of bits as needed.
        
            mx = Abs(Right(rsVal, Len(rsVal) - 2))
            
            ' Loop to make sure you have the maximum number.
            Do While Not rsCo.EOF
            rsVal = rsCo.Fields("[ID]").Value
            ' Change the below numbers of 0 to strip the first couple of
            ' bits as needed.  Must be the same as your changed above.
                If Abs(Right(rsVal, Len(rsVal) - 2)) > mx Then
                    mx = Abs(Right(rsVal, Len(rsVal) - 2))
                End If
                rsCo.MoveNext
         Loop
    Else
        mx = 0
    End If
    'Increment the maximum value by one and
    'combine the text with the maximum number.
    
    Me![ID] = Me![COUNTY] & "-" & (mx + 1)
    
        rsCo.Close
        db.Close

    Set rsCo = Nothing
    Set db = Nothing

ElseIf rsCoVal >= 10 Then

    If rsCo.RecordCount > 0 Then
        rsVal = rsCo.Fields("[ID]").Value
    
        ' Set mx equal to the numeric portion of the field.
        ' Change the below number of 0 to strip the first couple
        ' of bits as needed.

        mx = Abs(Right(rsVal, Len(rsVal) - 3))
    
        ' Loop to make sure you have the maximum number.
        Do While Not rsCo.EOF
        rsVal = rsCo.Fields("[ID]").Value
        ' Change the below numbers of 0 to strip the first couple of
        ' bits as needed.  Must be the same as your changed above.
            If Abs(Right(rsVal, Len(rsVal) - 3)) > mx Then
                mx = Abs(Right(rsVal, Len(rsVal) - 3))
            End If
            rsCo.MoveNext
        Loop
    Else
        mx = 0
    End If
        'Increment the maximum value by one and
        'combine the text with the maximum number.
    
    Me![ID] = Me![COUNTY] & "-" & (mx + 1)
    
    rsCo.Close
    db.Close

    Set rsCo = Nothing
    Set db = Nothing
                 
Else
    MsgBox "An error with the automatic filling of the ID number.", vbOKOnly
End If

End Sub

[AccessBeetle]

I hardly have doubt that it is gone like that due to just running the report. It is the daily procedure. I mean it is not follwoing any partucular pattern.

[gershwyn]

What is the county control and how is it used?

It looks to me that whenever the user leaves the County field (since this code is in the LostFocus event) the code calculates the new ID code and assigns it, which is probably not what you want. I would put a check at the beginning of the code to determine whether or not a new ID is needed, but what I don't know is how to determine that.

Can the county be changed after the ID is assigned the first time? And if so, what should happen?

[AccessBeetle]

What is the county control and how is it used?

It looks to me that whenever the user leaves the County field (since this code is in the LostFocus event) the code calculates the new ID code and assigns it, which is probably not what you want. I would put a check at the beginning of the code to determine whether or not a new ID is needed, but what I don't know is how to determine that.

Can the county be changed after the ID is assigned the first time? And if so, what should happen?

 I would put a check at the beginning of the code to determine whether or not a new ID is needed, but what I don't know is how to determine that.

Can the county be changed after the ID is assigned the first time? And if so, what should happen?

Yes, County can be changed while entering the record but not after the user has clicked on "Next" record. I put the break in the begining of the code and tried to enter a new record. When I tried to change the county multiple times, it was assigning the ID based on the county I select from the dd. But I would sure like to put a code that checks existing IDs. Give me some input on that.
Thanks

[patjones]

 I would put a check at the beginning of the code to determine whether or not a new ID is needed, but what I don't know is how to determine that.

Can the county be changed after the ID is assigned the first time? And if so, what should happen?

Yes, County can be changed while entering the record but not after the user has clicked on "Next" record. I put the break in the begining of the code and tried to enter a new record. When I tried to change the county multiple times, it was assigning the ID based on the county I select from the dd. But I would sure like to put a code that checks existing IDs. Give me some input on that.
Thanks

Can you post the code which actually commits new records to the table? In the code you posted yesterday, you calculate the ID for a new record, but I would like to see where it actually gets put in the table. Thanks.

Pat