Hello AccessForums,
I am creating a database to track inspections & recorded defects on assets. Frequently, defects will appear on multiple inspections, so to save creating new defect records, I have a join table between inspections and defects, so that one inspection can identify multiple defects and one defect may appear on multiple inspections. In order to "pick" the defects, I have a form with a list showing all of the defects from the previous inspection:
Code:
SELECT Max(ExtantDefectsAll.InspectionID) AS MaxOfInspectionID, DefectLog.Description, ExtantDefectsAll.DefectID, DefectLog.AssetID FROM (DefectLog INNER JOIN ExtantDefectsAll ON DefectLog.DefectID = ExtantDefectsAll.DefectID) LEFT JOIN InspectionDefectJoin ON DefectLog.DefectID = InspectionDefectJoin.DefectID GROUP BY DefectLog.Description, ExtantDefectsAll.DefectID, DefectLog.AssetID HAVING (((DefectLog.AssetID)=GetAssetID()));
I have a double click action where this creates a new entry on the join table to associate this defect with the new inspection:
Code:
Private Sub List0_DblClick(Cancel As Integer)
Dim StrSQL As String
StrSQL = "INSERT INTO InspectionDefectJoin(InspectionID,DefectID) VALUES (GetInspectionID()," & Me.List0.Column(2) & ")"
CurrentDb.Execute StrSQL, dbFailOnError
Me.List0.Requery
Me.List2.Requery
End Sub
I also have a second list that shows all the defects associated with the current inspection:
Code:
SELECT InspectionDefectJoin.InspectionID, DefectLog.Description, InspectionDefectJoin.DefectID, DefectLog.AssetID, InspectionDefectJoin.DefectJoinID FROM DefectLog LEFT JOIN InspectionDefectJoin ON DefectLog.DefectID = InspectionDefectJoin.DefectID GROUP BY InspectionDefectJoin.InspectionID, DefectLog.Description, InspectionDefectJoin.DefectID, DefectLog.AssetID, InspectionDefectJoin.DefectJoinID HAVING (((InspectionDefectJoin.InspectionID)=GetInspectionID()) AND ((DefectLog.AssetID)=GetAssetID()));
Along with a double click action to remove this association:
Code:
Private Sub List2_DblClick(Cancel As Integer)
Dim StrSQL As String
Dim DeleteID As Long
DeleteID = Me.List2.Column(4)
StrSQL = "DELETE * from InspectionDefectJoin WHERE DefectJoinID = " & DeleteID
CurrentDb.Execute StrSQL, dbFailOnError
Me.List0.Requery
Me.List2.Requery
End Sub
I need to create a condition where a row in the list showing defects on the previous inspection is hidden (without touching the record itself) once it has been double clicked to associate it with the new inspection; currently, the defect remains in place and permits the user to double click it multiple times, and create multiple records on the join table for the same defect and inspection.
(The GetFIELD() functions are to supply numbers to the SQL statement from public variables in order to pass data from the "add inspection" form to this form)