how to capture customer's signature on quote form

MIA · Mar 12, 2013

I'm developing a database for a subcontractor who will send his technicians to job sites. He wants the technicians to use a tablet of some sort (possibly iPads) to access the database (although not sure how...suggestions welcome). He would like the customer to be able to sign the quote directly on the tablet using some sort of stylus pen. I need to know how to capture and store their signature in the database. What kind of field would I use on my form? And is there some special code required to interface the stylus signature entry with the database? I tried to search the forums already but have not succeeding in finding a relevant answer. If anyone can point me in the right direction that would be great. Thanks!

MIA

Mr. SLP · Dec 25, 2017

Did you ever figure this out? I’m trying to do pretty much the same thing and I am stumped.

Sent from my iPad using Tapatalk

CJ_London · Dec 25, 2017

not free, but you can download a demo for free here and buy it if it meets your needs

https://www.microsoftaccessexpert.com/Microsoft-Access-Signature-Capture-Database.aspx

also this supplier

https://www.topazsystems.com/index.html

supplies signature pads and apparently has a demo in access

There are some references to it on this forum - search for Topaz

There are a number of other suppliers as well - google 'access capture a digital ink signature' or similar.

You may also find this link useful

https://msdn.microsoft.com/en-us/library/ms812501.aspx

isladogs · Dec 26, 2017

You can do this within Access without buying anything.
To do so, use an ActiveX control called Microsoft InkPicture Control

However, its clunky & needs a bit of effort to get half way acceptable results.
I managed to save & retrieve signatures within a few minutes BUT reloading a signature caused an error which I haven't yet solved. May look at this again in a day or so

However, I've since found a similar example online by IrogSinta
https://www.experts-exchange.com/questions/28218072/Access-2003-Capture-Signature.html

Its far from perfect but it does work.
This saves signatures as OLE objects so the database will rapidly grow as more signatures are saved. Suggest saving as external GIF files instead

I've tweaked the code slightly including adding an on screen keyboard when in tablet mode.

Attached are 2 zip files:
a) SignatureInk - demo database
b) SignatureInkVideo - an embarrassingly bad demo of me doing a signature on a tablet with a touchscreen

AccessBlaster · Dec 26, 2017

Just curious, what kind of device will you be using?

isladogs · Dec 26, 2017

I can't speak for the OP but I have a Windows tablet with Access 2010 installed.
That's what I used for my video.

jleach · Dec 26, 2017

You could use a web browser control and any one of the javascript/jquery libraries as well. Usually with the JS solutions you'd get the signature as an image in Base64 encoding which can either be saved directly to the DB or to disk.

Wayne · Dec 26, 2017

i also have to get my customer's signatures, but I didn't want to bloat the database with saved signatures. I output the work order to pdf format, and have the customer sign it on my tablet, I then save a link to the document in a field on a subform on each order form. Simple, but it works well.

When I was saving the signed documents and job photos as attachments in an attachment field, I was getting dangerously close to the 2 gb limit for Access.

AccessBlaster · Dec 26, 2017

One of our school districts use iPads to do delivery's and retrieve signatures. iPads are a fairly cheep consumable item. But you still have to interface with it.

I guess my point is, you still need network support, and that doesn't come easy. Sometimes it just easier to buy a sandbox or canned app, that specifically addresses your needs.

Pat Hartman · Dec 26, 2017

The Surface Pro is a real computer and that makes it more expensive than other tablets. However, it is capable of running Windows and normal desktop applications. So, with a keyboard and monitor, you could use it as a work computer. The nice thing about using it as a tablet is you get to use a stylus which works for entering a signature.

AccessBlaster · Dec 26, 2017

I can't imagine using that in a real world situation. It probably needs to be mobile, which means Wi-Fi and a cart. All your customers need to come to you. You can't take it with you easily, if your out of range of the Wi-Fi out comes the network connection. etc.

BTW the stylus is the first thing to go, the finger nail is the tool of choice.

isladogs · Dec 26, 2017

A Surface Pro sounds excellent but as Pat says it is expensive.

