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
EXECUTE dbo.AuditInsert @strPlant = N'{1}', @strDept = N'{2}', @strArea = N'{3}', @strShift = N'{4}', @strAuditor = N'{5}', @strSeries = N'{6}', @strUnitType = N'{7}'
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
Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.