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.
How To Parse A Flat File In Excel VBA
In another post I demonstrated how to access a file on your computer using the MS Office Library. Here it is if you don’t know what I’m talking about. In this post, I am going to show you how to access the file and load it into your spreadsheet. I will do the same thing […]
How to pick a file to load In VBA
Picking a file to load in your Microsoft App is a very important skill to know. In this blog post you will see how to do it. First you need to set a reference to the MS office object library From VBE editor –> select Tools > MS office object library (click check mark) Sub […]
How can I interact with other Office applications (Excel) using VBA in Access?
Need to write your Access data or query to an Excel file? Here is the how to do it: Most people are familiar with Excel and know how to use it well (enough), and when you start talking about Access, they get scared off, and don’t know what to do anymore. Well, here you are […]
What Is Microsoft Access Used For?
To those of you who are asking the question of “What is microsoft access used for?” , here is my opinion. I’ve been using this program for well over 15 years, and it’s become fairly easy to deal with. Many people feel that it is hard to work with, but that’s not my experience anymore […]