For comparison, my Windows tablet is a Linx 1010 which comes with detachable keyboard and was inexpensive (approx £130) when I bought it 2 years ago.
Interestingly the price has gone up a LOT since then.
https://www.amazon.co.uk/Linx-1010B-10-1-Tablet-Black/dp/B014D847FS

It runs the full version of Office 2010 with ease & can also be used as a work computer if necessary. Wifi etc but only 2GB of RAM.
It doesn't have a stylus and from experience with an earlier mini tablet, I wouldn't use it anyway.

Obviously the usual issues with any database if you tried to use it with Wifi.
To use it in the 'wild' would require synchronising data at the start/end of the day.
However there's no real reason why the idea that started this thread couldn't be used with this tablet or a similar device

AccessBlaster · Dec 26, 2017

While your busy researching and building this one off solution, your boss is wondering why he/she isn't seeing any production.

These issues were solved a decade ago, its why you don't see any major carriers hauling Surface Pro's around to get signatures.

isladogs · Dec 26, 2017

Despite Access Blaster's obvious lack of enthusiasm ....

I thought I'd also try the other other similar Active X control, Microsoft Ink Edit.
This converts ink input to text.
It seems very good at recognising letters / words

Very simple example attached together with a quick (zipped) video of me attempting to write, but this time using a mouse ....

In this case, possibly useful for a young child practising writing but not sure what else ....

Luckily I work for myself so no boss to breathe down my neck!

However, I'm already adding the signature idea to my UK postal addresses database which is intended to be used on a tablet e.g. for home deliveries as well as in the office!

Pat Hartman · Dec 26, 2017

@AccessBlaster
This is an Access topic so presumably, the poster wants to run Access. That eliminates any solution that does not run Access. I'm not saying whether I agree or disagree with the concept of using Access for this particular purpose. Surface Pro's are tablets so "lug them around" is a bit much since you wouldn't say that about an iPad. The point I was making is that if you don't want to buy something that becomes a doorstop (iPad) without an internet connection, the Surface Pro, although expensive, at least has other uses - including the ability to run the application WITHOUT an internet connection.

AccessBlaster · Dec 26, 2017

While I agree with the above posts, and having a bit of fun

. There is a reason why this post hasn't had any response since 2013.

You can use ActiveX in your design, the thing you can't predict is whether Microsoft will include or support that particular ActiveX in their next roll out.

Wayne · Dec 26, 2017

For me - I use a Surface Pro, because it does run Access, but it is expensive (I paid over $1,000.00 for it), and it is no bigger than an iPad. But for my two other techs on the road (they use their phones or iPads), I create a work order report, output it to pdf, and email it to them (all automated with vba). They capture the signatures using Adobe Fill and Sign (downloaded for free), using a stylus on their phone or iPad, and email it back to the office, where it is invoiced to the client. Before the techs arrive at their next job site, our client already has a signed proof of delivery, together with their invoice. They get signed documents, within minutes of actual service delivery. Not quite real-time, but close enough.

It is a simple, cost-effective, and PRODUCTIVE solution, without bloating the database with saved images, and with minimal cost, and minimal training. It would be nice to have captured signatures in the database to send to the client, but I do not have the resources of FedEx or UPS.

For our small business, it is a workable solution, and coincidentally, our system was built with ideas I learned from reading this forum. For all of you, Happy Holidays, and thank you all for the help you have given us over the years. Keep the inspiration coming.

Wayne

isladogs · Dec 26, 2017

Wayne

Perhaps you could post an example of your approach

Wayne · Dec 27, 2017

Colin,

The code to produce the work order (Based on a Click Event on a Command Button). The WHERE clause is in the SQL Select Query of the report to open the work order for the current record:

Code:

