Ambiguous Name Detected: ~ (1 Viewer)

[barbados2010]

I had to create a new FE of a DB I was working on and in the new FE I have been re ceiving an error for a function that was previously working and I can't figure out why. On a couple of the forms there is a command button to send an email; the user is prompted if they would like to use a template, if yes is clicked it creates a VBA error. If I close the VBA ending the debugger, the form to select template opens- it;s supposed to open when the user clicks yes to use a template. I've done compact and repair which doesn't resolve the issue either. Just not sure why this particular function produces the error now... If I choose no to selecting a template it will open a new message in outlook without a problem... The full code is below

Any possible solutions would be much appreciated!

I'm receiving the error "Ambiguous Name Detected: ~"
The word strDField in the line bolStoreEmailHistory = (strDField(Me.OpenArgs, "~", 1) = "True") is highlighted in blue and Private Sub Form_Load() is highlighted in yellow.

Option Compare Database
Option Explicit

Dim DisplayType As Integer '1 = full, 2 = full w No delete, 3 = send/preview only
Dim DisplayType2 As Integer '1 = regular, 2 = sending emails, 3 = previewing emails
Dim EmailTemplateID As Long
Dim bolSingular As Boolean
Dim FormTitle As String
Dim strDisplayType As String
Dim bolStoreEmailHistory As Boolean

Const olMailItem = 0
Const olFormatHTML = 2
Const olFormatPlain = 1

Private Sub ComboRecordCorrespondence_AfterUpdate()
If IsDebugMode = 0 Then On Error GoTo ComboRecordCorrespondence_AfterUpdate_Error

If (Nz(Me.ComboRecordCorrespondence, "") = "") Then
Me.ComboRecordCorrespondence = "Yes"
End If

ComboRecordCorrespondence_AfterUpdate_Exit:
Exit Sub

ComboRecordCorrespondence_AfterUpdate_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "ComboRecordCorrespondence_AfterUpdate")
Resume ComboRecordCorrespondence_AfterUpdate_Exit
End Sub

Private Sub ListEmailTemplates_DblClick(Cancel As Integer)
If IsDebugMode = 0 Then On Error GoTo ListEmailTemplates_DblClick_Error

cmdModify_Click

ListEmailTemplates_DblClick_Exit:
Exit Sub

ListEmailTemplates_DblClick_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "ListEmailTemplates_DblClick")
Resume ListEmailTemplates_DblClick_Exit
End Sub

Private Sub MergeEmails(Optional preview As String)
'If Preview = "Yes" then it doesn't send any email directly but instead
'just opens the emails in outlook
'Note: we limit previewing to < 10 emails
Dim result As Long

If IsDebugMode = 0 Then On Error GoTo MergeEmails_Error

If (DisplayType <> 3) Then
EmailTemplateID = Nz(Me.ListEmailTemplates, 0)
End If

If (EmailTemplateID > 0) Then

If (preview <> "Yes") Then
DisplayType2 = 2 'set to sending email mode

'set two global vars to indicate timestamp and whether to record correspondence
'the inidvidual correspondence records will be added in the routine that send the individual emails
'also set the screen control txtEmailTimeStamp so the query that drives the email results will work

result = SendEmail(EmailTemplateID, "")
If (result >= 0) Then
ElseIf (result = -1) Then
MsgBox "Problem matching merge fields in the E-Mail template. Please check over the E-Mail template for valid merge fields. Contact RPT Software for support if needed.", vbOKOnly, "Error"
End If

DoCmd.Close
Else
DisplayType2 = 3 'set to preview mode
result = SendEmail(EmailTemplateID, preview)

End If
Else
MsgBox "You need to select an E-Mail template", vbExclamation, "Error"
End If

MergeEmails_Exit:
Exit Sub

MergeEmails_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "MergeEmails")
Resume MergeEmails_Exit
End Sub
Private Sub cmdMerge_Click()
If IsDebugMode = 0 Then On Error GoTo cmdMerge_Click_Error

If IsNull(Me.ListEmailTemplates) = False Then
Call MergeEmails
Else
MsgBox "You need to select an E-Mail template from the list.", vbOKOnly, "Error"
End If

