Results 1 to 11 of 11
  1. #1
    zool is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Location
    Miri
    Posts
    21

    Question How to edit data from query inside a form?


    Hi,

    I have multilevel of queries and combined it inside a form. Is it possible to edit the data inside the form? Because i've tried it and it didn't work.
    I've also changed the recordset type to Dynaset(Inconsistent Updates) -from queries and form but i still could not edit the data inside the form.

    Zool

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,596
    'multilevel' of queries means what? - you have joined tables that have 1 to many relationship of parent and child tables? Probably need to use form/subform arrangement.
    Last edited by June7; 09-09-2012 at 11:57 PM.
    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
    zool is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Location
    Miri
    Posts
    21
    Multilevel Query = (Query 1) + (Query 2) whereby Query 1 = (Query A) + (Table 1) and Query 2 = (Query B) + (Table 2). Query A & Query B is calculation based Queries.

    I tried to use form but could not get those table and query to appear together using "Add Existing Fields". As if, the tables and queries doesn't have relationship. When i tried to add relationship between the tables and queries, the data messed up and i don't want that. The data is linked internally using queries but it doesn't want to connect to certain data from tables directly. Maybe it's due to calculation based queries that i have.

    P/S: I had to do this so that my query is more flexible.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,596
    I don't understand any of that. Need to show data structure and the SQL statements or provide db for analysis.
    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.

  5. #5
    zool is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Location
    Miri
    Posts
    21
    June7,

    Please find attached link of my db for you to analyze.
    Forms that i referred to was Inventory form.

    Edit:
    Lets say, i had to change the table and queries structure, i'd prefer to create a form(Lets say, Edit Record form) to edit the data.
    But i want to know how i can point the data highlighted inside Inventory form to automatically select the same data inside Edit Record form for edit.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,596
    The Inventory form has an aggregate query as RecordSource. Aggregate (Group By) queries are by their nature not editable. This query is joining other queries that also involve aggregate queries. The data on the form looks exactly like the data in the Purchased Equipment table. Do you use the Inventory query to create the Purchased Equipment table. Why create table?

    What records from what table do you want to open and edit?
    Last edited by June7; 09-11-2012 at 10:05 AM.
    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.

  7. #7
    zool is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Location
    Miri
    Posts
    21
    Do you use the Inventory query to create the Purchased Equipment table. Why create table?
    Nope, Inventory Query is based on Purchased Equipment table not the other way. It just that i created lots of query because i could it involves calculation.


    What records from what table do you want to open and edit?
    I'm trying to make a form for Inventory List based on Purchase Equipment table. Please refer existing "Inventory List" form for reference. I wanted to display data from Inventory query (Balance(Miri) and Balance(Labuan)) together with Purchased Equipment data. How do i add data from Inventory query to Purchased Equipment form(Inventory List)?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,596
    Can't join Inventory query into the form RecordSource so the only method I can see is DLookup function in textbox ControlSource.

    =DLookup("[Balance (Miri)]","Inventory","Model='" & [Model] & "'")

    Recommend no spaces, special characters, punctuation in names, nor reserved words as names. Better would be BalanceMiri or Balance_Miri.
    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.

  9. #9
    zool is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Location
    Miri
    Posts
    21
    Thank you, i didn't know that type of function existed inside Access.

    Is there any place to learn such function? I think i need to study it furthermore. Maybe i can even improve my db furthermore by using function.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,596
    DLookup (also DMax, DMin, DAvg, etc) is a domain aggregate function. Do search in Access Help or Google for those keywords.
    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.

  11. #11
    zool is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Location
    Miri
    Posts
    21
    Noted. I'll do that. Thanks a lot.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Unable to edit Data in Query
    By undee69 in forum Forms
    Replies: 4
    Last Post: 06-10-2012, 10:43 PM
  2. Edit Data and Recalculate WITHIN THE FORM ONLY
    By AusStiffler in forum Forms
    Replies: 3
    Last Post: 10-19-2010, 04:32 AM
  3. How to edit data in a form
    By x3ldonx in forum Forms
    Replies: 2
    Last Post: 10-11-2010, 01:20 AM
  4. UnMatched Data Query (Edit Data)
    By pedraza4 in forum Queries
    Replies: 3
    Last Post: 06-16-2010, 07:29 AM
  5. Most reliable way to edit form data in VBA...?
    By samalter in forum Programming
    Replies: 0
    Last Post: 06-23-2006, 12:39 PM

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
  •  
Other Forums: Microsoft Office Forums