Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    nkenney is offline Advanced Beginner
    Windows 2K Access 2007
    Join Date
    Mar 2009
    Posts
    40

    Help on error - This resordset is not updatable.


    I have a combo box when attempt to change the value I get the error messge "This recordset is not updateble". It flashes quickly at the bottem. Is there something within the form which gets set to update the data in this table? I am not sure where to begin and would love some help.Thanks!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would start with the RecordSource of your form. http://allenbrowne.com/ser-61.html

  3. #3
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92

    Access 2007 - Form Error : "Recordset Not Updatable"

    Hi - I read the Allene Brown website above that gives reasons why a form may turn read only - unfortunately, I am still unable to troubleshoot why this is happening to me.

    Basically - I have two tables:

    Table #1 - contains all the main ndividual information
    Table #2 - is purely a listing of Cost Centers, City, and an assigned business name. A combination of City and Cost Center, then aligns itself to one business name. One business name can have more than one cost center/city

    I have successfully linked City and Cost Center which also exists in Table#1, and am able to run a query that results in an assigned business name.

    My trouble now is when creating the form. Once I try to add "business name" into the form - so that it will automatically population once Cost Center and City are entered - the form becomes read only.

    Can someone advise what I might be doing wrong?

    i already checked the relationships for City and Cost Center between the two tables.

    Currently I have the join type for both as "Include All records from Table #1 and only those records from Table #2 where the joined fields are equal."

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You are having problems because your two tables have a 1:m relationship with one another. There is more than one record possible in table#2 for records in table#1. I also suspect you are not using AutoNumbers for your PrimaryKeys in both tables.

  5. #5
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    agree -

    in order for a match to exist, both cost center and city must match, not just one - basically i think of it similar to excel where you would use a vlookup with the "and" condition as well.

    i do not have any primary key in the cost center/city one - since nothing is unique individually.

  6. #6
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    thing is though - when i run the query - the correct Business Name does match in.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Which table are you trying to update with your form?

  8. #8
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    it says Relationship type; indeterminate.

    could that be why

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Access likes to have PrimaryKeys and ForeignKeys in order to determine the relationship. Which table are you trying to update with your form?

  10. #10
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    table #1 - new hires
    - this is the table i want to edit using a form. the form is just a pretty version of it, and this is where all data should be entered

    table #2 - office
    - purely a referential table that is not to be updated in the form - it purely shows the cost center and city, in two separate fields, and then business. for ex;
    cost center; a100 can be associated with three cities - new york, hong kong, london. and then this reflects it to three different businesses
    another cost center a 200 has three cities as well, and links to the same threee businesses, and so on.

    all data fields in the form are from table 1, and i am trying to insert the business field from table 2, after cost center and city fields from table 1 - whiare are look up fields off of table 2, are entered in

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You may need to use DLookup()'s in unbound controls for the information from Table #2.

  12. #12
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    how do I use the dlookup function?

    do i create a query to create teh variable?

    apologies, i am new to access and do not have vba experience - just lots of excel formula experience.

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's a good reference for the Domain Function syntax. All of the arguments are strings. You would simple create a procedure to retrieve the data you need and call it from the AfterUpdate event of the control that is the basis for the other values. Or you could have is as the control source of the unbound control.

  14. #14
    diane802 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    92
    i just updated the recordset type for the Form to be dynaset inconsistent updates

    and then it allowed me to edit...

    any thing i should be concerned with

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows XP Access 2002
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Hmm...that's the second time I've seen that for ac2007. Do you remember what it was before you changed it? If access is happy then I would not be worried. I believe you have a good solution for this thread and you can mark this thread as Solved.

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

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