If Len(Dir("filepath" & [Forms]![frmOrders]![OrderNumber] & " " & [Forms]![frmOrders]![ClientUserlastName] & " POD.pdf")) > 0 Then
        If MsgBox("The file you are trying to create already exists. Would you like to overwirite the existing file?", vbYesNo, "File Exists") = vbYes Then
            DoCmd.OpenReport "rptWorkOrder", acViewPreview, , "[OrderNumber] = [Forms]![frmOrders]![OrderNumber]"
            DoCmd.SelectObject acReport, "rptWorkOrder"
            DoCmd.OutputTo acOutputReport, "rptWorkOrder", acFormatPDF, ("filepath" & [Forms]![frmOrders]![OrderNumber] & " " & _
            [Forms]![frmOrders]![ClientUserlastName] & " POD.pdf"), , , , acExportQualityPrint
            DoCmd.Close acReport, "rptWorkOrder", acSavePrompt
            DoCmd.Close acForm, "frmPDFSelect", acSavePrompt
            MsgBox "Your PDF file has been created, and saved to " & ("filepath" & [Forms]![frmOrders]![OrderNumber] & " " & _
            [Forms]![frmOrders]![ClientUserlastName] & " POD.pdf"), vbInformation, "File Created"
            Else
            DoCmd.Close acForm, "frmPDFSelect", acSavePrompt
            Exit Sub
        End If
    Else
            DoCmd.OpenReport "rptWorkOrder", acViewPreview, , "[OrderNumber] = [Forms]![frmOrders]![OrderNumber]"
            DoCmd.SelectObject acReport, "rptWorkOrder"
            DoCmd.OutputTo acOutputReport, "rptWorkOrder", acFormatPDF, ("filepath" & [Forms]![frmOrders]![OrderNumber] & " " & _
            [Forms]![frmOrders]![ClientUserlastName] & " POD.pdf"), , , , acExportQualityPrint
            DoCmd.Close acReport, "rptWorkOrder", acSavePrompt
            DoCmd.Close acForm, "frmPDFSelect", acSavePrompt
            MsgBox "Your PDF file has been created, and saved to " & ("filepath" & [Forms]![frmOrders]![OrderNumber] & " " & _
            [Forms]![frmOrders]![ClientUserlastName] & " POD.pdf"), vbInformation, "File Created"
    End If

The code to email it to the tech (also on a Click Event for another Command Button):

Code:

