First of all: If each purchase and sale has a customer ID, why do you save another customer ID in the PartNumber2 table? How is the PartNumber related to one distinct customer? A part may have a manufacturer or a vendor but in my honest opinion that would be an entity other than the customer. But what can I know about your business?
-------------------------------------------------------------------
The attached version has 2 more form documents.
Form2_Add is the one you already know with a filtering form and a form pulling the filter values into the new record.
There is one more thing you have to understand about this simple one. The refresh button belongs to the subform with the grid. It refreshes its containing subform based on the modified value(s) in the parent form. Base forms are saved automatically when the input focus changes from one modified form to any other (sub-)form. When clicking the refresh button, the button takes the focus which triggers the saving of the modified form which used to have the focus before clicking the button. Then the button performs its action or macro.
------------------------------------------------------------------------
Form2_Add1 has 3 levels: a filter form taking a customer ID, a subform with 2 listboxes and a refresh button [-->] showing the customers purchases and sales after hitting the refresh button and a sub-subform with the resulting form getting the preset values for the new record. I did not pass through the customID because I think it is obsolete but it would be possible.
The first refresh button takes the focus (saves the parent form), refreshes the subform with the green list boxes and the list box contents depend on the INT1 in row #1.
The 2 green list boxes store their values in INT1 and INT2 of row #2. When you click the second refresh button it moves the focus to the third form and refreshes it according to the corresponding values INT1 and INT2 of the parent form.
---------------------------------------------------
Form2_Add2 is a macro driven version. It requires this Python code:
download/file.php?id=35500 which comes as a text document with an install button which triggers an embedded Basic installer.
The macro is very minimalistic. It can refresh any collection of refreshable items (combo boxes, list boxes and subforms) within the same form document without editing the macro code. Because this macro is supposed to work with any form, the installing routine installs the macro to the global container "My Macros", subfolder "pyDBA", file AutoRefresh.py and my example form depends on the macro being installed to the right place. Just open the file and click the button.
The information about the items to be refreshed is stored in hidden controls. Hidden controls are named values attached to logical forms visible in the form navigator only. Their only purpose is macro configuration. For this auto refresh macro I added a hidden control named "AutoRefresh" to both filtering forms and entered the name of the subform to be refreshed below the name. Then I assigned the form_Action macro to the "After Record Action" event of same form having the hidden field AutoRefresh.
Any macro assigned to the "After Record Action" is called whenever the current record of the calling form has been modified and saved, deleted or when a new record has been inserted and saved.
The macro then looks up the hidden control "AutoRefresh", reads the (list of) item(s) to be refreshed and refreshes them.
I moved the two buttons which used to refresh their containing subforms one level up to their parent forms, changed the action property from "Reload" to "Save form". Furthermore I set property "Default button", so these save buttons are triggered by the enter key. Saving the parent form triggers the macro. Now the modified form is saved when you hit enter or click the button and the subform in the hidden control is refreshed.