cmdMerge_Click_Exit:
Exit Sub

cmdMerge_Click_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "cmdMerge_Click")
Resume cmdMerge_Click_Exit
End Sub
Private Sub cmdPreview_Click()
Dim preview As String

If IsDebugMode = 0 Then On Error GoTo cmdPreview_Click_Error

preview = "Yes"
Call MergeEmails(preview)

cmdPreview_Click_Exit:
Exit Sub

cmdPreview_Click_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "cmdPreview_Click")
Resume cmdPreview_Click_Exit
End Sub

Private Sub cmdAddNew_Click()
If IsDebugMode = 0 Then On Error GoTo cmdAddNew_Click_Error

DoCmd.OpenForm "frmEmail", , , , acFormAdd, acDialog
Call RequeryList
Me.ListEmailTemplates = GlobalID

cmdAddNew_Click_Exit:
Exit Sub

cmdAddNew_Click_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "cmdAddNew_Click")
Resume cmdAddNew_Click_Exit
End Sub

Private Sub cmdCancel_Click()
If IsDebugMode = 0 Then On Error GoTo cmdCancel_Click_Error

DoCmd.Close

cmdCancel_Click_Exit:
Exit Sub

cmdCancel_Click_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "cmdCancel_Click")
Resume cmdCancel_Click_Exit
End Sub

Private Sub cmdDelete_Click()
If IsDebugMode = 0 Then On Error GoTo cmdDelete_Click_Error

If (Nz(Me.ListEmailTemplates, 0) > 0) Then
If MsgBox("Are you sure you want to delete the template " & Me.ListEmailTemplates.Column(1) & "?", _
vbQuestion + vbYesNoCancel + vbDefaultButton2, "Delete template?") = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblEmailTemplate where EmailTemplateID = " & CStr(Me.ListEmailTemplates)
Call RequeryList
Me.ListEmailTemplates.Selected(0) = True
End If
Else
MsgBox "You need to select an E-Mail template to delete", vbExclamation, "Error"
End If

cmdDelete_Click_Exit:
DoCmd.SetWarnings True
Exit Sub

cmdDelete_Click_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "cmdDelete_Click")
Resume cmdDelete_Click_Exit
End Sub

Private Sub cmdModify_Click()
' edit template
If IsDebugMode = 0 Then On Error GoTo cmdModify_Click_Error

If (Nz(Me.ListEmailTemplates, 0) > 0) Then
DoCmd.OpenForm "frmEmail", , , "EmailTemplateID = " & CStr(Me.ListEmailTemplates), acFormEdit, acDialog
Call RequeryList
Else
MsgBox "You need to select an E-Mail template", vbExclamation, "Error"
End If

cmdModify_Click_Exit:
Exit Sub

cmdModify_Click_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "cmdModify_Click")
Resume cmdModify_Click_Exit
End Sub

Private Sub Form_Load()
If IsDebugMode = 0 Then On Error GoTo Form_Load_Error

Dim strDisplay As String

DisplayType = 1 'set to full functionality
DisplayType2 = 1 'set to regular mode
If IsNull(Me.OpenArgs) = False Then
bolStoreEmailHistory = (strDField(Me.OpenArgs, "~", 1) = "True")
EmailTemplateID = CLng(strDField(Me.OpenArgs, "~", 2))
DisplayType = CInt(strDField(Me.OpenArgs, "~", 3))
bolSingular = (strDField(Me.OpenArgs, "~", 4) = "True")
FormTitle = strDField(Me.OpenArgs, "~", 5)
strDisplay = strDField(Me.OpenArgs, "~", 6)
strDisplayType = strDField(Me.OpenArgs, "~", 7)
End If
Me.txtData = strDisplay

Me.ListEmailTemplates.SetFocus
Me.ListEmailTemplates.Selected(0) = True

Form_Load_Exit:
Exit Sub

Form_Load_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "Form_Load")
Resume Form_Load_Exit
End Sub

Private Sub RequeryList()
'requeries the main list box and sets focus back on the listbox
If IsDebugMode = 0 Then On Error GoTo RequeryList_Error

