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?

 

[Pat Hartman]

That's fine except that if you expect to merge the data, you can't use an autonumber. You'll need to generate your own sequence number because the autonumber field can't have duplicates.

 

[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?

 

[Pat Hartman]

No. You would need to use your own generated sequence number PLUS the AgentID as the pk. So just replace the autonumber with your own generated sequence number. Alternatively, when you merge the tables, merge them into a separate table that has a long integer field instead of an autonumber. This will allow you to keep the autonumbers in the separate .mdb's but in the merged .mdb the table definition is different. This would be fine and wouldn't require you to develop a numbering routing since the merged table would never have rows added to it. It would always be replaced by re-merging the source data.