Linking Table

[yrstrulysa]

Please look at my DATA Model attached. I can not select Permit ID, which is set as an Auto Number in a table using a form. I want the user to be able to select from a drop-down list, it appears PermitID 1 is fixed and does not allow editing. Do I create a linking table, or do I bring in a full table? In this instance it would be an PermitImport table.

 

[Ranman256]

The relationship has nothing to do with the combo box.

is the combo box used to Load the record?
or are you just puting data in a field?

normally
set the combo box to a query from 1 TABLE, that has 2 fields: ID, fieldForUserToSee
set the column count = 2
set the column widths to like: 0.5;1 , to see the ID
if you DONT want user to see the ID field, set to: 0;1

 

[yrstrulysa]

Would you please look at the images I attached? In the combo box, users will select a range of permits.ID's. Please note, that it work well in the Export Form linking to the Export table, but because I am using the PermitInformation table as my Import table also, I'm getting this issue.

Code for the Tables:

CREATE TABLE tblPermitInformation (
    PermitID AUTOINCREMENT PRIMARY KEY,
    PlantID TEXT(50),
    Import_Requestor TEXT(50),
    Permit_No TEXT(50),
    Permit_appl_date DATETIME,
    Permit_valid_until DATETIME,
    Authorization_application_date DATETIME,
    Authorization_no TEXT(50),
    Authorization_valid_until DATETIME,
    Date_shipped DATETIME,
    Arrival_date DATETIME,
    Export_country TEXT(50),
    Material_type TEXT(50),
    Quantity_of_material NUMBER
);

CREATE TABLE tblExportInformation (
    ExportID AUTOINCREMENT PRIMARY KEY,
    PermitID NUMBER NOT NULL,
    Export_Requestor TEXT(50),
    Permit_No TEXT(50),
    Permit_valid_until DATETIME,
    Date_shipped DATETIME,
    Destination_country TEXT(50),
    Material_type TEXT(50),
    Quantity NUMBER,
    Export_comments LONG TEXT
);

 

[Gasman]

Why are you creating tables dynamically?

 

[Pat Hartman]

Question #2 Is your combo bound to the autonumber? Combos used for searching/finding records should never be bound to a controlSource. No matter what other settings you choose, you will never be able to select any option from the list other than the value of the bound field because autonumbers cannot be changed.

 

[yrstrulysa]

Question #2 Is your combo bound to the autonumber? Combos used for searching/finding records should never be bound to a controlSource. No matter what other settings you choose, you will never be able to select any option from the list other than the value of the bound field because autonumbers cannot be changed.

Yes, it is linked to an Auto number. I did not know that this limits the visual in the Form.

 

[plog]

I think I got it, but not sure:

The Import Form works directly on tblPermitInformation, where PermitID is an AutoNumber field.
...
Import displayPermit ID, but I can not Select it.

Sounds like you are trying to use a drop down to select a PermitID for a new record in a table where PermitID is an autonumber primary key. That's never going to work.

1. Autonumbers are assigned by the system, you can't use a drop down to assign one.

2. Autonumbers are unique, you can't choose an existing one and assign it to a new record.

Any form based on tblPermitInformation should have the PermitID input locked and not editable. If you want to edit an existing tblPermitInformation record you should add a new form prior to this one so that the user can select the permit they want to edit and then use DoCmdOpenForm (https://learn.microsoft.com/en-us/office/vba/api/access.docmd.openform) to open your Import form to the exact ID they want instead of having them choose it on the import form themselves.

 

[Pat Hartman]

Yes, it is linked to an Auto number. I did not know that this limits the visual in the Form.

It has nothing to do with the visual. When you pick a different item from the list, Access thinks you are trying to change the autonumber and it will not let you!!! Search combos/lists/textboxes must be unbound.