Related fields

  • Thread starter Thread starter Christen
  • Start date Start date
C

Christen

I haven't been able to find a workable solution to this,
so hopefully someone out there knows the magic answer.
I have two tables(with forms)- one for new orders (input)
and one for the completion of the order (output). On the
input form, the user enters the P/O #, Part Number, Due
Date, etc. On the output form, they enter the P.O. #,
number completed, date completed, etc. I want to set it
up so that on the input page, the user enters the part #
the description of the part automatically fills in a
separate field. On the output form, I want the user to
choose the P.O. from a dropdown list (all of the P.O.'s
entered in the input table). Upon choosing a P.O. #, I
want the part # to automatically fill into another
field. The information from the output form should be
saved to the output table and vice versa with the input
table. I have been able to get parts of this plan to
work, but not all of them and not successfully. Any
thoughts would be appreciated! Thanks!
 
Christen,

Am I reading this correctly... that each PO relates to only one Part?
In any case, I suspect you are making life unnecessarily complicated for
yourself... on the basis of what you have said, it is a mistake to have
this data in two tables, it should all be in one table. Certainly you
can have two forms bound to the table, one to process the new order and
another for completion, but I can't see any reason why the data is split.

As regards the display of the PartDescription on the input form, there
are two approaches you could take here. Both assume you have a Parts
table, with PartNumber and PartDescription fields. One approach, the
one I would personally choose, is to make the form based on a query,
which includes both your main data table and the Parts table, joined on
the PartNumber field from both. Then you can include the
PartDescription field in the query, and hence on the form, and when the
PartNumber on the form is entered, the PartDescription will display. In
this case, you would want to set the Locked property of the
PartDescription textbox to Yes, as this would not be a suitable place to
allow this to be edited. The other approach is to use an unbound
textbox on the form, with its Control Source set to the equivalent of...
=DLookup("[PartDescription]","Parts","[PartNumber]='" & [PartNumber] & "'")

By the way, it is not a good idea to use # or . or / as part of the name
of a field or control or database object, and I recommend that these be
changed.
 
Dear Mr. Schapel:
Thank you for your suggestions. I will definitely try
them!
Christen
-----Original Message-----
Christen,

Am I reading this correctly... that each PO relates to only one Part?
In any case, I suspect you are making life unnecessarily complicated for
yourself... on the basis of what you have said, it is a mistake to have
this data in two tables, it should all be in one table. Certainly you
can have two forms bound to the table, one to process the new order and
another for completion, but I can't see any reason why the data is split.

As regards the display of the PartDescription on the input form, there
are two approaches you could take here. Both assume you have a Parts
table, with PartNumber and PartDescription fields. One approach, the
one I would personally choose, is to make the form based on a query,
which includes both your main data table and the Parts table, joined on
the PartNumber field from both. Then you can include the
PartDescription field in the query, and hence on the form, and when the
PartNumber on the form is entered, the PartDescription will display. In
this case, you would want to set the Locked property of the
PartDescription textbox to Yes, as this would not be a suitable place to
allow this to be edited. The other approach is to use an unbound
textbox on the form, with its Control Source set to the equivalent of...
=DLookup("[PartDescription]","Parts","[PartNumber]='" & [PartNumber] & "'")

By the way, it is not a good idea to use # or . or / as part of the name
of a field or control or database object, and I recommend that these be
changed.

--
Steve Schapel, Microsoft Access MVP

I haven't been able to find a workable solution to this,
so hopefully someone out there knows the magic answer.
I have two tables(with forms)- one for new orders (input)
and one for the completion of the order (output). On the
input form, the user enters the P/O #, Part Number, Due
Date, etc. On the output form, they enter the P.O. #,
number completed, date completed, etc. I want to set it
up so that on the input page, the user enters the part #
the description of the part automatically fills in a
separate field. On the output form, I want the user to
choose the P.O. from a dropdown list (all of the P.O.'s
entered in the input table). Upon choosing a P.O. #, I
want the part # to automatically fill into another
field. The information from the output form should be
saved to the output table and vice versa with the input
table. I have been able to get parts of this plan to
work, but not all of them and not successfully. Any
thoughts would be appreciated! Thanks!
Click to expand...
.
Click to expand...
 
Back
Top