Solved

MS Access Filedialog Multiselect to Subform Add New Record Help!

Posted on 2016-10-27
10
45 Views
Last Modified: 2016-10-31
I have a Filedialog that works great for single select but I need to be able to use multiselect. I click a add image button and on_click it calls to a Public Function AddLocalImagePath. That function then runs a series of commands to place the filepath name into a control on the subform.


I have been at this for hours and hours! What I have is below. Thank you!

AddLocalImagePath function in the subform sbfrmProductImages:

Public Function AddLocalImagePath() As Variant
Dim strImagePath As Variant

    strImagePath = ImagePathMulti
    
    
    If Me.NewRecord Then
         DoCmd.GoToControl "sbfrmProductImages"
         Me!ProductImageFileNm.Value = Dir$(strImagePath)
          
    Else
        DoCmd.GoToControl "sbfrmProductImages"
         DoCmd.GoToRecord , , acNewRec
       Me!ProductImageFileNm.Value = Dir$(strImagePath)
 End If

       
       Call ImageRequery
    
    
End Function

Open in new window



FileDialog in a module:

Public Function ImagePathMulti() As String
Dim fd As FileDialog
Dim FileChosen As Integer
Dim FileName As String
Dim I As Integer

Set fd = Application.FileDialog(msoFileDialogFilePicker)
'use the standard title and filters, but change the
'initial folder
fd.InitialFileName = "\\1-PC\Database\Images\"
fd.InitialView = msoFileDialogViewList
'allow multiple file selection
fd.AllowMultiSelect = True
FileChosen = fd.Show
If FileChosen <> -1 Then
Exit Function
Else
'open each of the files chosen
For I = 1 To fd.SelectedItems.Count
ImagePathMulti = fd.SelectedItems(I)
Next I
End If
End Function

Open in new window

0
Comment
Question by:Dustin Stanley
  • 5
  • 5
10 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41863503
All you need is to insert the record:
Set rst = ' your recordset

' Open each of the files chosen.
For I = 1 To fd.SelectedItems.Count
    ImagePathMulti = fd.SelectedItems(I)
    If ImagePathMulti <> "" Then
        ' Insert record.
        rst.AddNew
            rst!ProductImageFileNm.Value = ImagePathMulti 
        rst.Update
    End If
Next

Open in new window

/gustav
0
 

Author Comment

by:Dustin Stanley
ID: 41865446
Thank you I tried this and it gave an error for type mismatch.

Option Compare Database
Option Explicit

Public Function ImagePathMulti() As String
Dim fd As FileDialog
Dim FileChosen As Integer
Dim FileName As String
Dim i As Integer
Dim rst As Recordset

Set fd = Application.FileDialog(msoFileDialogFilePicker)
'use the standard title and filters, but change the
'initial folder
fd.InitialFileName = "\\1-PC\Database\Images\"
fd.InitialView = msoFileDialogViewList
'allow multiple file selection
fd.AllowMultiSelect = True
FileChosen = fd.Show
If FileChosen <> -1 Then
Exit Function
Else
Set rst = Forms!frmSkusEntry!sbfrmProductImages.Form!ProductImageFileNm

' Open each of the files chosen.
For i = 1 To fd.SelectedItems.Count
    ImagePathMulti = fd.SelectedItems(i)
    If ImagePathMulti <> "" Then
        ' Insert record.
        rst.AddNew
            rst!ProductImageFileNm.value = ImagePathMulti
        rst.Update
    End If
Next
End If
End Function

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41865469
You must set rst to a recordset (or recordsetclone) and you can't use ImagePathMulti as variable name as it is the name of the function.

/gustav
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:Dustin Stanley
ID: 41865569
I changed imagePathMulti to a string but I am very confused on the setting the recordset. I dimmed rst as recordset. But if I set it to a table isn't this going to just add the records directly to the table instead of my subform? I need it to know which Mainform SkuID (Master/Child Link) the ProductImages go to. Sorry I'm just not getting it. Thanks for your help!
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 41865660
You probably need this:

    Set rst = Me!sbfrmProductImages.Form.RecordsetClone

/gustav
0
 

Author Comment

by:Dustin Stanley
ID: 41867471
Thank you for your help! Seriously thank you!  I have gotten it to do multi select and insert file names only into the correct subform position correctly BUT it leaves SkuID control field blank in the Subform and it has to match the Main Forms SkuID control field in order for the image names to be synced with the right product. Again thank you!

In My main form frmSKUsEntry:
Private Sub btnAddImage_Click()
    Forms!frmSkusEntry!sbfrmProductImages.Form.AddLocalImagePath
End Sub

Open in new window



in my subform sbfrmProductImages:
Public Function AddLocalImagePath() As Variant
Dim strImagePath As String

    strImagePath = ImagePathMulti
    
    
    If Me.NewRecord Then
         DoCmd.GoToControl "sbfrmProductImages"
    Else
          DoCmd.GoToControl "sbfrmProductImages"
         DoCmd.GoToRecord , , acNewRec
 End If

       
       Call ImageRequery
    
    
End Function


Public Function ImagePathMulti() As String
Dim fd As FileDialog
Dim FileChosen As Integer
Dim FileName As String
Dim I As Integer
Dim rst As Recordset


Set fd = Application.FileDialog(msoFileDialogFilePicker)
'use the standard title and filters, but change the
'initial folder
fd.InitialFileName = "\\SURPIUS1-PC\OneDrive\SurpiusDatabase\Images"
fd.InitialView = msoFileDialogViewList
'allow multiple file selection
fd.AllowMultiSelect = True
FileChosen = fd.Show
If FileChosen <> -1 Then
Exit Function
Else
Set rst = Me.RecordsetClone
' Open each of the files chosen.
For I = 1 To fd.SelectedItems.Count
    ImagePathMulti = fd.SelectedItems(I)
    If ImagePathMulti <> "" Then
        ' Insert record.
        rst.AddNew
            rst!ProductImageFileNm.Value = Dir$(ImagePathMulti)
        rst.Update
    End If
Next
End If
End Function

Open in new window

0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 500 total points
ID: 41867591
But can't you just set that field too? As:

        ' Insert record.
        rst.AddNew
            rst!ProductImageFileNm.Value = Dir$(ImagePathMulti)
            rst!SkuID.Value = ' your SkuID value here.
        rst.Update

/gustav
0
 

Author Comment

by:Dustin Stanley
ID: 41867599
But can't you just set that field too?
Yes if i had that knowledge and now I do. Thank you Gustav! JUST BRILLIANT!!!!!!!!!!!!!!!!!!!!!! Works perfect!!! This means a ton to me thanks so much!
0
 

Author Closing Comment

by:Dustin Stanley
ID: 41867600
Thank you!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41867604
You are welcome!

/gustav
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question