Open report based on current record on form..... issues.....

[bazzanoid]

Hiya,

I'm scratching my head on this one. :banghead:

I have a button on my "Customers" Form to open the "CustomerReport" Report. I've put the standard DoCmd with a where condition in the OnClick of the button on the form.

DoCmd.OpenReport "CustomerReport", acViewPreview, , "Cust_ID = " & Me.Cust_ID

If i'm on record number one, the report opens fine. If i'm on any other record, the report opens with only the labels in place, and no actual data is displayed. I don't get it, help!

The Cust_ID field is an autonumber if that helps, i suspect that is causing the issue but can't for the life of me remember how to get access to handle it!

TIA

 

[arnelgp]

clue, is Cust_ID the name of the textbox control?

 

[bazzanoid]

Yes, as is the underlying table field

 

[arnelgp]

is this on the main form or on subform?

 

[bazzanoid]

Main form. There is a subform but that behaves itself....

But you just got me thinking. The subform doesn't always have data in it. I just created a test record with data on the subform and that produces a populated report, whereas anything with no data produces a blank one. A record is only created on the subform table if it is applicable. The Customer ID field is under a different name on the subform so it won't be that, but I am now wondering if I have my SELECT statement the wrong way round for the subform on the report and that's causing the issue...

*snip*
CustomerConvictions.ID, CustomerConvictions.CustomerID, CustomerConvictions.ConvictionCode, 
CustomerConvictions.ConvictionOffenceDate, CustomerConvictions.ConvictionDate, Convictions.Description 
FROM Convictions 
RIGHT JOIN (Customers RIGHT JOIN CustomerConvictions ON Customers.[Cust_ID] = CustomerConvictions.[CustomerID]) 
ON Convictions.ConvCode = CustomerConvictions.ConvictionCode;

 

[arnelgp]

i think you can resolve it now.

 

[bazzanoid]

Fixed completely by adding in a linked subreport instead, i was running in circles!

Now I have a pop up box asking for CustomerID every time the report opens and it makes no difference if i leave it blank or fill it. So somewhere I have a redundant mention of it but do you think i can find it? Nope