How to reference a value defined in another form

[ZachAtaiyan]

Hey all,

Here is my scenario.

I have a form with 3 subforms that can be selected using navagation tabs. On the first tab, I have a browse dialog that a user can choose a source folder. That source folder is associated to a String called RootFolder. On the second tab there is a button that would use the string RootFolder defined on the first tab to search and grab files within. I am having a hard time knowing what to do reference a value from another form...



Any suggestions?

[John_G]

In the declaration section of the main form, declare the variable as public:

Public RootFolder as string.

Then, in the subforms, you can refer to that variable with Forms![Mainformname].Rootfolder, or
me.Parent.Rootfolder.

HTH

John

[ZachAtaiyan]

Thank you!

[ZachAtaiyan]

So I have been trying to incorporate this, but It does not seem to work for this application. I know the rest of the code is correct because If I assign "C:\Users\bob\pictures\" to RootFolder2, it works perfectly. I inserted a message box to show what RootFolder2 was assigned to and it shows the destination I specified in the first form. Any ideas why this isn't working?

Overall Form:

Option Compare Database
Public RootFolder As String

1st form where folder is specified:

Option Compare Database
Public RootFolder As String
Private Sub SelectFolder_Click()
Dim strFolderName As String
    strFolderName = BrowseFolder("Choose Folder For Image Attachments")
    If Len(strFolderName) > 0 Then
        Forms!MainAssessmentForm.RootFolder = strFolderName
    End If
End Sub

2nd form where destination folder is used to import files:

'This sub-routine searches through a user-designated directory for every file that was created during a given time interval.
'Each file within the designated folder is run through the sub routine.
Sub LoopThroughFiles()
    Dim db As DAO.Database
    Dim rsParent As DAO.Recordset2
    Dim rsChild As DAO.Recordset2
    Dim StrFile As String
    Dim FileTime As Date
    Dim RootFolder2 As String
    
    'Captures end boundary timestamp
    Time2 = Now
    
    RootFolder2 = Forms!MainAssessmentForm.RootFolder
    'This section of code is used to search through the folder designated by the user in the introduction tab
    'to find files that were created between Time1 and Time2.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    StrFile = Dir(RootFolder2 & "*.jpg*")
    Do While Len(StrFile) > 0
        FileTime = FileDateTime(StrFile)                        'This code determines the time that each image was created.
        
        If FileTime >= Time1 And FileTime <= Time2 Then
            Set db = CurrentDb
            Set rsParent = Me.Recordset
            rsParent.Edit
            
            Set rsChild = rsParent.Fields("Field1").Value       'This code designates the target for the attachment within the form
            
            rsChild.AddNew
            rsChild.Fields("FileData").LoadFromFile StrFile     'This code attaches the file to the target destination
            
            rsChild.Update
            rsParent.Update
            
Exit_AddImage:
            Set rsChild = Nothing
            Set rsParent = Nothing
        End If
        
        StrFile = Dir                                           'This code calls the next file in the folder to determine if it should be attached
    Loop
End Sub

[June7]

What does "isn't working" mean - error message, wrong results, nothing happens?

Are you using a Tab control or Navigation Form? If using Navigation Form, review https://www.accessforums.net/forms/t...orm-32053.html

[ZachAtaiyan]

Sorry for not being so clear.

1. By not working, I mean nothing happens. There are no error boxes, it is just not using the string properly is the best I can tell...

2. I am using Navigation Control.

[John_G]

In your code, you use Time1, but I don't see it declared or given a value anywhere, so the IF.. statement is never true. I suspect that is the problem.

Suggestion:

In the declarations section of ALL code modules, put Option Explicit after the Option Compare Database.
This will force you to declare all variables with a Dim statement.

Then, after ANY coding change, compile your code (Debug - Compile in the VBA window).

You would be amazed at how many errors that will catch, including I think, this one.

John

[June7]

Did you review the referenced link?

If I understand Navigation forms correctly, cannot reference a subform directly from another subform because they are not open at the same time, hence the solution to use global variable or TempVars. The referenced link discusses this.

[ZachAtaiyan]

Time1 is defined by another section of code i didn't show, Whoops!

Currently this is used to call the paint app so I can easily create jpgs. It will be used to call the camera on the tablet this will be used on.

Private Sub Command27_Click()
'Image Capture Button - Utilizes Panasonic FZ-G1's Camera utility to capture images and store them to each record.
'Each photo is approx 149kb.
'Open the Camera Utility
    'Call Shell("C:\Program Files (x86)\Panasonic\PCam\PCam.exe", vbNormalFocus)
    Call Shell("C:\Windows\System32\mspaint.exe", vbNormalFocus)
'Captures initial boundary timestamp
Time1 = Now
End Sub

[John_G]

Hi -

If time1 is not declared with a Dim statement anywhere outside of the Command27_Click procedure, it is not going to be recognized outside that procedure, even more so if it is in another module. When you use Time1 in the LoopThroughFiles procedure, Access just makes a new variable of Variant type. Hence the importance of the Option Explicit statement.

Check the VBA help for "scope" for a more detailed explanation.

John

[ZachAtaiyan]

Everything related to the logic statements works fine. Everything is defined correctly. In your defense, I have done a poor job showing what i have defined.

@June7 I have been referencing the link. From: =[Forms]![NavigationForm]![NavigationSubform].[Form]![Base]
, What would be the [Base]? Mine would just be: "Forms!MainAssessmentForm!LandingPage.RootFold er" I dont know what the base would be and it only wants to consider LandingPage as a field and not as a subform.

[John_G]

Hi -

Quick question - where is the Dim statement for Time1 located?

Another thought - when you get the folder name from BrowseFolder, does it have a trailing "\"? If it does not, that is the problem. Change

StrFile = Dir(RootFolder2 & "*.jpg*")

to

StrFile = Dir(RootFolder2 & "\*.jpg*")

to see if that works.

HTH

John

[June7]

See my comment in post 8.

If I understand what you want, code behind one subform needs to reference value from another subform.

As I understand Navigation form, issue is both subforms are not available at same time. But I don't use Navigation form so I could be wrong. However, solution from the referenced links is to first set a global variable or TempVars from the first subform so it can be referenced by second subform.

[ZachAtaiyan]

@John_G, Thank you for the suggestion, it did not work but since I have added the Option Explicit command it is giving me run-time error 53. In the debugger it tells me that the error occurs at "FileTime = FileDateTime(StrFile)" but when I highlight that section of code it tells me the exact file it is referencing so I know it is locating it...

@June7 Thank you for your assistance as well. I will copy this code and attempt to use the Global variable and see if we gain any ground.

Thanks again to both of you!

[John_G]

I found the problem!

This statement : StrFile = Dir(RootFolder2 & "*.jpg*")

does not return the path name in to StrFile, only the actual file name (that's how Dir works), sot then this statement:

FileTime = FileDateTime(StrFile)

fails with the file-not-found error because it cannot find the file.

This little test I wrote works perfectly and shows how to solve the problem:

Sub DirTest()
  Dim str As String, Path As String
  Path = "C:\_NEW_JDDB\"
  str = Dir(Path & "*.*")
  While Len(str) > 0
    Debug.Print str
    Debug.Print FileDateTime(Path & str)
    str = Dir
  Wend
End Sub

You need to specify the full path in the initial Dir statement and in FileDateTime, but not in the second Dir.

With that, you should be on your way.

John