none
MS Access - Producing a report from combo boxes and populated text boxes RRS feed

  • Question

  • Hi,

    I have a form that has various text boxes that are populated from a query as the form loads which asks for certain values.

    Then on the form are a number of combo boxes, check boxes and text boxes(these include prices) which are all chosen by the user.

    I need a button that produces a report which can display all this data? When i have tried it often runs the query again.

    Can anyone help?

    Thanks,

    Joe.

    Thursday, November 3, 2016 10:47 AM

All replies

  • Hi Joe,

    A report requires its own record source. Even if it's the same as the record source of a form. So, when you open the form and the report, they both have to retrieve their sets of records separately. If the report has the same record source as your form and you want to apply the criteria on the form to the report when you open it from the form, then you can apply the "Filter" property of the form to the report using the WhereCondition argument of the OpenReport method. For example:

    DoCmd.OpenReport "ReportName", acViewPreview, , Me.Filter

    Hope it helps...


    • Edited by .theDBguy Thursday, November 3, 2016 1:15 PM
    Thursday, November 3, 2016 1:10 PM
  • Hello, Thanks for your help!

    Doing the above did enable me to have updated values in the report, but...

    Next to each combo box is the price of the item, which its data is simply cboproduct, or the combo it is with.

    So the combo box shows a colume count of 3, with widths 0,1,1 so only the second two are shown (product and price), so when selected the price then populates the text next to it.

    On the report it will only show this price as its basically showing =cboproduct as well, is there a way of having the actual combo box value shown or is it always the first columns value that has to be shown?

    I can get the price to show on report fine, but the product name i am having trouble with - if i set it as the record source it just displays the first record of product so this isn't linked to the combo at all.

    Thanks!

    Joe

    Thursday, November 3, 2016 2:41 PM
  • If it makes it simpler for you, basically on the report when i select what value and field etc for it to display from the form it selects it all correctly but it shows the ID number (Or the first column) of the field.

    Thanks,

    Joe

    Thursday, November 3, 2016 3:05 PM
  • Hi Joe,

    I think you have two options:

    1. Use a query to join the lookup table in your report, so you can include the item name from the lookup table rather than just the ID from the main table. Or,

    2. Also use a Combobox on your report to display the column you want to show on the report.

    Hope it helps...

    Thursday, November 3, 2016 6:18 PM
  • I need a button that produces a report which can display all this data? When i have tried it often runs the query again.
    It sounds like you are using simple parameters in the query.  Instead, reference controls in an unbound dialogue form which remains open after opening the bound form.  Rather than opening the bound form directly you'd then open it via a button in the dialogue form.  If necessary the dialogue form can be hidden by setting its Visible property to False in the button's code.

    When you open the report based on the same query the controls in the (possibly hidden) dialogue form will still be exposed to the query as parameters.  If the other controls in which the user enters data are unbound these can be referenced in the ControlSource properties of unbound controls in the report provided the bound form remains open.  If you wish to close the bound form when opening the report the values of these controls can be passed to the report as its OpenArgs property, and assigned to unbound controls in the report in the report's Open event procedure.  For an illustration of how to pass multiple named arguments via the OpenArgs mechanism see Args.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file values are passed to a form, but passing them to a report is exactly the same.


    Ken Sheridan, Stafford, England

    Thursday, November 3, 2016 11:58 PM