Private Sub btnEMailLeadTech_Click()

    On Error GoTo Err_btnEMailLeadTech_Click

    'Define some object variables for Outlook
    Dim olApp As Outlook.Application
    
    'The NameSpace object allows you to reference folders
    Dim olNS As Outlook.NameSpace
    Dim olFolder As Outlook.MAPIFolder
    
    'Create a reference to the email item you will use to send your email message
    Dim olMailItem As Outlook.MailItem
    Dim strBodyText As String
    Dim strEMail As Variant
    Dim Signature As Outlook.Items
        
    'Create the Outlook Object
    Set olApp = CreateObject("Outlook.Application")
    Set olNS = olApp.GetNameSpace("MAPI")
    Set olFolder = olNS.GetDefaultFolder(olFolderInbox)
    Set olMailItem = olFolder.Items.Add("IPM.Note")
    
    'Create the string for the email address
    strEMail = DLookup("[EmpEmailAddress]", "tblEmployees", "[EmpFullName] = '" & Me.LeadTechnician & "'")
    
    'Create the body of the message from the data in the form
    If IsNull(Me.ClientNotes) Then
        strBodyText = "<html><font face=Arial><font size=3>" & _
        "Copy of Work Order for " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName & " - Service Date set for " & _
        Format(Me.ServiceDate, "mmmm, dd, yyyy") & "." & "<br>" & vbCrLf & _
        "Currently booked for arrival/start time of " & Format(Me.ApptTime, "h:mm AMPM") & " - " & _
        Format(Me.ApptTimeEnd, "h:mm AMPM") & "." & "<br><br>" & vbCrLf & vbCrLf & _
        "Wayne" & "<br>" & vbCrLf & _
        "<b>" & "PSS Inc." & "</b><br>" & vbCrLf & _
        "<A HREF=url>" & "my website" & "</A><br>" & vbCrLf & _
        "(647) 926-5121" & "<br>" & vbCrLf & _
        "<picture><img src='Logo url path' width='160' height='100'></picture><br>"
    Else
        strBodyText = "<html><font face=Arial><font size=3>" & _
        "Copy of Work Order for " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName & " - Service Date set for " & _
        Format(Me.ServiceDate, "mmmm, dd, yyyy") & "." & "<br>" & vbCrLf & _
        "Currently booked for arrival/start time of " & Format(Me.ApptTime, "h:mm AMPM") & " - " & _
        Format(Me.ApptTimeEnd, "h:mm AMPM") & "." & "<br><br>" & vbCrLf & vbCrLf & _
        "<u><b>" & "Special Notes:" & "</u></b>" & " " & Me.ClientNotes & "<br><br>" & vbCrLf & vbCrLf & _
        "Wayne" & "<br>" & vbCrLf & _
        "<b>" & "PSS Inc." & "</b><br>" & vbCrLf & _
        "<A HREF=url>" & "www.paxall.ca" & "</A><br>" & vbCrLf & _
        "(647) 926-5121" & "<br>" & vbCrLf & _
        "<picture><img src='Logo url path' width='160' height='100'></picture><br>"
    End If
    
    If Len(Dir("filepath" & Me.OrderNumber & " " & _
                Me.ClientUserlastName & " POD" & ".pdf")) = 0 Then
        MsgBox "The Work Order you are trying to attach does not exist in the directory selected.", vbInformation, "Attention"
        Exit Sub
        End If
        If IsNull(Me.ApptTime) Then
        MsgBox "You haven't entered an appointment start time. You must have a start time.", vbInformation, "Attention"
        Exit Sub
        Else
    'Update the new email object with the form data
    With olMailItem
        .Subject = Me.OrderNumber & " - " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName
        .To = Replace(Mid(strEMail, InStr(1, strEMail, ":") + 1), "#", "")
        .BCC = "my email"
        .ReadReceiptRequested = True
        .HTMLBody = strBodyText
        .Importance = olImportanceHigh
        .Display
        .Attachments.Add "filepath" & Me.OrderNumber & " " & _
                Me.ClientUserlastName & " POD" & ".pdf"
        
        
        End With
        End If
    
    'Release all of the object variables
    Set olMailItem = Nothing
    Set olFolder = Nothing
    Set olNS = Nothing
    Set olApp = Nothing
    
Exit_btnEMailLeadTech_Click:
    Exit Sub
    
Err_btnEMailLeadTech_Click:
    MsgBox Err.Description, vbInformation, "Error"
    Resume Exit_btnEMailLeadTech_Click
    
End Sub

The tech will use his phone app (or iPad app) with Adobe Fill and Sign to capture the signature, save the pdf (with signature), and email it back to our office. We create the invoice, and email it to the client as an attachment, together with the signed proof of delivery using this code:

Code:

Private Sub btnSendEMail_Click()

On Error GoTo Err_btnSendEMail_Click

    'Define some object variables for Outlook
    Dim olApp As Outlook.Application
    
    'The NameSpace object allows you to reference folders
    Dim olNS As Outlook.NameSpace
    Dim olFolder As Outlook.MAPIFolder
    
    'Create a reference to the email item you will use to send your email message
    Dim olMailItem As Outlook.MailItem
    Dim strBodyText As String
    Dim Signature As Outlook.Items
        
    'Create the Outlook Object
    Set olApp = CreateObject("Outlook.Application")
    Set olNS = olApp.GetNameSpace("MAPI")
    Set olFolder = olNS.GetDefaultFolder(olFolderInbox)
    Set olMailItem = olFolder.Items.Add("IPM.Note")
    
    'Create the body of the message from the data in the form
    strBodyText = "<html><font face=Arial><font size=3>" & _
    "Update - " & Me.TypeofNote & "<br>" & vbCrLf & _
    "Date: " & Format(Me.DateNoteSent, "mm/dd/yyyy") & "<br>" & vbCrLf & _
    "Time: " & Format(Me.TimeNoteSent, "hh:mm AMPM") & "<br>" & vbCrLf & _
    "__________________________________________________" & "<br><br>" & vbCrLf & vbCrLf & _
    Me.NoteDetails & "<br><br>" & vbCrLf & vbCrLf & _
    "Wayne" & "<br>" & vbCrLf & _
    "<b>" & "PSS Inc." & "</b><br>" & vbCrLf & _
    "<A HREF=url>" & "my website" & "</A><br>" & vbCrLf & _
    "(647) 926-5121" & "<br>" & vbCrLf & _
    "<picture><img src='Logo url path' width='160' height='100'></picture><br>"
    
    'Update the new email object with the form data
    If IsNull(Me.Parent.Text68) Then
        With olMailItem
        .Subject = Me.OrderNumber & " - " & Me.Parent.Text61
        .To = Replace(Mid(Me.Parent.Text63.Value, InStr(1, Me.Parent.Text63, ":") + 1), "#", "")
        .BCC = "my email"
        .ReadReceiptRequested = True
        .Attachments.Add "logo filepath", , 0
        .HTMLBody = strBodyText
        .Importance = olImportanceHigh
        .Display
        .Attachments.Add "filepath" & "L" & [Forms]![frmOrders]![InvoiceNumber] & " " & _
                [Forms]![frmOrders]![CustNumber] & " Invoice - " & [Forms]![frmOrders]![ClientUserlastName] & ".pdf"
        .Attachments.Add "filepath" & (Me.Parent.OrderNumber) & " " & _
                (Me.Parent.ClientUserlastName) & " POD.pdf"
                
        End With
    Else
        With olMailItem
        .Subject = Me.OrderNumber & " - " & Me.Parent.Text61
        .To = Replace(Mid(Me.Parent.Text63.Value, InStr(1, Me.Parent.Text63, ":") + 1), "#", "")
        .CC = Replace(Mid(Me.Parent.Text68.Value, InStr(1, Me.Parent.Text68, ":") + 1), "#", "")
        .BCC = "my email"
        .ReadReceiptRequested = True
        .Attachments.Add "logo filepath", , 0
        .HTMLBody = strBodyText
        .Importance = olImportanceHigh
        .Display
        .Attachments.Add "filepath" & "L" & [Forms]![frmOrders]![InvoiceNumber] & " " & _
                [Forms]![frmOrders]![CustNumber] & " Invoice - " & [Forms]![frmOrders]![ClientUserlastName] & ".pdf"
        .Attachments.Add "filepath" & (Me.Parent.OrderNumber) & " " & _
                (Me.Parent.ClientUserlastName) & " POD.pdf"
                
        End With
    End If
    
    'Release all of the object variables
    Set olMailItem = Nothing
    Set olFolder = Nothing
    Set olNS = Nothing
    Set olApp = Nothing
    
Exit_btnSendEMail_Click:
    Exit Sub
    
Err_btnSendEMail_Click:
    MsgBox Err.Description, vbInformation, "Error"
    Resume Exit_btnSendEMail_Click
    
End Sub

We have been using this for almost 2 years without any problems. We save a link to the documents, and job pictures, in a field on a subform for each order. The link is a hyperlink of the filepath to the document or picture, and can be viewed by double-clicking the link. It is opened by the default photo viewer, or Acrobat, depending on the file type.

As I said, both you and pbaldy helped me come up with the coding and design of our system, both directly (me posting questions) and indirectly (I read the forum threads a lot). You gave me enough clues to find a way to build a working database. Many thanks to all of you at AWF for the insight and the inspiration to help us build our business. I don't know where we would be without AWF.

Cheers,

Wayne

isladogs · Dec 27, 2017

Thanks for posting this code Wayne

It all looks straightforward enough and I can see it will work well.
I hadn't thought of Adobe Fill and Sign as I've never used it.

You could consider combining the save signature and send email code to run from one button.

And of course using an inexpensive Windows tablet like the one I mentioned in a previous reply, this could all be done at the same time as the signature is collected

how to capture customer's signature on quote form

New member

Registered User.

Super Moderator

MVP / VIP

Attachments

Registered User.

Attachments

MVP / VIP

Registered User.

Crazy Canuck

Registered User.

Super Moderator

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Attachments

Super Moderator

Registered User.

Crazy Canuck

MVP / VIP

Crazy Canuck

MVP / VIP

Similar threads

Users who are viewing this thread