1

I want to build a small Access database to better keep track of the companies we are looking at. I read Access 2010 Inside Out by Conrad/Viescas, did a lot of their examples and had the feeling I understood the basics, so I started with my own data base. Now the struggle begins, I think I have a basic misunderstanding here.

The relation I started with is quite simple: Each company we look at can have listed peers that we want to use to compare this company to. Of course, each company can have many peers and each peer can be the peer of many of our companies. So I modelled this relation as a many-to-many relationship:

enter image description here

Next, I created the form for a company, which looked something like this:

enter image description here

I related the subform I used to show the peers with a query that is based on tblPeersCompanies_1 and gives some additional information. What I now want from a user perspective is straightforward: A user should simply add peers to this subform for the company he is currently viewing. Access should then automatically update tblPeersCompanies_1 and tblPeers_1. The peers really serve no other purpose than to relate them to a company.

However, I struggle implementing this. Adding a new peer to the subform does not work, simply because it is not based on tblPeers_1 and if I enter the information there, Access notices that the peer is not in that table yet. (That is at least what I think the problem is). How can this be achieved though? I don't want the user to open another form, enter the peer first, go back to this form, type the peer again and the other related information. I hope that there must a simple way to do that automatically. Or is this indeed not so simple.

In summary, the question probably can be phrased as: "How to add records to a matching table and a related one-table on the fly in a form?"

Christoph_J
  • 6,804
  • 8
  • 44
  • 58
  • 2
    Have a look at http://stackoverflow.com/questions/12131211/create-form-to-add-records-in-multiple-tables/12132196#12132196. Also, from what I can see, a peer is a company, so you have one table too many. – Fionnuala Jul 15 '14 at 10:07
  • @Remou Thank you very much for that link, I have not seen that. So it seems that I didn't miss anything obvious, it is just not that simple. Just to clarify then: In your answer, you do not show the code how to add records, you just mention that it should be done with the not in list event, right? Also a good point with the peer being just a company. But as far as I see it, I would need to do a self-join many times because a company can have many peers and those would have to saved in additional columns... – Christoph_J Jul 15 '14 at 10:57
  • If this is correct, I would rather keep the current set up because I do not need a lot of information on the peers. Anyways, the tip with the combo box is great and will be very handy for the future. Your answer is really helpful for this. So could you just post your comment as an answer and I can accept it? – Christoph_J Jul 15 '14 at 10:59
  • It is not really correct for me to post a link as an answer on SO. Regarding the self-joins, that is the whole point of a junction table. So: Company -> (one) Junction -> Peer (many) – Fionnuala Jul 15 '14 at 11:03
  • Thanks also for the explanation with the self-join. I can't wrap my head around it for now, so I leave it at it is, knowing that it is not the best data base design. I hope I can revise it when I get a better understanding. – Christoph_J Jul 15 '14 at 14:22

1 Answers1

1

Thanks to the great comment by @Remou, I found a solution to this problem. It contais three steps:

  1. Use a combo box as outlined in another SO post
  2. Use this function to automatically enter new records in case the peer is missing. Call this function in the "On Not in List" event
  3. Show other values from tblPeers by linking it to the selected value in the combo box, as explained here

I have to say, this is much harder than I hoped it to be. Let's hope that the learning curve is steep and that it will at least be easy to use for the user and quite robust.

Community
  • 1
  • 1
Christoph_J
  • 6,804
  • 8
  • 44
  • 58