Hello I am, as my name implies, a total novice when it comes to Access and SQL. I consider myself proficient in Excel, but before last week I had never created anything in Access. And then my boss turned my world upside down by requiring that I convert our archaic Excel project plan into Access. Thank you so much for being willing to help! I've been working on the shell of the Project Management template provided in Access. I am not sure what information is relevant so if I've left out something that may be pertinent, please let me know!
Here's my problem:
Each project needs to have at least one owner, but there is no limit to how many owners a project could have. I've created an MTM junction table (named tblOwnersmm) connecting [Project ID] to [Employee ID] to assign owners. I made a paired list box showing [Not Assigned] and [Assigned], which populate from some basic select statements in the row source. These return the ID and full name, displaying the full name and reading/writing based on the ID. The [Project ID] value being pulled is the currently open project passed to the form. Queries below.
Not Assigned:
Code:
SELECT e.ID, e.[Full Name] FROM tblEmployees AS e WHERE e.ID NOT IN (SELECT o.EmployeeID FROM tblOwnersmm AS o WHERE o.ProjectID = [Forms]![frmInputSub]![ProjectID]) ORDER BY e.[Full Name]
Assigned:
Code:
SELECT e.ID, e.[Full Name] FROM tblEmployees AS e INNER JOIN tblOwnersmm AS o ON e.ID = o.EmployeeID WHERE o.projectID=[Forms]![frmInputSub]![projectID] ORDER BY e.[Full Name]
I think it would be overwhelming to post the macros that move items between assigned and not assigned, but I'm happy to do so if it's valuable. Essentially, the macros cycle through the ItemsSelected attributes and insert/delete from the tblOwnersmm table then requeries both lists. For inserts, it's using an ADO recordset and for deletes I'm using Execute. Visually, it does what I'd expect and the junction table is also ending up with the correct values.
Issues arise if the Assigned listbox has no records at any points while the form is open.
1) If there was no entry on form load in tblOwnersmm (IE no one assigned), I'll get a "You cannot add or change a record because a related record is required in table 'tblEmployees'" error.
2) If an entry was present on form load in tblOwnersmm (IE at least one person assigned) and at any point in using the form the first entry in the table is removed, I get a "Record is deleted" error.
Both of these occur only when closing the form. I've tried putting error handlers in the macros for the buttons, form_unload, and form_close without success.
Thank you so much for wading through my wall of text. I appreciate the help!