pass parameter from form field to sql function

[Guest]

I have an access database that has recently been upsized to sql 2005 - adp
front end and sql 2005 backend. I have several reports that are set up as
hyperlinks that require a field value as a parameter. When I click on the
hyperlink i get the "Enter Parameter Value' dialogue box. If i manually enter
the info the report pulls. I changed the hyperlink (as i couldn't figure out
how to pass the parameter) to a button and set the onclick event procedure as
follows:

Private Sub PrintCurrRecord_Click()
On Error GoTo Err_PrintCurrRecord_Click

Dim stDocName As String

stDocName = "MTLCurrRecord"
DoCmd.OpenReport stDocName, acPreview, , , , "[Material.mtlid]='" &
Me.ID & "'"

Exit_PrintCurrRecord_Click:
Exit Sub

Err_PrintCurrRecord_Click:
MsgBox Err.Description
Resume Exit_PrintCurrRecord_Click

End Sub

The field [ID] value is alphanumeric eg. 'A002'.

The access report is based on the following function:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[MtlCurrentRecord] (@forms___material___mtlid nvarchar
(6)
)
RETURNS TABLE
AS RETURN (SELECT Material.MtlId, Material.MTLDesc, Material.CatID,
Material.OrderID, Material.State, Material.Density, Material.SKUCost,
Material.PurchUnit, Material.SKUUnit, Material.OnHand, Material.MinStock,
Material.Allocated, Material.InProd, Material.Location, Material.NonStock,
Material.Premix, Material.LastCount, Material.Safety, Material.WhmisID,
Material.SupplierID
FROM Material
WHERE (((Material.MtlId)=@forms___material___mtlid)))

When I click the button I still get the "Enter Parameter Value" dialogue box
with a drop down following 'forms___material___mtlid???


Thank you

 

[Guest]

I have also tried the same thing with the "[material.mtlid] = request in the
WHERE section of the command with no luck????