Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107

    Stop Sub when criteria's are met.

    I have very minimal programming skills but created function which almost works. This Sub contains 4 parts. Part 1 & 2 opens folder if exists, 3 & 4 creates it if doesn't.  
    I can't figure it out how to stop part 2 and exit sub when criteria's are met and if not then execute part 3 or 4. 


    What Part 2 does instead, it opens the folder if exists and this is what I want but also jumps to part 4 and try to crate new folder which I don't want.

    HTML Code:
    'Open Zone Folder under different conditions
    Private Sub btnOpenZone_Click()
        Dim db As Dao.Database
        Set db = CurrentDb
        Dim rsFolder As Dao.Recordset
        Dim rsExistingZone As Dao.Recordset
        Dim OpenExistingZone As String
        Dim ExistingZoneCWP As String
        Dim oSQL As String
        Dim Path_Link As String
        Dim ZONE As String
        Dim CWP As String
        Dim PathCWP As String
        Dim PathZone As String
        Dim answer As Integer
        Dim ZoneFolderName As String
        
    'Get subdirectories for Zone
        Set rsFolder = db.OpenRecordset("tbl_PathsZoneFolderName", dbOpenDynaset, dbSeeChanges)
        
    'Get path from table tbl_Paths
        Path_Link = Nz(DLookup("[PathLink]", "tbl_Paths", "[PathID] = 4"), "")
    'Get CWP number from form tbl_Tracking
        CWP = Nz(DLookup("[EHT_EWP]", "[tbl_Tracking]", "[P_Iso_Dwg] ='" & Me.txtPIsoDwg & "'"), "")
    'Get zone number from table tbl_Tracking
        ZONE = Nz(DLookup("[EHT_Zone]", "[tbl_Tracking]", "[P_Iso_Dwg] ='" & Me.txtPIsoDwg & "'"), "")
        
    'Create CWP path
        PathCWP = Path_Link & CStr(CWP) ' & "\"
    'Create Zone path
        PathZone = PathCWP & "\" & CStr(ZONE)
        
    'Message for missing path in table tbl_Paths
        If Path_Link = "" Then
            MsgBox "Path not assigned in tbl_Paths", vbExclamation, "Empty Path"
            Exit Sub
        End If
    'Message, zone cell is empty on tracking form
        If ZONE = "" Then
            MsgBox "Zone not assigned yet.", vbExclamation, "EHT Zone"
            Exit Sub
        End If
        
    'Create paths
        PathCWP = Path_Link & CStr(CWP)
        PathZone = PathCWP & "\" & CStr(ZONE)
        
    'Part 1
    'Open existing directory for active record for which /CWP/Zone directory was created
        If Len(Dir(PathZone, vbDirectory)) <> 0 Then
            DoCmd.Hourglass True
            OpenNativeApp (PathZone)
            DoCmd.Hourglass False
            Exit Sub
        End If
        
    'Part 2.
    'Check if another CWP directory has Zone assigned for active record and if does open it (/differentCWP/zone).
        If Len(Dir(PathCWP, vbDirectory)) = 0 Then
    'Get CWP for active zone
            oSQL = "SELECT tbl_Tracking.EHT_Zone, tbl_Tracking.EHT_EWP"
            oSQL = oSQL & " FROM tbl_Tracking"
            oSQL = oSQL & " WHERE tbl_Tracking.EHT_Zone  = '" & Me.txtEhtZone & "'"
            
            Set rsExistingZone = db.OpenRecordset(oSQL)
            With rsExistingZone
                If Not .BOF And Not .EOF Then
                    .MoveLast
                    .MoveFirst
                    
                    Do Until rsExistingZone.EOF
                        ExistingZoneCWP = rsExistingZone("EHT_EWP")
                        rsExistingZone.MoveNext
                        OpenExistingZone = Path_Link & "\" & CStr(ExistingZoneCWP) & "\" & CStr(ZONE)
    'Open directory
                        DoCmd.Hourglass True
                        OpenNativeApp (OpenExistingZone)
                        DoCmd.Hourglass False
                    Loop
                    
                End If
                .Close
                Set rsExistingZone = Nothing
            End With
        End If
    'Exit Sub
        
    'Part 3
    'Check if CWP folder exists and if it does add active Zone subfolder
        
        If Len(Dir(PathCWP, vbDirectory)) <> 0 Then
            answer = MsgBox("No Zone  " + ZONE & " folder was created yet under CWP " + CWP & ". Do you want to add Zone " + ZONE & "  to CWP " + CWP & "?", vbInformation + vbYesNo + vbDefaultButton2, "Crate zone directory")
            If answer = vbNo Then
                Exit Sub
            Else
                MkDir PathZone
                Do Until rsFolder.EOF
                    ZoneFolderName = rsFolder!FolderName
                    MkDir (PathZone & "\" & ZoneFolderName)
                    rsFolder.MoveNext
                Loop
                rsFolder.Close
                Set rsFolder = Nothing
            End If
            MsgBox "done"
    'End If
    'Open directory
            DoCmd.Hourglass True
            OpenNativeApp (PathZone)
            DoCmd.Hourglass False
    'Exit Sub
        End If
    'Part 4
    'If no CWP folder exists then create it with active zone as a subfolder
        
        If Len(Dir(PathZone, vbDirectory)) = 0 Then
            answer = MsgBox("CWP folder " + CWP & " with Zone " + ZONE & " wasn't created yet . Do you want to create Zone " + ZONE & "  within new folder " + CWP & "", vbInformation + vbYesNo + vbDefaultButton2, "Crate zone directory")
            If answer = vbNo Then
                Exit Sub
            Else
                MkDir PathCWP
                MkDir PathZone
                
                Do Until rsFolder.EOF
                    ZoneFolderName = rsFolder!FolderName
                    MkDir (PathZone & "\" & ZoneFolderName)
                    rsFolder.MoveNext
                Loop
                rsFolder.Close
                Set rsFolder = Nothing
            End If
            MsgBox "done"
    'Open directory.
            DoCmd.Hourglass True
            OpenNativeApp (PathZone)
            DoCmd.Hourglass False
        End If
        
    '    Debug.Print Path_Link
    '    Debug.Print PathCWP
    '    Debug.Print PathZone
    '    Debug.Print OpenExistingZone
    '    Debug.Print ExistingZoneCWP
    '    Debug.Print ZoneFolderName
    End Sub
     


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What criteria must be met in part 2? Maybe use Exit Sub as you did in part 1? You are looping a recordset and opening multiple files?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In step 2, you open recordset "rsExistingZone".
    If "rsExistingZone" has records => Loop Until rsExistingZone.EOF, then exit sub.
    Code:
            Set rsExistingZone = db.OpenRecordset(oSQL)
            With rsExistingZone
                If Not .BOF And Not .EOF Then
                    .MoveLast
                    .MoveFirst
                    
                    Do Until rsExistingZone.EOF
                        ExistingZoneCWP = rsExistingZone("EHT_EWP")
                        rsExistingZone.MoveNext
                        OpenExistingZone = Path_Link & "\" & CStr(ExistingZoneCWP) & "\" & CStr(ZONE)
                        'Open directory
                        DoCmd.Hourglass True
                        OpenNativeApp (OpenExistingZone)
                        DoCmd.Hourglass False
                    Loop
                    
                    .Close
                    Set rsExistingZone = Nothing
                    
                    rsFolder.Close
                    Set rsFolder = Nothing
                    Set db = Nothing
                    Exit Sub
                    
                End If     
            End With
        End If
        'Exit Sub
        
        'Part 3


    If "rsExistingZone" has no records , What should happen?

    Exit sub???? or go to Step 3???

  4. #4
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    ssanfu, when rsexistinZone has no records then go to step 3, if it does then open folder and exit sub

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think the logic is around the wrong way.

    Why not check it exists first, if not create it , then you can simply always open it, as it must exist as you just created it?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    June7, part 2 is looking for Zone folder assigned under different CWP directory and if finds it then opens the Zone directory and code should exit the sub. If it can not then go to part 3 and look if CWP folder was created for another zone and if it is then add active Zone to it. If CWP folder doesn't exist yet then go to Part 4 and create new folder with new CWP and zone, CWP/Zone/Subfolders (all the same for each record)

  7. #7
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Minty, that's my problem I know I should check first if the conditions are met but don't know how to do that.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This is rather confusing code. You want to verify multiple folders exist and if not create them? Then you want to open multiple folders in loop with OpenNativeApp (OpenExistingZone)? Maybe should explain just what it is you want to accomplish. Exactly what process are you trying to automate?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    June7, Each time I want to open only one folder, yes I want to open folder when exists and if not then create it, basically I want program to evaluate each Part in sequential order and if the criteria's are met then open the folder and exit the sub, if not then keep on going till the criteria's are met. 
    There is one rule here, a Zone can belong to many CWP's but only one Zone folder is created. 
    Folder directory structure is as follow \\path from table\CWP\Zone\Subfolders (all the same for all zones), I want to open only one zone each time 

    Part 1 of the code opens me folder if the button "Z" was pressed under which folder was created. lets say I created folder H-9013-01 under CWP AB, if I am on this record then button opens the folder and exit the sub 

    Part 2 if the button for zone H-9013-01 was clicked under CWP AD then the code loops though all CWP's for that zone, finds the folder zone was created under in my example AB, opens the folder and exit the sub 

    Part 3 if I click on record W-9017-22 button and the zone folder was not created yet under AB then code adds new zone W-9017-22 to folder AB, opens the folder and exit the sub. 

    Part 4 if I click on record G-9001-01 under CWP AC and the directory AC was not created yet neither zone folder then this code creates new CWP directory AC and adds folder G-9001-01 to it opens it and end sub. 
    Attached Thumbnails Attached Thumbnails June.JPG  

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So if zone folder H-9013-01 exists under CWP folder AB, don't create under AD, use AB? Why AB and not AA or AC? AA and AC were created after AB and don't have a zone subfolder?

    If zone folder W-9017-22 doesn't exist under any CWP, create it under AB? There would never be a CWP AA for this zone or if it does get created it would refer to zone under AB?

    Very odd file structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    June7, yes only one zone folder is allowed. The user decides which CWP to choose (could be any of the listed CWP's). There is never going to be 2 the same zone folders under different CWP
    Yes your statement is correct for zone W-9017-22

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    See if this gets you somewhere.
    Code:
    Private Sub Command3_Click()
    Const strBasePath = "C:\Users\Owner\June\Forums\Test\"
    Dim strP As String
    Dim rs As DAO.Recordset
    
    If Dir(strBasePath & Me.EHTCWP & "\" & Me.EHTZone, vbDirectory) <> "" Then
        strP = strBasePath & Me.EHTCWP & "\" & Me.EHTZone
    Else
        'search for zone folder
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTracking WHERE EHTZone='" & Me.EHTZone & "'")
        Do While Not rs.EOF
            If Dir(strBasePath & rs!EHTCWP & "\" & rs!EHTZone, vbDirectory) <> "" Then
                strP = strBasePath & rs!EHTCWP & "\" & rs!EHTZone
                Exit Do
            End If
            rs.MoveNext
        Loop
    End If
    If strP = "" Then
        If Dir(strBasePath & Me.EHTCWP, vbDirectory) = "" Then MkDir strBasePath & "\" & Me.EHTCWP
        If Dir(strBasePath & Me.EHTCWP & "\" & Me.EHTZone, vbDirectory) = "" Then MkDir strBasePath & Me.EHTCWP & "\" & Me.EHTZone
        strP = strBasePath & Me.EHTCWP & "\" & Me.EHTZone
    End If
    Debug.Print strP 'or do something else with path
    End Sub
    This does assume zone folder has not been created in a parent folder that does not have a corresponding record in table.
    Last edited by June7; 04-08-2021 at 01:26 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I agree with June that file structure is a real maze, and probably the cause of your complications.

    If you made a simple change to

    \\path from table\Zone\CWP\Subfolders

    You would only need the Zone once followed by the CWP.
    It is like the hierarchy is all about face?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  14. #14
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    June7, thank you very much for your help, I tested the code and it does what it should.

  15. #15
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Minty, thank you for the advise , will do as instructed.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Stop an autofill
    By leot in forum Access
    Replies: 6
    Last Post: 08-20-2016, 11:34 AM
  2. Tab Stop in forms
    By data808 in forum Forms
    Replies: 8
    Last Post: 02-26-2014, 09:24 PM
  3. automatische stop
    By Foli in forum Access
    Replies: 0
    Last Post: 12-20-2010, 02:24 AM
  4. Replies: 2
    Last Post: 11-29-2009, 12:00 PM
  5. How to stop....
    By swampdonkey in forum Forms
    Replies: 0
    Last Post: 12-04-2006, 01:56 PM

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