Start by creating a new query with the fields that you want to be able to see, including the part number and save this.
Create a form based on the query that you have just made and add the wanted fields.
Now add a combobox (using the wizard) based on the same table as the query and select part number as it only field. When you get the choice, make sure that you select "Remember the values for later use." Once you have created the combobox go to its property sheet and create a code event for its AfterUpdate event. You need a line of code like Forms![MyForm].Requery (Changing "MyForm" to the name of your form.).
Reopen the query and under the part number add a criteria looking at the combobox that you have just created.
This will look like [Forms]![MyForm]![MyBox] (change the names to your names!) save and close.
You should save the form again and close it and then reopen it to make sure that it has picked up the changes to the query.
Now when you select a part number using the combobox the other fields should show just the details for that record.
HTH
Peter