Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26

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

  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,287
    Relationship between modules and lecturers is many-to-many, hence the 'junction' table lecmods.



    Main form bound to modules for viewing module details. Move to new record row to create a new module record.

    Subform bound to lecmods. The module code will automatically save in the subform - this is what the Master/Child Links properties accomplish. Select an existing lecturer from combobox. If you want to create new lecturer record 'on the fly' during data entry of lecmods subform, this will require opening the lecturers form, enter the new record, requery the combobox so the new record will be available. This can be accomplished manually with intrinsic tools - open lecturers form from Navigation Pane, enter new record, close form, click Refresh All on the ribbon. Select new lecturer from combobox. Automating these steps will require code (I use only VBA). The more 'user friendly' the more code.
    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

  2. #17
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    This experience is my training......I have also decided to avoid access in the future because of this horrible experience. MS could make this so much more user friendly.
    I appreciate your help but between the jargon and the roundabout way of doing everything with warnings and error's every 20 seconds i may just quit on this all together.
    I cant seem to visualise in my mind what is happening making everything one big mess.

  3. #18
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    getting there slowly but the subform confuses things further.slowprogress..zip

  4. #19
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,147
    Yep don't give up, Access is like anything else, it can do some amazing things but you have to have experience with it. And working out the table structure at the beginning is the most important part of a database.

    As far as your original error, not sure if it was mentioned but when you have a relationship 1 to many, you cannot had a record with a Foreign key and not have that Primary Key in the related record. So you were trying to add an email into the 2nd table and did not have a matching email in Lecturer I believe.

  5. #20
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    the lecturer table does have email, and it seems the fields i want in my subform needs to be in the link table as well meaning a duplicate of email.l following advice i have my main form linked to modules, and then a subform linked to lecmod(link table) but then access is dumb and wont let me add the required fields from lecturer. one module selection needs to show ALL lecturers names and email. the above zip needs the fields added in to the sub form. will need to start from scratch soon becuase stuff is getting too broken now. This is just stupid, hours spent doing a 20 minute job. ffs.

  6. #21
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    i want to kill myself, change one thing ten things break. this is torture.

  7. #22
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    im nowhere closer than i was friday, riddles are all that are offered as help across the entire internet. It is as though people who know access don't really want to share step by step help, expecting instead beginners to have a background already.

  8. #23
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,147
    Well that's pretty disrespectful to those giving their FREE time to try to help Access users. The issue is not with Access but your understanding of it. Mods I think you should lock or remove this thread.

  9. #24
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,287
    Certainly defeats purpose of asking for assistance if you can't or won't follow advice provided. I am looking at your db again and little has changed. You still have the course description saved into lecmods. And the course description is duplicated for several course codes in modules table. For instance, "Advanced Software Engineering" is the same description for both CHM1320 and SEM1020. There is no way for Access to know which course code to display for specific record so it will show both. That means for each record in lecmods with "Advanced Software Engineering" when joined to modules table in query, two records will result to show both course codes. Delete all records from lecmods and set the Master/Child Links properties of the subform container to link on course code and module code fields.

    Also, you have not modified the naming convention as suggested. Believe me, will be much better not to have spaces. Better would be CourseCode or Course_Code.

    DO NOT use a bound combobox for CourseCode on the main form. The main form is bound to Modules table. Just display the CourseDesc and CourseCode fields in textboxes.

    The ModuleCode in subform should not be editable textbox. There is no reason to even display ModuleCode in subform. Use textboxes with expression referencing lecuturer combobox to display related lecturer info.

    I still don't understand why you want users to select lecturer by email instead of name in combobox.

    You should spend a solid week with an introductory tutorial book to at least get a grounding in the basics. Because it is understanding the basics that you lack as all errors are related to basic concepts. This db is far too simple at this point for you to be experiencing such difficulty. Some people can just 'wade in' and learn as they develop, others should first go through a structured learning process before attempting the 'real thing'. You appear to be the latter type.
    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

  10. #25
    wayne01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    15
    The data is messed up through trying hundreds of different thing in different forms and tables and until i know i am getting somewhere don't want to keep adding and deleting it, i have wasted enough time on this nonsense.. I want to use email because it is one field, trying to put 2 together is clearly beyond me. I also have to enter all of the data hence the need for combo boxes, pairing up lecturers and modules. all i want is a supposedly simple form to let me enter 2 bits of data and show the results of matching a single module to potentially multiple lecturers in a subform. This is my training and the other version i did with slight differences was no issue, but alas, this one does nothing i want it to. I have already spent a week on this and other stuff and only came here because google keeps throwing out of date stuff at me. I can use competently many programmes but this just makes no sense to me. Maya is one example of software i learnt by "wading" in. The form wizard itself keeps adding unwanted fields and hiding parts of the main form. I tell access to display column 1 of a table no problem, try 2 i get an error instead of the next column. But i am done, i will unregister from here as soon as i can find the option, clearly ruffled a few feathers.

  11. #26
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,287
    I NEVER use the design wizards because they do things I don't like.

    RowSource for the lecturer combobox to display name could be:

    SELECT ID, Surname & ", " & ForeName AS FullName, Email, Room, Location, Ext FROM Lecturers;

    Title of thread is "...what is wrong with my relationships". Answer has been provided.
    If you don't delete the records from lecmods as instructed then definitely will not progress in getting db functional.

    I have no idea what Maya is.
    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

Page 2 of 2 FirstFirst 12
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