How get picture file name to be written into table field

I have the following code on a onclick event of a command button on a form.  The code opens a Windows Explorer window and the user can cop/paste a jpg file into the Explorer window.  The use then closes the Explorer window and goes ahead and fills in other fields I=on the form.

But What I would also like to have happen when the user is taken back to the form is have the name of the jpg file written to a field named "PictureFileName" in the records source table, tblRecords.

Can this even be done?  Here is the onclick event code:

Private Sub cmdCreateClientImageFolder_Click()
On Error GoTo Err_cmdCreateClientImageFolder_Click

    Dim strAppName As String
    Dim strClientImageFolderDataPath As String

    strClientImageFolderDataPath = CreateClientImageFolder(Me.txtClientName)
    If Len(strClientImageFolderDataPath) > 0 Then
        MsgBox _
            "The folder has been created on server.  Remember to place image files in this folder."
        strAppName = "explorer.exe " & strClientImageFolderDataPath
        Call Shell(strAppName, 1)
        Me.txtClientImageFolderDataPath = strClientImageFolderDataPath
        MsgBox "An error has occurred. Please contact the system administrator.", _
            vbCritical + vbOKOnly
    End If

    Exit Sub

    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & _
        Err.Description & vbCrLf & "Error Source: " & Err.Source
    Resume Exit_cmdCreateClientImageFolder_Click

End Sub

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Yes it can be done. The code you have posted however is for creating a folder? I dont quite see the context.

The filepath can be saved in several ways. If the form you speak of is bound to tblRecords, then the easiest is to save it to a control/field on the form.

Otherwise you can either do a SQL command or a recordset operation.
Dim rs as dao.recordset
set rs=CurrentDB.OpenRecordset("SELECT * from tblRecords",dbOpenDynaset+DbSeechanges)
Set rs=nothing

Open in new window

SteveL13Author Commented:
Yes, the code is meant to create a folder.  And when the folder opens on the users screen they can copy/paste a jpg image into that folder.

If I use your code where does it go?
Eric ShermanAccountant/DeveloperCommented:
Will the jpg picture file name relate to a particular record in the table or is the [PictureFileName] field related to the current record open on the form???

What is the table name for the Record Source of your Form object???

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SteveL13Author Commented:
Jpg, isrelated to the current record open on the form.

Table is tblRecords.
Eric ShermanAccountant/DeveloperCommented:
>>>>>But What I would also like to have happen when the user is taken back to the form is have the name of the jpg file written to a field named "PictureFileName" in the records source table, tblRecords<<<<<

1.) Include the field [PictureFileName] on your form ... you can even make it a hidden field ... name the Text Control TxtPictureFileName.
2.) In your code when the user is taken back to the form ... use the form's OnCurrent Event to populate this control as shown below.

Me.TxtPictureFileName = "YourPictureFileName"
Me.Dirty = False


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Curious if you have considered the following scenarios:
What if a user copies multiple files into the folder?
What if the copies say, an excel spreadsheet into the folder?

Is there any reason why you are not having the user select the file directly from within your application?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.