0

I am attempting to recreate in MS Access a personal productivity database that I had in Notion (created based on this https://www.youtube.com/watch?v=32dLXdB4ozs). Basically I have a bunch of things (Projects, Meetings, People, Partners (which are orgs), tasks, etc.) and relationships between then. Each project has partners. Each meeting covers certain projects. People work on particular projects and for particular partner organizations, etc. Lots of these relationships are many-to-many.

I've set up most of the back-end stuff: I have a table for each "thing". I also have tables for each many-to-many relationship. So for example I have a People by Partners table to show which people work for which partners, a Projects by People table to show which people work on which projects. A Meetings by Projects table for which meetings are about which project...etc. etc. i have made each field in this a linked relationship to the other two forms. So for example, the projects by people table has a project field linked to the project page and a people field linked to the people table. I've set all of these relationships to have data integrity and to do cascading updates.

Now I'm creating the more interface-like part, which will be used both for viewing and entering data. So I'm starting with a project form. In the project form, I'm putting a sub-form that will list the partners on that project. I created that form based on this video (https://www.youtube.com/watch?v=N46mgIqLXbA&t=0s) which you probably can't see, but the basic idea is that it's a combo box with the record pulled from the partner table and then the update linked to the partner by project table. I have set this to pick from a list since there are a limited number of partners.

Ok, now here's where I'm running into a problem:

I want to set this so that if I enter a partner name not already on the partner list, it gives me the option to add it as new. I've set it up using this code: http://msaccessgurus.com/VBA/Code/Combo_NotInList.htm . T

he code works fine (* sort of, see next para) if I do it directly to update one of the original tables (e.g. Partners), but I need it to update the Partners by Projects table.. I need it to put in the new Partner name, link it to the currently-viewed project, and then from there update the Partners table (which I thought would happen with the cascading updates).

Instead if I enter info not on the list, it asks me if I want to add it and then when I say yes, nothing happens. It doesn't add. In the form field, it shows me the dropdown list of existing partners to pick from. It tells me there's a syntax error and the error seems to be somewhere in the error proc.

How do I get this set up so I can enter the names of new partners and it will appear correctly as added wherever it should appear? Obviously the partners/projects is just one case where I will need to do this, so i need to figure this out or the whole system can't work!

Thank you for any help. Please note: I am not a professional (or even particularly skilled) programmer. My job is something else entirely and I am a muddling amateur with a basic understanding of programming and database concepts and some minor experience with other programming languages, but absolutely nothing with vb.

Ali M
  • 1
  • 1
  • if you are not married to using the notinlist event I suggest using the access create form or create form wizard to create an add partner form. Then add a button to your form and use the button wizard to create a button that opens the add partner form. This leverages the access technology and the code behind the button whether macro or vba is 1 line and easy to learn. If you must do it this way see the next comment. – mazoula Nov 24 '22 at 06:05
  • with the notinlist event you are doing everything manually. So to make an entry in the PartnersProjects table you need a PartnerID and a ProjectID. With your current code you would do this inside the if .RecordsAffected >0. Your code allows you to run SQL so add something like: 'Select PartnerID FROM Partners WHERE PartnerName = ""' & nameyouadded & '""' Do something similar for ProjectID but you probably have access to the ProjectID. Be careful about the string delimiters. – mazoula Nov 24 '22 at 06:18
  • it is probably too much to ask but the recordset works better for this task. For instance you don't have to turn off error messages. see these two links: https://learn.microsoft.com/en-us/office/vba/api/access.combobox.notinlist https://stackoverflow.com/questions/8839377/access-get-newly-created-auto-number-in-dao – mazoula Nov 24 '22 at 06:20
  • Regardless of approach, need VBA. For NotInList, review https://blueclawdatabase.com/notinlist-event-code/ – June7 Nov 24 '22 at 06:43
  • The main purpose of the projects page is as a dashboard, so I don't think the button idea will work well. I mean I want to just display everything about projects (so there will eventually also be subforms for tasks and people and meetings associated with the project, for example). I don't want to have to click buttons to open and close things. I want everything about the project to just be visible and editable. The rest of the comments are mainly beyond my understanding, but I will look through them and google things and hopefully figure them out. Thank you. – Ali M Nov 24 '22 at 11:58
  • I think I was hoping I could somehow add a line of code that would pass the project name to the add code based on what project was being viewed/edited when the add was made. – Ali M Nov 24 '22 at 12:03
  • You have a combobox on dashboard to select a project? If project not in list you want to add a new project? That's what NotInList event should facilitate. As already noted, code is required. – June7 Nov 24 '22 at 21:55
  • Yes, I am using code. I am trying to figure out why the code isn't working. It worked on the main tables, but does not work to update the join table. I'm trying to figure out why. – Ali M Nov 25 '22 at 15:30

0 Answers0