Reports and Single Records

[darthcalis]

Hi all,

I have some ongoing problems with getting a report to 'work' with a single record. So far I have 3 issues; displaying a single record, printing a single record, and outputing a single record to a PDF file.

The report itself is a report of customer and supplier contracts based on data in multiple tables, and I only want to print/view/output a single contract at a time. I have a query which pulls all the required data into one place. The report launches from a command button I have in a form which is displaying the record I currently want to work with. The name of the form is stored in a constant called 'const_rptContractReport', and the ID of the record I want is stored in a field called '[CONTRACT_ID]'.

Regarding viewing, I initially tried this:

DoCmd.OpenReport const_rptContractReport, acViewPreview, , "[CONTRACT_ID] = " & Me!CONTRACT_ID.value

It's my understanding that this should work, but it was inconsistent. Sometimes it would open the correct record, but sometimes it would open the first record in the query. There seemed to be no logical reason for it. I tried printing out the contract ID and indeed it was the case that sometimes the ID was correct, and sometimes it wasn't, even though the same record was always being displayed. Not getting very far, I switched to this:

DoCmd.OpenReport const_rptContractReport, acViewPreview, , "[CONTRACT_ID] = " & Forms(const_frmContractBuilderForm)!CONTRACT_ID.value

This seems to work consistently (at least for now!), so not really understanding what was going on there. Even weirder, if I want to print the record I use this:

DoCmd.OpenReport const_rptContractReport, acViewNormal, , "[CONTRACT_ID] = " & Forms(const_frmContractBuilderForm)!CONTRACT_ID.value

This prints the record, but all my unbound fields turn up empty when printed! I think I may be missing something obvious with the printing due to my lack of experience with 2010, as it is just the unbound fields that don't print.

Finally, is there anyway to output a single record to a PDF? I've checked out the OutputTo method, but as far as I can work out this dumps the entire report. It would be so convenient if this could be limited to a single record. Is there any way to do this? I'm thinking that I may have to create a query that will pull out only the single record that I want and use that as the report source. Would this be the best way, or is there a simple command that I'm missing.

Sorry for the long winded post, especially as it's my first technical post on this forum. Any help would be much appreciated.

 

[pbaldy]

I often use this method when I might want to export the report:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

When it could go either way, I might have code like:

  If Not IsNull(Forms!frmMainMenu.txtCustomerCode) Then
    Me.Filter = "customer_code = '" & Forms!frmMainMenu.txtCustomerCode & "'"
    Me.FilterOn = True
  Else
    Me.FilterOn = False
  End If

I can't think of why the unbound fields (controls?) are only empty when printing. What is an example of their control source?

 

[darthcalis]

Paul, you're a lifesaver! Thanks very much, you managed to solve most of my problems in one hit.

Oddly, I did try and use the report filter method first, but I put the code in the report's OnLoad event rather than OnOpen which I guess it where I went wrong. Anyway, I always only get a single record now regardless of viewing, printing, or outputing a PDF or where I launch from, and the code is consolidated in just the report event so that's excellent.

I still have the one problem remaining with the unbound fields. Yes, you're quite right they are controls, text boxes to be exact. The reason I'm using them is that if some of the bound text boxes have nothing to display, I turn off their visible property so that they don't take up space on the form, which is what my client currently does manually (delete the related contract text that is). This also makes the associated label vanish, which is fine, but they still take up a line of space on the report which then looks like a sort of phantom gap. So I deleted the labels, use the unbound text boxes as labels with their CanShrink property set to Yes, that way I reclaim back the entire line and the report looks normal. I am hiding the unneeded bound controls and setting the values for the unbound text boxes in the report's OnLoad event using the following type of code:

'price control...
If Nz(Me.PRICE_DESC_FINAL) = "" Then
    Me.PRICE_DESC_FINAL.Visible = False
    Me.lblPrice.Visible = False
Else
    Me.lblPrice = "Price:"
End If

Works perfectly when viewing, but as I say, none of the bound controls show up in either the print version, or the PDF output.

Am I missing something obvious?

Emilio

 

[pbaldy]

Presuming that value could vary by record, that type of code would typically be in the detail section's format event. The load event only runs in Report view, IIRC.

 

[darthcalis]

Yes, those values can vary by record, and that fixed my problem. I've been working solidly for the past 3 months with tables and forms and I only looked at reports for the first time a couple of days ago. I was thinking of events working in almost the exact same way as they do in forms, but I clearly need to read up a bit more on report events as that's where I've been going wrong. My code's been right, but in the wrong place.

All issues resolved now, thanks Paul.

Emilio

 

[pbaldy]

Happy to help Emilio! The new Report view introduced with 2007 muddied the water, as some report events fire in the other views but not that one, and vice versa. It is confusing.