Query doesn't recognized function which is trying to pass a variable to the search criteria (1 Viewer)

VB3336

New member
Local time
Today, 09:32
Joined
Jul 2, 2021
Messages
4
The code I am using will email a report based on an open form. The report uses a query that calls a function to get the search criteria from a public variable created by the code based on the open form. When the button is selected I get an error message Runtime error 3085, undefined function 'myForm' in expression. I have several unique forms with associated reports but want to use just one query for all of the reports and dynamically change the criteria with the code. When I use a query with the form name hardcoded into the query, it works fine... here's the code... Any help would be appreciated...

thanks in advance

Sub Command67_Click()

Dim strTo As String
Dim strSubject As String
Dim strMessageText As String


Dim frmCurrentForm As Form
Dim myQry As String


Set frmCurrentForm = Screen.ActiveForm
myQry = "[forms]!" & "[" & frmCurrentForm.Name & "]" & "!" & "[ID]"
myFormName = myQry

Me.Dirty = False

strTo = "XXX.XXXX@XXXX.com"
strSubject = "ICAM UPDATE REQUEST from " & Me.UnitID
strMessageText = vbNewLine & vbNewLine & _
"Please update the ICAM MAP for the " & Me.GISLayer & " Layer"


Call myForm

"This is where the code stops"

DoCmd.SendObject ObjectType:=acSendReport, _
ObjectName:="rptEleEQ", _
OutputFormat:=acFormatPDF, _
To:=strTo, _
Subject:=strSubject, _
MESSAGETEXT:=strMessageText, _

EditMessage:=True


End Sub

Public Function myForm()
' Return the value of the module variable.

myForm = myFormName

End Function

This is a clip of the query, the report is based on that contains the Function to get the variable....I think this is what is generating the error
1625237473164.png
 

Minty

AWF VIP
Local time
Today, 13:32
Joined
Jul 26, 2013
Messages
10,355
You can't call a function per se - you would set a value to it?

MyVariable = MyForm()
 

plog

Banishment Pending
Local time
Today, 08:32
Joined
May 11, 2011
Messages
11,613
Code:
Public Function myForm()
' Return the value of the module variable.

myForm = myFormName

End Function

Scope.


myFormName doesn't exist in that function and when you try to return a Null value you blow it up. myFormName does exist in your code, but it only exists inside Command67_Click()--anything outside of that Sub has no idea what myFormName is. So, you need the code that calculates myFormName inside myForm()
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:32
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Are you able to share a sample copy of your db for testing?
 

Minty

AWF VIP
Local time
Today, 13:32
Joined
Jul 26, 2013
Messages
10,355
public variable created by the code based on the open form.

Where is the variable declared in the form module or a separate code module?
It has to be the latter.
 

Minty

AWF VIP
Local time
Today, 13:32
Joined
Jul 26, 2013
Messages
10,355
Hmmm...
Try sticking an Eval() around your query criteria.

> Eval(myForm())

I can't really see how this is saving you much in terms of coding though, but that could be me being thick, it being Friday afternoon and all.
 

VB3336

New member
Local time
Today, 09:32
Joined
Jul 2, 2021
Messages
4
I have modified the function code to include the capture of the form name, but I am still getting the error that the function is undefined. The public variable is declared on module 1.

1625244362398.png
 

Minty

AWF VIP
Local time
Today, 13:32
Joined
Jul 26, 2013
Messages
10,355
Add Option Explicit to the top of all your code modules Under Option Compare Database.
Then click Debug and compile.

By doing what you have in your current code you have duplicated the myFormName variable is now local only as far as that procedure is concerned
 

VB3336

New member
Local time
Today, 09:32
Joined
Jul 2, 2021
Messages
4
you may be right with all the time I have spent...I could have created 15 simple queries with hardcoded form names, but it seemed so simple. Now its become a challenge...
 

Users who are viewing this thread

Top Bottom