Handling AutoNumber Fields in a Form When a Value is Required

[yrstruly]

I have a form where PermitID is an AutoNumber field in tblPermitInformation.


Since AutoNumber fields cannot be selected manually, I introduced a Permit_Ref field (a numeric field) to store a copy of the PermitID.

Problem:

• The data saves correctly when entering records.
• However, PermitID is stuck on 3, meaning new records keep saving with PermitID = 3, overriding previous entries instead of creating new records.

[Welshgasman]

You need to add a new record each time.

[jojowhite]

how do you select a New Record. Obviously, if you have 3 records, it keep
on selecting the 3rd record. That's why it got overwritten everytime.

[yrstruly]

Yes i do add new records.
https://drive.google.com/file/d/1ijr...usp=drive_link

CREATE TABLE tblPermitInformation (    PermitID AUTOINCREMENT PRIMARY KEY,   -- AutoNumber, Unique Identifier
    PlantID TEXT(50),                     -- Plant Identifier
    Import_Requestor TEXT(100),           -- Requestor Name
    Permit_No TEXT(50),                    -- Permit Number (Non-Auto)
    Permit_appl_date DATE,                -- Application Date
    Permit_valid_until DATE,              -- Expiration Date
    Authorization_application_date DATE,   -- Authorization Date
    Authorization_no TEXT(50),             -- Authorization Number
    Authorization_valid_until DATE,        -- Authorization Expiration
    Date_shipped DATE,                     -- Date Shipped
    Arrival_date DATE,                      -- Arrival Date
    Export_country TEXT(50),               -- Export Country
    Material_type TEXT(50),                -- Material Type
    Quantity_of_material INT,              -- Quantity
    Import_comments TEXT(255),             -- Additional Comments
    PermitAuthApprovedDate DATE,           -- Approval Date
    ExpectedReleaseDate DATE,              -- Expected Release
    FumigationStatus YESNO,                -- Yes/No for Fumigation
    Permit_Ref INT UNIQUE                  -- New column to mimic PermitID (non-auto)
);

CREATE TABLE tblImportInformation (
    ImportID AUTOINCREMENT PRIMARY KEY,    -- Unique Import Record
    PermitID INT,                          -- Linked to tblPermitInformation (AutoNumber)
    Permit_Ref INT,                        -- Linked to tblPermitInformation (New field)
    Import_Requestor TEXT(100),            -- Name of Importer
    Import_Date DATE,                      -- Date of Import
    Supplier TEXT(100),                    -- Supplier Information
    Country_Of_Origin TEXT(50),            -- Origin Country
    Quantity INT,                           -- Quantity Imported
    Import_Comments TEXT(255),             -- Additional Comments
    FOREIGN KEY (PermitID) REFERENCES tblPermitInformation(PermitID),
    FOREIGN KEY (Permit_Ref) REFERENCES tblPermitInformation(Permit_Ref)
);

[yrstruly]

I enter new records via the form. The records do save, but overides previous records/data.

[jojowhite]

I enter new records via the form. The records do save, but overides previous records/data.

You are not in New record then, since previously saved records are overwritten.

[yrstruly]

Will you please recreate the tblImportInformation table in this Application and link it to the PermitInformation table. I have given the DDL's and share with me what you get?
https://drive.google.com/file/d/1I5F...ew?usp=sharing .
Data Model: https://drive.google.com/file/d/1ijr...usp=drive_link

Table tblPermitInformation does exist please
create "frmCultivarePermitImport".

Sample Data

INSERT INTO tblPermitInformation (PlantID, Import_Requestor, Permit_No, Permit_appl_date, Permit_valid_until, Authorization_application_date, Authorization_no, Authorization_valid_until, Date_shipped, Arrival_date, Export_country, Material_type, Quantity_of_material, Import_comments, PermitAuthApprovedDate, ExpectedReleaseDate, FumigationStatus, Permit_Ref)VALUES ('GR0289', 'John Doe', 'P0123456', #2024-01-15#, #2024-12-15#, #2024-02-01#, '001/2024', #2024-02-10#, #2024-12-30#, #2024-03-15#, 'USA', 'Seeds', 100, 'Urgent import', #2024-02-20#, #2024-03-01#, True, 1);


INSERT INTO tblPermitInformation (PlantID, Import_Requestor, Permit_No, Permit_appl_date, Permit_valid_until, Authorization_application_date, Authorization_no, Authorization_valid_until, Date_shipped, Arrival_date, Export_country, Material_type, Quantity_of_material, Import_comments, PermitAuthApprovedDate, ExpectedReleaseDate, FumigationStatus, Permit_Ref)
VALUES ('CH1597', 'Jane Smith', 'P0098765', #2024-02-10#, #2025-01-10#, #2024-02-20#, '002/2024', #2024-03-01#, #2025-01-20#, #2024-04-10#, 'Canada', 'Saplings', 50, 'Standard processing', #2024-03-10#, #2024-04-01#, False, 2);

[orange]

Can you do a Record count before you attempt to add the new record, and a count after your add? This would indicate if a NEW record was added or a record was updated.

Sample code for test:

' ----------------------------------------------------------------
' Procedure Name: getRecCount
' Purpose: To get count of records in specified Table
' Procedure Kind: Function
' Procedure Access: Public
' Parameter YourTableName (String): Name of table to check record count
' Author: Jack
' Date: 08-Mar-25
' ----------------------------------------------------------------
Function getRecCount(YourTableName As String)
    Dim recordCount As Long
    recordCount = DCount("*", YourTableName)
    Debug.Print "Total records in table: " & YourTableName & "  " & recordCount & "  " & Now
End Function

Sample usage:
?getreccount ("tblKeyWord")
Total records in table: tblKeyWord 12 08-Mar-25 9:23:41 AM

[yrstruly]

Please see my data mode and Acces.
I have give two lines of sample data for PermitInformation table. A Permit.ID can contain multiple permit import records. A user is suppose to link Permit Ref/I.d to this table and enter new records according to the permit.id.

Take note about the bypass i created because Permit.ID is an Auto number.

Can you do a Record count before you attempt to add the new record, and a count after your add? This would indicate if a NEW record was added.

[yrstruly]

How do solve the puzzle, because each table has got to have a Primary key? Its asking for the primary key value in this table via the Form;

CREATE TABLE tblImportInformation (    ImportID AUTOINCREMENT PRIMARY KEY,    -- Unique Import Record
    PermitID INT,                          -- Linked to tblPermitInformation (AutoNumber)
    Permit_Ref INT,                        -- Linked to tblPermitInformation (New field)
    Import_Requestor TEXT(100),            -- Name of Importer
    Import_Date DATE,                      -- Date of Import
    Supplier TEXT(100),                    -- Supplier Information
    Country_Of_Origin TEXT(50),            -- Origin Country
    Quantity INT,                           -- Quantity Imported
    Import_Comments TEXT(255),             -- Additional Comments
    FOREIGN KEY (PermitID) REFERENCES tblPermitInformation(PermitID),
    FOREIGN KEY (Permit_Ref) REFERENCES tblPermitInformation(Permit_Ref) )

[Welshgasman]

When you create a new record the autonumber is automatically populated.

Instead of asking us to go wandering off to download a file all the time, why not post the relevant code within code tags. The # icon?

[orange]

Your create queries work.
Your Insert queries work.

Now what?

I am off to an appointment most of afternoon.