By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 1,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

Creating a Loop to attach multiple files

hargrav123
P: 5
Hello. Below is the code i have so far. It words if I want my users to attach only one file. They need to attach multiple files. I changed the .AllowMultiSelect from = False to =True, but something else must need to be added. This still only allows for one document even though i have the ability to select more than one. Suggestions and ideas are requested.

Expand|Select|Wrap|Line Numbers
  1.     Dim rsParent As DAO.Recordset
  2.     Dim rsAttachment As DAO.Recordset2
  3.     Dim i As Integer
  4.     Dim SQL As String
  5.     'Add "Microsoft Office 16.0 Object Library" in references
  6.     Dim fDialog As Office.FileDialog
  7.     Dim varFile As Variant
  8.  
  9.     'Get the file to upload
  10.     Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  11.     With fDialog
  12.         .Title = "Choose the document/s to add to the form..."
  13.         .AllowMultiSelect = True
  14.         .InitialFileName = "U:\Director's Office\Directors Office DB"
  15.  
  16.         If .Show = True Then
  17.             If .SelectedItems.Count = 0 Then
  18.                 'User clicked open but didn't select a file
  19.                 GoTo SubExit
  20.             End If
  21.  
  22.              'Needed when MultiSelect = True
  23.              For i = 0 To rs.RecordsetCount - 1
  24.               For Each varFile In .SelectedItems
  25.                 strFileName = varFile
  26.                 rs.MoveNext
  27.  
  28.             Next i
  29.         Else
  30.             GoTo SubExit
  31.         End If
  32.     End With
  33.     'find the record in the attachments table we are currently on
  34.     'SQL = "SELECT * FROM tblAgenda WHERE Agenda_ID = " & Me.Agenda_ID
  35.  
  36.     'Set rsParent = CurrentDb.OpenRecordset("tblAgenda", dbOpenDynaset)
  37. '
  38. '    If rsParent.RecordCount = 0 Then
  39. '        MsgBox "There was a problem locating the selected record", vbCritical + vbOKOnly, "Error"
  40. '        GoTo SubExit
  41. '    Else
  42.         'Put recordset in edit mode
  43.         'rsParent.AddNew
  44.         'Set the child recordset
  45.         'Set rsAttachment = rsParent!Files_Attached.Value
  46.         'Add a new record
  47.         'rsAttachment.AddNew
  48.        ' rsAttachment.Fields("FileData").LoadFromFile strFileName
  49.         'Save changes
  50.        ' rsAttachment.Update
  51.         'rsParent.Update
  52.         'Update the sub-form
  53.         'Forms!frmAgendaReqst!subfrmAttachment_Details.Requery
  54.     'End If
  55.  
  56. SubExit:
  57. On Error Resume Next
  58. '    If Not rsParent Is Nothing Then
  59. '        rsParent.Close
  60. '        Set rsParent = Nothing
  61. '    End If
  62.     Exit Sub
  63.  
  64. SubError:
  65.     MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
  66.         "An error occurred"
  67.     GoTo SubExit
  68.     Resume
  69. End Sub
1 Week Ago #1
Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,054
hargrav123,

OK - so I'm going to ask a really stupid question, because I've been pondering this and your last thread as to the best way to do this. BTW, I can't get Recordset2 to be recognized on my machine, even though I have used them in the past for experimentation purposes.

Here's my question: Why not just use the built in functionality of the Attachment field to allow users to add files? All you have to do is add the control to the form and you're set.

This seems like overcomplexifying things beyond what you need to.
1 Week Ago #2

