Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Axlegrease is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    9

    Table structure advice to avoid error 'CANNOT CHANGE A RECORD BECAUSE A RELATED RECORD IS REQUIRED'

    Please can I get some help to decide how best to structure my tables for the scenario below?
    I want to get ‘tools’ made and I want to do so by issuing a request via a printed request form (printed report).
    So table ‘A’ will be ‘Tool Request’ which will contain a description of what is required, who raised the request and attachments for drawings etc.

    This ‘Tool Request’ will be used to make different types of tools by category.
    One tool category in particular is ‘Fixtures’ which needs to have an incremental serial number and be recorded with that and other specific information relating only to this specific category.


    We also have lots of ‘Fixtures’ that were created and serial numbered via manual paper system (approximately 1450) so I created a separate table for ‘Fixtures’ to store those that were made via the Tool Request form and also to record the old ‘Fixtures’ to this single table.

    I added a field to the ‘Fixtures’ table to store the Tool Request ID so that I could link a subform (child) to the Tool Request (main) form.
    Problem is, for any record that I want to enter for an old ‘Fixture’ that will not have a Tool Request ID, I get the error message that I ‘cannot add or change a record because a related record is required in Tool Request table’
    Can anyone please suggest how to overcome this or a better way to record the information?

    Thanks (in anticipation!)
    Steve

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why is RequestID in Fixtures table? Can each fixture be associated with only 1 request? Will each request have only one fixture?

    Depending on above answers, options:

    1. 'dummy' record in ToolRequest to associate with the 'old' fixtures.

    2. FixtureID in ToolRequest instead of ToolRequestID in Fixtures
    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
    Axlegrease is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    9
    Thanks for your reply,
    The reason for having the RequestID in the Fixtures table was to have a hyperlink and open that specific Tool Request entry to view the details.
    Each new fixture will have a specific record of who raised the request, so yes, each request will only have one fixture (if at all, as the request may be for another tool type)

    I will give option 2 a try!

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you show us your tables and relationships (printscreeen/capture ) of your relationships window?

  5. #5
    Axlegrease is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    9
    The database is lifted from the standard MS template 'Issues' which I have hashed for my own needs..well nearly!
    Attached Thumbnails Attached Thumbnails Tool Request Relationships screen print.PNG  

  6. #6
    Axlegrease is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    9
    Hi again,
    Sorry June7, I didn't manage to make option 2 work as you suggested above but have attached a stripped down DB of what I am trying to do and hopefully you may be able to suggest a better setup.

    I want to have a single 'Fixture Register' Table that can have entries input via the Tool Request sub form (and have the Tool Request ID linked for reference) but also input the old Fixtures (that I do not want to raise a Tool Request for) via the Fixture Register form.

    (Or would I be better have a separate table for new and a table for old and bring all the fixtures (new & old) back together somehow?)

    Any help VERY much appreciated!
    Attached Files Attached Files

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Option 2 would not be a form/subform arrangement.

    You would have an independent form for entry of records into FixtureRegister.

    Then another form bound to ToolRoomRequests with a combobox to select fixture. FixtureRegister would be the data source for the combobox list.

    Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.
    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.

  8. #8
    Axlegrease is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    9
    Hi again,

    Thanks June7 for your last message. I have mocked up the DB as suggested with the combo box to select the new 'Fixture'.
    The command button launches the entry form for 'Fixtures' and the combo box has a Requery added to it to then include the new record in the drop down box.

    I am a little nervous that the user, having created the new record, will have to then manually select it from the combo box. Is there a practical way perhaps to update the combo box field automatically by transferring the newly created record ID to it for example?
    Attached Files Attached Files

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am not understanding business process. Will user always begin data entry by creating a new record in FixtureRegister? If so, does this mean Requests are never for existing Fixtures?

    Might find this of interest http://www.blueclaw-db.com/access_no...ed_example.htm
    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.

  10. #10
    Axlegrease is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    9
    The 1st user will raise the request for the fixture. These second user will see the open request assigned to them, perform the task as per the request and create the fixture record with the specific fixture details. If the need to then look up the fixture record can be avoided, that would be nice.
    You are correct, requests will only be raised for new fixture requirements going forward but fixtures already in existence will just be recorded to the fixture register via the Fixture Register input form.
    Thanks for the link!

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So what is this data relationship - 1-to-1? If so, might as well be 1 table.
    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.

  12. #12
    Axlegrease is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    9
    If we didn't have any old fixtures to record, the relationship would be 1-request and 1-fixture and all that information could be in one table.
    Problem is, I have over several hundred fixtures that have already been made that I need to record and it would be a pointless exercise raising Request records for these. I just want to make an entry to a Fixture table.

    I started out first go by having a separate table for the Requests to include the 'new' Fixture details combined and a separate table for old fixtures. I was viewing them on a form with 2 sub reports (one sub report for new and one for old).
    For some reason I thought it would be a good idea to have a single table as a Fixture Register for all fixtures but got stuck and hence this post.

    Ultimately, I just need a way to view all fixtures old and new and ideally, see a reference to the ones that have have requests raised.
    As I am going to commit to typing hundreds of records in, I want to find the most appropriate table arrangement.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You need to view old fixtures on data entry forms? If they aren't connected to a Request and won't be in the future - Why view on forms?

    You could have 1 table that includes the old records - will just be a lot of empty fields for those records. However, eliminates the issue of enforcing relational integrity.
    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.

  14. #14
    Axlegrease is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    9
    Hi June7,

    So for that particular relationship, which is one to one, there is no great need to have referential integrity checked then, thus avoiding the 'related record is required issue'?
    Is there anything to be cautious of when not enforcing referential integrity, is the worst I can expect in my case, records with an empty field where there isn't data?
    Regards,

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Many developers don't bother with Relationships and instead control integrity with code through forms because users aren't supposed to work directly with tables and queries. Only you can decide if data should be allowed in either table without a related record in other table. If you want to allow new fixtures without a Request record, then so be it. If you don't then either use Relationships or manage with code or have a single table.
    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.

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

Similar Threads

  1. Replies: 1
    Last Post: 12-05-2014, 07:47 PM
  2. Replies: 8
    Last Post: 06-26-2014, 08:52 AM
  3. Replies: 40
    Last Post: 08-20-2013, 11:38 PM
  4. Related Record Required
    By ungratefulninja in forum Database Design
    Replies: 7
    Last Post: 11-02-2012, 10:21 AM
  5. ...related record required...
    By degras in forum Forms
    Replies: 11
    Last Post: 11-03-2011, 12:20 PM

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