Function on Subreprt Error

[GPnew]

In Access 2002 i have a function on a subreport the is returning #error. It works fine on the main reprt
Its control source is
=UseDefaultCodes([ProductCode],"CARTSTAFF")

The [ProductCode] is the link to the master form
The code is
Public Function UseDefaultCodes(PCode As String, ExpCode As String)
UseDefaultCodes = DLookup("[DeFaultAmt]", "Costcodes", "([Costcodes].[COSTCODE]) = '" & ExpCode & "'")
End If
End Function

Any Ideas

 

[Remou]

Do you meant that ProductCode is a field / control on the main report? If so, you can try:

Parent.ProductCode

Or

Reports![Main Report Name].ProductCode

 

[dhookom]

I am confused since this function will always return the same value regardless of ProductCode. I am also confused because you mention "subreport" and "link to the master form".

Is the function in a standard module?

It looks like you could just set the control source to:
= DLookup("DeFaultAmt", "Costcodes", "COSTCODE = 'CARTSTAFF'")


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GPnew]

Sorry ,

I abbreviated the function code to keep it simple and copied the wrong part of the if statment.
The corrct on is.
PRODUCTCODE is the link child field and link master field of the sub report

UseDefaultCodes = DLookup("[Quantity]", "ProdCostAllocation", "([ProdCostAllocation].[PRODCODE]) = '" & PCode & "' AND ([ProdCostAllocation].[DetailedExpense]) = '" & ExpCode & "'")

 

[dhookom]

[red]Is the function in a standard module?[/red]


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GPnew]

Sorry, Yes it is in a standard module

 

[dhookom]

Are any of the arguments ever Null? Have you tried just using the Dlookup() rather than wrapping it in a function call?

Have you tried adding ProdCostAllocation to your report's record source so the Quantity field is available?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GPnew]

HI,

Sorry its been such a long time getting back to you.
When I used [ProductCode] in the function ie UseDefaultCodes([ProductCode],"CARTSTAFF") the function returned an #error (This only happened when the function was on the sub report)

I got the function to work by creating a textbox (text135) on subreport with a control source PRODUCTCODE

IN the function I used UseDefaultCodes([Text135],"CARTSTAFF") and it worked.

Obviously I had something wrong in the syntax. Not that it matters any more ...but out of curiosity ...can anyone tell what should the syntax be ...I tried "PRODUCTCODE" and [PRODUCTCODE] in the function ie UseDefaultCodes("PRODUCTCODE","CARTSTAFF") and UseDefaultCodes([PRODUCTCODE],"CARTSTAFF") as well ... but neither worked

Thanks

 

[dhookom]

This would make sense to me if the function was used in code rather than a control source. I would troubleshoot with the Debug window or setting a break point in the code.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]