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
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