Page 1 of 2 12 LastLast
Results 1 to 15 of 26

Cannot add or change records error..... what is wrong with my relationships.

  1. #1
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15

    Cannot add or change records error..... what is wrong with my relationships.

    Here goes...
    I get an error saying "you cannot add or change a record as a related record is required in table 'lecturer'."

    i have 3 tables

    lecturer - lecmod - modules

    in those tables

    forename - email - module code(pk)
    surname - module code - module description
    email(pk)
    room
    location
    extension

    My relationships are- lecturer(one)-lecmod(many) and modules(one)-lecmod(many)

    I need a form that shows a modules code and description and all the lecturers that teach that module as well as their name and email.

    many thanks.
    Attached Thumbnails Attached Thumbnails form.PNG   relation.PNG   result.PNG  

  2. #2
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,643
    Why do you have Surname, Forename and email in two different tables? Why doesn't the Lecturer table have an AutoNumber PK?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    Thanks for the reply. I used email as a unique identifier, same with the module code. i was just trying the lecmod table again with ID(autonumber), email and module code. Should i be applying autonumber to the lecturer table? should lecmod have just email and module code fields?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,305
    Like people names, email is a very poor unique identifier - people tend to change emails occasionally. As RuralGuy suggests, use autonumber PK for lecturer and save the PK as FK in lecmod. Don't repeat name and email in lecmod.

    lecmod is a 'junction' table. Customary arrangement for data entry form would be:

    Main form bound to lecturer, subform bound to lecmod with a combobox to select module

    Otherwise, the solo form you have should work. What is the RecordSource for this form?

    BTW, suggest making table name a plural just as you did for modules - lecturers.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    Thanks for reply but no matter what i do i get an error. My new relationships and tables just give me the error on the modules table instead of lecturers. I also feel i must have email in the link table otherwise how do i enter the lecturers, there is no way i can remember or know the autonumber. Nothing makes sense in access, it is a messy, unintuitive programmes that actively dissuades people from using it. closing dialog boxes every ten seconds is a joke. I am also having issues with this forum telling me to log in repeatedly and not uploading images.

  6. #6
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15

    my new error prone tables

    Click image for larger version. 

Name:	relation2.PNG 
Views:	13 
Size:	6.9 KB 
ID:	31253This is the new table with the record required in modules table now.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,305
    You select the lecturer from a multi-column combobox that displays the email or even their name but saves the autonumber, the autonumber would even be hidden. Users never even have to know it exists. This is a common technique.

    Or use the form/subform arrangement I described earlier.

    Access is the only relational database I have used and can't say I find it 'messy, unintuitive'. Have you used any other relational database to compare Access with? I took a stab at installing SQLServer Express once and gave up. Can't compare a database app with a word processor or spreadsheet app, they are way too different. Have you completed any tutorial books? Anything new has a learning curve.

    Why are dialog boxes opening?

    If you want to provide db for analysis, follow instructions at bottom of my post.

    As for the forum issues, has nothing to do with Access and not issues I have.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  8. #8
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    I think im getting somewhere, the combo box displays the field i want to use for record entry but the autonumber fields are different data types to the criteria i want to use causing an error.diffinfieldtypes_cpy.zip

  9. #9
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    So it seems i shall just quit uni all together, if this is what i have to look forward to i would rather shave with an electric sander. Access provides no help only criticsm forcing me to spend more time opening and closing stuff than actually working, everything online is from ten years ago, worst methods of working ever dreamed of, going round in circles chasing and moving a problem.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,305
    There are no forms in the db.

    So all I can review is the combobox settings in the lecmod table, which is major source of errors because cannot save text descriptors to number type fields. In both cases the BoundColumn property should be set to 1 so the ID is saved, not text descriptors. Now the lecmod table works.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  11. #11
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    Thanks for your help. With your pointing in the right direction and lots of trial and error I seem to have gotten to a point where I can input the data via the combo boxes. Once finished inputying all that remains is to put the results into a what I think is a sub form to show a module it's description and all its lecturers by name and email.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,305
    Conventional form arrangments for many-to-many relationship:

    1. solo form bound to lecmod with comboboxes to select lecturer and module

    2. main form bound to lecturers and subform bound to lecmod with combobox to select module

    3. main form bound to modules and subform bound to lecmod with combobox to select lecturer

    One simple method to display related data is to have textboxes with expressions that reference columns of combobox by index. Index begins with 0. So if Surname is in column 3 its index is 2:

    = [comboboxname].[Column](2)
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  13. #13
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    Again, thank you. i am now lost again. I need to select a module and then in the subform show its decription and ALL the lecturer that teach it and their details.workingnot (2).zip

    this is a disaster why cant access be noob friendly and just ask in ENGLISH what i want and do it?gettingajokenow.zip

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,305
    See option 3 in post 12.

    However, the linking on module code and course code isn't working because lecmods table has the module description saved, not the module code. This is because you originally had the combobox BoundColumn as 3 instead of 1. I NEVER set lookups in table. You will have to correct lecmods data. Just delete the records.

    There is no need to have module code displayed in the subform. Use textboxes instead of comboboxes on main form to display the module code and description.

    Use the described method of expression in textbox to display the related lecturer info.

    Advise not to use spaces in naming convention. Better would be Course_Code or CourseCode. Modules is a reserved word - should not use it as name for any objects.

    If you are trying to use Access without any training, I can understand why you feel frustrated. Recommend you get an introductory tutorial book and work through it. Really need a basic understanding of relational database principles, Access functionality, programming concepts, and VBA or macro coding.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  15. #15
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    Now i am even more confused....i needed to input both lecturer and module details, this has to be done with combobox, how does removing one let me input the data for that field? I understand sub form to be one to many, that means modules would be the one and lecturers the many? what am i not seeing that makes access so awkward? delete the records? and put them in again at some point?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-27-2017, 04:02 PM
  2. Replies: 3
    Last Post: 07-18-2015, 04:02 PM
  3. Replies: 5
    Last Post: 12-04-2014, 05:37 PM
  4. VBA If getting error because I know it is wrong
    By Ruegen in forum Programming
    Replies: 8
    Last Post: 10-27-2013, 08:34 PM
  5. Replies: 9
    Last Post: 05-11-2012, 10:18 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
  •  
Tech Forums: Microsoft Office Forums