Results 1 to 11 of 11
  1. #1
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91

    how to open a dialog box to move files inside a folder that is setup as a curent folder

    Hi all,

    I'm trying to automate a process of selecting a set of file/s and move them in a folder. When I click on a button, it should open a current folder that is setup in the code.
    Lets say that I have a folder C:\documents\test, and very time I click on the button, it should open the dialog box with that path so I can select the files from another folder, drag them there and they will be saved in that folder.
    this will form part of wider automation that will send an email stating that those file/s where placed in that folder.
    I have in the same form where the button is placed, 3 check boxes that needs to be passed to the email as well that one or all the files where placed in the folder.

    Any ideas how to do this?



    Greetings.

  2. #2
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi all,

    I managed something so far :-)

    Code:
    Private Sub Command6_Click()
    Dim fd As FileDialog
    Dim check_1 As String
    Dim check_2 As String
    Dim check_3 As String
    
    If Me.[check_1] = True Then
    check_1 = "Point file"
    Else: checlk_1 = ""
    End If
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.InitialFileName = "C:\documents\ToStoreIn"
    fd.Show
    End Sub
    How can I now check if one of the check boxes is checke and then pass that value on?
    on the above, I get error in the first check box, there is something wrong, but no idea what.

    Greetings.

  3. #3
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi all,

    I get error 438 in this line:
    "If Me.[check_1] = True Then"

    Greetings.

  4. #4
    Mahendra1000 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    101
    Quote Originally Posted by megatronixs View Post
    Hi all,

    I managed something so far :-)

    Code:
    Private Sub Command6_Click()
    Dim fd As FileDialog
    Dim check_1 As String
    Dim check_2 As String
    Dim check_3 As String
    
    If Me.[check_1] = True Then
    check_1 = "Point file"
    Else: checlk_1 = ""
    End If
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.InitialFileName = "C:\documents\ToStoreIn"
    fd.Show
    End Sub
    How can I now check if one of the check boxes is checke and then pass that value on?
    on the above, I get error in the first check box, there is something wrong, but no idea what.

    Greetings.
    Am not an expert in Access but based on my knowledge I would like to ask if the Else statement is right or not. The Check_1 is in typo error in your code

  5. #5
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,

    I found the mistake :-) I was reffering the label of the check box instead of the check box itself
    (of course, and the typo too)
    Now I will try to pass the values to the email creation and it will almost be ready.

    Greetings.

  6. #6
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi all,

    I actually made it work :-)
    please find the full code below. I hope some one can make use of it :-)

    the setup is as follow: the form has 3 check boxes, a button to submit (creates the email) and one button to close the form.
    WHen a user wants to uploads files into a folder, he needs to check one or more of the check boxes with the type of files she/he will store in the folder. after doing so, she/he clicks the button and a file dialog box appears. The person just drags the files from there and when pressing OK, the resto of the codes prepares the email with listing the type of docuemt added to the folder and shows as a preview where the person just clicks the send button.

    Code:
     Private Sub Command6_Click()
    Dim fd As FileDialog
    Dim myval As String
    Dim myval2 As String
    Dim myval3 As String
    Dim Check0 As String
    Dim Check2 As String
    Dim Check4 As String
    Dim objItem As Object
    Dim oMail As Outlook.MailItem
    Set oMail = objItem
    Dim oApp As Object
    If Me.Check0 = True Then
    myval = "Point File"
    Else
    mywal = ""
    End If
    
    If Me.Check2 = True Then
    myval2 = "Word doc"
    Else
    myval2 = ""
    End If
    
    If Me.Check4 = True Then
    myval3 = "Description"
    Else
    myval3 = ""
    End If
    
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.InitialFileName = "C:\Documents\ToStoreIn"
    fd.Show
    Set oApp = CreateObject("Outlook.application")
    Set oMail = oApp.CreateItem(olMailItem)
    oMail.HTMLBody = "<HTML><HEAD><Font Size= 1><style> table, th, td </style> </HEAD> <BODY><br><p>" & _
    "Dear reader.</p>" & _
    "You have added the following file/s:<br>" & _
    "<P>" & myval & "<p>" & _
    "<P>" & myval2 & "<p>" & _
    "<P>" & myval3 & "<br></p>" & _
    "<P>Have a freaking great day!<p>"
    oMail.Subject = "File/s uploaded into folder"
    oMail.To = " "
    oMail.BodyFormat = 3
    oMail.Display
    Set oMail = Nothing
    Set oApp = Nothing
     
    End Sub

  7. #7
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi all,

    One more thing.
    When there is actually no check box checked for the second and the third one, how can I prevent that the space will be taken?
    Now the empty space appears in the email, even if the second and third check box is blank. There is a second part where there are 7 check boxes. and at the bottom I added a commets text box. I have selected one of the check boxes, the comments appear really far below in the email.

    greetings.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Try this:
    Code:
    Private Sub Command6_Click()
        Dim fd As FileDialog
        Dim myval As String
        Dim myval2 As String
        Dim myval3 As String
    
        Dim vBodyMsg As String
    
        '    Dim Check0 As String
        '    Dim Check2 As String
        '    Dim Check4 As String
    
        Dim objItem As Object
        Dim oMail As Outlook.MailItem
        Set oMail = objItem
        Dim oApp As Object
        If Me.Check0 = True Then
            myval = "Point File"
        Else
            mywal = ""
        End If
    
        If Me.Check2 = True Then
            myval2 = "Word doc"
        Else
            myval2 = ""
        End If
    
        If Me.Check4 = True Then
            myval3 = "Description"
        Else
            myval3 = ""
        End If
    
        vBodyMsg = "<HTML><HEAD><Font Size= 1><style> table, th, td </style> </HEAD> <BODY><br><p>"
        vBodyMsg = vBodyMsg & "Dear reader.</p>"
        vBodyMsg = vBodyMsg & "You have added the following file/s:<br>"
        
        'only add if check box is true
        If Me.Check0 Then
            vBodyMsg = vBodyMsg & "<P>" & myval & "<p>"
        End If
        If Me.Check2 Then
            vBodyMsg = vBodyMsg & "<P>" & myval2 & "<p>"
        End If
        If Me.Check4 Then
            vBodyMsg = vBodyMsg & "<P>" & myval3 & "<br></p>"
        End If
    
        vBodyMsg = vBodyMsg & "<P>Have a freaking great day!<p>"
    
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        fd.InitialFileName = "C:\Documents\ToStoreIn"
        fd.Show
        Set oApp = CreateObject("Outlook.application")
        Set oMail = oApp.CreateItem(olMailItem)
    
        oMail.HTMLBody = vBodyMsg
        '    oMail.HTMLBody = "<HTML><HEAD><Font Size= 1><style> table, th, td </style> </HEAD> <BODY><br><p>" & _
             "Dear reader.</p>" & _
             "You have added the following file/s:<br>" & _
             "<P>" & myval & "<p>" & _
             "<P>" & myval2 & "<p>" & _
             "<P>" & myval3 & "<br></p>" & _
             "<P>Have a freaking great day!<p>"
    
        oMail.Subject = "File/s uploaded into folder"
        oMail.To = " "
        oMail.BodyFormat = 3
        oMail.Display
    
        Set oMail = Nothing
        Set oApp = Nothing
        Set fd = Nothing
    End Sub
    You should check this by trying all combinations of the check boxes. All checked, none checked, only one checked at a time, two checked at a time,....

    You can single step through the code and look at the variable "vBodyMsg" to see what the message looks like.

  9. #9
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi ssanfu,

    That works really great :-)

    one little thing that could be still implemented. how to get a message box when none of the check boxes is checked?
    (just to let them know that they did not select any checkbox before sending the email)

    Greeetings.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Check boxes can have values of TRUE, FALSE or NULL. So I always set the default value for a check box to FALSE.
    Because I don't know the default value for your check boxes, I had to use the NZ() function.

    I changed the code (a little)....
    Code:
    Private Sub Command6_Click()
        Dim fd As FileDialog
        Dim myval As String
        Dim myval2 As String
        Dim myval3 As String
    
        Dim vBodyMsg As String
    
        Dim vCheck0 As Integer
        Dim vCheck2 As Integer
        Dim vCheck4 As Integer
    
        Dim oApp As Object
        Dim objItem As Object
        Dim oMail As Outlook.MailItem
        Set oMail = objItem
    
        ' get the values of the check boxes
        ' If null, change to 0
        vCheck0 = Nz(Me.Check0, 0)
        vCheck2 = Nz(Me.Check2, 0)
        vCheck4 = Nz(Me.Check4, 0)
    
        ' was at least one check box selected before sending email?
        If (vCheck0 + vCheck2 + vCheck4) = 0 Then
            msgbox "Please select at least one option" & vbNewLine & "Exiting....."
            Exit Sub
        End If
    
        ' initialize variables to default values
        myval = ""
        myval2 = ""
        myval3 = ""
    
        ' now check which check boxes are selected
        If vCheck0 = True Then
            myval = "Point File"
        End If
    
        If vCheck2 = True Then
            myval2 = "Word doc"
        End If
    
        If vCheck4 = True Then
            myval3 = "Description"
        End If
    
        ' create the body of the message
        vBodyMsg = "<HTML><HEAD><Font Size= 1><style> table, th, td </style> </HEAD> <BODY><br><p>"
        vBodyMsg = vBodyMsg & "Dear reader.</p>"
        vBodyMsg = vBodyMsg & "You have added the following file/s:<br>"
    
        'only add if check box is true
        If vCheck0 Then
            vBodyMsg = vBodyMsg & "<P>" & myval & "<p>"
        End If
        If vCheck2 Then
            vBodyMsg = vBodyMsg & "<P>" & myval2 & "<p>"
        End If
        If vCheck4 Then
            vBodyMsg = vBodyMsg & "<P>" & myval3 & "<br></p>"
        End If
    
        vBodyMsg = vBodyMsg & "<P>Have a freaking great day!<p>"
    
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        fd.InitialFileName = "C:\Documents\ToStoreIn"
        fd.Show
        
        Set oApp = CreateObject("Outlook.application")
        Set oMail = oApp.CreateItem(olMailItem)
    
        oMail.HTMLBody = vBodyMsg
    
        oMail.Subject = "File/s uploaded into folder"
        oMail.To = " "
        oMail.BodyFormat = 3
        oMail.Display
    
        Set oMail = Nothing
        Set oApp = Nothing
        Set fd = Nothing
    End Sub

  11. #11
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi ssanfu,

    It is working nice :-)

    BIG thanks.

    I will still add a combobox where they can select the month for the things are uploaded, but I guess with your great code it will be easy to implemnt :-)

    Greetings.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-15-2015, 10:58 AM
  2. Replies: 3
    Last Post: 02-22-2013, 06:41 AM
  3. Replies: 21
    Last Post: 08-20-2012, 11:59 PM
  4. open folder/Make new folder(example)-VBA Code
    By Madmax in forum Code Repository
    Replies: 3
    Last Post: 03-13-2012, 09:17 AM
  5. Enter a folder name and open that folder
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 09-05-2010, 04:39 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