File Dialog

[ted.martin]

I am using the File Browse Dialog to select and store a file path.

Here is the full code

Public Sub FileBrowse(myForm As String, myControl As String)

' This requires a reference to the Microsoft Office 12.0 Object Library.

   Dim fDialog As Office.FileDialog
   Dim varFile As Variant

   ' Set up the File dialog box.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   
   With fDialog
      ' Allow the user to make multiple selections in the dialog box.
      .AllowMultiSelect = False
            
      ' Set the title of the dialog box.
      .Title = "Select a File"

      ' Clear out the current filters, and then add your own.
      .Filters.Clear
      '.Filters.Add "Access Databases", "*.MDB"
      '.Filters.Add "Access Projects", "*.ADP"
      '.Filters.Add "HTML Document", "*.htm*"
      .Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the user picked at least one file.
      If .Show = True Then
            .Application.Forms(myForm).Controls(myControl).Value = .SelectedItems(1)

            
      Else
         Exit Sub ' Cancel clicked
      End If

As the control is on a subform, when the File Browse module is called with

Call FileBrowse("[F-Jobs]![SF-Job Hyperlinks].Form", "JobHyperlink")

it errors - on the line below - with cannot find the form F-Jobs]![SF-Job Hyperlinks (interesting with the lack of [ and ] )

.Application.Forms(myForm).Controls(myControl).Value

.... whereas this line does work

            Forms![F-Jobs]![SF-Job Hyperlinks].Form!JobHyperlink.Value = .SelectedItems(1)

I would like to use the generic form of File Browse declaring the form and field as variables. Can anyone suggest a correction to my Calling Code?

Many thanks as always

 

[spikepl]

Try Call FileBrowse([F-Jobs]![SF-Job Hyperlinks].Form.Name, "JobHyperlink")

 

[ted.martin]

Hi Spike - that does not work. myForm is a string so tried it with the Argument myForm as Form but that did't work either.

Error is

Can't find the the form [F-Jobs].[SF-Job Hyperlinks].Form.Name etc ....


Thanks for your suggestion.

 

[spikepl]

Read my post again - NO "" for the first argument

 

[ted.martin]

Copied yours precisely.

The error message now is Can't find the field '|' referred to in your expression.

 

[spikepl]

Try this hardwired inside your sub:
Forms("name of your form").Controls("name of your control").Value="bla bla"

 

[ted.martin]

As per my original post

Forms![F-Jobs]![SF-Job Hyperlinks].Form!JobHyperlink.Value = .SelectedItems(1)

does work BUT I want to use a generic version where the form/subform and control are variables - hence the

Application.Forms(myForm).Controls(myControl).Value method

Hence the problem.

 

[spikepl]

Tell you a secret - I sort of cottoned onto your problem from first post.
The reason why I asked you to do what I did was to check if you indeed have the required form and control on it, and that the are not misspelled or anything, , because I do not have your app in front of me.

The normal process of sorting out bugs is to take each little bit, and make sure that it works. Thats why.

So want to give it another go? Or a do you aim for Immaculate Problemresolution? :_-)

 

[ted.martin]

.Application.Forms(myForm).Controls(myControl).Value = .SelectedItems(1)

works when the control is directly on a Form but in my design, the control is on a subform.

Now ; I think you are on the right path with this (sorry for the pun) as the error is now at control level whereas previously it was with the form/subform. So I guess all we need to do is make sure that we refer to the subform control correctly. Agree?

 

[spikepl]

ah.. there you go!

You need the parent form as well - a subform is not in the forms collection - I am not sure if it is in the Controls collection of a form or where

 

[spikepl]

try this, with hardwired values at first Forms(MyParentFormName).Controls(MySubformName).Controls(MyControlName).Value=

 

[ted.martin]

Very frustrating

Yours just errors at the line.

Forms![F-Jobs]![SF-Job Hyperlinks].Form!JobHyperlink.Value = .SelectedItems(1) ' WORKS

 

[spikepl]

I cannot help you remotely when you don't do what I ask.

try this, with hardwired values at first Forms("MyParentFormName").Controls("MySubformName").Co ntrols("MyControlName").Value=

all the red-marked texts are strings - the names of the form, subform and control, and must be inserted exactly as shown , within the (" ")

so MyFormParentName is whatever it is you called it in the text, not Forms![bla bla] but "Bla bla"

 

[spikepl]

Forms("F-Jobs").Controls("SF-Job Hyperlinks").Co ntrols("JobHyperlink").Value= this might work - there can be some funny stuff with names containing spaces, so also try with spacese replaced by underscores

 

[ted.martin]

Bingo - now that worked - so all I need it to call it with 3 not 2 arguments.

Will post back shortly.

 

[ted.martin]

OK this now works

Call FileBrowse("F-Jobs", "SF-Job Hyperlinks", "JobHyperlink")

and

Public Sub FileBrowse(myForm As String, mySubform As String, myControl As String)

and

Forms(myForm).Controls(mySubform).Controls(myControl).Value = .SelectedItems(1)

Ok - it doesn't use the Application method but I was never precious about that.

Well done and thanks

 

[ted.martin]

Public Sub FileBrowse(myForm As String, mySubform As String, myControl As String)

and either

.Application.Forms(myForm).Controls(mySubform).Controls(myControl).Value = .SelectedItems(1)

or

Forms(myForm).Controls(mySubform).Controls(myControl).Value = .SelectedItems(1)

Thanks - well done Spike & thanks