Finding a Record In One Table and Writing it to Another Table.

[ksmith]

Thanks in advance for ANY help.
I’m in the process of writing an Inventory Control Program. I have everything written except a form that will create the shipping manifest. This form-subform will get its information from several different tables and or queries.
As of Now:
The Form/Subform builds the shipping manifest data. The Table: tblShippingMaster is the table that will hold all of the items that will be shipped from my location.
Mainform: frmShipping1
SubForm: frmSubShippingMaster
On the main form the user will enter the usual shipping stuff; Ship to location, what shipping company for this order, shipping clerk and shipping date.
The subform part will ‘build’ the items that will ship, and store all of the above data plus the items that are shipped.
The subform; frmSubShippingMaster, has four fields that the user will see. The first field needs to be a combo box that looks-up data from a TABLE; tblCatalogModelNumber but writes the Catalog Model Number and the Model Description to TABLE; tblShippingMaster. Also another field in the subform will need to look-up available work order numbers that the current catalog model number will use to ship against.
For example: One record in frmSubShippingMaster, table; tblShipppingMaster will have a catalog model number, model description, amount shipped, work order number for that order (based on catalog model number) , and data from the main form; date, shipping clerk, unique identifier number, ship location and ship via information.


The trouble that I am having is how to write the code to look up data in one table and write that data into another table.
Thanks for any help.

[evander]

The trouble that I am having is how to write the code to look up data in one table and write that data into another table.

Having trouble in:
(1) Looking up the right batch of goods to be shipped based on your shipping table, and appending that batch of goods in another table (say, sales invoice table)? or,

(2) Looking up the correct product you are to include in the shipping table?

My answers to those two are:

For (1): The table that will receive a set of product data based on some batch number should have a field that identifies such batch. Then, you can create an append query whose criteria is the referenced batch number of that table (as displayed in that table's form).

For (2): As a simple solution, you can use a combo box that draws data from another table and displays that set of data as a list available for your selection. If you want a more advanced solution, you can create a pop-up lookup form. When you close that lookup form, the selected data will be pasted on another form that called the lookup form.

Good luck.

[Access_Blaster]

tblShipppingMaster
catalog model number
model description
amount shipped
work order number

table_2?
unique identifier number
Ship to location
shipping company
shipping clerk
shipping date

Is this the correct table structure?
I don't see a primary key for table 1 or any foreign key?

Richard