MS Access pass through query and SQL stored procedure stops working properly after a day or two.

Hello!

I have a very strange problem I have never run into before.  I am using Access 2013 front end and SQL Server 2008 on the back.  The application is run on Dell Venue tablets that are running Windows 8.1.

This code works perfectly for a day or two and then for some reason it stops working properly.  All of the fields are written correctly to dbo.tblQMSAuditMaster except for the Dept field, which will start to select a random value from the Dept list box on the form.  The Dept list box is populated by a simple select query.

The weird thing is if you delete the .accdr file on the tablet and reinstall it, it will work perfectly for a day or two before it starts to act up again.  If this is a coincidence or actually fixing the problem I do not know.

I've had several people far smarter than myself that I know go over this code and none of us can figure out what is going on.  I really hope some of you have ideas that I can try.  I have read that network congestion can sometimes hose things up but could find no examples that are similar to this.

Here is the code for the sproc:

USE [DB1XXX]
GO
/****** Object:  StoredProcedure [dbo].[AuditInsert]    Script Date: 12/16/2015 10:27:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AuditInsert]   (
	@strPlant		AS	varchar(50),
	@strDept		AS	varchar(50),
	@strArea		AS	varchar(50),
	@strShift		AS	int,
	@strAuditor		AS	varchar(50),
	@strSeries		AS	varchar(50),
	@strUnitType	AS	varchar(20)
	)
AS
BEGIN
DECLARE @FirstAuditMasterID int, @AuditMasterID int

	SET NOCOUNT ON;
	
	SET XACT_ABORT ON;

	BEGIN TRY
		BEGIN TRANSACTION

			SELECT @FirstAuditMasterID = MAX(AuditMasterID) FROM tblQMSAuditMaster
			
			INSERT INTO dbo.tblQMSAuditMaster (Plant, Dept, Area, Shift, Auditor, Series, UnitType)
			VALUES (@strPlant, @strDept, @strArea, @strShift, @strAuditor, @strSeries, @strUnitType);
			
			SELECT @AuditMasterID = MAX(AuditMasterID) FROM tblQMSAuditMaster
			SELECT @FirstAuditMasterID = @FirstAuditMasterID + 1
			
			IF @AuditMasterID <> @FirstAuditMasterID
				SELECT @AuditMasterID = @FirstAuditMasterID
				
			Select @AuditMasterID as ScopeID;
			
			INSERT INTO dbo.tblQMSAudits (Series, UnitType, IPoint, Task, AuditMasterID, InspectID, MatrixID)
			SELECT @strSeries, UnitType, IPoint, Inspect, @AuditMasterID, InspectID, MatrixID 
			FROM dbo.tblQMSAuditMatrix 
			WHERE UnitType = @strUnitType AND IPoint = @strArea;
																
		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
	-- Test whether the transaction is uncommittable.
		IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION;
		RETURN -1;
	END CATCH;
	RETURN 0;
		
END

Select all Open in new window

The code for the pass-through query:

EXECUTE dbo.AuditInsert @strPlant = N'{1}', @strDept = N'{2}', @strArea = N'{3}', 				@strShift = N'{4}', @strAuditor = N'{5}', @strSeries = N'{6}', @strUnitType = N'{7}'

Select all Open in new window

And finally the code for cmdStartAudit button:

Private Sub cmdStartAudit_Click()

If IsNull(Me!Dept) Or Me!Dept = "" Then
      MsgBox "You must select a department.", vbOKOnly, "Required Data"
        Me!Dept.SetFocus
        Exit Sub
    End If
    
    If IsNull(Me!UnitType) Or Me!UnitType = "" Then
      MsgBox "You must select a Unit Type.", vbOKOnly, "Required Data"
        Me!UnitType.SetFocus
        Exit Sub
    End If
    
    If IsNull(Me!Series) Or Me!Series = "" Then
      MsgBox "You must select a Series.", vbOKOnly, "Required Data"
        Me!Series.SetFocus
        Exit Sub
    End If
    
    If IsNull(Me!Area) Or Me!Area = "" Then
      MsgBox "You must select an Audit area.", vbOKOnly, "Required Data"
        Me!Dept.SetFocus
        Exit Sub
    End If
    
    Dim strMessage As String
    
    strMessage = "Please verify the criteria selected." & vbCrLf
    strMessage = strMessage & "Plant: " & Me.txtPlant & vbCrLf
    strMessage = strMessage & "Dept: " & Me.Dept & vbCrLf
    strMessage = strMessage & "Area: " & Me.Area & vbCrLf
    strMessage = strMessage & "Shift: " & Me.txtShift & vbCrLf
    strMessage = strMessage & "Auditor: " & Me.txtAuditor & vbCrLf
    strMessage = strMessage & "Series: " & Me.Series & vbCrLf
    strMessage = strMessage & "UnitType: " & Me.UnitType & vbCrLf
         
    If MsgBox(strMessage, vbYesNo + vbQuestion, "Verify Data") = vbNo Then
        Exit Sub
    End If
    
    Dim strSQL As String
    With CurrentDb.QueryDefs("qryPT")
        
        strSQL = .SQL
        .SQL = Replace(.SQL, "{1}", Me.txtPlant)
        .SQL = Replace(.SQL, "{2}", Me.Dept)
        .SQL = Replace(.SQL, "{3}", Me.Area)
        .SQL = Replace(.SQL, "{4}", Me.txtShift)
        .SQL = Replace(.SQL, "{5}", Me.txtAuditor)
        .SQL = Replace(.SQL, "{6}", Me.Series)
        .SQL = Replace(.SQL, "{7}", Me.UnitType)
        
        End With
       ‘Return new record number 
        Dim rs As DAO.Recordset
        With CurrentDb
        Set rs = .OpenRecordset("qryPT")
        Me.txtReturnValue = rs.Fields("ScopeID")
        End With
               
        With CurrentDb.QueryDefs("qryPT")
        .SQL = strSQL ' restore for the next execution
        End With
               
  ‘Go to new record    
   Me.Filter = "AuditMasterID = " & txtReturnValue & ""
   Me.FilterOn = True
   Me.Requery
       
   Me!fsubAudits.Form.Filter = "[AuditMasterID] = " & txtAuditMasterID & ""
   Me!fsubAudits.Form.FilterOn = True
   'Me!fsubAudits.Requery
    
'Hide main form controls and open fsubDescLookup
Me.cmdInfo.SetFocus
Forms!frmSTF.Dept.Visible = False
Forms!frmSTF.cmdPrevious.Enabled = False
Forms!frmSTF.cmdNext.Enabled = False
Forms!frmSTF.Area.Visible = False
Forms!frmSTF.UnitType.Visible = False
Forms!frmSTF.Series.Visible = False
Forms!frmSTF.fsubDescLookup.Visible = True
Me!Dept.Enabled = False
Me!UnitType.Enabled = False
Me!Series.Enabled = False
Me!Area.Enabled = False
Me!cmdStartAudit.Enabled = False

End Sub

Select all Open in new window

Thank you for your help.  Have a great day!