Module Error

Fastone

Registered User.
Local time
Today, 16:39
Joined
Nov 21, 2002
Messages
56
I have a problem with a function. I have entered it into the VBA windows and have done a debugging so the function should be fine. If I now want to use it in a query I get the "undefined function" error.

In the query I type:
Commission: CalCom([Products Complete].[Price Excl VAT])

And the function is:
Function CalCom(priceEV As Double)
Dim inCom As Double
inCom = InputBox("Enter Commission > percentage in decimals (20% = 0,20)", Commission)
CalCom = inCom * priceEV
'Debug.print "Commission: " & inCom & " Price: " & priceEV & " Calculated: " & CalCom
End Function

The strange thing is that even if I use the expression builder, it gives me this error. Any ideas?
 
The declaration of your function hasn't been assigned a variable. You must declare it differently than a Sub since a function returns a value.

So, you'd probably set your function declaration like this:

Function CalCom(priceEV As Double) As Double

Regards,
John
 
Thank you for the tip but unfortunately, this didn't make a difference in the result.
I read somewhere that the module cannot have the same name as the function, so I changed the name and now I get a different error:
The name is not unequivocal, in query expression CalCom([Products Complete].[Price Excl VAT]).

(this might not be the exact error you will get in the English version, I translated from Dutch).

This pops up when I added the As Double and changed the name and without the As Double added but with a changed name.
 
Have you tried removing the As Doubls and hav it undeclared ie:
Function CalCom(priceEV)

If you are worried about it being a number then force later on in the code to make sure that it is a number.

HTH
 
I have tried this, but no change. This is starting to become quite frustating. But please, let the ideas come because I'm sure the correct way of doing this is out there somewhere (I just haven't found it yet;) )
 
To get a better idea of where it is going wrong:

Open a new module sheet and call it TEST_FN
Type in the following:

Function RetNum(MyNum)
RetNum = MyNum
End Function

Now create a query and use data from a small table with a numeric field and create two fields

TestField: RetNum([FieldName])
and
[FieldName]

The answers ought to be the same. Let me know if it works
It
 
I don't get an error anymore. Both answer are also the same so this seems to be working.

Thanks
 
In which case use this function and slowly start to build it up to what you want.

One thing that you might want to be wary of is that I think that InputBox returns a string so you might need to convert it ie
Val(InputBox(...etc))
 
Last edited:
Thanks, I'll try although I don't really know VB but I'll figure it out as I go along.

That box will go away anyways with the iif function.
 
Last edited:
Try declaring the function as public.

Public Function CalCom(priceEV As Double)
....

The function needs to be in a standard module. It can't be in a form or report class module.
 
I have tried that before, but this didn't make a difference. I am also sure that it is in a standard module and not in any other form.

Any other suggestions would be very welcome.

Thanks
 
F,

Change
Code:
inCom = InputBox("Enter Commission > percentage in decimals (20% = 0,20)", Commission)
to
Code:
inCom = InputBox("Enter Commission > percentage in decimals (20% = 0,20)", "Commission")
and hopefully you'll be a step closer to a fix.

Regards,
Tim
 
Thanks but between the time that I posted the code and now, I had already changed this. Any other suggestions maybe?

Thanks
 
Some thoughts
Function CalCom(priceEV As Currency) As Currency
On Error Resume Next
Dim inCom
inCom = InputBox("Enter Commission > percentage in decimals (20% = 0,20)", "Commission")
CalCom = inCom * Nz(priceEV, 0)
End Function
works to a certain degree, you mention that you're using this in a query,
a, you'll be prompted for an input for every record in the query,
b, your query may return null values which was the reason for adding the Nz function
Do you wish to apply a single commission value to all the records returned by the query?
 
I'll try that.

The inputbox is just a temporary solution and this will be changed into a quite large iif function (see my topic in general).

Thanks
 
I have tried what you gave me and now I get the error that the name is not unequivocal (see earlier post). I have created this in a new module with a different name and I removed the original module but nothing changed.

Thanks anyway
 
Have you tried the function in a query which just has the table Products Complete in the window, in which case the expr becomes CalcCom([Price excl Vat]), I can't find an error containing unequivocal, is there some other literal translation?
 
another translation is unabiguous. I think it would mean something like that it is not the only value or table that contains the function (something like that).

I tried this and it gives the same error. undefined function.

Thanks,

P.S.: I assumed you meant CalCom and not CalcCom
 
Try removing the InputBox and replace it with a value
ie. inCom = .5
 

Users who are viewing this thread

Back
Top Bottom