Me.ListEmailTemplates.Requery
Me.ListEmailTemplates.SetFocus

RequeryList_Exit:
Exit Sub

RequeryList_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "RequeryList")
Resume RequeryList_Exit
End Sub

Private Function SendEmail(EmailTemplateID As Long, preview As String) As Long
'This function runs the GlobalEmailSQL (supplied at the very beginning in the ModuleEmail)
'and sends emails using the supplied emailTemplateID
'Note: if Preview = "YES" then it does NOT send the emails and instead
' just opens the first email email in Outlook
'Returns postive number = number of emails it tried to send
'Returns -1 = could not send emails due to token problems in email template

If IsDebugMode = 0 Then On Error GoTo SendEmail_Error

Dim db As Database
Dim rs As DAO.Recordset
Dim RSAttachments As DAO.Recordset
Dim filepath As String
Dim emailtemplatesql As String
Dim fld As DAO.Field
Dim OrigToString As String
Dim OrigCCString As String
Dim OrigBCCString As String
Dim OrigSubjectString As String
Dim OrigBodyString As String
Dim OrigBodyHTMLString As String
Dim ToString As String
Dim CCString As String
Dim BCCString As String
Dim SubjectString As String
Dim BodyString As String
Dim BodyHTMLString As String
Dim result As Variant
Dim ReturnValue As Long
Dim ContactID As Long
Dim GoodEmail As Long
Dim BadEmail As Long
Dim Attachments(9) As String
Dim iii As Long

'counters to count emails sent and not sent
GoodEmail = 0
BadEmail = 0
ReturnValue = 0

