How to refer to files that reside in a subfolder?

[MNNovice]

I have linked a bunch of pictures that are contained within a subfolder. The path is defined in the On Load and On Timer Events of a form. When the form is open, every two seconds 33 pictures flip from one to the other. It runs just perfect.

I get error message only when I make a copy of the DB from my PC to my laptop as it looks for the path as defined in my code (e.g.“C:\folder name\sbufoldern ame” etc. ).

How do I modify the codes to make it generic so that when a copy of the database is transferred from one pc to another, the links to all the pictures will also transfer and the form will open without an error message. Any help with this is much appreciated. Thanks.

Right now the code looks like this:

Private Sub Form_Load()
Me.TimerInterval = 2000

ShowImage Me.Image1, "C:\HRS_DATABASE\MenuPics\Waheed.jpg"

End Sub

Private Sub Form_Timer()
' on any error go to Err_Form_Timer
On Error GoTo Err_Form_Timer

Static i As Integer
Dim path As String
Dim path1 As String
Dim iString As String
  
 
    path = "C:\HRS_DATABASE\MenuPics\C-"
    path1 = ".jpg"
  
   If i = 33 Then
        i = 1
    Else
        i = i + 1
    End If
  
  iString = i
  
    Select Case i
  
    Case i
        ShowImage Me.Image1, path & iString & path1
  
    End Select
  
Exit_Form_Timer:
  
    Exit Sub
  
Err_Form_Timer:
  
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Form_Timer:
  
End Sub

[patjones]

If you have the files in a network location, and you're transferring the database from one networked PC to another, it seems like it shouldn't be a problem.

If you are transferring the database from one random PC to another, where the files are kept on the local drive of each PC, it seems like you would first want to raise a file dialog to ask the user where the JPEGs are...not a difficult process if indeed you think it might work for you.

Pat

[MNNovice]

If you have the files in a network location, and you're transferring the database from one networked PC to another, it seems like it shouldn't be a problem.

If you are transferring the database from one random PC to another, where the files are kept on the local drive of each PC, it seems like you would first want to raise a file dialog to ask the user where the JPEGs are...not a difficult process if indeed you think it might work for you.

Pat

PAT:

Thanks for your response. I am a novice and don't know much about VBA. My codes are compiled based on input from various gurus like yourself. Can you give me some guidence on how to write the codes to:

raise a file dialog to ask the user where the JPEGs are

.

Thanks.

[patjones]

The most basic way to accomplish this is

Dim fd As FileDialog
Dim strFolder As String
  
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  
If fd.Show = -1 Then
     strFolder = fd.SelectedItems.Item(1)
Else
     Exit Sub
End If

In order for the FileDialog to work, you need to set the proper reference to the Microsoft 12.0 Object Library (it might be a prior version number if you are using Access 2003 or before) by going Tools > References... and then scrolling down to the reference.

You could put this code in the On Load event for the form. After making a selection from the folder picker, the path to the location gets put in strFolder. The name of the first file you want to view would need to be added to this path. For instance

ShowImage Me.Image1, strFolder & "\Waheed.jpg"

Pat

[NeoPa]

A technique I often use is to make store them relative to the subfolder the database resides in. That way I can use CurrentProject. Path to tell me the subfolder the db is in and work from there.

For instance, if your database is found in C:\Databases\My NewProject\ then your images could be stored in C:\Databases\My NewProject\Menu Pics\.

Private Sub Form_Timer()
' on any error go to Err_Form_Timer
On Error GoTo Err_Form_Timer

