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.

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:

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:

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.

Free! Subscribe To Our YouTube Channel!

facebooktwittergoogle_plusredditpinterestlinkedinmailby feather


Call Erik Now