Results 1 to 4 of 4
  1. #1
    doobybug is offline Advanced Beginner
    Windows 98/ME Access 2003
    Join Date
    Jan 2009
    Posts
    39

    Get path and save it


    Hi,

    First of all I am really grateful for this forum as it is helping me a lot. I have this code, where I am trying to get a path from a button - publications and save the path to a table. The path is clearly read out well as it is being outputted when the button publications is clicked, however when the button save is clicked, somehow the filePath variable is blank. Any ideas why? I declared filePath as a global variable

    Code:
    Option Compare DatabaseOption Explicit
    Public filePath As String
    
    
    
    
    Private Sub btnSave_Click()
        Dim refNo As String
        Dim Volume As Integer
        Dim dateStart As Date
        Dim dateEnd As Date
        Dim condition As String
        Dim shelf As String
        Dim publication As String
        
        Dim sql As String
         
        If (IsNull(Me.cmbNotary.Column(0)) Or IsNull(Me.txtVolume) Or IsNull(Me.txtDateStart) Or IsNull(Me.txtDateEnd) Or IsNull(Me.cmbShelf)) Then
           MsgBox "Fields cannot be left blank"
            
        Else
                
                refNo = cmbNotary.Column(0)
                Volume = Me.txtVolume
                dateStart = Me.txtDateStart
                dateEnd = Me.txtDateEnd
                
                
                If (Not IsNull(Me.txtCondition)) Then
                     condition = Me.txtCondition
                Else
                    condition = ""
                End If
                
                If (Not IsNull(Me.txtPublication)) Then
                     publication = Me.txtPublication
                Else
                    publication = ""
                End If
                
                shelf = Me.cmbShelf.Column(0)
                Dim lngRowsAffected As Long
                MsgBox filePath
                
                sql = "INSERT INTO tblNotaryIndex([NotaryRefNo], [Volume], [Date Start], [Date End], [Condition], [Publication], [Publication Link], [ShelfId]) " & _
                "VALUES('" & refNo & "'," & Volume & ",#" & dateStart & "#,#" & dateEnd & "#, '" & condition & "', '" & publication & "','" & filePath & "','" & shelf & "')"
                
                On Error GoTo ErrorMessage
                CurrentDb.Execute sql, dbFailOnError
          
                    
                cmbNotary.Value = Null
                Me.txtVolume = Null
                Me.txtDateStart = Null
                Me.txtDateEnd = Null
                Me.txtCondition = Null
                Me.txtPublication = Null
                cmbShelf.Value = Null
                filePath = ""
            
           
           
            
        End If
         Exit Sub
    
    
    ErrorMessage:
        MsgBox "Record already exists"
        
        
    End Sub
    
    
    
    
    
    
    Private Sub txtDateStart_AfterUpdate()
        VerifyDates
    End Sub
    Private Sub txtDateEnd_AfterUpdate()
        VerifyDates
    End Sub
    
    
    Private Sub VerifyDates()
        If Not (IsNull(Me.txtDateStart) Or IsNull(Me.txtDateEnd)) Then
            If Me.txtDateEnd < Me.txtDateStart Then
                MsgBox "Date End must be after Date Start"
                Me.txtDateEnd = Null
            End If
        End If
    End Sub
    
    
    Private Sub btnPublicationLink_Click()
       Dim f As Object
        Dim strFile As String
        Dim strFolder As String
        Dim varItem As Variant
        Dim filePath As String
    
    
        Set f = Application.FileDialog(3)
        f.AllowMultiSelect = True
        If f.Show Then
            For Each varItem In f.SelectedItems
                strFile = Dir(varItem)
                strFolder = Left(varItem, Len(varItem) - Len(strFile))
                filePath = strFolder & strFile
                MsgBox strFolder & strFile
               
            Next
        End If
      Set f = Nothing
    End Sub

  2. #2
    doobybug is offline Advanced Beginner
    Windows 98/ME Access 2003
    Join Date
    Jan 2009
    Posts
    39
    Ok problem solved. I did not know that global variables are global to all forms not just for that particular form. Changed the variable name and worked fine.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Perhaps a bit of explanation of variable scope will help because what you said is true and false. It depends.
    This one seems better to me than any M$oft explanation I've ever read
    https://bytes.com/topic/access/insig...-vba-ms-access
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Actually, that wasn't the problem. Take a look at your sub btnPublicationLink_Click()


    In it you have the declaration Dim filePath As String. That declaration overrides (i.e. takes precedence over) the global variable declaration for filePath, and it makes that occurance of filePath local to that sub, so that the value you give to filePath in the Sub is unknown outside of it.

    I did not know that global variables are global to all forms
    They are only global to all forms if you declare them in a code module that is not part of a form. If you declare them in a form's code module, they are known only in that form - other forms will not see them, even if you declare them as Public.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Browse to a File and Save Path in a Text Field
    By PSSMargaret in forum Access
    Replies: 2
    Last Post: 03-27-2017, 08:05 AM
  2. save as prompt with hard code path
    By xopherira in forum Programming
    Replies: 8
    Last Post: 09-09-2015, 12:57 PM
  3. Shared drive path to save attachments
    By zaffar_mughal in forum Access
    Replies: 3
    Last Post: 08-27-2014, 10:43 PM
  4. Browse buttons that save file path into field
    By Zandia in forum Import/Export Data
    Replies: 1
    Last Post: 06-04-2014, 02:55 PM
  5. Replies: 6
    Last Post: 05-27-2014, 05:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums