Move folder content depends on Listbox selection in MS Access (1 Viewer)

Alhakeem1977

Registered User.
Local time
Today, 10:19
Joined
Jun 24, 2017
Messages
308
Hi All,

How can I move folder contents usually are pdf files to another folder depends on a listbox selection?

The listbox populates the folder contents as a text.

When I type my code manually working fine as the below:
Code:
Name "D:\FOR REVIEW\02-JAN-19-AAA-001.pdf" As "D:\WORK IN PROGRESS\02-JAN-19-AAA-001" & "-R.pdf"

I want to automate this process as I stated above by the below syntax:

Code:
    Name "D:\FOR REVIEW" & "\Me.ListBox1.value" As "D:\WORK IN PROGRESS" & "\Me.ListBox1.value" & "-R.pdf"

I am struggling for a long time.

Appreciate your help ASAP
 

bastanu

AWF VIP
Local time
Today, 00:19
Joined
Apr 13, 2010
Messages
1,401
Try
Name "D:\FOR REVIEW" & Me.ListBox1.value & " As "D:\WORK IN PROGRESS" & Me.ListBox1.value & "-R.pdf"

(assumes the list box contains the file name including the .pdf extension).
Cheers,
Vlad
 

Alhakeem1977

Registered User.
Local time
Today, 10:19
Joined
Jun 24, 2017
Messages
308
Try
Name "D:\FOR REVIEW" & Me.ListBox1.value & " As "D:\WORK IN PROGRESS" & Me.ListBox1.value & "-R.pdf"

(assumes the list box contains the file name including the .pdf extension).
Cheers,
Vlad
Thanks for your response.

I got a syntax error
 

Alhakeem1977

Registered User.
Local time
Today, 10:19
Joined
Jun 24, 2017
Messages
308
Still the issue does not solve. [emoji51]

Sent from my HUAWEI NXT-L29 using Tapatalk
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:19
Joined
Oct 29, 2018
Messages
21,358
What is the Row Source of your Listbox?
 

Alhakeem1977

Registered User.
Local time
Today, 10:19
Joined
Jun 24, 2017
Messages
308
What is the Row Source of your Listbox?
Thanks the DBGuy for your response, you're always available.

it's unbound list box it returns the correct value of our shared folder's pdf files as a text with the below Syntax:
Code:
Private Sub Form_Current()
On Error Resume Next
    Dim Filename As String
        Filename = Dir("D:\FOR REVIEW" & "\*.pdf", vbNormal)
            Do While Len(Filename) > 0
        Me.ListBox1.AddItem Filename
    Filename = Dir()
