Results 1 to 4 of 4
  1. #1
    TotalNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    7

    Question Errors when successfully writing to junction table from paired list

    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!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I am impressed by your narrative. You seem to have developed a strong understanding of Access in only a week.

    Are you coding macros or VBA? They really are quite different things in Access.

    Record is committed to table when 1. closing table/query/form or 2. move to another record or 3. run code.

    1. seems like a new record is somehow initiated but because no data is entered in required field, get error when form closes, maybe need to UNDO

    2. maybe need to requery form after the delete action
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    TotalNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    7
    Thanks for the response June7. I typically enjoy learning under tight deadlines like this.

    Good callout about the verbiage; I'm writing the updates in VBA, not with the macro builder.

    1. Putting a Me.Undo in the Form_Unload seems to have done the trick. I'll be testing a number of scenarios to make sure but early results look good!
    2. A form requery after the delete moved the error from the application into VBA, allowing me to skip it with an "On Error Resume Next" - not an actual fix, but it should help me meet the deadline.

    I'm hopeful that my problems have disappeared. I'll update my post here either way.

    Thanks again!

    EDIT:
    I found the real problem while looking through my tables: [Forms]![frmInputSub]![ProjectID] was setting its value to 0 on certain changes. Since that variable is passed from a previous form, I changed the reference. It explains everything - there is no employee or project 0. Again, big thanks to June7 for getting me unstuck. Without getting past those application errors, this could have taken a lot longer to find!

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Beware when designing table, access automatically sets DefaultValue property for number type field to 0 and AllowZeroLengthString to Yes for text field. Since I don't allow either, I find quite annoying as I always have to change.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to use a Junction table
    By gwboolean in forum Access
    Replies: 17
    Last Post: 09-15-2017, 03:06 PM
  2. Replies: 13
    Last Post: 05-10-2017, 12:43 AM
  3. Junction Tables and 3164 Errors
    By pdevito3 in forum Access
    Replies: 5
    Last Post: 06-24-2015, 08:24 AM
  4. Junction Table
    By troachjr in forum Database Design
    Replies: 1
    Last Post: 03-08-2013, 02:10 AM
  5. Junction Table ?
    By KCC47 in forum Access
    Replies: 1
    Last Post: 02-19-2013, 10:19 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums