Application Manages Quotes in the Construction Industry

Application Profile

Name	Quotation Management
Tasks	Processing customer inquiries regarding planned construction projects, from receipt of email to submission of quotation
Users	About 10, local and remote via Terminal Server
Software Versions	Office/Access 365, SQL Server 2019, Power Apps with Dataverse for Teams
Notable Features	Integrated mail client, design manager, live connection to SAP, import and output of GAEB files, connection to Pipedrive via WebHooks, connection of field staff via Power Apps
Company	cm IT-Service, Reken, Germany
Author	Bernd Siebelist (Owner) Adapted for AFo by Karl Donaubauer

A company in the German construction industry had previously received incoming inquiries via Outlook, distributed them to the responsible employees for processing with Excel lists, and created the resulting quotes using industry-specific software. They were looking for an integrating solution to speed up processes and create transparency. Over the past five years, this Access application has been developed, which is now capable of doing more than the customer could have hoped for.

The Data Flow

All data is stored in SQL Server, with the exception of a few temporary tables. All Access forms are unbound, and all CRUD actions are controlled by Stored Procedures. This means that only a few lines of code are required in each form.

After logging in the users receive their own dashboard displaying their assigned inquiries:

Main form of the application with "My Inquiries" active

Inquiries from customer emails are handled with the integrated mail module. The emails are retrieved and sent from an Exchange Server using a DLL and OAuth2.0 verification.

Send email screen of the mail module

For further processing, the emails are written to a table in SQL Server and the attachments are saved in the file system. Customer and project data is carried over automatically: The text of the emails and attached PDFs is searched for specific keywords. Values such as dates, addresses, project names, and inquiry numbers are determined using Regular Expressions and written into the created inquiry.

Automatic takeover of data from emails and PDFs

From the inquiry, an offer/quote will then be created. Items from any existing reference offers can be dragged and dropped (using an ActiveX grid control) into the new offer. After entering an item number, prices and conditions are queried directly via a SOAP interface from SAP. The quote can be saved as a PDF or GAEB file (exchange format of the German construction industry) and sent with the integrated email function. In addition, there's a connection to the cloud CRM Pipedrive via WebHooks.

An indivdual statistics dashboard and module, in which the user creates and saves any evaluations, are also included.

Special Design Features

The application contains many self-designed controls to obtain functionalities that cannot be implemented with built-in features of Access, or only with great effort (color gradients, rounded corners, StackPanel, ScrollViewer, etc.). These are based on an ActiveX control that gets programmed with the markup language XAML. For example, the sidebar of the main form was created this way:

Sidebar folded and unfolded

Or the tab control used in several forms:

Another special feature is the built-in design module. A new color scheme can be created within a minute by simply entering a name, a base color, and a fade value (lighter/darker). The corresponding colors for the background, font, etc. are then created using a predefined template. Each element, like a tab color, can be customized. The effect is shown immediately in the preview area:

Design screen

This is what the application looks like in dark mode, for example:

All these design features enable a contemporary UI and address objections that Access applications look outdated. I monitor the selected DesignIDs and therefore know that each user chooses a different design and also changes it.

Achievements and Perspectives

Over time, using this integrated Access application has enabled the customer to process almost 100 inquiries per day instead of around 20. Furthermore, a lot of additional data can now be collected, (re-)used and evaluated.

The connection to the field staff using Power Apps with the data stored in Dataverse for Teams is currently in the planning/implementation phase.

The Showcase section presents interesting Access projects to demonstrate the current range of usage and capabilities of the product. Interesting can refer to technical, organizational or thematic aspects. If you would like to suggest an application for an article, please contact us using this form.