Save data from one form into a different table?

[megamef]

Hi All,

This seems simple but I just can’t work it out.

I have a form called “new jobs form” populated by a query called “New Jobs query”

This saves data in a table called “jobs”.

I have 5 fields in the “jobs” table called:
“Purchase order number 1”
“Purchase order number 2”
“Purchase order number 3”
“Purchase order number 4”
“Purchase order number 5”

I’m using a combo box on my form which is populated by a query called “purchase orders query” which itself is using a different table called “purchase orders”.

I’m using the on click event of the combo box to increment a counter and put its value into the appropriate “Purchase order number #” field on my form.

This all works fine.

Now I’ve realised I need to store the “Job Number” (which is a unique number for each record in “jobs” table) in the “purchase orders” table.

So for example:
In the jobs table I have:
Job001 ¦ Purchase001 ¦ Purchase002 ¦ Purchase003

When I save the information in the “new jobs form” I now need 3 records in the Purchase orders table that are:
Purchase001 ¦ Job001
Purchase002 ¦ Job001
Purchase003 ¦ Job001

I’ve NO idea how to do this.

Can anyone help me?

 

[KenHigg]

Unless you have this developed and in production with a lot of data in it I would suggest you re-think your data model, 5 po flds in one table is bad juju...

 

[megamef]

Well this is half in production. The Jobs table has been in use for about 2 years now and has 2000+ records but the purchase order table is new (at the moment we still do this by hand until I complete the mods to the database). So while I can re think some aspects I can't alter the structure of the jobs table a lot (all I can do is add fields).

How ever I structure it though I need to add multiple purchases to one to one Job and they need indication in both directions.