Form button to scan documents to file folder

[jjake]

Is there a way to create a button on a form that will allow me to scan documents into a file folder and save the path in a field for later reference?


User clicks button > Opens a prompt to save to file > Opens default scanner interface > User scans documents which saves filepath to current record

 

[theDBguy]

I know it's possible, but I don't have any example for you. I just know someone here has done it before, so I'm hoping he'll see this post and jump in. Otherwise, you might also try looking into DBPix. Just a thought...

 

[jjake]

I did download DBpix but i think it wasn't what i was looking for, it was more for image management.

after scouring the net i did find this module

Option Compare Database
Option Explicit

Public Sub GetImage()
    Dim fileLocation As String
    Dim diagFile As FileDialog
    
    Set diagFile = Application.FileDialog(msoFileDialogSaveAs)
    
    diagFile.Title = "Save Bitmap File As..."
    diagFile.InitialFileName = "*.bmp"
    
    If diagFile.Show Then
        fileLocation = diagFile.SelectedItems(1)
        
        Dim scanDiag As Object
        Dim image As Object
        
        Set scanDiag = CreateObject("WIA.CommonDialog")
        Set image = CreateObject("WIA.ImageFile")
        
'        Dim scanDiag As New WIA.CommonDialog
'        Dim image As WIA.ImageFile
        
        Set image = scanDiag.ShowAcquireImage()
        image.SaveFile fileLocation
        
    End If
    
End Sub

I'm calling the sub with my command button,

How would i modify the file location to a specific directory then save this to my record path?

 

[theDBguy]

Hi. To specify a specific directory is easy, but what did you want to use for the filename?

 

[jjake]

[UserName] + customtext + current date

Ford Truck Insurance Papers 02.25.2019

 

[theDBguy]

[UserName] + customtext + current date

Ford Truck Insurance Papers 02.25.2019

Okay, in that case, you could try it this way:

Option Compare Database 
Option Explicit  

Public Sub GetImage()     

Dim fileLocation As String     
     
fileLocation = "c:\YourSpecificFolderNameHere\" _
         & [Username] & "CustomTextHere" & Format(Date(),"mm.dd.yyyy") & ".bmp"                  

Dim scanDiag As Object         
Dim image As Object                  

Set scanDiag = CreateObject("WIA.CommonDialog")         
Set image = CreateObject("WIA.ImageFile")         

'        Dim scanDiag As New WIA.CommonDialog 
'        Dim image As WIA.ImageFile                  

Set image = scanDiag.ShowAcquireImage()        
image.SaveFile fileLocation           

End Sub

 

[jjake]

i got the following,

External name not defined on the [UserName]

Also this button is on a subform as well as the field.

 

[theDBguy]

Okay, where are you executing this code from? Do you have any reference to [Username] on it? If not, you could figure it out later. For now, we just want to check if the code you found will work for you. So, try replacing (or removing) [Username] with something literal, like "Ford Truck."

 

[jjake]

Yes this worked! It did save the format incorrectly though.

Ford TruckInsurance Papers02.25.2019

The button is on the subform where the field is on click event.

 

[theDBguy]

Yes this worked!, it did save the format incorrectly though.

Ford TruckInsurance Papers02.25.2019

Okay, so the next step is to figure out where you'll get the information for [Username] and also just add spaces between them. For example, once you've figured it out, try:

[Username] & " CustomText " & Format(Date(),"dd.mm.yyyy")

Please notice the extra spaces in the middle.
So, where is Username supposed to come from?
Edit: Looks like you added this:

The button is on the subform where the field is on click event.

If so, try it this way:

Me.[Username] & " something here " & Format(Date(),"dd.mm.yyyy")

 

[jjake]

Me.[Username] & " something here " & Format(Date(),"dd.mm.yyyy")

i got the following,

invalid use of me keyword

 

[jjake]

invalid use of me keyword

I figured it out based on this post - https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/invalid-use-of-me-keyword

& Forms!frm1!frm2.Form.UserName & "Insurance Papers" & Format(Date, "mm.dd.yyyy") & ".bmp"

 

[theDBguy]

Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[jjake]

One more thing. How would i save this file path to the record it was saved from so i may view it later from a form?

 

[theDBguy]

You could probably do something similar to your Forms!... syntax. For example:


Forms!... = fileLocation