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.