Okay, here is what you do:
Create a combo box using the wizard to look up the MFGID, Manufacturer in Table 1. Hide the PK column, unless your users use the MFGID number to look up the Man.
Then create a combo box to look up the PartNumer, Description, and MFGID. Hide the PK field, and the MFGID field as well.
Then open the query designer on the second combo box, and add this criteria to the MFGID field:
Then on the After Update event of the first combo box, add this code:
This will do the first part and synchronize the combo boxes. Then on the On Current event of the form, put this code:
Me.cboMFGID = Me.MFGID
This will set the first one to the current record, and synchronize the second. When you scroll to a new record, they should go back to their start state. See if this throws an error.
This should take care of the first part.