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]

@zepphead80
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]

@zepphead80
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]

@gershwyn

 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]

@AccessBeetle
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

[AccessBeetle]

@zepphead80
As soon as you select County from the dropdown it runs the following code to make sure to reach to the maximum number

' 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 
 

Then it runs

'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 
 

this code and assigns the ID to the record. This is the only code that does ID calculation and assigning.
Thanks

[patjones]

@AccessBeetle
But isn't Me![ID] just a text box on your form? Are you working with a bound form?

[AccessBeetle]

@zepphead80
Yes, it is a bound form. It is bound to the table Class. The unique column is ID and surprisingly (but true) it is text.

[patjones]

I don't think it is necessarily a problem that it's a text field. As long as you ensure uniqueness of these values, and as long as the field is locked when you are viewing existing records, I think it is okay.

I feel at a loss. I would say that if you want to attach a stripped down version of your database to the thread, I'd be happy to take a look at it.

I will post the attachment guidelines momentarily.

Pat

[patjones]

When attaching your work please follow these steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database.
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.