Here's an approach to play with and see if you like the results:
Define a value list of 2 columns of information. You'd specify your ID field as the source for column 1 and your part number field as the source for column 2. Use the value list options to hide the first column. Now format your ID number field in field/control | setup... to use this value list in a pop up menu (not a drop down list). Pop-up menus will hide the ID number and show the matching part number from the value list instead.
Note: in many databases, you'd want to connect to a different record when the part number is changed. Apparently, this is not the case in your situation.
The problem with this set up is that if a part # changes, the related record in the other table is lost.
Where I am getting stuck is setting up the RecordID to change accordingly when the Part # is entered or changed.
Isn't changing the RecordID precisely what you are trying to avoid? Leave the record ID alone to maintain your linkages, change the part# as you need.
You have a functionality in mind that makes you want to change the RecordID. What is that functionality, and what makes you want to change the RecordID to accomplish it?
I see where I am unclear.
A change of the Material Part# in the Material table, should not lose the relationship to the Formula table (a slightly different part # or product replacement).
A change of the Material Part# in the Formula table, should change the relationship to the new desired record (a change in the formula).
That's a good work-around, but we are using drop down menus so you can type and narrow the selection (hundreds of records). I would hate to lose that feature for a cumbersome pop-up menu.
You may want to add a search field with a script (put a button next to the field or give it a script trigger) The script would search your table for a matching part number and then use set field to enter the ID number into the parent record's ID field.
I don't know the level of discretion or access folks have for changing formulae. In my shop, a formula change is about as hard as a vendor change for a government program ;)
Since it doesn't happen often, I have a button that pops up a new window showing a layout that has the part name and "RecordID" on it along with a "Find" button.
You find the "official name" of the new ingredient in the new window, then scroll quickly through the dropdown and select it. All in all it takes less than a minute. If you're changing multiple formulae daily (insert cringe here) I can see where even this method would be cumbersome.