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

can't change or add record because related record is required it table?

  1. #1
    FJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    10

    can't change or add record because related record is required it table?


    I created a data base to allow people to input equipment problems the form has combo boxes for date, person reporting, building location, room, and item. I got this working just fine with them selection item by serial number. I created tables for each one of these. In the item table I have additional information about each item Rebuilt date, size ect. and a tag number.

    In the combo box of the item only list the items in the room selected and only shows the serial numbers available. It has worked fine but when adding the record it doesn't include the tag number. I want to change so that only the tag number is needed in the form but will record both the tag number and the serial number in the record?

    I managed to switch the combo box to the tag number instead of the serial number and added a text box that then shows the serial number also but when I try to save the record I get this error message ( you can not add or change a record because a related record is required in table "RackIdt" ) this is the table that has the rack information SN, tag number ect.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You could have two comboboxes, one each for tag and SN. When the user selects one then the other will get updated.

    In the AfterUpdate event of the relevant combobox,
    Me!other_combobox_name=Me!this_combobox_name.Colum n(relevant column number)

    This way you will get both fields updated in the table.

  3. #3
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    212
    Sounds like you have a required field in table "RackIdt" and it is not populating the way you have it setup and therefore will not permit a save.

    I have similar thing were certain fields are required in order to save a record, which cannot be bypassed. If those fields are null (i.e. unavailable) then no record may be saved.

    So either remove the required field in table
    "RackIdt" or figure out why it is not populating the field content when you attempt to create a record.

    Other than that post some snippets of the items of interest (tables, forms, VBA) and explain each snippet so we may understand what you have going on.


    For example, in my database there are fields that are generated automatically based on prescribed format in the
    BeforeInsert [Event Procedure], which generates the unique ID as soon as user starts to type in a new record. However, when I save the record a date-time stamp is generated using the BeforeUpdate [Event Procedure], which occurs as soon as the save button is clicked. Both the uniquely generated ID number and date-time stamp are required fields. If either one does not append then the record refuses to save. Of course there are far more technical things I have going on as far as evaluations and such but beyond the scope of discussion here. Hopefully this helps explain what you might require looking at.


  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,563
    you can not add or change a record because a related record is required in table "RackIdt"
    This implies you have set a one to many relationship between RacIdt and another table to maintain referential integrity with RacIdt being on the one side. The error is then generated because you are trying to add a record to the other table without having a pre existing matching record in RacIdt.

  5. #5
    FJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    10
    I changed the relationship and removed the referential integrity and that worked it allowed it to be saved. But I have another problem it won't save both the tag number and the serial number. It never has I had the rack tag in the original design but didn't have data in the table for all Id's that has been added later. How can I get it to save both items?

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,563
    don't know how your form is designed but if you have a subform, ensure both fields are in the listchild/master properties - separate them with semi colon.

    otherwise you will need to use some vba to populate the field.

  7. #7
    FJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    10
    no subform what would I need to have in vba to populate the field. the table is RackIdt and the fields are Rack ID (this is the one not saving) and Rack Tag.

    Thanks for your help

  8. #8
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,563
    if you have a one to many relationship, then the main form should have the one table and a subform the many table

  9. #9
    FJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    10
    Sorry I new at this built this by watching youtube so my knowledge is limited. I don't understand what you are saying I will send you the db. please take a look at it and help me understand where I am lacking.
    Attached Files Attached Files

  10. #10
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,563
    you'll need to explain how your db works - either your db is corrupt or you've disabled everything. The ribbon is incomplete and will not all me to edit, see code, view relationships etc

  11. #11
    FJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    10
    sorry I deleted some data to make it smaller. Try this one.
    Attached Files Attached Files

  12. #12
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,563
    Nope, still doesn't work, as soon as you close the form that opens automatically, the ribbon loses the various options.

    Just copy the relevant tables/forms/relationships to a new db and leave out all the fancy security stuff

  13. #13
    FJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    10
    Sorry hold the shift key down and hold while it opens

  14. #14
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,563
    With respect, having these security elements (particularly when they are so easily overcome), removing window tabs etc makes it very difficult to develop - and even harder to work out what you are trying to do. People will not help if you make it difficult for them

    However my best guess is you do not need to store the rackID (or the room). You have a combo in rack tag which brings that information through. So unbind the RackID control from the data and instead put

    =cboRackTag.column(1)

    and do the same for room


    =cboRackTag.column(2)

  15. #15
    FJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    10
    Ok thanks I'll try that and as far as the tab I lost them somehow and don't know how to get them back. I agree it is difficult to work with.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-02-2017, 02:33 PM
  2. Replies: 6
    Last Post: 07-28-2016, 06:07 PM
  3. Replies: 15
    Last Post: 03-05-2015, 03:30 PM
  4. Replies: 1
    Last Post: 12-05-2014, 07:47 PM
  5. Replies: 40
    Last Post: 08-20-2013, 11:38 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
  •  
Tech Forums: Microsoft Office Forums