'retrieve email template information from table into variables
emailtemplatesql = "Select * from tblEmailTemplate WHERE EmailTemplateID = " & CStr(EmailTemplateID)
Set db = CurrentDb()
Set rs = db.OpenRecordset(emailtemplatesql, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
OrigToString = rs("To")
OrigCCString = Nz(rs("CC"), "")
OrigBCCString = Nz(rs("BCC"), "")
OrigSubjectString = rs("Subject")
OrigBodyString = rs("Body")
OrigBodyHTMLString = Nz(rs("BodyHTML"), "")
'Open recordset of attachments, save each one to disk and assign to attachments() array
For iii = 1 To 9
Attachments(iii) = ""
Next iii
filepath = CurrentDBDir() & "Attachments999\"
If Len(Dir(filepath, vbDirectory)) = 0 Then
MkDir filepath
End If
iii = 1
Set RSAttachments = rs.Fields("Attachments").Value
While Not RSAttachments.EOF
If (FileExists(filepath & RSAttachments.Fields("FileName").Value)) Then
Kill filepath & RSAttachments.Fields("FileName").Value
End If
RSAttachments.Fields("FileData").SaveToFile filepath
Attachments(iii) = filepath & RSAttachments.Fields("FileName").Value
iii = iii + 1
RSAttachments.MoveNext
Wend
RSAttachments.Close
End If
rs.Close

'run through recordset and send email for each record
Set rs = db.OpenRecordset(GlobalEmailSQL, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do While Not rs.EOF

'check to see if user stopped email process
DoEvents

'get ids for use in correspondence history
ContactID = rs("ContactID")

'Set vars back to original values (needed if doing multiple emails)
ToString = OrigToString
CCString = OrigCCString
BCCString = OrigBCCString
SubjectString = OrigSubjectString
BodyString = OrigBodyString
BodyHTMLString = OrigBodyHTMLString

'Change variables as needed by replacing ##xxxxx## token in the email template with actual values
For Each fld In rs.Fields
'Debug.Print fld.Name
If (fld.Name <> "Attachments") Then 'attachments field is special
ToString = ReplaceIfNeeded(ToString, fld.Name, CStr(Nz(rs(fld.Name), "")))
CCString = ReplaceIfNeeded(CCString, fld.Name, CStr(Nz(rs(fld.Name), "")))
BCCString = ReplaceIfNeeded(BCCString, fld.Name, CStr(Nz(rs(fld.Name), "")))
SubjectString = ReplaceIfNeeded(SubjectString, fld.Name, CStr(Nz(rs(fld.Name), "")))
BodyString = ReplaceIfNeeded(BodyString, fld.Name, CStr(Nz(rs(fld.Name), "")))
BodyHTMLString = ReplaceIfNeeded(BodyHTMLString, fld.Name, CStr(Nz(rs(fld.Name), "")))
End If
Next

'check to make sure all variables have been replaced (if not exit function with error
result = InStr(ToString & CCString & BCCString & SubjectString & BodyString, "##")
If Not ((result = 0) Or (IsNull(result))) Then
ReturnValue = -1
GoTo SendEmail_Exit
End If

'Send ONE email (if previewing email will open in Outlook but not send)
'On Error Resume Next
If (SendONEEmail(ContactID, ToString, CCString, BCCString, SubjectString, BodyString, BodyHTMLString, Attachments, preview) = 1) Then
GoodEmail = GoodEmail + 1
Else
BadEmail = BadEmail + 1
End If
On Error GoTo SendEmail_Error

ReturnValue = ReturnValue + 1

'if previewing emails only open the first email in Outlook
If (Nz(preview, "") = "Yes") Then
GoTo SendEmail_Exit
End If
rs.MoveNext
Loop
End If

'if sending emails display a message bos indicating how many emails were sent (good and bad)
If (Nz(preview, "") <> "Yes") Then
MsgBox CStr(GoodEmail) & " Email" & IIf((GoodEmail > 1), "s have", " has") & " been sent." & IIf(BadEmail > 0, " Had problems sending " & CStr(BadEmail) & " Email" & IIf((BadEmail > 1), "s.", ""), "")
GoTo SendEmail_Exit
End If

SendEmail_Exit:
rs.Close
SendEmail = ReturnValue
Set rs = Nothing
Set RSAttachments = Nothing
Set db = Nothing
Exit Function

SendEmail_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "SendEmail")
Resume SendEmail_Exit
End Function
Function ReplaceIfNeeded(TheString As String, TheFieldName As String, TheValue As String)
'If it finds ##TheFieldName## in TheString then replace with TheValue
If IsDebugMode = 0 Then On Error GoTo ReplaceIfNeeded_Error

Dim NewString As String
Dim result As Variant
Dim CharsOnRight As Long
Dim doneflag As Boolean

doneflag = False
While Not (doneflag)
result = InStr(TheString, "##" & TheFieldName & "##")
If Not ((result = 0) Or (IsNull(result))) Then
'get text before the ##xxxxx# token
NewString = Left(TheString, result - 1)
'append the real value (that replaces the token)
NewString = NewString & TheValue
'append the rest of the string after the token
CharsOnRight = Len(TheString) - (result + Len(TheFieldName) + 3)
NewString = NewString & Right(TheString, CharsOnRight)
'Set TheString equal to the newly changed string (we might have more substitutions and have to loop again)
TheString = NewString
Else
doneflag = True
NewString = TheString
End If
Wend

ReplaceIfNeeded = NewString


ReplaceIfNeeded_Exit:
Exit Function

ReplaceIfNeeded_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "ReplaceIfNeeded")
Resume ReplaceIfNeeded_Exit
End Function

Private Function SendONEEmail(ContactID As Long, ToString As String, CCString As String, BCCString As String, SubjectString As String, BodyString As String, BodyHTMLString As String, Attachments() As String, preview As String) As Long
'tries to send ONE email, logs results to tblCorrespondence and returns either
' a 1 for a successful send or 0 for a failure due to some error
'Note: tblCorrespondence is used temporarily to show status of each email (even if user doesn't want to store correspondence)
Dim ReturnValue As Long

Dim OlApp As Object 'Outlook.Application
Dim olMail As Object 'MailItem
Dim i As Integer
Dim Emailtext As String
Dim TheDateTime As Date

ReturnValue = 0

'Code removed (early binding)
'Set OlApp = New Outlook.Application

