From a table to a form to a table

[foxygrandma]

A friend helped me develop in VBA a form that allows me to simply enter a shipment number and freight cost, and then it adds the calculated data to the main table. What it does is irrelevant.

What I have now is a table with two fields, shipment number and freight cost. It is basically a list of all of the shipment numbers with their freight costs that I have to enter into the form, which will in turn update the main table.

I will have to go through one by one, plugging in the values into the form for the entire list. Which is 6000 rows long. Is there an automated way to pull the data from the table with two fields, enter it into the form, press the button on the form to update the data of the main table, and then return to the table but go down one row and repeat the process? If so, how do I go about doing this?

[pod]

Hello Foxy

What you want to do is SKIP the Form, and go directly from table1 (your new table) to table2 (your data table). There are many ways to do this, and a simple way is to

1. build a query to get a record set of table1 and LOOP through its rows
2. do the required calculations that your Access Form performs
3. build the INSERT query for this row of data and execute it

hope this helps

P :oD

[NeoPa]

Unless you have a VERY good reason otherwise (this would be relevant to the question), I would recommend following the advice given in Pod's post.

Processing the data in the way you've outlined would be very cumbersome and (relatively) very slow (faster than typing the data in manually of course). I expect it's possible but I haven't tried it, so there may even be elements of the process that can't be got to work even.