Retrieving Data

[Otacustes]

Hi,

I am hoping that someone may be able to advise me on the best way to manipulate reports using VBA??? What I have currently is a report and in it's record source there is a query. When I run the report the query builds the information and then I can see the information in the report. What I want to do is to be able to extract, in vba, one of the fields from the query. I assume that because there is no direct access to the query (as it is run when the report is opened) that cannot do it by modifying what I have??

At the end of the day, I want to be able to click a button, type in a number (which forms the where clause in the query) and a report is generated. The report is then to be emailed but some of the data, from the query which builds the report, is needed to be added to the subject line of the email hence the access to certain fields from the query.

Hopefully this all makes sense?? If anyone can shed any light on how to modify what I have or can suggest another way of achieving this I would be very grateful.

Many thanks

Ota


All help is greatly appreciated
Kind regards
Ota

 

[sozzer]

On click of a button:

Text0 is the control on the form that holds the number of the field in the record I want to filter by
Reports!ReportName.Filter = "FieldName = " & Me.Text0.Value
Reports!ReportName.FilterOn = True

To put the value of a control on the report into text2 on the form:
Me.Text2.Value = Reports.ReportName!ControlName.Value

 

[Remou]

Please excuse the over-explanation. I think from the above, that the report record source looks something like:
[tt]SELECT Field1, Field2 FROM tblTable WHERE Something = SomethingElse[/tt]
If this is the case, you can modify the query by clicking on the three dots to the right of Record Source on the property sheet. This will bring up the query design screen and from there, you can save the Record Source as a 'regular' query, or add to the criteria. If you add a prompt in square brackets (parameter query), a box will pop up asking for data. This will allow you to fill in the number you want.
However, as you wish to email this report, it may be best to add a reference to a control on a form to your criteria (eg Forms!frmForm!txtTextBox). This will allow you to fill the number you require into the form field. You can then use SendObject to email your report.
You should be able to use record source for the report to obtain the data you need for your email (DlookUp?).
If I have misunderstood or missed your point, it may be best to post a little more information.