Static i As Integer
Dim path As String

    path = CurrentProject.Path & "\MenuPics\C-"

    i = (i Mod 32) + 1
    Call ShowImage(Me.Image1, path & i & ".jpg"

Exit_Form_Timer:

    Exit Sub

Err_Form_Timer:

    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Form_Timer:

End Sub

PS. I simplified your code a little, but didn't lose anything.

[MNNovice]

A technique I often use is to make store them relative to the subfolder the database resides in. That way I can use CurrentProject. Path to tell me the subfolder the db is in and work from there.

For instance, if your database is found in C:\Databases\My NewProject\ then your images could be stored in C:\Databases\My NewProject\Menu Pics\.

Private Sub Form_Timer()
' on any error go to Err_Form_Timer
On Error GoTo Err_Form_Timer

Static i As Integer
Dim path As String

    path = CurrentProject.Path & "\MenuPics\C-"

    i = (i Mod 32) + 1
    Call ShowImage(Me.Image1, path & i & ".jpg"

Exit_Form_Timer:

    Exit Sub

Err_Form_Timer:

    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Form_Timer:

End Sub

PS. I simplified your code a little, but didn't lose anything.

NeoPa:

I will try this and shall get back with my progress. Many thanks. M

[MNNovice]

The most basic way to accomplish this is

Dim fd As FileDialog
Dim strFolder As String
  
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  
If fd.Show = -1 Then
     strFolder = fd.SelectedItems.Item(1)
Else
     Exit Sub
End If

In order for the FileDialog to work, you need to set the proper reference to the Microsoft 12.0 Object Library (it might be a prior version number if you are using Access 2003 or before) by going Tools > References... and then scrolling down to the reference.

You could put this code in the On Load event for the form. After making a selection from the folder picker, the path to the location gets put in strFolder. The name of the first file you want to view would need to be added to this path. For instance

ShowImage Me.Image1, strFolder & "\Waheed.jpg"

Pat

Pat:

Sorry but I am not yet clear on how to work / set up Microsoft 12.0 object. I am using Office 2007.

Thanks for your suggestions. MNNovice

[patjones]

Pat:

Sorry but I am not yet clear on how to work / set up Microsoft 12.0 object. I am using Office 2007.

Thanks for your suggestions. MNNovice

In the VBA code window, go Tools > References...

You'll see a long list of references that you can select; you'll want to select Microsoft Office 12.0 Object Library.

References are often needed in order to make VBA interact with system objects like file dialogs.

Pat

[MNNovice]

In the VBA code window, go Tools > References...

You'll see a long list of references that you can select; you'll want to select Microsoft Office 12.0 Object Library.

References are often needed in order to make VBA interact with system objects like file dialogs.

Pat

I located that part. And I can see that MS 12.0 Object has a check mark next to it. Now what do I do? Click on browse? What did you mean by: [Quote] After making a selection from the folder picker, the path to the location gets put in strFolder?

Do I browse to select C:\HRS_Database \...etc?

How does this prevent the error message I get when I open a copy of the database from a different pc? My home pcs/laptops are not on network. Thanks.

[patjones]

You are supposed to navigate to the folder that contains the files you want to open. In the code you first posted, you would navigate to "C:\HRS_DATABAS E\MenuPics" and select that location, which will make strFolder = "C:\HRS_DATABAS E\MenuPics".

When you do the concatenation strFolder & "\Waheed.jp g", you get a complete path and filename that ShowImage can understand.

You would also use strFolder in the On Timer event procedure.

Pat

[MNNovice]

A technique I often use is to make store them relative to the subfolder the database resides in. That way I can use CurrentProject. Path to tell me the subfolder the db is in and work from there.

For instance, if your database is found in C:\Databases\My NewProject\ then your images could be stored in C:\Databases\My NewProject\Menu Pics\.

Private Sub Form_Timer()
' on any error go to Err_Form_Timer
On Error GoTo Err_Form_Timer

Static i As Integer
Dim path As String

    path = CurrentProject.Path & "\MenuPics\C-"

    i = (i Mod 32) + 1
    Call ShowImage(Me.Image1, path & i & ".jpg"

Exit_Form_Timer:

    Exit Sub

Err_Form_Timer:

    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Form_Timer:

End Sub

PS. I simplified your code a little, but didn't lose anything.

NeoPa:

These codes you sent me are so much simpler and clean looking. Wow! You have really mastered this skill.

I changed the codes to yours. Now I am getting an error message: “MS Access cannot open C:\HRS_DATABASE \MenuPics\Wahee d.jpb”

When I click on to fix it I am taken to Module 1 where .Picture = strImagePath inside ShowImage is highlighted. [Line 52 in Module 1]

Q3:What do I need to do to fix this problem?

Q2: My other form fItems also contains some picture files. How do I modify its codes so that my Database can be transferred from one PC to the other without any problem?

Here are all the codes for your review and comments:

Codes: Module 1

Option Compare Database
Option Explicit

Dim strResult As String
Dim strDatabasePath As String
Dim intSlashLocation As Integer
Public Function DisplayImage(ctlImageControl As Control, strImagePath As Variant) As String
On Error GoTo Err_DisplayImage


With ctlImageControl
    If IsNull(strImagePath) Then
        .Visible = False
        strResult = "No image Available"
    Else
        If InStr(1, strImagePath, "\") = 0 Then
            ' Path is relative
            strDatabasePath = CurrentProject.FullName
            intSlashLocation = InStrRev(strDatabasePath, "\", Len(strDatabasePath))
            strDatabasePath = Left(strDatabasePath, intSlashLocation)
            strImagePath = strDatabasePath & strImagePath
        End If
        .Visible = True
        .Picture = strImagePath
        strResult = ""
    End If
End With
    
Exit_DisplayImage:
    DisplayImage = strResult
    Exit Function

Err_DisplayImage:
    Select Case Err.Number
        Case 2220       ' Can't find the picture.
            ctlImageControl.Visible = False
            strResult = "Can't find image in the specified name."
            Resume Exit_DisplayImage:
        Case Else       ' Some other error.
            MsgBox Err.Number & " " & Err.Description
            strResult = "An error occurred displaying image."
            Resume Exit_DisplayImage:
    End Select
End Function
 
    Public Function ShowImage(ctlImageControl As Control, strImagePath As Variant)
Dim strDatabasePath As String
Dim intSlashLocation As Integer
  
    With ctlImageControl
        .Visible = True
        .Picture = strImagePath
    End With
   
End Function

Form: Switchboardhas these codes. It flips 33 pics every 2 seconds when the form is open.

Private Sub Form_Load()
Me.TimerInterval = 3000
End Sub

Private Sub Form_Timer()
' on any error go to Err_Form_Timer
On Error GoTo Err_Form_Timer

Static i As Integer
Dim path As String
  
 
    path = CurrentProject.path & "\MenuPics\C-"
      
    i = (i Mod 32) + 1
        Call ShowImage(Me.Image1, path & i & ".jpg")
  
Exit_Form_Timer:
  
    Exit Sub
  
Err_Form_Timer:
  
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Form_Timer:
  
End Sub

Form: fItems This is based on the main table called Items. I added an image of the cover on the Form's Header, for each DVD as it is entered/ added to the DB.

Option Compare Database
Option Explicit
Private Sub Form_AfterUpdate()
    CallDisplayImage
End Sub


Private Sub pName_AfterUpdate()
    CallDisplayImage
End Sub

Private Sub CallDisplayImage()
    Me!pNote = DisplayImage(Me!pFrame, Me!pName)
End Sub

Congrats on England's victory. Hard to believe USA won the group...Oh well...

[NeoPa]

MNNovice: I changed the codes to yours. Now I am getting an error message: “MS Access cannot open C:\HRS_DATABASE \MenuPics\Wahee d.jpb”

When I click on to fix it I am taken to Module 1 where .Picture = strImagePath inside ShowImage is highlighted. [Line 52 in Module 1]

Q3:What do I need to do to fix this problem?

If that is an accurate message then you have miscopied the code. The extension should be ".jpg".

MNNovice: Q2: My other form fItems also contains some picture files. How do I modify its codes so that my Database can be transferred from one PC to the other without any problem?

This question is much too open-ended. If you want help on something the question needs to be specific.

[OldBirdman]

“MS Access cannot open C:\HRS_DATABASE \MenuPics\Wahee d.jpb” is probably a typo, because this works per post #1. Microsoft, in its infinite wisdom does not allow copying from the message of a msgbox, so these must be continually re-typed into Google, Forums, Help, etc.

The problem here is what the database has stored in the properties of an the image control. If the image control is Image1, then Image1.PictureT ype="Linked" and Image1.Picture= "C:\HRS_DATABAS E\MenuPics\Wahe ed.jpg". This has been saved when the form was saved.

When Access opens this form, it checks various links, libraries, and other items to be sure all resources are available. This occurs even if the form is opened in Design View. The error cannot be trapped because it occurs before any event is triggered.

Once this has occurred it is difficult to remove. Image1.picture cannot be set to "". Access simply replaces it. You must first change Image1.PictureT ype to "", but this is invalid, 'Not in List'.

Access will defeat any work-around if the form is modified after image has been given a value. So, delete the image control, then recreate it on the form. Save the form. If you need to modify the form, do not switch to design view. Close the project, then reopen the project without the form opening. Open the form in design view and make any changes. Save. This will not have any path for Image1, so the suggestions previously given by others should work.

[MNNovice]

If that is an accurate message then you have miscopied the code. The extension should be ".jpg".

This question is much too open-ended. If you want help on something the question needs to be specific.

If that is an accurate message then you have miscopied the code. The extension should be ".jpg".

I verified my codes - it matches that of yours perfectly. Why shouldn't it? I used "copy" and "paste"

The extension was a typo when I was writing the e-mail - that's all.

This question is much too open-ended. If you want help on something the question needs to be specific.

All I was trying to explain is that the Module 1 contains some codes which refers to another form called fItems. Do these codes need to be modified in any way so that my DB can be transferred from one PC to the other without getting error messages like "Cannot find ....jpg file". If yes, how do I do it?

Hope this explains my question better. Thanks for your time.

[NeoPa]

MNNovice: All I was trying to explain is that the Module 1 contains some codes which refers to another form called fItems. Do these codes need to be modified in any way so that my DB can be transferred from one PC to the other without getting error messages like "Cannot find ....jpg file". If yes, how do I do it?

If you say so M. I looked and couldn't see any such references. Perhaps it may help if you told me which lines I should be looking at.

To be honest, I can't imagine any internal references (EG. to other forms within your database) requiring any changes to run on different PCs. It's only references to items outside that *may* be different, depending on how things are set up.