Update pass-through query from a form (an alternative to stored procedures)
The first thing I would suggest is to avoid as much as possible depending on the DoCmd object. The DoCmd object is essentially replicating the actual user action as if you had clicked on a button via the UI, with all the dialogs, etc. that comes with it. 

For the purpose of executing a SQL, I recommend that you use the Database.Execute method, remembering to use dbFailOnError flag and very likely dbSeeChanges if your Oracle tables uses something equivalent to auto-increment for its primary key. So instead of

DoCmd.Close acQuery, "sptROAD_INSP"
DoCmd.OpenQuery strSPTName


You should want to do something like this:

CurrentDb.Execute strSPTName, dbFailOnError Or dbSeeChanges


This does essentially the same thing but without all the extraneous UI baggages that the DoCmd brings to the table. It will NOT prompt you to confirm if you want to make changes, and does NOT require you to use DoCmd.SetWarnings to suppress the unwanted UI messages associated with OpenQuery or RunSQL methods which are member of the DoCmd object.

However, Execute method is only appropriate when it's in fact a statement that is not intended to return results. If you expect results (e.g. the qdf.ReturnRecords is set to true), you would use OpenRecordset method. Therefore, you could revise your function so that it does this (untested aircode):

If qdf.ReturnRecords Then
  Set Results = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
Else
  qdf.Execute dbFailOnError Or dbSeeChanges
End If


assuming that you have a variable named Results that is a `DAO.Recordset type.

Next, you are writing SQL raw. Unfortunately, a passthrough query cannot be properly parameterized. For best protection against SQL injection, you might want to consider using ADO instead of DAO, and if possible use OLEDB provider for Oracle (note that there are multiple providers, some by Oracle themselves, other by Microsoft - I can't say which is better; experimenting is needed). Note that you are not restricted to using ADO exclusively; you can use ADO in VBA code and DAO for Access form binding. 

If for some reasons you cannot use ADO and must use DAO, then you should at least consider writing functions to prepare the parameters so that all strings are properly delimited, and that empty values in a textbox will result in a NULL or maybe '', depending on your needs.