Interactive MS-Access Reports

Question

Is it possible to create a report that initially loads with data from tables, and then contains interactive text boxes? For examples, say I load a form that contains a payment calculator. All the initial fields are populated when the form is displayed (based on a credit app number, primary key). At that point I would like to be able to change the interest rate while viewing the report and the remainder of the fields dynamically change based on my input. However, I don't want the adjusted interest rate to be saved back to the table - thus the reason I don't use a form.

Thanks!

Answer 1

I'm going to jump onto Marshall discussion, instead of continuing 2 different conversations about the same thing and this way Marshall can add to my thoughts... (or give you an alternative approach)

To load your initial values you could simply use the DLookUp function for your various controls or if they are static value, simply enter the value as the control's default value.

Daniel Pineault

http://www.cardaconsultants.com

Answer 2

The form will need to be bound because you want it to display real data.  But, you can also use unbound text boxes for other purposes.

A form is much simpler than a report for what you are trying to do.  Just make the bound rate text box invisible and use a little VBA code in the form's CLoad or Current event procedure to set an unbound text box (named txtTheRate) value depending on whether you have entered a value in another unbound text box (named txtTestRate):

   If IsNull(Me.txtTestRate) Then

      Me.txtTheRate = Me.ratefield

   Else

      Me.txtTheRate = Me.txtTestRate

   End If

Put the same code in the txtTestRate text box's AfterUpdate event too.

Use txtTheRate everywhere in you calculations inplace of where you are now using the rate field.

Now. when you enter a value in txtTestRate it will automatically recalculate  the text box expressions you are using to display the other values.

Answer 3

I think A2010 has a featue for that kind of thing and I don't know/use A2007.

In any version you can use a form with a text box to enter the value you want and set the report's Filter or OrderBy property to anything and the report will recalculate.  The report can the use the value from the form conditionally based on the filter or OrderBy property.  To tell if the report was being restarted from the form, I use a button on the form to set the property.  E.g.

   If Reports!![the report].Filter = "" Then

      Reports![the report].Filter = "True"     'has no effect

      Reports![the report].OrderBYOn = True   Else

   Else

      Reports![the report].Filter = Reports![the report].Filter & ",True"

   End If

Then in the report header section's Format event procrdure, you can do things like:

    If Right(Me.Filter, 4) = "True" Then

        Me.txtParm = Forms!Form1.Text0

    Else

        Me.txtParm = Me.InterestRate

    End If

Make the InterestRate text box invisible and use the txtParm text box to display the rate and in the calculations.

All in all, I think this kind of devious stuff is rarely worth the effort and if you can devise a form that will present the data in an acceptable way, it's a better way to go.  But if you have a real need to use a report and not close and reopen it (with the changed interest rate passed through OpenArgs), then this kind of trick can be done.  It does seem to be a little faster but that may be just because you don't have to click on the report's close button.

Answer 4

A thought I had was to use an unbound form with query criteria textboxes, query run button that runs macro to Set Value of the required textboxes of the form.

Another button to refresh calculations and third to print or save data somewhere.

---

Build a little, test a little.

Answer 5

Two ideas come to mind for such a case:

1. A configuration form, that ajusts/updates the report variables.
2. Instead of using a Report simulate it using an interactive form and add a print button to print out a report based on the selection(s) made by the user.

I hope this helps,

Daniel Pineault

http://www.cardaconsultants.com