Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108

    Add data to a table by "on click" action from a report

    I would like to add data to a table by clicking on the record in a report.

    I have a report that's displaying supply, demand and projected balance. Daily I scroll through it to see what needs to be reordered.



    When I see an item that needs to be reordered, instead of writing it down on a piece of paper, I'd like to click on the line item and pass that value to a table.

    Then from the table I can build a printable report of all the items that need to be ordered. Passing just one field (item id) is fine, ideally I would want to pass multiple fields from the report.

    Is that possible?

    thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sure, you can either execute an append query that gets the value from the report (which would have to be in Report view) or use the AddNew method of a recordset.

    I'd probably have it automated, where a process examined the underlying data and created entries automatically.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Why wouldn't a form be the right tool for this job? IMHO, reports are for doing just that (reporting). Forms are for displaying data that may need to be edited, appended to or deleted from. From a report, it's a one choice deal.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Quote Originally Posted by Micron View Post
    Why wouldn't a form be the right tool for this job? IMHO, reports are for doing just that (reporting). Forms are for displaying data that may need to be edited, appended to or deleted from. From a report, it's a one choice deal.
    As of now I have that nice report that works and don't want to rework the whole thing, it was very complex to build it in the first place so that is what I need to work with but I appreciate the suggestion, I'll have to remember for future similar work that it is also an option.

  5. #5
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Quote Originally Posted by pbaldy View Post
    Sure, you can either execute an append query that gets the value from the report (which would have to be in Report view) or use the AddNew method of a recordset.

    I'd probably have it automated, where a process examined the underlying data and created entries automatically.
    Thanks for the suggestions, I'll look into that. Automation is the holly grail down the road but I need first to fix a little problem that's blocking me for that.

    The reports includes line calculations and to make it automatic I need to figure out how a subsequent line can take in account the calculation results of the previous line.

    For example if line 1 stock is zero it calculates that I need to order 10 because the MOQ / order multi is 5 and demand is 8, the balance is 2.

    If on line 2 the demand is 1, it will suggest ordering 5 because it is not seeing there would be a balance on line 1 if 10 were ordered.

    That's why it's somewhat manual still, ideally it would print a report writing that I just need to order 10 for line 1 and not print 5 for line 2 because there would be stock left after line 1 is fulfilled. Until I fix that problem I need to do it manually.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by geotrouvetout67 View Post
    Thanks for the suggestions, I'll look into that.
    No problem, post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Trying to run this command

    Code:
    Private Sub Component_ID_Click()
    
    DoCmd.OpenQuery "qry_TEST", acViewNormal, acAdd, "Component_ID = '" & Me.Component_ID & "'"
    
    
    
    
    End Sub
    I'm getting an error "wrong number of arguments or invalid property assignment".

    It's the same code I'm already using that works just fine as below, so I don't get it but I had the same issue before with "OpenQuery"

    Code:
    Private Sub Demand_Click()'Opens demand orders on click
    DoCmd.OpenReport "rpt_DemandOrdersByItem", acViewReport, , "Component_Id = '" & Me.Component_ID & "'"
    End Sub
    With the OpenReport, the report calls the data from a query and Component_ID is passed to that query as a criteria filter before opening the report, don't know why it bugs when only calling the query using the same method.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Because OpenQuery doesn't have the same arguments that OpenReport has:

    https://docs.microsoft.com/en-us/off...ocmd.openquery

    You'd put a criteria in the query then run it with OpenQuery. Alternatively you build the SQL in VBA code and execute it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    That's what I was just reading, bummer.

    I need to successively click on multiple [Component_ID] fields from the report and store them first before being able to build the new report I want so it looks like VBA code is the only option

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I meant that the query could get the ID field from the report, so each time you clicked it would run for that ID. Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    I can't share the db.

    Yes I need to pass the clicked field value from the report to the query but if OpenQuery is not taking the parameter then I need to use another method to pass that value

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It won't take the [Enter ID] parameter; it will take something like:

    Reports!ReportName.[Component_ID]
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    I'm thinking about another way.

    I created a query that does the same calculation as the report and I can select lines where my stock balance is negative, which is where I need to see if I want to order material.

    I still need to control that part manually because the calculation does not decide if we really want to order or not, there is no rule I can set in stone, I need to use my own judgement for that.

    But once I have that list of negative items either in a query result or in a table, I need to find a way to either weed out order lines I won't keep or send the lines I want to keep to another table from which I can print a report of the items to be ordered. I probably need to use a form for that with either a save or delete button for a given selected line.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You are correct about needing a form, you have pretty much zero ability to do anything from a query. Your query could be the source of a multiselect listbox, from which you can save the selected items to your table:

    http://www.baldyweb.com/MultiselectAppend.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    I think this is where I'm going more or less, it's better to select and save records needed than deleting records not needed (avoid deleting errors)

    I had a look at your sample db, could not make it work the add record button did not seem to add anything to either table.

    I was thinking about a double click action from a list box but I need to use something else than DoCmd to save the record, although it seems the commonly used method is the multiselection combo.

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

Similar Threads

  1. "Close form" action does not release table
    By TFisher in forum Programming
    Replies: 8
    Last Post: 10-23-2022, 11:54 AM
  2. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  3. How to suppress "Right-Click" action on a text box
    By GraeagleBill in forum Programming
    Replies: 9
    Last Post: 11-19-2016, 02:41 AM
  4. Fill in data into table upon "on click"
    By teekc in forum Forms
    Replies: 4
    Last Post: 04-01-2014, 08:27 PM
  5. saves data with out click "Save" button in forms
    By terrythomasvda in forum Forms
    Replies: 4
    Last Post: 01-14-2013, 01:31 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