Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    johnd is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Florida
    Posts
    9

    A button that does several things

    Howdy folks. I hope there is a answer to this one. I have a roadway history database (access 2016) running on a network with windows 10.
    What I am trying to do is complicated, so I hope I can make sense in explaining it.



    If users want to add a record there are several text boxes that their control source is Table 1. So updating that isnt the issue.
    On this same form I have a webbrowzer window that its control source is a common folder where all history items will go.

    Adding new information to the table is easy enough but I want to have a button that if the users want to add files or photos. By pressing the button they can create folder in the common history folder. This new folder would have the same record ID name as the record being created. Then to make it even more complicated, that folder location be added to Table 1 with the record being created.

    I hope this makes sense

    Thoughts?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you cannot create the ID until the record is saved. THEN you can click a button to make the folder.
    I find it best NOT to add files /photos to the db, instead, the field holds the path, and then you can open any item in the folder via:

    there's a default path where all files will go, (use full UNC path) : gvBase = "\\server\folder"
    then clicking the file storage button opens that ID
    sPath = gvBase & me.ID

    open the folder using the code below:
    Code:
    public gvBase = "\\server\folder\"
    
    sub btnFiles_click()
    sPath = gvBase & me.ID
    if not DirExists(sPath) then MakeDir(sPath)
    OpenNativeApp sPath
    end sub

    paste this code into a module. (Alt-F11, insert , module)
    Then it will open ANY file via its extension....
    .pdf files will open in acrobat,
    .doc files in word
    etc

    USAGE:
    OpenNativeApp "c:\folder\file.xls"
    'opens in excel
    or
    OpenNativeApp me.field
    'opens item in field in native app

    Code:
    Option Compare Database
    Option Explicit
    
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
    Private Declare Function GetDesktopWindow Lib "user32" () As Long
    Const SW_SHOWNORMAL = 1
    Const SE_ERR_FNF = 2&
    Const SE_ERR_PNF = 3&
    Const SE_ERR_ACCESSDENIED = 5&
    Const SE_ERR_OOM = 8&
    Const SE_ERR_DLLNOTFOUND = 32&
    Const SE_ERR_SHARE = 26&
    Const SE_ERR_ASSOCINCOMPLETE = 27&
    Const SE_ERR_DDETIMEOUT = 28&
    Const SE_ERR_DDEFAIL = 29&
    Const SE_ERR_DDEBUSY = 30&
    Const SE_ERR_NOASSOC = 31&
    Const ERROR_BAD_FORMAT = 11&
    
    Public Sub OpenNativeApp(ByVal psDocName As String)
    Dim r As Long, msg As String
    
    r = StartDoc(psDocName)
    If r <= 32 Then
        'There was an error
        Select Case r
            Case SE_ERR_FNF
                msg = "File not found"
            Case SE_ERR_PNF
                msg = "Path not found"
            Case SE_ERR_ACCESSDENIED
                msg = "Access denied"
            Case SE_ERR_OOM
                msg = "Out of memory"
            Case SE_ERR_DLLNOTFOUND
                msg = "DLL not found"
            Case SE_ERR_SHARE
                msg = "A sharing violation occurred"
            Case SE_ERR_ASSOCINCOMPLETE
                msg = "Incomplete or invalid file association"
            Case SE_ERR_DDETIMEOUT
                msg = "DDE Time out"
            Case SE_ERR_DDEFAIL
                msg = "DDE transaction failed"
            Case SE_ERR_DDEBUSY
                msg = "DDE busy"
            Case SE_ERR_NOASSOC
                msg = "No association for file extension"
            Case ERROR_BAD_FORMAT
                msg = "Invalid EXE file or error in EXE image"
            Case Else
                msg = "Unknown error"
        End Select
    '    MsgBox msg
    End If
    End Sub
    
    Private Function StartDoc(psDocName As String) As Long
    Dim Scr_hDC As Long
    
    Scr_hDC = GetDesktopWindow()
    StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:\", SW_SHOWNORMAL)
    End Function
    
    Public Function DirExists(ByVal pvDir) As Boolean
    Dim FSO
    Set FSO = CreateObject("Scripting.FileSystemObject")
    DirExists = FSO.FolderExists(pvDir)
    Set FSO = Nothing
    End Function
      
    Public Sub MakeDir(ByVal pvDir)
    Dim FSO
    Set FSO = CreateObject("Scripting.FileSystemObject")
    If Not FSO.FolderExists(pvDir) Then FSO.CreateFolder pvDir     'MkDir pvDir
    
    Set FSO = Nothing
    End Sub

  3. #3
    johnd is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Florida
    Posts
    9
    Thanks for the reply. After I got some sleep I thought it might be better to show (pictures) what I am looking for.

    I'm not wanting to add the photos or files to the table instead I want to add the location of the folder they located in to the table.

    The second part of the code above I'm not even going to pretend I know where that goes and what it does. (No disrespect)

    Trying to think of a way to do this in steps. I am thinking users will type the information into the text boxes, hit save. Then I can make visible the "Add Files" button. Click that and it could open a form that creates new folder in the History folder (on the server). I think the first part of the code above might work for that. I mean I can also right click, create folder in webbrowser control.
    Now rename new folder the same as record ID and save that to the table.
    Thanks for any help

    Click image for larger version. 

Name:	problem 1.jpg 
Views:	36 
Size:	104.0 KB 
ID:	35496

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I am confused. Are users locating a folder or creating a folder?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    By pressing the button they can create folder
    So the end goal is to create a folder via Access vba, then store it's path into a table? Why would this be the preferred method when there is the Windows Explorer (file dialog) utility for creating the folder? All you seem to want is an empty folder in the end. But you say
    I'm not wanting to add the photos or files to the table instead I want to add the location of the folder they located in
    I presume "they" are the files, which don't seem to be dealt with in any of your code.

    Maybe what you need is to create a folder using Windows Explorer, then navigating to it by using Access vba, and when the user selects that folder you copy the path to the table? The code you have is too complicated for that (and probably wouldn't work anyway but I didn't study it too hard). What you'd need is the msoFileDialogFolderPicker.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    johnd is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Florida
    Posts
    9
    Thank you for the replies.

    @June7 they are creating a folder that users can place files and photos in. Can not send DB since it is on a state server.

    @Micro quote "So the end goal is to create a folder via Access vba, then store it's path into a table?" YES. With the ID of the current record being entered
    The reason for the control source of the webbrowser control to be just a folder is users can see, drag and drop and I don't have to enter every new file into or remove from the table.

    Please see photo of the form

    Thanks for any help..I know this is complicated....no not the form..understanding me.
    Attached Thumbnails Attached Thumbnails 123.jpg   123.jpg  

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Should DEERFILED be spelled DEERFIELD?

    Creating a new folder is simple:

    If Dir(strPath, vbDirectory) = "" Then MkDir strPath

    Assuming IT allows permissions to programmatically change file structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    johnd is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Florida
    Posts
    9
    Thank you..spelling is optional in rough examples

    Sorry for being dense. I am also assuming the record ID I have in the hidden textbox can name the folder but I'm not sure what that code would look like.

    Then there is a the second issue of getting that folder path into the table.

  9. #9
    johnd is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Florida
    Posts
    9
    I found this but not sure if its right

    Code:
    Sub CreateFolder(sFolder As String) 'Check if a folder exists, and if it doesn't, create folder with VBA MkDir
        If Len(Dir(sFolder, vbDirectory)) = 0 Then
            MkDir sFolder
        End If
    End Sub
    Im guessing that goes into the general declarations

    then

    Code:
    Private Sub Command12_Click()
    CreateFolder ("C:\history\& recordID.text")
    End Sub

    or am I way off

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Concatenate variables. Don't use .Text - you want .Value which is the default and need not be typed.

    CreateFolder ("C:\history\" & Me.recordID)

    If the path will always be D:\history\, really don't have to save into table. Can always build the path string with an expression when needed.
    Last edited by June7; 09-17-2018 at 12:02 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    johnd is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Florida
    Posts
    9
    Thanks for the reply, Im starting to get it.

    The reason for saving the path string to the table is the webbrowser control(at the bottom where it says "Files and Photos"), the path string of the newly created folder ends up being its control source.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yes, ControlSource can be an expression. Examples:

    ="C:\history\" & Me.recordID

    If the folder is saved in same location as the database:

    =CurrentProject.Path & "\history\" & Me.recordID
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    johnd is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Florida
    Posts
    9
    So I tired various versions of the code today at work and could not get anything to work.
    Seems pretty simple
    I put this in general Declarations
    Code:
    Sub CreateFolder(sFolder As String) 'Check if a folder exists, and if it doesn't, create folder with VBA MkDir     If Len(Dir(sFolder, vbDirectory)) = 0 Then
            MkDir sFolder
        End If
    End Sub
    In button On click Event
    Code:
    Private Sub Command12_Click()
    CreateFolder ("C:\history\"& "text74.text")
    End Sub
    Didnt work.
    Seems this should be fairly straight forward for the button to make a sub folder in a given location.
    Name it the same ID as the current record.
    Store the subfolders location in a given table.

    Heres an example of a subfolder location I have stored in a table.Click image for larger version. 

Name:	12345.jpg 
Views:	13 
Size:	252.9 KB 
ID:	35516

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Do not put variables within quote marks. If this is VBA, do not use .Text in this context.


    CreateFolder ("C:\history\" & Me.text74)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    johnd is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Florida
    Posts
    9
    Got ya

    Put CreateFolder ("C:\history" & Me.text74) in the on click event?

    Then all I have to deal with is getting the sub-folder location into the table. Maybe a invisible textbox that mirrors the subfolder location..and a update query to get it in the table??

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Speeding things up
    By aytee111 in forum Access
    Replies: 22
    Last Post: 09-22-2016, 07:06 AM
  2. An update query except for certain things?
    By Darla in forum Queries
    Replies: 14
    Last Post: 09-02-2016, 09:36 AM
  3. Lining things up
    By Lou_Reed in forum Access
    Replies: 19
    Last Post: 08-20-2015, 11:33 AM
  4. Help on many things
    By scarruth2 in forum Access
    Replies: 11
    Last Post: 09-04-2012, 06:27 PM
  5. Replies: 10
    Last Post: 01-27-2012, 01:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums