RTE 3085 Undefined Function in expression (1 Viewer)

rockb · Jan 1, 2020

I have a button on a form that displays a report that is based on a query. That query currently has a criteria that prompts the user to enter a date. What I would like to have happen is the user be able to select a date from a list box that is on the for mentioned form. I already have that list box and code behind that list box that places the selected date string in a public variable on that form.

I followed the instructions I found at online and created a function that returns that public variable and then for the criteria for the query entered >FunctionName()

For your reference I tried to post a link to the site where I got the example but could not due to this being my first post.

When I test I get the subject error Undefined Function "GetScanDate" in expression.

I tried adding Public in front of the Function but that made no difference. The error is being generated on the click event of the button. The function is part of the menu form.

Relevant code below.

I am not married to this solution if someone can direct me to a better solution. Thank you in advance for your assistance.

List box containing scan dates.

Code:

Private Sub List22_Click()
    strScanDate = Me.List22.Column(0)
    cmdSelectedScans.Enabled = True
End Sub

Button

Code:

Private Sub cmdSelectedScans_Click()
     DoCmd.OpenReport "ScansOnDate", acViewReport
End Sub

Query Criteria

Code:

>GetScanDate()

Function

Code:

Public Function GetScanDate()
    GetScanDate = strScanDate
End Function

vba_php · Jan 1, 2020

I think you've overcomplicated things here my friend. criteria in a report can be entered in a multitude of ways. for instance, you can pop up the dialog and request a hard value entry from the user, which is caused directly by the underlying query having a criteria clause in it. another way is to use the "openargs()" section of the *docmd.openreport" method, but using that AND having a an "input criteria" section in the underlying query's sql statement might confuse the program. then of course you can also use the WHERE clause section of the *docmd.openreport" method and put in criteria that way. bottom line, I doubt you need any vba code for this. it seems like you can get it done *without* criteria in your query, but rather a simple line of code:

Code:

'behind button
docmd.openreport "reportnameHere", acViewReport, , "[criteriaFieldHere] = #" & me.list22.column(0) & "#"

make sure you check to see that my code has put the [field] argument into the correct place. it's spose to go into the WHERE clause section of the method.

rockb · Jan 1, 2020

VBA

Thank you for the quick reply.

I am all for a simple solution.

I tried

Code:

docmd.openreport "ScansOnDate, acViewReport, , "[B][Scandate][/B] = #" & me.list22.column(0) & "#"

and [ScanDate] is highlighted with error of expected end of statement.

Gasman · Jan 1, 2020

rockb said:
VBA

Thank you for the quick reply.

I am all for a simple solution.

I tried

Code:

docmd.openreport "ScansOnDate, acViewReport, , "[B][Scandate][/B] = #" & me.list22.column(0) & "#"

and [ScanDate] is highlighted with error of expected end of statement.

That will likely be as you have left off the closing double quote from the report name?

HTH

vba_php · Jan 1, 2020

rockb said:
[ScanDate] is highlighted with error of expected end of statement.

[scanDate] has to be a field in the underlying query the drives the report you're trying to open. are you sure you have a field with that name? also, are you sure that you put that small line of code in the right argument section of the docmd command? I did not check that for you.

<edit>
discard this advice. gasman has the answer.

rockb · Jan 1, 2020

Thank you.

vba_php · Jan 1, 2020

rockb said:
Although the report generates the report is blank.

then it's pretty clear that no records were found with the date that matches the date in LIST22.COLUMN(0). are you sure your pointing to the right column of the listbox? the report is blank because the query's return is 0 I would think.

CJ_London · Jan 1, 2020

you are also missing a comma - there should be three after the name of the report. At the moment you are providing a filter name parameter, not the WHERE parameter. The filter parameter would be the name of a query, the WHERE parameter is the criteria as you have it.

See this link for a more detailed explanation
https://docs.microsoft.com/en-us/of.../apply-a-filter-when-opening-a-form-or-report

The_Doc_Man · Jan 1, 2020

I didn't see this discussed. Where is the function defined?

IF the function is only called from the form, it can be in the form's Class Module. But if more than one form or query or code entity calls the function, it must be declared as Public in a General Module.

Note for future reference if you didn't know this already, that "Public" has no practical meaning in a Class Module as all things in Class Modules are private.

Micron · Jan 2, 2020

"Public" has no practical meaning in a Class Module as all things in Class Modules are private.

You can call a control event (e.g. button click) contained in open form A from form B IF the event in form A is Public, but not if it's Private. Doing so has limited use I'd say, but it is something that gets asked from time to time. So I'm not 100% in agreement with that statement.

The_Doc_Man · Jan 2, 2020

Micron, I understand your special case and I've actually used it. But that which the OP is describing isn't an event routine. It's an arbitrarily named sub.

I think the exception of which you speak occurs because event entry points are "special" in a few different ways. That could well be one of the specialties.

There is also the "Friend" exception, but again it is rarely used for simple DB situations.

Micron · Jan 2, 2020

But that which the OP is describing isn't an event routine. It's an arbitrarily named sub.

I'm afraid I don't see the point of that. I'm not the one who brought up class modules and stated that all things in class modules are Private thus Public has no meaning. If that didn't mean it was pointless to ever make them Private because it would make no difference then I apologize for butting in.

The_Doc_Man · Jan 2, 2020

No, Micron, it is worthy of exploration. I'm not offended. I had to review things a bit because it has been a while.

First, by default event routine entry points are declared PRIVATE so I know you can trigger an event call from within the same Class module, but surely the scope is limited to that class module only.

Second, by default a sub is implicitly declared Public unless the keyword Private is used OR there is an Option clause in the declaration area that specifies that everything is Private unless explicitly made Public (or Friend).

