Many-to-Many relationship question?

[Icehousman2]

Here is my problem. I have three tables. tblOrder, tblProduct, and tblOrderDetails. tblOrder and tblProduct have a many-to-many relationship so I created tblOrderDetails that contains both primary keys from the other tables. The primary key in tblOrder is two fields OrderID(autonumber) and AgentID(Number). The primary key in tblProduct is ProductID(number). Now on the form for the user , I have a series of checkboxes to determine which product(s) are going with the order. I then have a submit button that checks which checkboxes are checked and inputs information into the tables accordingly. Using the following code.

Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("tblOrder")

My problem is that the OrderID field is an autonumber field but needs to be inputted in two tables. How do I input the same number in both tables, when I am not specifying what the number is? Is there a way to get around this? Or am I going to have to change how I’m going about this. Thanks for the help in advance.

 

[wazz]

sry in advance for not answering ur question directly.
i'm wondering why the AgentID is part of the tblOrder PK.
w

 

[Icehousman2]

Thanks for the response. The reason that i had to use the AgentID in the primary key is that this program will be used in four different offices. In four different cities. And i can't have them all share the same database because speed is such a factor. So i put in AgentID into the primary key so that i could merge the information on some interval so that all four offices would still be essentially sharing the same Order information. Is this not a good way to go about this?

 

[Icehousman2]

Thanks for the reply Pat. I thought I was getting by the autonumber problem, by using AgentID along with the autonumber to create the key field. If I am just using a sequenced number wouldn't I inevitably end up with duplicate numbers, because all four programs run separately?