Access - Promting user to enter information 2

[GoldenHaddock]

I have created a small system that imports data from an SQL query into access, and then displays it in a report.

Access doesn't know what this information is or what dates it relates to or which department.

What I would like to do is promt the user to enter a "From Date", "To Date" and "Department", for titling purposes on the report so that the boss knows what the data relates to!

Seems a simple enough task, but I can't seem to figure it out!

Funny really as i've managed to do some quite nifty stuff with the report and cant seem to work out this

Any help gladly appreciated.

Thanks!

 

[lupins46]

This will illustrate the principle:

Option Compare Database
Option Explicit
Dim x
Private Sub Report_Open(Cancel As Integer)
x = InputBox("enter description")
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Me!mydesc = x
End Sub

 

[GoldenHaddock]

Thanks for that, however unfortunatley i'm not that familiar with using VB in Access...how would I insert that into the report?

Is there a "point and click" method?

 

[xlbo]

Easiest way I cn think of would be to bring that data back in the query and then reference it via a bound textbox / label

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Hi GoldenHaddock,

It would be far better and more reliable to extract this from the Query but if you really can't ...

In Report Design mode, right click the little square at the top left just under the title bar and select Properties. In the properties box click on the Event tab. Click in the box beside "On Open" then click on the ellipsis button that appears at the right of it. In the dialog which pops up, select Code Builder and press OK. You will be thrown into the VBE positioned ready to write code. You can then do something along the lines suggested by lupins46.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[lupins46]

To run my example you need to add a textbox to the report header called mydesc.
Then add the code to the report's code module.
With the report in deign view choose View >Code from the menu. Just paste in what I have posted.
If either of the top 2 lines are duplicated delete the lower one.

 

[GoldenHaddock]

Thanks for the help, much appreciated, it's working now