MS Access dao setting autonumber field to 0 (2 Viewers)

[strager]

Am I missing something? For some reason I have a dao recordset that sets my autonumber field to zero (0). At first I thought that it wasn't returning the new autonumber id but when I opened the table there was the record but the autonumber field was 0. I have tried numerous ways to get it to work. What is going on?
I tried setting the db connection using Set db = CurrentDb instead of Set rs = CurrentDb.OpenRecordset("tblEstimate"). I've compressed database, I compiled database/I created a new table from scratch and even renamed it from tblEstimating to tblEstimate. I also tested another procedure that is the same but to a different table and it works.

        Set db = CurrentDb
       
        Set rs = db.OpenRecordset("tblEstimate")
       
        With rs
       
        .AddNew
       
            !CustomerID = LCustID
            !EstID = LEstID
            !Empid = LAddEdit
            !SalesRepID = LSales
            !EstStatusID = LStatus
            !estcode = sEstCode
            !DateCreated = dDate
            !DueDate = dDue
            !CompletedOn = dComp
            !dateaccepted = dAccept
            !TotalEstimate = cTotalEst
            !SubTotal = cSubTotal
            !DiscountPercent = sngDiscPercent
           
           
            !DiscountAmt = cDiscAmt
            !TaxAmt = cTaxAmt
            !TaxTypeID = LTaxType
            !Qty = sngQty
            !jobdescription = sJobDesc
            !OtherCost = 0
            !ShipCost = cShip
            !empcost = cEmpCost
            !deposit = cDeposit
           
           
            !BillAdd1 = sBAdd1
            !BillAdd2 = sBAdd2
            !BillCityName = sBCity
            !BillStateID = LBState
            !BillPostal = sBZip
            !ShipAdd1 = sSAdd1
            !ShipAdd2 = sSAdd2
            !ShipStateID = LSState
            !ShipCityName = sSCity
            !ShipPostal = sSZip
            !AddedOn = dAddEditOn
            !AddedBy = LAddEdit
            !ShipTo = sSTo
            !BillTo = sBTo

        .Update
       
        .Bookmark = .LastModified
        LEstID = !EstID
        Debug.Print !EstID
        SaveEstimate = LEstID
       
        rs.Close
       
    End With


Procedure that works

    Set rs = CurrentDb.OpenRecordset("tblMediaSizes")
           
            With rs
           
                .AddNew
               
                !depthMeasurementID = iDepthMea
                !HeightMeasurementID = iHeightMea
                !WidthMeasurementID = iWidthMea
                !MediaTypeID = LMediaTypeID
                !SizeName = sSize
                !LengthMeasurementID = iLengthMea
                !Width = sngWidth
                !Length = sngLength
                !Depth = sngDepth
                !Height = sngHeight
                !AvgCost = cCost
                !CostPerInch = cInch
                !CostPerFoot = cFoot
                !Qty = 1
                !RequiredMediaID = LRequiredMediaID
                !TotalInchesInRoll = sngTotalInchesInRoll
               
                .Update
                .Bookmark = .LastModified
                LMediaSizeID = rs!MediaSizeID
               
                .Close
                   
                MsgBoxRecordSaved
                bDirty = False
                   
            End With

 

[theDBguy]

I wonder if your table got corrupted somehow. Can you try your code in a new blank database?

 

[strager]

I thought the same thing so I created a new table from scratch. I even renamed it just in case.

 

[strager]

It's not the database because I tried running another procedure from a different form but setup the same way and it returned the id value.

 

[theDBguy]

It's not the database because I tried running another procedure from a different form but setup the same way and it returned the id value.

It may not, but it could also be a combination of both. So, I was just saying it's safer if you simply create a new blank database, create a new table in it, and then try your code there as well to see if you still get the same problem. Just a thought...

 

[GPGeorge]

It may not, but it could also be a combination of both. So, I was just saying it's safer if you simply create a new blank database, create a new table in it, and then try your code there as well to see if you still get the same problem. Just a thought...

I agree with theDBGuy's analysis. This has the ring of corruption. I don't remember ever seeing AutoNumber assign a 0 to a Primary Key in more than 30 years working with Access and looking at Access mdbs and accdbs created by others. That makes me think first of corruption.


However, I'd also like to see the result, not of one single record being added to an empty table. I'd like to see the result of attempting to add three or more records in a row. Can you run that test, please?

 

[strager]

I will try that. It's strange that I don't have any problems with similar procedures. I have a picture in my description that shows the PO procedure. No problems.

 

[strager]

Autonumbering is not working at all. Just tried to add another record and I got the duplicate values error.

 

[strager]

This is weird. I tried to add a manual entry and it worked. Maybe I'm missing something in my code.

 

[strager]

I found out what I did. I carefully examined each line of code and I was setting my primary id to 0. Not sure why it accepted it but it did.

Thank you all for your help. BTW I did create a new db to test but as I was doing it I thought I should look at my code again.

 

[theDBguy]

I found out what I did. I carefully examined each line of code and I was setting my primary id to 0. Not sure why it accepted it but it did.
View attachment 113727
Thank you all for your help. BTW I did create a new db to test but as I was doing it I thought I should look at my code again.

Glad to hear you got it sorted out. If you did managed to create a new db and tried your code, you would have found out for sure that your code was causing the problem, because you would have run into the same issue, which would rule out corruption.

Good luck with your project.

 

[Galaxiom]

I found out what I did. I carefully examined each line of code and I was setting my primary id to 0. Not sure why it accepted it but it did.

Access doesn't have a problem inserting records with the value for the Autonumber column predefined, as long as it is not a duplicate of an existing value.

This will also reset the sequential autonumber seed, so can be a problem waiting to happen if a record is inserted with a value less than any existing records. (I have not checked this behaviour in a very long time, so it might have changed.)

 

[gemma-the-husky]

Out of interest I think you would have had an issue with a SQL server back end, because I'm pretty sure you can't poke an autonumber of zero into a SQL server table, but you can do so with an access table.

 

[GPGeorge]

I would want to verify this observation about SQL Server, but I do think you are correct.
Note the way the Identity value is defined:


CREATE TABLE [dbo].[tblCompany](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](100) NOT NULL,
[AccessTS] [timestamp] NOT NULL,
CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

This specifies that the Identify value for CompanyID starts with 1 and increments by 1, thus eliminating 0 and negative values, if I understand correctly how that assignment works.

 

[GPGeorge]

I would want to verify this observation about SQL Server, but I do think you are correct.
Note the way the Identity value is defined:


CREATE TABLE [dbo].[tblCompany](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](100) NOT NULL,
[AccessTS] [timestamp] NOT NULL,
CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

This specifies that the Identify value for CompanyID starts with 1 and increments by 1, thus eliminating 0 and negative values, if I understand correctly how that assignment works.

Also, I just was reminding that you can't explicitly insert ANY value into an identity column in SQL Server when IDENTITY_INSERT is set to OFF, i.e. when SQL Server is not explicitly required to accept it.

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'tblCompany' when IDENTITY_INSERT is set to OFF.

Completion time: 2024-04-16T07:07:55.5112318-07:00

However, that also overrides the restriction on IDENTITY (1,1), thus allowing 0 and negative numbers.

I can see this veering off into a new discussion, though, so I'll pull back.

 

[Pat Hartman]

Earlier in Access' history, MS set the default for numeric fields to null. Currently it is set to 0. So, make sure for ALL your FK's to remove the 0 so that FK's ALWAYS default to null. When an FK is required, the null will cause an error and not allow the record to be saved when the FK is missing but if the FK is optional, the null value will be accepted as valid.