Two possibly common mistakes casual coders should be aware of

[bxdobs]

As a result of the never-ending Cyber Policy changes with restrictions to how EMAILS can be sent (emails forwarded by our website's forms are being blocked), resulted in dredging up an old application in ACCESS that pulls down form data and then SENDs NOTIFICATION and CONFIRMATION emails via SMTP services.

As a casual Access programmer, what I had expected should have been a quick job has turned into more than a couple of weeks due to some very likely COMMON mistakes



1) SLOPPY/LAZY USE OF ERROR HANDLING ... USE "ON ERROR RESUME NEXT" VERY CAREFULLY (I have repositioned this to the location where it was needed)

2) in conjunction with 1, DAO "FINDFIRST" does find a record ... just NOT the one you expect if you don't use "dbOpenDynaset"

Example:
Using a procedure that is passed in 3 parameters representing FID, PID, and Application

function fnXML( vFID as Variant, vPID as Variant, vApp as Varaint) as Boolean

xPath = <global data path>vFID-vPID<.xml> provides the procedure with a link to an xml file that it is supposed to import (generating a record)

Application.ImportXML DataSource:=xPath, ImportOptions:=acAppendData

Each application has a different set of Fields so to be generic I want to just open this newly created record and pull key data out to include Confirmation or Notification emails

All Tables/Records use vFID (form id), vPID (process id) as their Primary Key

Outlook has 5 new ABC application requests waiting to be processed ... each request has a unique FID-PID which is passed to fnXML
fnXML imports the attached XML file no with no issue

The issue:
For my initial coding, I was using a DAO FINDFIRST method to find this new XML imported record ... which took me some time to figure out why ALL my Notification and Confirmation emails contained the SAME DATA

The key here is the FINDFIRST statement was causing an ERROR because it wasn't happy with my missing the dbOpenDynaset ... the RESUME NEXT just ignored the error, as it was told to do, and the NOT xs.NOMATCH was happy because there was a record available to review (albeit the SAME record EVERY TIME)

ON ERROR RESUME NEXT ' SLOPPY/LAZY ERROR HANDLING
Dim xs as DAO.recordset
Set xs = currentdb.OpenRecordset(vApp) ' MISSING dbOpenDynaset param in OpenRecordset
xs.FindFirst "[TFID] = '" & vFID & "' and [TPID] = '" & vPID & "'" 
If Not xs.NoMatch Then

This SQL code Always works and is most likely a better approach to finding records going forward.

  Dim sql As String
  Dim xs as DAO.recordset
  sql = "select * from " & vApp & " where [TFID] = '" & vFID & "' and [TPID] = '" & vPID & "';"
  Set xs = currentdb.OpenRecordset(sql)
  If xs.RecordCount > 0 Then
   xs.MoveFirst

NOTE:
The RESUME NEXT was originally a last-ditch effort to squash an error imported from an OUTLOOK Email Body which included non-ASCII characters
When attempting to do a FL.WRITELINE with this string containing non-ASCII characters an error occurs ... using an ON ERROR GOTO I have now redirected these strings to a Procedure that strips all non-ASCII characters and then RESUME NEXT

Is there a way to quickly check if a string has non-ASCII characters without resorting to using an ERROR TRAP?

[pbaldy]

>>Is there a way to quickly check if a string has non-ASCII characters without resorting to using an ERROR TRAP?

You can loop the string with the Mid() functino and the Asc() function to see the ASCII value of each character.