Field Inspection App In MS Access

Today, we will consider how to track field inspections, by building a framework for a field inspection app in MS Access.

This sample application is just a framework, and just shares some potential ideas. You can customize it and put your own “spice” on it.

Here’s what it will look like when we’re done:

First we have a few tables where we’ll store our information. Remember that the table is the only database object that stores information! (Forms, Queries, and Reports DO NOT STORE INFORMATION)

Here are the tables with field names:

There are really only 2 main forms here, the one is the main form, and the other is the subform.

The main form pertains to the main thing we want to track, and that’s inspections.

Here are the main questions we want to track:

–Who is doing the inspection?
–What department are we inspecting?
–What date did the inspection occur?

The main form data is coming out of the “tblVehRecInspections” table.

The subform is just a form in datasheet format which links to the inspection on the main form.

*I prefer to use the datasheet for my subforms when I can because it’s faster to implement.

The subform’s data is coming from the “tblFindings” table.

I am linking the subform to the main form on the “inspection id”. diID = finVehInspectionsID

How does it work, Erik?

Basically an employee would go out and do an inspections based on some form (printed report based on tblFindingType), which contains some specific “findings”. Then they would return, and select their name, date of inspection, and whether they passed a finding (check) or failed (no check).

…and there is no code yet.

Here’s where the code comes in:

The only code so far on this form is on those “Pick Report” options:

When you select an option, and click “Create Report”, the code will decide what to do based on the button clicked.

Currently, just the selected record can be clicked. I’ll add more options in future posts.

Private Sub btnCreate_Click()
    Select Case Me.fraPickReport
        Case 1
            DoCmd.OpenReport "rptInspections", acViewPreview, , , , 1
            
        Case 2
            MsgBox "Under Construction"
        Case 3
            MsgBox "Under Construction"
        Case 4
            MsgBox "Under Construction"
    
    End Select
End Sub

In the above code, under option 1, we will pass an argument to the report, much like we can pass open arguments to forms, and do an action based on that open argument.

Here, the report “rptInspections” deals with the open argument:

Private Sub Report_Open(Cancel As Integer)
    If Len(Me.OpenArgs) Then
        Select Case Me.OpenArgs
            Case 1
            
                Me.RecordSource = "SELECT * FROM qryInspections WHERE diID=" & Forms![frmASRTool]![diID]
            Case 2
                MsgBox "Under Construction"
            Case 3
                MsgBox "Under Construction"
            Case 4
                MsgBox "Under Construction"
        End Select
    End If
End Sub

Then the other button “Excel Export” will convert the contents of the Access report into an Excel spreadsheet, so we can have 2 different formats to view the report in, pdf and Excel.

Here is the VBA Excel automation code:

Private Sub btnExcel_Click()

    Dim rst As Recordset

   
    Dim strSQL As String
   
    'Dim objExcelBook As Excel.Workbook
    'Dim objExcelSheet As Excel.Worksheet
   
    'USING EARLY BINDING
    'Dim objExcelApp As Excel.Application
    'Dim objExcelBook As Excel.Workbook
    'Dim objExcelSheet As Excel.worksheet
   
    ''USING LATE BINDING
    Dim objExcelApp As Object
    Dim objExcelBook As Object
    Dim objExcelSheet As Object

    Dim strTemplate As String
    Dim intExcelRow As Integer
   
    'TEMPLATE MUST BE SAVED AS TEMPLATE FOR CORRECT FUNCTIONALITY
    strTemplate = CurrentProject.Path & "\report.xlsx"
   
    'Set m_objExcelApp = New Excel.Application
    On Error Resume Next
   
    strSheet = "Sheet1"
 
    DoCmd.Hourglass True
    '-------------------------------------------------------
    'STEP 1: EXPORT THE CHART DATA
    '-------------------------------------------------------
    strSQL = "SELECT * FROM qryInspectionsExcel WHERE diID = " & Me.diID
 
    '-------------------------------------------------------------
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
       
    'don't do all the excel stuff if there is no data...
    If Not rst.EOF Then
       
        'this results in an error, not fatal so resume
        Set objExcelApp = GetObject(, "Excel.Application")
        If objExcelApp Is Nothing Then
            Set objExcelApp = CreateObject("Excel.Application")
        End If
   
        Set objExcelBook = objExcelApp.Workbooks.Add(strTemplate)
        Set objExcelSheet = objExcelBook.Worksheets(strSheet)

        
        objExcelApp.Visible = True
        
        objExcelSheet.Range("A1") = "Employee: "
        objExcelSheet.Range("A2") = "Date: "
        
        objExcelSheet.Range("A1").Font.Bold = 1
        objExcelSheet.Range("A1").Font.Name = "Calibri"
        objExcelSheet.Range("A2").Font.Bold = 1
        objExcelSheet.Range("A2").Font.Name = "Calibri"
        
        objExcelSheet.Range("B1") = rst.Fields("empName")
        objExcelSheet.Range("B2") = rst.Fields("diInspectionDate")
        objExcelSheet.Range("B2").NumberFormat = "m/d/yyyy"

        
        intExcelRow = 4
        
        Do Until rst.EOF
            objExcelSheet.cells(intExcelRow, 1) = rst.Fields("ftName")
            
            
            intExcelRow = intExcelRow + 1
            rst.MoveNext
        Loop

        rst.Close
        Set rst = Nothing
        
   
        'CLOSE EXCEL REFERENCES:
        Set objExcelSheet = Nothing
        Set objExcelBook = Nothing
        'objExcelApp.Quit
        'Set objExcelApp = Nothing
       
 
        DoCmd.Hourglass False
 
    Else
 
       
      rst.Close
      Set rst = Nothing
 
     
 
    End If
End Sub

I talked about this in another post How To Have Access Export A Table To An Excel XLSX In VBA

However, this one offers you a bit more simplicity and control.

Stay tuned for future updates.

Let me know if you have any questions.

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!