none
Custom reports or queries RRS feed

  • Question

  • Hi all,

    Is there a way to invoke the report wizard with Access Runtime 2013? (I suspect not).

    If not, does somebody have some sample code on writing my own report wizard?

    The reason being that I get a lot of requests from the users for custom reports. There is a few reports available, but they still ask for other reports. What I was considering, is to give them the option of a custom report / query by selecting the fields they want and then run a report (or query) to suit what they want.

    All the reports comes out of 1 table.

    Thanks

    Tuesday, October 18, 2016 10:30 AM

Answers

  •        Write #1, rst!CustomReportFields(1), rst!CustomReportFields(2), rst!CustomReportFields(3)

    Hi Deon,

    I think that I know now what you mean.

    When you use  rst!CustomReportFields(1), then the program is looking for a field with exactly that name.

    But you want to have the value of CustomReportFields(1) etc., so you can try:

             Write #1, rst(CustomReportFields(1)), rst(CustomReportFields(2)), rst(CustomReportFields(3))   

    Imb.

    • Marked as answer by Deon SA Wednesday, October 19, 2016 5:26 AM
    Tuesday, October 18, 2016 2:44 PM

All replies

  • The reason being that I get a lot of requests from the users for custom reports. There is a few reports available, but they still ask for other reports. What I was considering, is to give them the option of a custom report / query by selecting the fields they want and then run a report (or query) to suit what they want.

    Hi Deon,

    You can make an input form for the users to select the fields they want to see. This information is passed to the Report, e.g. through OpenArgs.

    Inside the report you can hide the controls the users are not interested in. Eventually you can adept the Left propertie of the visible control to remove blank columns in the report.

    It is a straight forward process, but you need a little coding. I have no simple sample code available, as the code that I use is integrated in a deeper level of programming.

    Imb.

    Tuesday, October 18, 2016 11:53 AM
  • Thanks Imb.

    I have created the form and loaded the fields into a combobox. From this combo box, the user can select the fields they want to see. I load these fields into an array called "CustomReportFields()". No problem.

    I am retrieving the data from the table with a SELECT statement. No problem.

    Now I want to write this data that I retrieved, to an Excel file - here's the problem:

    My code as follows:

            While Not rst.EOF
                Write #1, rst!CustomReportFields(1), rst!CustomReportFields(2), rst!CustomReportFields(3)
                rst.MoveNext
            Wend

    The rst!CustomReportField(1,2,3...) contains the table fieldnames.

    When the "Write" executes, an error occurs with the "rst!...", saying "Item not found in this collection".

    How can I write the value of the fields to a file? Using the array names seems to generate the error.

    Thanks

    Tuesday, October 18, 2016 1:30 PM
  •         While Not rst.EOF
                Write #1, rst!CustomReportFields(1), rst!CustomReportFields(2), rst!CustomReportFields(3)
                rst.MoveNext
            Wend

    Hi Deon,

    While the fieldnames are in an array, can't you simply use:

                Write #1, CustomReportFields(1), CustomReportFields(2), CustomReportFields(3) ?

     

    Imb.

    Tuesday, October 18, 2016 1:45 PM
  • Unfortunately not. The array contains the Table field names only, not the data. I retrieve the data with the following code:

    CustomReportSQL = "Select " & CustomReportFields(1) & ", " & CustomReportFields(2) & ", " & CustomReportFields(3) & " " _
     & " FROM TripMaster;"
     Set rst = dbs.OpenRecordset(CustomReportSQL)
     rst.MoveFirst

    I now want to write the data to a text file:

    Open "c:\temp\custom.txt" For Output As #1   'Open the text file for output
    Write #1, CustomReportFields(1), CustomReportFields(2), CustomReportFields(3)   ' Write the headings
    While Not rst.EOF      'Loop through the data retrieved and write to the text file
           Write #1, rst!CustomReportFields(1), rst!CustomReportFields(2), rst!CustomReportFields(3)
           rst.MoveNext
    Wend

    Normally, I would code "Write #1, rst!TMNumber, rst!TMdate", etc. which works fine, but I do not know what fields the user has selected, that's why I put the field names into an array. The array "CustomReportFields(1)" contains the fieldname "TMNumber", but for some reason it does not write the data, but gives the error mentioned earlier.

    I hope I am making the issue clear.

    Thanks

    Tuesday, October 18, 2016 2:02 PM
  •        Write #1, rst!CustomReportFields(1), rst!CustomReportFields(2), rst!CustomReportFields(3)

    Hi Deon,

    I think that I know now what you mean.

    When you use  rst!CustomReportFields(1), then the program is looking for a field with exactly that name.

    But you want to have the value of CustomReportFields(1) etc., so you can try:

             Write #1, rst(CustomReportFields(1)), rst(CustomReportFields(2)), rst(CustomReportFields(3))   

    Imb.

    • Marked as answer by Deon SA Wednesday, October 19, 2016 5:26 AM
    Tuesday, October 18, 2016 2:44 PM
  • Thanks Imb. It works 100% and giving me the correct data.

    Much appreciated

    Deon

    Wednesday, October 19, 2016 5:26 AM