Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2009
    Location
    Östersund
    Posts
    9

    Unanswered: Can't edit field - why?

    I have attached my database (all names and numbers are made up just to see if it works). When using it, open form "f_Production_Data" and navigate to january 5th 2009 with the Calendar control.

    This is how I have constructed my database but one thing doesn't work, namely updating the "Real" field in "t_Production_Data" from a continuous subform. This field is supposed to represent the actual number of produced items for chosen date, but when typing in the text box I just get a "Bling!".

    Please consider that this is my first Access project!!! My knowledge about relations and stuff is very limited! And don't worry about ugly forms, ugly code and lack of code comments for now, I'm only in the very beginning of this project.

    Can somebody guide me in the right direction? Did I start off completely wrong? I would be very thankful for any help!
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The query your subform is based on is Read-Only. For a primer on why this happens and how to correct it, take a look at Allen Browne's paper on the subject:

    Microsoft Access tips: Why is my query read-only?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2009
    Location
    Östersund
    Posts
    9
    I've been there already but I can't figure out what my problem is. I went through the list again but I still can't find it. So I'm still stuck.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Could you post just the SQL you're having problems with instead of the whole database?
    Me.Geek = True

  5. #5
    Join Date
    Apr 2009
    Location
    Östersund
    Posts
    9
    Do you mean the SQL view of the query? In that case, here it is:

    SELECT t_Production_Data.ProdDataID, t_Production_Data.DateA, t_Production_Data.Product, t_Production_Data.Real, t_Production_Planning.ProdPlanningID, t_Production_Planning.Product, t_Production_Planning.FromDate, t_Production_Planning.ToDate, t_Production_Planning.PlannedProduction
    FROM t_Production_Data INNER JOIN t_Production_Planning ON t_Production_Data.Product = t_Production_Planning.Product
    WHERE (((t_Production_Data.DateA)>=[FromDate] And (t_Production_Data.DateA)<=[ToDate]))
    ORDER BY t_Production_Data.Product;

    And the problem persists even if I remove the ORDER BY or/and WHERE rows.

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    The Enabled and Locked properties of those textboxes are set to No and Yes, respectively, switch them and I think that should work.
    Me.Geek = True

  7. #7
    Join Date
    Apr 2009
    Location
    Östersund
    Posts
    9
    Nope, doesn't work.
    I can't edit the values even directly in the query (datasheet view) so there's something wrong. I can combine each of these two tables (t_Production_Data and t_Production_Planning) with other tables in a query and the fields are editable, but these two tables together in a query doesn't work for some reason, nothing is editable.

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Does this help at all?
    Me.Geek = True

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You can always change the query properties (ie. Recordset Type) to "Dynaset - Inconsistent Updates" versus just "Dynaset". I've found this successful for making updates when doing analysis (and the query wasn't updatable) but I'm not sure what drawbacks this might have on your form. It's not the preferred way of updating.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Apr 2009
    Location
    Östersund
    Posts
    9
    nckdryr: It seems like I might have an Index or Join problem somewhere. Still don't know where or why, though...

    pkstormy: I tried real quick and found that I can edit in the query (datasheet view) but not in my form when I set the query to "Dynaset - Inconsistent Updates".

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You've got some issues in your DB. These may or not be related to your problem, however they should be sorted out anyway.

    You have no relationships defined, but you use the dreaded look ups. these should be avoided - they are documented to be the source of plenty of problems and corruptions. They are Access specific and not part of the relational model.

    Secondly, when you read about database design did you come across terms such as candidate keys, alternate keys, surrogate keys, natural keys?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2009
    Location
    Östersund
    Posts
    9
    OK, problem solved now. The main problem was that my query did not generate unique records "automatically" because I couldn't use a unique key to relate the two tables. Therefore I had to change the query to "Dynaset - Inconsistent Updates" but I didn't think of the fact that the form also has to be set to "Dynaset - Inconsistent Updates". If I do this it works, but I have gotten rid of the Lookups and created relations. Also, I need to add some verification code for entry of Production Planning.

    I will do some more reading as I'm not yet comfortable with basic concepts about databases. I could easily have done this in Excel but Excel is not really suitable for this project.

    Thanks for help and comments everyone! I will probably return with more questions

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •