Automatically fill in data in a form

[Datameister]

I am working with and existing table (Table1) with 11,000 records of parts,
their specifications, and other information. The table includes about 60
fields. My goal is to create a form which, after selecting a "part number",
automatically fills in about 5 of the fields (specifications) which are
static, and then allow the remaining fields to be manually filled.

I've created a "Parts Table" with "Part number" as the primary key. I then
attempted to create a form using a combo box to propagate the specifications
in text boxes, and more textboxes for the remaining fields for Table1. It
works nicely except that the 5 specification fields are not completed in
Table1. The primary key in Table1 is "ID" (autonumber).

I need help with the design of my form to make it function correctly. I
have a one-to-many (part number-to-ID) relationship in place. I've tried
various methods, including queries, to no avail. I am not very experienced
using code or events.

In addition, I have an alphabetical "Lab Code" field which I want to
automatically generate the code. Such as Record 1:ABCD, Record 2:ABCE, Record
3:ABCF, etc.

Thanks in advance for help with any or all of this.

 

[Datameister]

Correction: My relationship is "Part #-to-Part #" between the two tables.
It is not "Part #-to-ID" as previously stated.

 

[Datameister]

I am working with a database which I inherited that contains 5 years of data.
If I were starting from scratch I would design it differently. I am
attempting to update it and make improvements by using a form to reduce input
error.

The database is used to record information about "Orders" that are filled
with "Parts" for the customer.

I need to retain 2 years worth of the most recent data. The rest can be
archived.

I want to make a form that the user types in the "Part number". After the
part number is entered, I want 5 fields to automatically fill in respective
data which is unique for that part. The related data does not change and/or
it doesn't not change often. There are tens or hundreds of different "Part
numbers" and "Orders" are being filled continuously.

I have 2 tables. The original table is named "Table1" which contains 5
years of data. I am creating another table named "Parts Table" which will
contain "Part numbers" (with no duplicates) and their respective 5 fields of
information.

The field "Part number" is contained in both tables and I have them both
related. The "one" side, on the one-to-many relationship, is "Parts Table".
The "many" side is "Table1".

Thanks for your help.