Need help with Related Fields!

[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!

 

[Nikos Yannacopoulos]

Christen,

To get a control to display information based on what's on another, e.g.
part description based on part number:

1. Use an unbound text box (i.e. its control source property is empty) to
display the description, and call it, say, txtDecsr

2. Use the On Change event of the control holding the part number (I'll
assume it's called txtP_No) to run a line of code like the following:

Me.txtDescr = DLookup("[Description]","tblParts","[P_No] = " & Me.txtP_No)

Here I have assumed that the Parts masterdata table is called tblParts, and
the fields in it are called Description and P_No. Change accordingly.

Also, I ahve assumed that the part number is numeric. If it is text, then
you will have to change the DLookup as follows:

Me.txtDescr = DLookup("[Description]","tblParts","[P_No] = ' " & Me.txtP_No
& " ' ")

Likewise for every other look-up control.

Now, for saving: the input part is straightforward, just have your controls
bound to the table fields (except for the part description, which should not
be in the orders table at all). On the output part, the thing to notice is
that you need to pull the part number with a lookup (just like before) fired
by the on change event of the P/O# control, but the part number control must
not be unbound in this case; it must be bound to to the part number field in
the output table, so it gets stored there. Of course, if the part in the
output is ALWAYS the same as the part in the related input, you need not
store it in the output table at all!

Note: on your output form you should make sure it always goes to a new
record upon opening; otherwise, it will go to the first record in the table,
and when the user enters another P/O# it will just ovewrite the existing
record!

Of cource, there are nice and safe ways to deal with this, such as, e.g.,
using unbound controls and code to save to the table, if you're prepared to
go that way.



HTH,

Nikos

 

[Guest]

Dear Mr. Yannacopoulos:
Thank you for your suggestions!
Christen

-----Original Message-----
Christen,

To get a control to display information based on what's on another, e.g.
part description based on part number:

1. Use an unbound text box (i.e. its control source property is empty) to
display the description, and call it, say, txtDecsr

2. Use the On Change event of the control holding the part number (I'll
assume it's called txtP_No) to run a line of code like the following:

Me.txtDescr = DLookup("[Description]","tblParts","[P_No] = " & Me.txtP_No)

Here I have assumed that the Parts masterdata table is called tblParts, and
the fields in it are called Description and P_No. Change accordingly.

Also, I ahve assumed that the part number is numeric. If it is text, then
you will have to change the DLookup as follows:

Me.txtDescr = DLookup("[Description]","tblParts","[P_No] = ' " & Me.txtP_No
& " ' ")

Likewise for every other look-up control.

Now, for saving: the input part is straightforward, just have your controls
bound to the table fields (except for the part description, which should not
be in the orders table at all). On the output part, the thing to notice is
that you need to pull the part number with a lookup (just like before) fired
by the on change event of the P/O# control, but the part number control must
not be unbound in this case; it must be bound to to the part number field in
the output table, so it gets stored there. Of course, if the part in the
output is ALWAYS the same as the part in the related input, you need not
store it in the output table at all!

Note: on your output form you should make sure it always goes to a new
record upon opening; otherwise, it will go to the first record in the table,
and when the user enters another P/O# it will just ovewrite the existing
record!

Of cource, there are nice and safe ways to deal with this, such as, e.g.,
using unbound controls and code to save to the table, if you're prepared to
go that way.



HTH,

Nikos

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!

.