Autofill relationship needed for primary key (1 Viewer)

Hi guys,

I am creating an engine database that contains several tables: Engine, Emissions Control (A), Emissions Control (T), System, Pump, Rail, Injector, ECU. I need this many tables as the amount of fields is too large for them to fit in one table.

Each engine (data entry row) is specifically linked to each table row entry and so the 'Engine ID No' (the primary key for Engine table) needs to be in each table for the corresponding data entries. For example, Engine ID No 1 (autonumber) should correspond to Engine ID No 1 in the other tables because the first engine data entered in the engine table needs to relate to the first line of injector data etc.

I have set up foreign keys and implemented the combobox table/query process of selection so I can select the Engine ID No's from the engine table list, however I would really like for it to automatically fill the engine ID No with the correct no for each data entry, eliminating any room for error. Is this possible? If so, how would I go about doing it?

Thank you for any help

How is your data entry form setup? Do you have the Engine table as the record source for the main form and subsequent subforms for each of the remaining tables? If not, you should set it up that way so you can take advantage of Master - Child linking which would automatically fill in the foreign keys not requiring any manual data entry.

Welcome to the forum !

I have set up foreign keys and implemented the combobox table/query process of selection so I can select the Engine ID No's from the engine table list, however I would really like for it to automatically fill the engine ID No with the correct no for each data entry, eliminating any room for error. Is this possible?

Click to expand...

Not only possible, but recommended practice.
For this you should create a main form based on table Engines that hold a subform based on table subassembly..
But this are other words for what billmeye already have said to you.

I post because I'm afraid that you haven't the right setup for your database.
So, in my opinion, and without a closer anliyze, your DB should look like this:

tblEngines
ID_Engine (PK - AutoNumber)
EngineType

tblSubassemblies
ID_Subassy (PK - AutoNumber)
SubassyName

tblEnginesSubassemblies
ID_EngineSubassy (PK - AutoNumber)
ID_Engine (FK - Number)
ID_Subassy (FK - Number)

tblParts
ID_Part (PK - AutoNumber)
PartName

tblSubassembliesParts
ID_SubassyPart (PK - AutoNumber)
ID_Subassy (FK - Number)
ID_Part (FK - Number)

My main form is the engine table yes and then I have the other tables as subforms, I have them laid out in tab design form. The first sub form (and consequently first tab) updates automatically however the others do not. Do you know why this would be? Is there something I haven't done? Would it be my relationship set-up maybe that is the problem?

Thanks again.

Can be a thousands problems here.
Without seen your DB is impossible to answer.

Thanks Mihail,

I have been trying to tinker with it as I attempt to solve the problem but here is a basic version of what my database is with the forms, tables and relationships all included.

Some of the 'System ID No' fields do autofill, some do not and other subforms do not even have data entry sections. It's all very confusing. 'ESD_formv1' is the main form.

Thanks again for your help.

P.s, It is now the 'project overview' table that is the main form as after your initial comment I realised introducing this was a better idea.

Each of your subforms needs to have the recordsource based directly on the table, not a query. Also, the System ID No control for each subform should be locked to prevent inadvertent data entry.

That's exactly what I needed. Thanks billmeye and thanks Mihail

Good to hear you worked it out.