Third, I withdraw the comment about visibility. Since it was declared Public, it should be visible. HOWEVER, there are exceptions. If it was declared in a Class Module then it ain't there when you call it unless the associated class object is already open. I'm not sure whether make a call in that case would actually launch the object containing that referenced entry point. Also, if there is more than one instance of the subject class module (e.g. a form is open twice) you have to specify which one you wanted or else you might get the wrong context.

So now my question changes to the variable strScanDate. Where (and how) is IT defined?

Micron · Jan 2, 2020

I'm not sure whether make a call in that case would actually launch the object containing that referenced entry point.

I can say that if the class is a form module, then no. The form has to be open.

vba_php · Jan 2, 2020

Micron said:
I can say that if the class is a form module, then no.

speaking in reverse...based on everything ive read from the experts about access's model of oop....a form's module IS considered a class itself. Makes perfect sense to me.

Micron · Jan 2, 2020

It is. So you are saying you realize that all form modules are class modules but not all class modules belong to forms? If that's not the point then I missed it. You say it as if you're not certain, which would be surprising given your 20 odd years of Access experience.

vba_php · Jan 3, 2020

Micron said:
It is. So you are saying you realize that all form modules are class modules but not all class modules belong to forms? If that's not the point then I missed it. You say it as if you're not certain, which would be surprising given your 20 odd years of Access experience.

well i dont really Know micron. Surely your words are accurate and the concept can go both ways. But in terms of what i do thats hardly relevant to me. Thats more of a discussion for all you smart cookies that like to talk about semantics and theory. If someone forced me into the conversation though and asked for my view then i would say yes...a form objects module and a class module are basically the same thing. Its just that 1 comes prepackaged the other 1 doesnt. Im working on a FAQ right now that covers class modules and OOP when used in VBA and ill send it to the experts here for approval when im done. But i wouldnt be surprised a bit if everyone of you guys come back in varying degrees and tell me why its "wrong".

. But its purpose is to help people, that dont understand engineering "lingo", to wrap their heads around the ideas that drive you smart cookies! Since youre not a fan of mine micron, at the very least itll be an entertaining read for you....and maybe even a bit humorous if you look upon me as someone whos "off base" with regard to a knowledgebase and skillset in ms access. Its turning out to be a long write but ill get it submitted when i can. It might not even get through the authoritative firewall here though.....

Micron · Jan 3, 2020

I doubt I will read it. If I'm not a fan it's because of
a) your writing style here, which doesn't resemble other places, which only makes one wonder if you've regressed or were practicing plagiarism.
b) your expansiveness
c) a disconnection of notions or ideas, even within the same sentence such as
"in terms of what i do thats hardly relevant to me." If what you do is not relevant to you, then who is it relevant to? No need to answer that.
d) your constant self deprecation which might be OK if it were occasional or meant to be humorous, but it seems to be neither.
e) continually referring to others as "you smart cookies" etc. etc. seems to be either disdain or disrespectful of everyone you'd place in that category, or is again, self deprecation.

@rockb - I'm so sorry that you are being subjected to this but I didn't know what else to do because one can only PM on the same subject so much. At most I will pollute someone's thread with one off-the-topic retort if I can help it.

I'm going to unsubscribe from this one as I don't want to encourage any more off topic responses.

MajP · Jan 3, 2020

@ RockB

I agree that opening a report using the where condition of the DOCMD.openreport is probably the best approach. However Using a UDF should work fine, but you cannot call that UDF from a query as written, because the UDF is inside a form's class module and not in a standard module.

Since that UDF is located in a forms module you have to reference it like any class module method and that is through the instantiated class. There is two ways to do that

In the query it would have to be

Code:

 Forms!YourFormName.GetScanDate

Or you could put the GetScanDate into a standard module, and let it reference it through the class.

Code:

Public Function GetScanDate() as date
  GetScanDate = Forms!YourFormName.list22.Column(0)
end Function

For example assume I was not doing this in a query but in the immediate window

debug.print Forms!YourFormName.GetScanDate()

That is how you have to call a procedure in a form class from outside the form.

MajP · Jan 3, 2020

There is a lot of misinformation in this thread. To be clear yes a form's and report's module is a class module just like a custom class. The only difference is one has a gui component. In something like vb.net it is even more obvious because you can see the form designer code which creates all the form's objects.

But most definitely any class has public and private methods and properties. This may help demonstrate. Assume I have a form MyForm and it has two procedures in it.

Code:

Public Sub HelloWorld()
  MsgBox "Hello WOrld"
End Sub

Private Sub GoodNightWorld()
  MsgBox "Good Night WOrld"
End Sub

I can call the first from the outside world but not the second

Code:

Public Sub Test()
  Dim frm As Form_MyForm
  DoCmd.OpenForm "MyForm"
  Set frm = Forms!Myform
  frm.HelloWorld
  'cannot call frm.goodnightworld
End Sub

However a procedure in the class can call the private procedure

Code:

Public Sub CallGoodNightWorld()
  Call GoodNightWorld
End Sub

Then this could work

Code:

Public Sub Test()
  Dim frm As Form_MyForm
  DoCmd.OpenForm "MyForm"
  Set frm = Forms!Myform
  frm.HelloWorld
  'cannot call frm.goodnightworld but can call the public procedure
  frm.CallGoodNightWorld
End Sub

RTE 3085 Undefined Function in expression (1 Viewer)

New member

Forum Troll

New member

Enthusiastic Amateur

Forum Troll

New member

Forum Troll

Super Moderator

Immoderate Moderator

AWF VIP

Immoderate Moderator

AWF VIP

Immoderate Moderator

AWF VIP

Forum Troll

AWF VIP

Forum Troll

AWF VIP

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Similar threads

Users who are viewing this thread