I spent an hour or so this morning compiling this, and since I have started reading Clean Code and The Pragmatic Programmer I figured I would let you help me get slightly better at this.
Due to some crappy limitations with the specifics I was forced to work in Excel VBA instead of Access (someone doesn't want to build tables for the two lists in the Excel sheet).
The code pulls a list of defects from a production table, checks a master list to see if it ever existed, then checks an open list to see if it's current and updates the table accordingly. This could be super easy and potentially 100% automated if they would make tables for the two lists. The log of what was found per defect# (writing to sheet) is something I added just in case they want a log.
Private Sub thisbetheshitmane()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim vAr As String
Dim i As Integer
Dim y As Integer
Dim InCombined As Boolean
Dim InOpen As Boolean
Set db = DBEngine.OpenDatabase("C:\Users\dzcoats\Documents\Microsoft.accdb")
Set rst = db.OpenRecordset("SELECT DISTINCT [VDefects].Defect FROM [VDefects] WHERE [VDefects].Defect IS NOT NULL;")
Dim QResult() As Variant
QResult = rst.GetRows(rst.RecordCount)
For a = LBound(QResult, 2) To UBound(QResult, 2)
vAr = QResult(0, a)
Debug.Print ; vAr
Next a
Dim CombinedList() As Variant
CombinedList = Application.Transpose(Worksheets(1).Range("b2:b2000").Value)
Dim OpenList() As Variant
OpenList = Application.Transpose(Worksheets(1).Range("a2:a2000").Value)
For y = LBound(QResult, 2) To UBound(QResult, 2)
vAr = Trim(QResult(0, y))
InCombined = False
For a = LBound(CombinedList) To UBound(CombinedList)
If vAr = CombinedList(a) Then InCombined = True
Next a
InOpen = False
For a = LBound(OpenList) To UBound(OpenList)
If vAr = OpenList(a) Then InOpen = True
Next a
If vAr <> "Defect" And vAr <> vbNullString And vAr <> "" Then
If InCombined = False And InOpen = False Then
set rst = db.OpenRecordSet ("UPDATE [VDefects] SET [VDefects].Status ='Bad Defect Number' WHERE ((([VDefect].Defect)='"& vAr &"'));")
Debug.Print "BAD "; vAr
ThisWorkbook.Sheets("Sheet2").Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = "Bad"
ThisWorkbook.Sheets("Sheet2").Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(0, 1).Value = vAr
End If
If InCombined = True And InOpen = False Then
set rst = db.OpenRecordSet ("UPDATE [VDefects] SET [VDefects].Status ='Completed' WHERE ((([VDefects].Defect)='"& vAr &"'));")
Debug.Print "CLOSED "; vAr
ThisWorkbook.Sheets("Sheet2").Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = "Closed"
ThisWorkbook.Sheets("Sheet2").Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(0, 1).Value = vAr
End If
If InCombined = True And InOpen = True Then
Debug.Print "OPEN "; vAr
ThisWorkbook.Sheets("Sheet2").Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = "Open"
ThisWorkbook.Sheets("Sheet2").Cells(Sheet2.Rows.Count, 1).End(xlUp).Offset(0, 1).Value = vAr
End If
End If
Next y
rst.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub