Wednesday, May 02, 2007

Using A Form Field In A Crosstab Query

A client asked me how to use a form field in a crosstab query. Initially I thought it would be a matter of adding the following in the criteria field of a query
[Forms]![_MyApplication]![cboProduct]

Unfortunately this brings up the error Microsoft Office Access does not recognise FieldName as a valid field name or expression. Much hunting around and then I remembered that you can use a Function as criteria for a query. So I wrote the following Function

Public Function CrossTabParam() As String On Error GoTo error_exit
CrossTabParam = [Forms]![_MyApplication]![cboProduct]
Exit Function
error_exit:
MsgBox "Error in function { No. " & Err.Number & "} " & Err.description
Exit Function
End Function


Now i changed the criteria to be and it works... Here is the working crosstab

WHERE zSales_Demo.ProductName =CrossTabParam()


The example is taken from a table in my popular graphical program graf-fx.
http://www.vb123.com.au/explore

2 comments:

Alex Dybenko said...

Hi Garry,
you can also create a parameter for query with same name - [Forms]![_MyApplication]![cboProduct], and it will work.

SusieFresh said...

Hi,
You can reference the forms just as you had originally planned but you need to hard code all of the possible column names in the properties (in query design right click in table area then look for the property called column headings and specify your columns). I had used your function method but after reading this post I went back and changed it.http://msdn.microsoft.com/en-us/library/office/aa191550(v=office.11).aspx