Automatically fill in data in a form

[Guest]

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.

 

[Guest]

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

 

[Joseph Meehan]

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.

Note, I suspect you hare committing spreadsheet. That is using a
database like a spreadsheet. It is rare to need 60 fields. It is also rare
to have a reason to store data that can be computer or looked up later.
Those two things tend to go together when a database is improperly deigned
and not normalized.

How many tables do you have and are they related?

I don't mean to sound preachy. It is a very common problem. We all do
it when we start.

I can't easily figure out what you area really doing from your
description and with the likelihood that there is a normalization issue, it
would be far better to result any table design issues first.

 

[Guest]

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.

 

[Joseph Meehan]

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.

If the relationship does not change, there is no reason to save the data
in each record. You just "look it up" in a related table anytime you want
to display it.

If it can change, then you need to decide if you need to maintain the
original data or the changed data. If you want the changed data, then again
you want to use a related table since you can change it once there and all
the references will then show the new data.

The question comes up when the data changes and you want to maintain the
original data. Is that what you have?

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.

That is all you need. There should be no need to have those five fields
of data in Table #1. You just let the form, query or report to look at the
"Parts Table" when you want to display anything from those five fields.

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".

That's the way.