Table

I have tables. talbe1, fields a PartNumber, PartDescribtion, PartTypeID.
Table2, fields a PartTypeID, Partype.
Table3, fields a FormID, Date, PartNumber, PartType, Customer......

What I am going to do is when I enter PartNumber, PartType will shows
automatically, so I don't need to pick the type from the list again.

Thanks for everyone's help.

On Fri, 19 Jan 2007 13:54:01 -0800, Hong
wrote:

I have tables. talbe1, fields a PartNumber, PartDescribtion, PartTypeID.
Table2, fields a PartTypeID, Partype.
Table3, fields a FormID, Date, PartNumber, PartType, Customer......

What I am going to do is when I enter PartNumber, PartType will shows
automatically, so I don't need to pick the type from the list again.

Thanks for everyone's help.

You're using a relational database. Use it relationally!

The third table should NOT contain the PartType *at all*. It can be
looked up using a Query, or you can use a Combo Box on the form to
*display* the PartType. It's not necessary to store it redundantly in
the table.

John W. Vinson[MVP]

Because the database was created by another person, and has been used for a
long time. I am not good enough to fix it. I am trying to make the work
easier.
Even in the form, I also need to do the same thing.

So I am trying to fix table and the form base on the situation right now.

"John Vinson" wrote:

On Fri, 19 Jan 2007 13:54:01 -0800, Hong
wrote:

I have tables. talbe1, fields a PartNumber, PartDescribtion, PartTypeID.
Table2, fields a PartTypeID, Partype.
Table3, fields a FormID, Date, PartNumber, PartType, Customer......

What I am going to do is when I enter PartNumber, PartType will shows
automatically, so I don't need to pick the type from the list again.

Thanks for everyone's help.

You're using a relational database. Use it relationally!

The third table should NOT contain the PartType *at all*. It can be
looked up using a Query, or you can use a Combo Box on the form to
*display* the PartType. It's not necessary to store it redundantly in
the table.

John W. Vinson[MVP]

On Sat, 20 Jan 2007 20:42:00 -0800, Hong
wrote:

Because the database was created by another person, and has been used for a
long time. I am not good enough to fix it. I am trying to make the work
easier.
Even in the form, I also need to do the same thing.

So I am trying to fix table and the form base on the situation right now.

It's often easier in the long run to invest the time to fix the
database so it is RIGHT (and becomes easier to maintain, more
reliable, simpler and more trustworthy) rather than continually having
to struggle with a bad design.

That said... if you have a redundant field and you insist that it's
necessary to populate it, use the AfterUpdate event of the field that
controls it. Are you using a Form to fill the tables (in which case
you can use code to do this update)? or shudder are you doing your
data entry directly in Tables? If so... you're out of luck; tables
don't have usable events to do this automatically.

If you are using a Form please post the name of the form and the names
of the controls bound to these fields (they may be the same as the
fieldname but might not).

John W. Vinson[MVP]

The table3 created a query, and the form is from the query, so how can I
change the Part type property to automatically to show the part type base on
the part number entered?

Thank you!

"John Vinson" wrote:

On Sat, 20 Jan 2007 20:42:00 -0800, Hong
wrote:

Because the database was created by another person, and has been used for a
long time. I am not good enough to fix it. I am trying to make the work
easier.
Even in the form, I also need to do the same thing.

So I am trying to fix table and the form base on the situation right now.

It's often easier in the long run to invest the time to fix the
database so it is RIGHT (and becomes easier to maintain, more
reliable, simpler and more trustworthy) rather than continually having
to struggle with a bad design.

That said... if you have a redundant field and you insist that it's
necessary to populate it, use the AfterUpdate event of the field that
controls it. Are you using a Form to fill the tables (in which case
you can use code to do this update)? or shudder are you doing your
data entry directly in Tables? If so... you're out of luck; tables
don't have usable events to do this automatically.

If you are using a Form please post the name of the form and the names
of the controls bound to these fields (they may be the same as the
fieldname but might not).

John W. Vinson[MVP]

On Mon, 22 Jan 2007 09:04:00 -0800, Hong
wrote:

The table3 created a query, and the form is from the query, so how can I
change the Part type property to automatically to show the part type base on
the part number entered?

YOu say "table3 created a query". Sorry, that makes no sense: tables
don't "create" queries! I presume *you*, or the original developer,
created a query? Is Table3 in the Tables window as a stand-alone
table, or is it in fact just a query based on Table1 and Table2?

If the form uses a Query as its recordsource, please open that Query
in SQL view and post the SQL text here.

John W. Vinson[MVP]

Yes, the deveplop created a query form the tables 3 with all the field, and
the form property showing the form source from the query.

"John Vinson" wrote:

On Mon, 22 Jan 2007 09:04:00 -0800, Hong
wrote:

The table3 created a query, and the form is from the query, so how can I
change the Part type property to automatically to show the part type base on
the part number entered?

YOu say "table3 created a query". Sorry, that makes no sense: tables
don't "create" queries! I presume *you*, or the original developer,
created a query? Is Table3 in the Tables window as a stand-alone
table, or is it in fact just a query based on Table1 and Table2?

If the form uses a Query as its recordsource, please open that Query
in SQL view and post the SQL text here.

John W. Vinson[MVP]

On Mon, 22 Jan 2007 13:57:00 -0800, Hong
wrote:

Yes, the deveplop created a query form the tables 3 with all the field, and
the form property showing the form source from the query.

OK, then I'll ask again:

If the form uses a Query as its recordsource, please open that Query
in SQL view and post the SQL text here.

I cannot help you unless you let me do so by providing the information
needed to understand the problem and compose an answer.

John W. Vinson[MVP]

The problem is now on the form, the people need to enter the Partnumber and
also need to enter the Part type, I am trying to make it easier, when they
enter the part number, the field for part type autofill according to what
part number they enter.

Sorry for the trouble.

"John Vinson" wrote:

On Mon, 22 Jan 2007 13:57:00 -0800, Hong
wrote:

Yes, the deveplop created a query form the tables 3 with all the field, and
the form property showing the form source from the query.

OK, then I'll ask again:

If the form uses a Query as its recordsource, please open that Query
in SQL view and post the SQL text here.

I cannot help you unless you let me do so by providing the information
needed to understand the problem and compose an answer.

John W. Vinson[MVP]

On Tue, 23 Jan 2007 08:48:58 -0800, Hong
wrote:

The problem is now on the form, the people need to enter the Partnumber and
also need to enter the Part type, I am trying to make it easier, when they
enter the part number, the field for part type autofill according to what
part number they enter.

Sorry for the trouble.

I would recommend using a Combo Box (named cboPartNumber let's say)
for the part number, so that the user can select it from a list (which
will autocomplete if they type into it), to prevent typing errors.
Include the Part Type in the Query upon which the combo is based:

SELECT [Part Number], [Part Type]
FROM [Parts]
ORDER BY [Part Number];

In the Combo Box's AfterUpdate event, "push" the part type into the
bound textbox (named txtPartType) on the form:

Private Sub cboPartNumber_AfterUpdate()
If IsNull(Me!txtPartType) Then
Me!txtPartType = Me.cboPartNumber.Column(1)
End If
End Sub


Be aware that having the part type stored in both tables is VERY
DANGEROUS - since the user can overtype the part type, you *will* at
some point have one Part Number which has multiple different Part
Types stored. All but one of them WILL BE WRONG, and you'll have no
easy way to detect this fact.

John W. Vinson[MVP]