Problem with inserting values / records from one to another table 1

[JB83]

Dear reader(s),

I have a problem with inserting values / records from one table to another table. The situation:

 --- PRODUCTNR --
¦  ____________  ¦
¦ ¦            ¦ ¦
¦ ¦ ProdNr     ¦_¦___  N
¦ ¦____________¦ ¦   | 
¦  ------------  ¦   |
¦ ¦ ProdNr1    ¦ ¦   |
¦  ------------  ¦   |
¦ ¦ ProdNr2    ¦ ¦   |
¦  ------------  ¦   |
¦ ¦ ProdNr3    ¦ ¦   |
¦  ------------  ¦   |
 ----------------    |  (One-to-many relationship)
                     |
- PRODUCTGROUPNR -   |
¦  ____________  ¦   |
¦ ¦            ¦ ¦   |
¦ ¦ ProdGrpNr  ¦_¦___|
¦ ¦____________¦ ¦     1
¦  ------------  ¦
¦ ¦ ProdGrpNr1 ¦ ¦
¦  ------------  ¦
¦ ¦ ProdGrpNr2 ¦ ¦
¦  ------------  ¦
¦ ¦ ProdGrpNr3 ¦ ¦
¦  ------------  ¦
 ----------------

Note: This is not the whole scheme of tables.

Immediately after ProdNr has been filled, it has automatically have to add a record in PRODUCTGROUPNR and copy the value from ProdNr and paste it into ProdGrpNr.
Can this be done with the Append-query or is there another possibility to tackle this problem?

Thanks in advance,

Jochen.

 

[hjgoldstein]

Hi.

It looks like you have a field missing in your ProductNr table.

You should have ProdNr, ProdGrp, Prodnr1......etc.

The relationship between the two tables should then be:

ProductGroupNr.ProdGrpNr -> ProductNr.ProdGrp in a one-to-many relationship.

Then when you add a product, you select (from a combo box?) the group number. This is an essential part of the product record for the relationship.

Hope this helps.

 

[JB83]

hjgoldstein,

I have already found a solution. Thank you for your help anyway. I have another question though: Is there a possibility to remove the message box which warns when a record is being added in the DoCmd.RunSQL-query?

Thanks in advance,

Jochen.

 

[hjgoldstein]

Jochen,

Use the "SetWarningsOff" command.

Remember to use "SetWarningsOn" afterwards, or no warning messages will be generated for anything else either.

 

[JB83]

hjgoldstein,

Thank you for your fast responding and help, it works!