Results 1 to 13 of 13
  1. #1
    tonygg is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    115

    Updating fields selected from a nested query

    Hi. I wanted to create a tabular form to allow a user to update a field (colour) in a 3rd party DB I use. In order to find the correct rows and get all the info I need to show the user I need a nested query:



    Code:
    SELECT DISTINCT P4.[Product reference], P4.[Short description], P4.[nParentSectionID], P4.[Price], TONYProductHistoryTable.[Product Reference Num], P4.sValue, P4.[Image FileName], "C:\Users\tonyg\Documents\SellerDeck 2013\Sites\Site1\" & P4.[Image FileName] As ImagePathFROM (SELECT Distinct Q4.SContentID, Q4.sValue, Product.[Product Reference], Product.[Short description], Product.[nParentSectionID], Product.[Price], Product.[Image FileName]
    FROM  Product INNER JOIN
    (SELECT UserDefinedProperties.SContentID, UserDefinedProperties.sValue FROM UserDefinedProperties LEFT JOIN Variable on Variable.nID = UserDefinedProperties.nVariableID Where Variable.sName = 'Colour' and  UserDefinedProperties.SContentID Not Like '*!*') As Q4 
    on Product.[Product Reference] = Q4.SContentID
    where ((Product.[Product reference]) Not Like '*!*')  AND Not IsNull(Product.[Short description]) AND (Product.[bSuppressHtml] <> -1))  AS P4 LEFT JOIN TONYProductHistoryTable ON (val(P4.[Product reference]) = TONYProductHistoryTable.[Product Reference Num])
    WHERE (TONYProductHistoryTable.[ProductCategoriesChecked] = 0);
    This works fine and brings me back all the info I need into the form using the query.

    My issue is I want the user to be able to look through each row (there are hundreds) and select a colour for each product. I cant see a simple way to write back the changed values into the field "UserDefinedProperties.sValue".

    If it was not a nested query then I guess updating the field in the form would also update the underlying field in the table. I need to simulate this process or have a big save button on the form that can take write back the data. Either way is ok but I am struggling to know the easiest/most efficient way to do it?

    Any ideas would be appreciated?

    many thanks

    Tony

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    The form would allow users to alter the record,
    then you'd run an update query using the key as criteria to update all other fields.

  3. #3
    tonygg is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    115
    Hi ranman

    Thank you. I am not sure how to do this?

    I have built a tabular form from the query using form builder. It shows all the rows found and displays the data correctly.

    I think I have 2 issues (1 small and one big):

    SMALL: The field I want to update appears to be no-editable in the form (maybe just a configuration)

    BIG: I don't see how to write a query based on the contents of all rows in the form. I can certainly retrieve the primary key of the table I need to update. I just cant really see how to use the data in the form to run a multi row update which is what I think you are proposing.

    Any examples or skeletons would be very helpful.

    Many thanks

    Tony

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    Because you cannot edit the form, you would use a form with an empty table to alter the rec.

    select a record from the query,
    it loads into an empty table,append qry
    alter the fields,
    user clicks Save btn,
    a query runs an update query using the key as criteria to change all fields to the ones on the edit form.

  5. #5
    tonygg is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    115
    thank you again. Sorry I am being a bit thick....

    If I have understood correctly then I need to create a temporary table and load the data into that table. Change the contents into that table. Once I am ready to save have a button that when I press it loads the temp table contents into a DAO.Recordset. Then loop around it and update each record in the original table.

    Have I got that correct?

    Many thanks

    tony

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If there are hundreds of records, you want this to be as quick and simple as possible for the user. For instance, display the records in an listbox rather than a tabular form. Your query would then be the row source for the listbox. The form will be bound (record source) to the one table that contains the value you want to update. The user will click on a row in the listbox, in the OnClick event filter the form based on the PK of the table - make the PK the first field in the query, then
    Me.Filter="pK_field_name=" & me!listbox_name
    Me.FilterOn=True
    Me.Requery

    This will set up the form with the actual record in the table which you want to update.

    Then the user will select the color which is bound (control source) to the table. That is all. The record will be saved automatically as soon as the user either clicks another row in the listbox or closes the form/database.

    This can be done by using a form or subform to display the data but gets a little more complicated - altho it looks nicer!

  7. #7
    tonygg is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    115
    Hi aytee,

    Thank you. I am not sure I am following you regarding the update. my source query is nested so I dont think you can update the source values. Are you suggesting that I have an additional query to the source data as a single column list box that can allow me to update the table directly? If so how do I synchtronise the rows from the source query and the list box? Sorry if I got the wrong end of the stick.

    many thanks

    tony

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What I am saying is to separate out your process into (1) display and (2) update, don't try and do them both together. Have all the data displayed in one place - either a listbox or a subform - and the data to be updated in another place, the main form. Two totally different sources, handled in different manners.

    Try it out and see if it is giving you what you want: create a form, bind it to the table where the color is stored. Add a listbox and take the query from above and make it a listbox. Add the code to filter and see if the updating works.

  9. #9
    tonygg is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    115
    I now see. I will have a go. Unfortunately the key field I need from the nested query is used to display an image (.jpg file) that allows the user to see what colour should be added. Not sure i can do that in the listbox.

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Create an unbound field on the form for the image. When the user clicks on an item in the listbox display it.
    In the AfterUpdate, Me!image_box=Me!List_box.Column(x)

  11. #11
    tonygg is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    115
    Got it. Thank you.

    The field the user will update is the fabric Colour. I need them to choose from a list. I was wondering if it possible to arrange the query on that field that I need to update in such a way that a user can select the colour from the list and that selection directly updates the field (which is a text field in the DB). I could get away with using a hard coded list of choices but the actual colour choices are stored in another table.

    Many thanks

    Tony

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Make the colour a combobox with its row source getting the values from the colour table. Bind the field to the table being updated (control source). That way the user is presented with the list from where those values are stored and on selection will be storing that value on the form's table. Usually in this scenario the colour being updated will be the primary key from the colour table, not the colour description - which could change over time.

  13. #13
    tonygg is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    115
    Thank you.

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

Similar Threads

  1. Query to extract selected fields from table
    By Traceyann1964 in forum Queries
    Replies: 2
    Last Post: 08-30-2016, 07:12 AM
  2. Replies: 11
    Last Post: 01-21-2014, 10:51 AM
  3. Replies: 11
    Last Post: 04-22-2013, 04:21 PM
  4. Replies: 6
    Last Post: 05-10-2012, 08:20 PM
  5. Replies: 1
    Last Post: 08-17-2010, 02:33 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