Return function result to form control

[Vin]

Hi, I am not an access expert and so hope the solution to this may be fairly straight forward.
I am trying to return a value gained from a function into a specific field on a form. I have a few fields in which i would like to do this so rather than creating multiple codes referencing individual fileds I am hoping that I could pass this info to the function and then have the function pass the result back. The below code is predominantly from microsoft where I am able to browse to a file and return its path & name.
I have called the below function with...

Call Attachments(Form, Form.PassField)

The following function retrieves the path and file name ok but when requested to return that info ('**** section) into the form and control above i get the error...

Run-time error '2465':
Application-defined or object-defined error



Function Attachments(frm As Form, strFieldName As Control)
'Requires reference to Microsoft Office 10.0 Object Library.

Dim fDialog As Office.FileDialog
Dim varFile As Variant

'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog

'Set the title of the dialog box.
.Title = "Please select a file"

'Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add "Access Databases", "*.JPG"
.Filters.Add "Access Databases", "*.BMP"
.Filters.Add "Access Databases", "*.PDF"
.Filters.Add "All Files", "*.*"

'Show the dialog box. If the .Show method returns True, the
'user picked at least one file. If the .Show method returns
'False, the user clicked Cancel.
If .Show = True Then
'Loop through each file selected and add it to our list box.
For Each varFile In .SelectedItems
'****
frm.strFieldName.Name = varFile
'****
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With

End Function

Any assistance would be greatly appreciated - thanks Vin

 

[vbaInet]

Your function should return the contents of varFile.

Attachments = varFile

When you call Attachments you should save the value into a variable and use it as you wish.

 

[Vin]

Thanks VbaInet,

I'm not sure what you mean though.
the Function itself is fine varFile has the required info I just can't get it back into the form and field that parametised the called the function. I am sure the The frm (frm as Form) part seems fine and the strFieldName (strFieldName As Control) is calling the me.PassField contents. I am able to elswhere in the code refer to the name of the control rather than its content with PassField.Name. But when i try and combine the two as below i am getting the error...

Run-time error '2465':
Application-defined or object-defined error

frm.strFieldName.Name

Am i perhaps not declaring the items correctly?


Cheers,
Vin

 

[vbaInet]

Give me an example of a paramter values that you are passing to the function. Exact strings. And where are you calling the function from? Subform or form?

By the way, that line you are referring to isn't adding to the list box. To add you use the AddItem method.

 

[Vin]

Thanks for your help on this.
I am calling the function from a command button (cmdAttachDoc) on a form (called frmLog), which isn’t a subform. Next to the cmdAttachDoc is a bound text control (field) called PassField that I would like the information provided by the function to be stored.
I have used an event procedure ‘On Click’ with Call Attachments(Form, Form.PassField) to pass the form (frmLog) & Control (PassField) info over.

The receiving Function has been structured as below…
Function Attachments (frm As Form, strFieldName As Control)
The rest of the code works well except for when I try and return the selected filename & path(eg C:\Database\Image.bmp) to frm.strFieldName.Name = varFile.
I have tested the function returning this info by removing the ‘strFieldName.Name’ and replacing it with the control’s actual name, such as frm.PassField = varFile and it works fine.

I can replicate this function for the 10 fields I would like to use this with (PassField1 – 10), but thought I should be able to do it with the one bit of code?

Thanks for the listbox ‘AddItem’ reminder – I changed it to be stored in a text field, but didn’t change the comments.

 

[vbaInet]

I'm not entirely sure you can do it in such a way, but replace:

frm.strFieldName.Name

with:

Forms(frm)!Controls(strFieldName).Value

I also noticed you are referring to the "Name" property. It's the Value property you use to set the control's value. If the above works, try replacing it with this:

Me!Controls(strFieldName).Value

And if that works too, try this:

Me!strFieldName.value

 

[Vin]

Something different..
I left the pasing and receiving as is:
Call Attachments(Form, Form.PassField)
Function Attachments(frm As Form, strFieldName As Control)
The only thing I changed was the frm.strFieldName.Name to Forms(frm)!Controls(strFieldName).Value and I get Run-time error 13: Type mismatch.

 

[vbaInet]

Remove the "Form." in Form.PassField in your call statement.

 

[Vin]

No Luck - same error

 

[vbaInet]

It's hard to diagnose like this. I think it's the way the control is referenced. Is the size of your db small? Zip it and attach it.

 

[vbaInet]

Tell me also, what line is it throwing up that error?

 

[Vin]

Please see attached, and thanks again for your help

 

[vbaInet]

There you go. Take note of the the few comments I wrote in your code. Also, instead of using form type and control type as your paramters, just pass a string of the form name and control name and handle it using the Forms and Controls collection in the function.

 

[Vin]

Brilliant!
I really appreciate you spending your time to help vbaInet.
Thankyou once again - I will take your suggestions on board - I tend to be overly code verbose at times when less is often more.
Cheers,
Vince

 

[vbaInet]

No worries Vince. I remember trying to do that once and I couldn't. I would have to investigate that more, I feel there might be a way.

Good luck with the rest.