popup message yes/no

[lookforsmt]

HI!

Below code is to copies the file names from a folder to table in access. This codes works well with one message "file name imported"
I need to put a popup message for the below
a) Do you want to copy the file names, "Yes/No" yes will continue and no will stop the code and do nothing.

b)if yes then display in popup message the names of files copied

b) if the same file names are copied earlier then the popup msg to alert "file names already copied"

below is the code

Private Sub cmdImport_FileName_Click()
On Error GoTo Err_Handler_1

    Dim folderspec As String
    Dim fs, f, f1, fc, s
    Dim rs As DAO.Recordset
        
    Set rs = CurrentDb.OpenRecordset("tbl_Imported_FileName")

    folderspec = "E:\Import\Folder1\Daily download folder\"

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files

For Each f1 In fc
    rs.AddNew
    rs.Fields("File_Name") = f1.Name
    rs.Update
Next
    MsgBox f1 & " " & "file name Imported"


Set rs = Nothing

Exit_Handler:
    Exit Sub
    
Err_Handler_1:
    If Err = 3022 Then
         Resume Next 'tells the routine to ignore error and continue
    Else
         MsgBox "Error " & Err.Number & "  cmdImport_FileName_Click procedure : " & vbCrLf & Err.Description
    End If

End Sub

I am not sure where to put the popup message code. Any suggestions

 

[lookforsmt]

Hi! all the Guru out here. Request your guidance where can i put the popup message if the filename is already copied in access table.

It may be simple solution but for me this is very challenging.
Hope someone can look into this.

 

[constableparks]

For the first message box, place this at the top of your code block:

If MsgBox("Do you want to copy the file names?", vbYesNo, "Copy Files?") = vbNo then Exit Sub

Do you want a separate message box for each file name or one message box that would include all file names?

 

[lookforsmt]

HI! Thank you for your time and looking into my concern.

i have place the code as mentioned on top of the code. It works absolutely fantastic.

From your code provided below. I have changed the vbNo to vbYes and excluded "Exit Sub" since stops the code and does nothing, highlighted below. (Am I doing it correctly)

If MsgBox("Do you want to copy the file names?", vbYesNo, "Copy Files?") = vbNo then Exit Sub

I want one message box to display all the filenames that have been imported.
But for my knowledge can you also tell me how the separate message box for each file name is done.

once i have this done, i want to further put one more control, that is it copies filename only of those file which have todays date.
My files has a part of the name in the middle as date in format yymmdd

File name: ICCSC01001026170927111101

Thank you for your help

 

[moke123]

I want one message box to display all the filenames that have been imported.

to do this I would construct a string in your loop

Dim strCopied as string

then add it in your loop

For Each f1 In fc
    rs.AddNew
    rs.Fields("File_Name") = f1.Name
    rs.Update

strCopied = strCopied & f1.Name & vbnewline

Next

msgbox The following files were imported : " & vbnewline & strCopied

execute the msgbox after the loop finishes

 

[moke123]

But for my knowledge can you also tell me how the separate message box for each file name is done.

to do this, include the msgbox within your loop

For Each f1 In fc
    rs.AddNew
    rs.Fields("File_Name") = f1.Name
    rs.Update

msgbox f1.Name & " has been added"

Next

 

[lookforsmt]

Thank you Moke123 for your support in helping me to complete this. I have tried both ways and it work flawlessly.
This is part of the other codes that are followed after it copies the filename.

Now i am trying to check the date before i copy the file name from the folder. The date is also part of the filename. (File name: ICCSC01001026170927111101

Do i need to controlled this action by function code and whether the below code can be amended for this.

Function SQLDate(dt As Date) As String
On Error Resume Next
    If IsDate(dt) Then SQLDate = Format(dt, "\#dd-mmm-yyyy#")
End Function

or
Format(Date, "yymmdd")
i tried various ways but was not successful.
Any suggestions whether i am on the right track. Thanks

 

[lookforsmt]

can i use this function to get identify the date. Only the format is with slash (/) and i need the function in yymmdd format.

Public Function fnGetDatePart(vfield As Variant) As Variant

Dim v As Variant
vfield = vfield & ""
v = Split(vfield, "-")
On Error Resume Next
fnGetDatePart = Format(DateValue(CDate(v(1))), "mm/dd/yyyy")

End Function

 

[Beetle]

Aircode (untested) and this does not include the code modifications that others have suggested so you'll have to adjust for that as well.

Private Sub cmdImport_FileName_Click()
On Error GoTo Err_Handler_1

    Dim folderspec As String
    Dim fs, f, f1, fc, s
    Dim rs As DAO.Recordset
   [COLOR="Red"] Dim strFileDate As String[/COLOR]
        
    Set rs = CurrentDb.OpenRecordset("tbl_Imported_FileName")

    folderspec = "E:\Import\Folder1\Daily download folder\"

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files

    [COLOR="red"]strFileDate = Format(Date, "yymmdd")[/COLOR]

For Each f1 In fc
    [COLOR="red"]If Instr(f1, strFileDate)>0 Then[/COLOR]
        rs.AddNew
        rs.Fields("File_Name") = f1.Name
        rs.Update
   [COLOR="red"] End If[/COLOR]
Next

 MsgBox f1 & " " & "file name Imported"

   

Set rs = Nothing

Exit_Handler:
    Exit Sub
    
Err_Handler_1:
    If Err = 3022 Then
         Resume Next 'tells the routine to ignore error and continue
    Else
         MsgBox "Error " & Err.Number & "  cmdImport_FileName_Click procedure : " & vbCrLf & Err.Description
    End If

End Sub

 

[lookforsmt]

Thanks Beetle. Now i am able to import only current date txt file.

But i have a challenge now, when I have txt files less then 8 files in the folder i get below errors:

Error 3078. The Microsoft database cannot find the input table or query.

I know the reason this is caused. for e.g. when i import the text file "ICCSC01001026170927111101" into access i convert the table name to C01001026 but if this txt file is not availabe in the folder then the code looks for the table C01001026 and hence the error.

Error 2495. The action or method requires a table name argument and the DB goes in coma until i X (close) and reopen it again.

I want to amend my code that it ignores if the txt files are less than 8 and imports only those txt files which are available.
Hope i have not confused too much

 

[lookforsmt]

Thank you all for the help provided.

Sorry for the delayed post