hargrav123
P: 5
Hi twinnyfo. I am using the field in an unbound form containing two subforms. One of the subforms is for a selection only that populates several fields based on the selection from a drop-down menu. The second form is activated upon a specific selection from the first subform. Other controls in the main form are then completed by user typing information in. The code below is what occurrs when the user clicks the save/submit button. It also opens a report of what they just entered for them to print for their records.



Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSaveSubmit_Click()
  2. Dim rs As DAO.Recordset, db As DAO.Database
  3. Dim txt, msgReply, Filter_Name
  4. Dim recent_Agenda
  5. Dim rsAttachment As DAO.Recordset2
  6. 'Last_Item = DMax("[Agenda_ID]", "tblAgenda")
  7. 'SQLtext = "SELECT * FROM [tblAgenda] WHERE [AGENDA_ID]=" & Last_Item
  8. Set db = CurrentDb
  9. Set rs = db.OpenRecordset("tblAgenda", dbOpenDynaset, dbSeeChanges)
  10.    ' Add a new attachment.
  11.  
  12.    With rs
  13.         .AddNew
  14.         '!Files_Attached.Fields("FileData").LoadFromFile Me.attFiles
  15.  
  16.         ' common to two tabs
  17.             !AgendaTopic_Num = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaTopic_Num
  18.             !AgendaTopic = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaTopic
  19.             !MtgDate = Me.txtMtgDate
  20.             !SubTopic_ID = Me.SubTopic_ID
  21.             !Background = Me.Background
  22.             txt = ActionRequested
  23.             !ActionRequested = txt
  24.             !Action_Request_Desc = Me.Action_Request_Desc
  25.             !Budget_Policy_Impact = Me.Budget_Policy_Impact
  26.             !Attached_Files = Me.chkAttachment
  27.              'txt2 = Me.att
  28.             '!Files_Attached = Me.txtSelectedFiles
  29.             !Attachments_Desc = Me.DescribeFile
  30.             !Presenter_1 = Me.Presenter_1
  31.             !Presenter_2 = Me.Presenter_2
  32.             !Presenter_3 = Me.Presenter_3
  33.             !SubmittedBy = Me.SubmittedBy
  34.             !Email = Me.Email
  35.             !Phone = Me.Phone
  36.             !DateSubmitted = Me.DateSubmitted
  37.             !ET_Submit_Chk = Me.ET_Submit_Chk
  38.             txt1 = ETMember
  39.             !ET_Submit_Appvl = txt1
  40.  
  41.         'Select Case Me.OptionBtns
  42.         If Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaTopic_Num <> 17 Then
  43.         'Case 1  ' txtTopicOptions
  44.  
  45.            ' unique to gen topic
  46.             !SubTopic_ID = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtSubID
  47.             !AgendaSubTopicTrkg = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaSubTopicTrkg
  48.             !AgendaSubTopic = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaSubTopic
  49.             !Responsible = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtResponsible
  50.             !TimeNeeded = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtTime_Required
  51.             !TimeNeeded_txt = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtTime
  52.             !AgendaSubject = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtAgendaSubject
  53.             !Focus_Tasks = Forms!frmAgendaReqst!subfrmAgendaGeneralTopics.Form.txtFocus_Tasks
  54.  
  55.          Else
  56.            'Case 2
  57.             ' unique to ad hoc
  58.             !SubTopic_ID = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtHocSubID
  59.             !AdHocDescription = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.AdHocDescription
  60.             !AdHocTimeDecimal = CDbl(Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtTimeNeeded.Column(0))
  61.             !AdHocTimeNeeded = Forms!frmAgendaReqst!subfrmAdHocTopics.Form.txtHocTimeTXT
  62.  
  63.             ' unique to attachments
  64.             '!Files_Attached = Forms!frmAgendaReqst!subfrmAttachment_Details.Form.txtFileData
  65.  
  66.         End If
  67.  
  68.         If Me.chkAttachment = True Then
  69.             Attachment_Add
  70.            Set rsAttachment = rs!Files_Attached.Value
  71.             'Add a new record
  72.             rsAttachment.AddNew
  73.            rsAttachment.Fields("FileData").LoadFromFile strFileName
  74.             'Save changes
  75.             rsAttachment.Update
  76.  
  77.         End If
  78.  
  79.         'End Select
  80.  
  81.  
  82.  
  83.       .Update
  84.  
  85.     End With
  86.     Agenda_ID = DMax("[Agenda_ID]", "tblAgenda")
  87.     Filter_Name = "[Agenda_ID] = " & Me.Agenda_ID
  88.     'rsAttachment.Close
  89.     rs.Close
  90.     db.Close
  91.  
  92.  
  93.     DoCmd.OpenReport "rptsubmitteditem", acViewReport, , Filter_Name
  94.     DoCmd.Close acForm, "frmAgendaReqst"
  95.   'msgReply = MsgBox("Do you want to print?", vbYesNo, "Print Report")
  96.    ' If msgReply = 6 Then 'Yes
  97.     '    DoCmd.PrintOut
  98.    ' End If
  99.     'DoCmd.Close acReport, "rptsubmitteditem"
  100.  
  101. End Sub
1 Week Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,054
Your response, however, begs the question even more as to why you are using an unbound form in order to save data to a table, when this is entirely unnecessary.

If it is a questoin of data validation, you can always validate data prior to the user completing their work (or even field by field). Again, why make a form complicated when you don't need to?

It looks like every item is saved to tblAgenda--instead of using Sub-Forms, you could also very easily use a tab control with a different page for the data that you are using a sub-form for.

I'm trying to make this easier for you, rather than more difficult. There appears to be no need to design this form in the manner you currently have it.
1 Week Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.