Loop
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:19
Joined
May 7, 2009
Messages
19,169
add a button on your form that will do the actual moving of your pdf files.
on it's click event add this code:
Code:
Private Sub Command2_Click()
    Dim var As Variant
    Dim col As Collection
    Dim i As Integer
    If Me.List0.ListIndex = -1 Then
        MsgBox "Nothing is selected"
        
    Else
        Set col = New Collection
        For Each var In Me.List0.ItemsSelected
            'copy selected items to a folder
            'but check first if already present
            'and delete if necessary
            If Dir("D:\WORK IN PROGRESS\" & Replace(Me.List0.ItemData(var),".pdf","-R.pdf") <> "" Then VBA.Kill "D:\WORK IN PROGRESS\" & Replace(Me.List0.ItemData(var), ".pdf", "-R.pdf")
            'copy the file
            VBA.FileCopy "D:\FOR REVIEW" & Me.List0.ItemData(var), "D:\WORK IN PROGRESS\" & Replace(Me.List0.ItemData(var), ".pdf", "-R.pdf")
            'remove it from original folder
            VBA.Kill "D:\FOR REVIEW\" & Me.List0.ItemData(var)
            'save the list item in collection, for later to be removed
            col.Add CLng(var)
        Next
        'now remove all selected items from the listbox
        For i = col.Count To 1 Step -1
            'we will do backward loop
            Me.List0.RemoveItem (col.Item(i))
        Next
    End If
End Sub
 

Alhakeem1977

Registered User.
Local time
Today, 10:19
Joined
Jun 24, 2017
Messages
308
add a button on your form that will do the actual moving of your pdf files.
on it's click event add this code:
Code:
Private Sub Command2_Click()
    Dim var As Variant
    Dim col As Collection
    Dim i As Integer
    If Me.List0.ListIndex = -1 Then
        MsgBox "Nothing is selected"
        
    Else
        Set col = New Collection
        For Each var In Me.List0.ItemsSelected
            'copy selected items to a folder
            'but check first if already present
            'and delete if necessary
            [COLOR="Red"]If Dir("D:\WORK IN PROGRESS\" & Replace(Me.List0.ItemData(var),".pdf","-R.pdf") <> "" Then VBA.Kill "D:\WORK IN PROGRESS\" & Replace(Me.List0.ItemData(var), ".pdf", "-R.pdf")[/COLOR]
            'copy the file
            VBA.FileCopy "D:\FOR REVIEW" & Me.List0.ItemData(var), "D:\WORK IN PROGRESS\" & Replace(Me.List0.ItemData(var), ".pdf", "-R.pdf")
            'remove it from original folder
            VBA.Kill "D:\FOR REVIEW\" & Me.List0.ItemData(var)
            'save the list item in collection, for later to be removed
            col.Add CLng(var)
        Next
        'now remove all selected items from the listbox
        For i = col.Count To 1 Step -1
            'we will do backward loop
            Me.List0.RemoveItem (col.Item(i))
        Next
    End If
End Sub

Thanks for your response, sorry may I did not mention the record should move from [ListBox1] represents the FOR REVIEW LIST to [ListBox2] represents WORK IN PROGRESS LIST
I got a Syntax error in the below line:

If Dir("D:\WORK IN PROGRESS" & Replace(Me.List0.ItemData(var),".pdf","-R.pdf") <> "" Then VBA.Kill "D:\WORK IN PROGRESS" & Replace(Me.List0.ItemData(var), ".pdf", "-R.pdf")

I'm so sorry to bother you with me.

Thanks a lot!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:19
Joined
May 7, 2009
Messages
19,169
you need to replace all control names with the one's you have.

list0 is but an example, supply it with correct control name. so does Command2,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:19
Joined
May 7, 2009
Messages
19,169
here I change it altogether. just rename the Command2 button name with what you have:
Code:
Private Sub Command2_Click()
    Dim var As Variant
    Dim col As Collection
    Dim i As Integer
    If Me.Listbox1.ListIndex = -1 Then
        MsgBox "Nothing is selected"
        
    Else
        Set col = New Collection
        For Each var In Me.Listbox1.ItemsSelected
            'copy selected items to a folder
            'but check first if already present
            'and delete if necessary
            If Dir("D:\WORK IN PROGRESS\" & Replace(Me.Listbox1.ItemData(var), ".pdf", "-R.pdf")) <> "" Then _
                VBA.Kill "D:\WORK IN PROGRESS\" & Replace(Me.Listbox1.ItemData(var), ".pdf", "-R.pdf")
            
            'copy the file
            VBA.FileCopy "D:\FOR REVIEW" & Me.Listbox1.ItemData(var), "D:\WORK IN PROGRESS\" & Replace(Me.Listbox1.ItemData(var), ".pdf", "-R.pdf")
            
            'remove it from original folder
            VBA.Kill "D:\FOR REVIEW\" & Me.Listbox1.ItemData(var)
            
            'save the list item in collection, for later to be removed
            col.Add CLng(var)
            
            ' add selected item of listbox1 to listbox2
            Me.listbox2.AddItem Me.Listbox1.ItemData(var)
        Next
        'now remove all selected items from the listbox
        For i = col.Count To 1 Step -1
            'we will do backward loop
            Me.Listbox1.RemoveItem (col.Item(i))
        Next
    End If
End Sub
 

Alhakeem1977

Registered User.
Local time
Today, 10:19
Joined
Jun 24, 2017
Messages
308
Thank you so much arnelgp
The code works as I need but only one thing in the list WORK IN PROGRESS Listbox it shows the record without -R like this: 02JAN19-OPS-DM-001 it suppose to be like this: 02JAN19-OPS-DM-001-R

In the folder, it does add it but in the listbox it didn't.

Thanks in advance!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:19
Joined
May 7, 2009
Messages
19,169
change this portion in your code, from:
Code:
          ' add selected item of listbox1 to listbox2
            Me.listbox2.AddItem Me.Listbox1.ItemData(var)
to
Code:
          ' add selected item of listbox1 to listbox2
            Me.listbox2.AddItem Replace(Me.Listbox1.ItemData(var), ".pdf", "-R.pdf")
 

Alhakeem1977

Registered User.
Local time
Today, 10:19
Joined
Jun 24, 2017
Messages
308
change this portion in your code, from:
Code:
          ' add selected item of listbox1 to listbox2
            Me.listbox2.AddItem Me.Listbox1.ItemData(var)
to
Code:
          ' add selected item of listbox1 to listbox2
            Me.listbox2.AddItem Replace(Me.Listbox1.ItemData(var), ".pdf", "-R.pdf")
Waw, thank you so much I do not know how to thank you.

Amazing it's more than my expectation. [emoji122][emoji122][emoji122]

Sent from my HUAWEI NXT-L29 using Tapatalk
 

Alhakeem1977

Registered User.
Local time
Today, 10:19
Joined
Jun 24, 2017
Messages
308
change this portion in your code, from:
Code:
          ' add selected item of listbox1 to listbox2
            Me.listbox2.AddItem Me.Listbox1.ItemData(var)
to
Code:
          ' add selected item of listbox1 to listbox2
            Me.listbox2.AddItem Replace(Me.Listbox1.ItemData(var), ".pdf", "-R.pdf")
I am sorry I have got another folder called REVIEWED and a listbox3 will represent that folder but the name of the pdf file will remain as it is without adding the ’-R’ with the movement of the file from WORK IN PROCESS folder how can redefine the code on click event of the button?

I tried a lot but always I am getting a Syntax error.

Sorry to bother you with me again [emoji18]

Sent from my HUAWEI NXT-L29 using Tapatalk
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:19
Joined
May 7, 2009
Messages
19,169
you're welcome!
 

Alhakeem1977

Registered User.
Local time
Today, 10:19
Joined
Jun 24, 2017
Messages
308
you're welcome!
I am sorry I have got another folder called REVIEWED and a listbox3 will represent that folder but the name of the pdf file will remain as it is without adding the ’-R’ with the movement of the file from WORK IN PROCESS folder how can redefine the code on click event of the button?

I tried a lot but always I am getting a Syntax error.

Sorry to bother you with me again [emoji18]

Sent from my HUAWEI NXT-L29 using Tapatalk

Sent from my HUAWEI NXT-L29 using Tapatalk
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:19
Joined
May 7, 2009
Messages
19,169
you mean from "WORK IN PROGRESS" folder to "REVIEWED".
add another button for this. the code is same so we will move our code in a Standard Module. on VBA menu, Insert->Module and paste this:
Code:
Public Sub subListToFolder(ByRef FromListBox As Listbox, _
                           byRef ToListBox As Listbox, _
                           ByVal FromFolder As String, _
                           ByVal ToFolder As String)
 
    Dim var As Variant
    Dim col As Collection
    Dim i As Integer
    Dim strSourcePDF As String
    Dim strTargetPDF As String

    If FromListbox.ListIndex = -1 Then
        MsgBox "Nothing is selected"
        
    Else
        Set col = New Collection
        For Each var FromListbox.ItemsSelected
            'copy selected items to a folder
            'but check first if already present
            'and delete if necessary

            strSourcePDF = FromListbox.ItemData(var)
            strTargetPDF = strSourcePDF

            FromFolder = Replace(FromFolder & "\", "\\", "\")
            ToFolder = Replace(ToFolder & "\", "\\", "\")

            If Instr(strTargetPDF, "-R.pdf") > 0 Then
                strTargetPDF = Replace(strTargetPDF, "-R.pdf", ".pdf")
            Else
                strTargetPDF = Replace(strTargetPDF, ".pdf", "-R.pdf")
            End If

            If Dir(ToFolder & strTargetPDF) <> "" Then _
                VBA.Kill ToFolder & strTargetPDF
            
            'copy the file
            VBA.FileCopy FromFolder & strSourcePDF, ToFolder & strTargetPDF
            
            'remove it from original folder
            VBA.Kill FromFolder & strSourcePDF
            
            'save the list item in collection, for later to be removed
            col.Add CLng(var)
            
            ' add selected item of listbox1 to listbox2
            ToListbox.AddItem strTargetPDF
        Next
        'now remove all selected items from the listbox
        For i = col.Count To 1 Step -1
            'we will do backward loop
            FromListbox.RemoveItem (col.Item(i))
        Next
    End If
End Sub
remove all the code from your first button (moving listitem from listbox1 to listbox2).
your code should look like this:
Code:
Private Sub button1_Click()
    Call subListToFolder(Me.Listbox1, Me.Listbox2, "D:\FOR REVIEW", "D:\WORK IN PROGRESS")
End Sub
now add another button, for moving from listbox1 to listbox3.
add this code on it's Click event:
Code:
Private Sub button2_Click()
    Call subListToFolder(Me.Listbox2, Me.Listbox3, "D:\WORK IN PROGRESS", "D:\REVIEWED")
End Sub
 

Alhakeem1977

Registered User.
Local time
Today, 10:19
Joined
Jun 24, 2017
Messages
308
you mean from "WORK IN PROGRESS" folder to "REVIEWED".
add another button for this. the code is same so we will move our code in a Standard Module. on VBA menu, Insert->Module and paste this:
Code:
Public Sub subListToFolder(ByRef FromListBox As Listbox, _
                           byRef ToListBox As Listbox, _
                           ByVal FromFolder As String, _
                           ByVal ToFolder As String)
 
    Dim var As Variant
    Dim col As Collection
    Dim i As Integer
    Dim strSourcePDF As String
    Dim strTargetPDF As String

    If FromListbox.ListIndex = -1 Then
        MsgBox "Nothing is selected"
        
    Else
        Set col = New Collection
        For Each var FromListbox.ItemsSelected
            'copy selected items to a folder
            'but check first if already present
            'and delete if necessary

            strSourcePDF = FromListbox.ItemData(var)
            strTargetPDF = strSourcePDF

            FromFolder = Replace(FromFolder & "\", "\\", "\")
            ToFolder = Replace(ToFolder & "\", "\\", "\")

            If Instr(strTargetPDF, "-R.pdf") > 0 Then
                strTargetPDF = Replace(strTargetPDF, "-R.pdf", ".pdf")
            Else
                strTargetPDF = Replace(strTargetPDF, ".pdf", "-R.pdf")
            End If

            If Dir(ToFolder & strTargetPDF) <> "" Then _
                VBA.Kill ToFolder & strTargetPDF
            
            'copy the file
            VBA.FileCopy FromFolder & strSourcePDF, ToFolder & strTargetPDF
            
            'remove it from original folder
            VBA.Kill FromFolder & strSourcePDF
            
            'save the list item in collection, for later to be removed
            col.Add CLng(var)
            
            ' add selected item of listbox1 to listbox2
            ToListbox.AddItem strTargetPDF
        Next
        'now remove all selected items from the listbox
        For i = col.Count To 1 Step -1
            'we will do backward loop
            FromListbox.RemoveItem (col.Item(i))
        Next
    End If
End Sub
remove all the code from your first button (moving listitem from listbox1 to listbox2).
your code should look like this:
Code:
Private Sub button1_Click()
    Call subListToFolder(Me.Listbox1, Me.Listbox2, "D:\FOR REVIEW", "D:\WORK IN PROGRESS")
End Sub
now add another button, for moving from listbox1 to listbox3.
add this code on it's Click event:
Code:
Private Sub button2_Click()
    Call subListToFolder(Me.Listbox2, Me.Listbox3, "D:\WORK IN PROGRESS", "D:\REVIEWED")
End Sub
I have just checked it works perfect [emoji106]

Thank you so much!

Sent from my HUAWEI NXT-L29 using Tapatalk
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:19
Joined
May 7, 2009
Messages
19,169
you're welcome!
 

Users who are viewing this thread

Top Bottom