'Code added for late binding
'---------------------------
On Error Resume Next
Set OlApp = GetObject(, "Outlook.Application")
If Err.Number = 429 Then
Set OlApp = CreateObject("Outlook.application")
End If
If IsDebugMode = 0 Then On Error GoTo SendONEEmail_Error
'---------------------------

Set olMail = OlApp.CreateItem(olMailItem)
With olMail
.To = ToString
.CC = CCString
.BCC = BCCString
.Subject = SubjectString
If (Nz(BodyHTMLString, "") <> "") Then
.BodyFormat = olFormatHTML
.Body = BodyString
.HTMLBody = BodyHTMLString
Else
.BodyFormat = olFormatPlain
.Body = BodyString
End If

For i = 1 To 9
If (Attachments(i) <> "") Then
.Attachments.Add Attachments(i)
End If
Next i
If (Nz(preview, "") = "Yes") Then
.Display
ReturnValue = 1
Else
.Send
'Note if NO email address specified or some other error occurs then the code will jump to
'the error handling and will return a 0 (indicating a problem sending this email)
ReturnValue = 1

'if ComboRecordCorrespondence = "Yes" then create an activity to
'indicate that an email has been sent
If (Me.ComboRecordCorrespondence = "Yes") Then
Emailtext = "TO: " & ToString & "<br>" & IIf(CCString = "", "", "CC: " & CCString & "<br>") & IIf(BCCString = "", "", "BCC: " & BCCString & "<br>") & "Subject: " & SubjectString & "<br>" & Replace(BodyString, vbCrLf, "<br>")
TheDateTime = Now()
Call AddCorrespondenceHistory(ContactID, Emailtext, TheDateTime)
End If
End If
End With


SendONEEmail_Exit:
SendONEEmail = ReturnValue
Set olMail = Nothing
Set OlApp = Nothing
Exit Function

SendONEEmail_Error:
'Don't display message - allow errors to continue
'Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "SendONEEmail")
Resume SendONEEmail_Exit

End Function
Private Sub AddCorrespondenceHistory(ContactID As Long, Emailtext As String, TheDateTime As Date)
If IsDebugMode = 0 Then On Error GoTo AddCorrespondenceHistory_Error

Dim sql As String
sql = "INSERT INTO tblActivity ( ContactID, ActivityType, ActivityDate, ActivityTime, Description, Notes, UpdatedDate, CreatedDate ) " & _
"SELECT tblContact.ContactID, 'E-Mail' AS Expr3, #" & Format(TheDateTime, "mm/dd/yyyy") & "# AS Expr5, #" & Format(TheDateTime, "hh:mm AMPM") & "# AS Expr6, 'E-Mail using E-Mail template' AS Expr7, """ & Emailtext & """ as Expr8, #" & Format(TheDateTime, "mm/dd/yyyy hh:mm AMPM") & "# AS Expr9, #" & Format(TheDateTime, "mm/dd/yyyy hh:mm AMPM") & "# AS Expr10 " & _
"From tblContact " & _
"WHERE (tblContact.ContactID = " & ContactID & ");"

CurrentDb.Execute (sql)

AddCorrespondenceHistory_Exit:
Exit Sub

AddCorrespondenceHistory_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name, Erl, "AddCorrespondenceHistory")
Resume AddCorrespondenceHistory_Exit
End Sub

 

[gemma-the-husky]

ambiguous name means the function name is used more than once.

if you hover over a function name, and click shift-F2 - you should jump to the function. if the function name is duplicated, access cannot do this

use Ctrl-H or ctrl-F to find the occurrences of the function, and rename or delete 1. maybe you have a duplicated module.

 

[barbados2010]

ambiguous name means the function name is used more than once.

if you hover over a function name, and click shift-F2 - you should jump to the function. if the function name is duplicated, access cannot do this

use Ctrl-H or ctrl-F to find the occurrences of the function, and rename or delete 1. maybe you have a duplicated module.

I did that actually but when I do the shift+F2 I get an error " Identifier under cursor is not recognized" and I couldn't find any duplicates of the module either...

 

[mdlueck]

Sounds like time to make a backup copy of your database file and run:

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk/forums/showthread.php?t=219948