DoCmd.OpenQuery...Property not found

[Gavx]

I have a button on a form which on clicking runs this code.
Everything runs fine except the line;

DoCmd.OpenQuery "qryProductByType".
In this query is the criteria GetProdType().

The error message is Run time error 3270 Property not found. Is the problem with the Function GetProdType() procedure?

thanks

Option Compare Database
Option Explicit
Public gIntProdType As Integer

Private Sub cmdInboundTransport_Click()
Dim iProductType As Integer


On Error GoTo cmdInboundTransport_Err

Dim intProdType As Integer

intProdType = 1

 SetProdType (intProdType)
DoCmd.OpenQuery "qryProductByType"

cmdInboundTransport_Exit:
   Exit Sub

cmdInboundTransport_Err:
    MsgBox Error$
    Resume cmdInboundTransport_Exit
    
End Sub

Sub SetProdType(intProdType As Integer)
' Set the public variable to be the value passed in.

gIntProdType = intProdType
End Sub

Function GetProdType()
   ' Return the value of the module variable.
   GetProdType() = gIntProdType

End Function

 

[JHB]

To use a UDF function in a query, it has to be in a Module and not like here in the form.

 

[Gavx]

So
Insert->Module
and then insert code;

Option Compare Database
Option Explicit


Function GetProdType()
Dim gIntProdType As Integer
' Return the value of the module variable.
GetProdType() = gIntProdType

End Function

But this doesn't work.
Bit of a beginner, can someone give me some direction please.

 

[CJ_London]

number of issues

you need to declare as a public function and ideally the type

Public Function GetProdType() AS Integer

then you are assigning it the value of gIntProdType which you have declared locally within the function and not assigned a value - so it won't return anything

 

[Gavx]

Okay.
So I have;

Public Function GetProdType() As Integer
Dim gIntProdType As Integer
GetProdType() = gIntProdType

End Function

But then I get a compile error; Function call on left side of assignment must return Variant or Object.

 

[CJ_London]

GetProdType = gIntProdType (no brackets)

 

[Gavx]

Thanks, everything compiles.
The value of of gIntProdType is successfully stored throughout the form code but when the query is called this value must be lost because the query returns an empty value ie the value of gIntProdType =0.

Following the code, the value of gIntProdType is lost the moment the first declaration of the UDF runs ie Public Function GetProdType() As Integer

 

[CJ_London]

reread my post #4

 

[Gavx]

then you are assigning it the value of gIntProdType which you have declared locally within the function and not assigned a value - so it won't return anything

Right, so this is the problem, or at least one of the issues you alluded to.

So therefore the value of gIntProdType will have to be stored publicly.

 

[CJ_London]

correct - the function as you've posted it (but without the dim statement) is the standard way to get public vba variables 'used' in a query

 

[Gavx]

I don't understand. At teh head of the code is the statement Public gIntProdType As Integer - therefore its value has been declared publicly.

Why is it not available to the UDF?

 

[CJ_London]

perhaps you had better post the code you are actually using - the code posted so far says

Option Compare Database
Option Explicit


Function GetProdType()
[COLOR=red]Dim gIntProdType As Integer
[/COLOR]' Return the value of the module variable.
GetProdType() = gIntProdType

End Function

And where do you actually assign a value to this variable?

 

[spikepl]

Because your local declaration in the function overrides the public one in the module and reinitializes the variable. just delete it

 

[Gavx]

Thanks spikepl but if I delete that declaration I get an error saying gIntProdType hasn't been defined.

 

[Gavx]

So here is the code;

Option Compare Database
Option Explicit

Public gIntProdType As Integer

Private Sub cmdInboundTransport_Click()

	Dim intProdType As Integer

		intProdType = 1

	 		SetProdType (intProdType)
			DoCmd.OpenQuery "qryProductByType"
 
End Sub



Sub SetProdType(intProdType As Integer)

	gIntProdType = intProdType
End Sub

And then in a separate module I have this code;

Public Function GetProdType() As Integer

	GetProdType = gIntProdType

End Function

And the problem is the value of gIntProdType is not successfully passed from the private module to the public function - despite being publicly declared Public. Also now I am getting the response that gIntProdType is not defined.

 

[jdraw]

There is info here that may shed some light.

 

[CJ_London]

Well, perhaps if you put the public declaration in the correct module, it would make a difference