Solved If Then and On Error GoTo

[Denise2020]

Hello again, I have, in relation to another post, come upon another error that is confusing me. I have the following If then statement:

On Error GoTo ErrMsg

If Len(Me.Referens.Value & "") = 0 Then
    [Referens] = "#.....Explorer directory path..." & Me.Arendenr
    Me.Refresh
    Msgbox "....message to user telling them to create a folder in the specified directory"
    Application.FollowHyperlink "....Explorer directory path..."
Else
    Application.FollowHyperlink ("...Explorer directory path the user created..." & [Arendenr])
End If

On Error Resume Next
ErrMsg:
MsgBox "message to user"

The problem is that the error message box pops up even if the the code succeeds. It does what it is supposed to, but still pops up the "message to user" error msgbox (in both instances, whether it is doing the "If" part or the "Else" part) and I cannot figure out why. If I remove the error handler, then the Else part fails with a windows error message (cannot follow link) if the user has not created a folder at the directory as directed.

I am sure it is quite obvious but I am unfortunately not well versed in VBA. Thank you in advance!

 

[MarkK]

On Error GoTo ErrMsg

If Len(Me.Referens.Value & "") = 0 Then
    [Referens] = "#.....Explorer directory path..." & Me.Arendenr
    Me.Refresh
    Msgbox "....message to user"
    Application.FollowHyperlink "....Explorer directory path..."
Else
    Application.FollowHyperlink ("...Explorer directory path..." & [Arendenr])
End If

Exit Sub

ErrMsg:
MsgBox "message to user"

 

[Gasman]

Hello again, I have, in relation to another post, come upon another error that is confusing me. I have the following If then statement:

On Error GoTo ErrMsg

If Len(Me.Referens.Value & "") = 0 Then
    [Referens] = "#.....Explorer directory path..." & Me.Arendenr
    Me.Refresh
    Msgbox "....message to user telling them to create a folder in the specified directory"
    Application.FollowHyperlink "....Explorer directory path..."
Else
    Application.FollowHyperlink ("...Explorer directory path the user created..." & [Arendenr])
End If

On Error Resume Next
ErrMsg:
MsgBox "message to user"

The problem is that the error message box pops up even if the the code succeeds. It does what it is supposed to, but still pops up the "message to user" error msgbox (in both instances, whether it is doing the "If" part or the "Else" part) and I cannot figure out why. If I remove the error handler, then the Else part fails with a windows error message (cannot follow link) if the user has not created a folder at the directory as directed.

I am sure it is quite obvious but I am unfortunately not well versed in VBA. Thank you in advance!

That is because the code 'falls through' to that line.
Walk your code line by line and you will see it happen.

 

[moke123]

Personally I would probably use FSO. Test if the directory exists and create it if it doesn't. Skip the messages.

 

[Pat Hartman]

Do you KNOW what Me.Refresh does? Why are you using it in this context? You've cut off the surrounding code so your code is out of context therefore we have no idea if Me.Refresh will even work in whatever event you are running it from.

 

[moke123]

Sub TestMe()

   Dim strPath As String
  
   strPath = " Your directory path here "
    
    sCreateMissingFolders strPath
    
    Application.FollowHyperlink strPath
    
End Sub


Sub sCreateMissingFolders(strIN As String)

    Dim fso As Object, VarStr As Variant, strOut As String, i As Integer

    Set fso = CreateObject("Scripting.FileSystemObject")

    If Right(strIN, 1) = "\" Then strIN = Left(strIN, Len(strIN) - 1)  'remove trailing \

    VarStr = Split(strIN, "\")

    For i = 0 To UBound(VarStr)

        If strOut = "" Then
            strOut = VarStr(i)
        Else
            strOut = strOut & "\" & VarStr(i)
        End If
 
        If Not fso.FolderExists(strOut) Then  'if the folder does not exist, create it and any subfolders
            fso.CreateFolder (strOut)
        End If

    Next i

End Sub

 

[Denise2020]

Do you KNOW what Me.Refresh does? Why are you using it in this context? You've cut off the surrounding code so your code is out of context therefore we have no idea if Me.Refresh will even work in whatever event you are running it from.

I can't say I KNOW much about VBA but in this instance, I added it because after the auto-hyperlink is generated, it was not showing up until I refreshed the page. When I click the button, the text is generated and is filled in to the text box. Is there another/better way around this besides forcing users to refresh manually?

 

[Pat Hartman]

Me.Refresh forces Access to save the current record if it is dirty. Therefore, you can't use it in every event. Me.Repaint might solve the visual problem.

I don't ever use the hyperlink data type because it cannot be upsized to SQLServer. It obfuscates the actual stored data which can be confusing since there are two values involved. The one that is displayed and the one that is the actual hyperlink. You can use the FollowHyperlink method on a plain text field so you still get the hyperlink functionality without using the Hyperlink data type.

 

[Denise2020]

On Error GoTo ErrMsg

If Len(Me.Referens.Value & "") = 0 Then
    [Referens] = "#.....Explorer directory path..." & Me.Arendenr
    Me.Refresh
    Msgbox "....message to user"
    Application.FollowHyperlink "....Explorer directory path..."
Else
    Application.FollowHyperlink ("...Explorer directory path..." & [Arendenr])
End If

Exit Sub

ErrMsg:
MsgBox "message to user"

Exactly what I needed, thank you so much! I realized as soon as I saw it that I had the Exit Sub earlier but had retyped and changed and forgot to add that line again. Thank you again!

 

[Denise2020]

Me.Refresh forces Access to save the current record if it is dirty. Therefore, you can't use it in every event. Me.Repaint might solve the visual problem.

I don't ever use the hyperlink data type because it cannot be upsized to SQLServer. It obfuscates the actual stored data which can be confusing since there are two values involved. The one that is displayed and the one that is the actual hyperlink. You can use the FollowHyperlink method on a plain text field so you still get the hyperlink functionality without using the Hyperlink data type.

Thank you for the me.Repaint suggestion, I will try that!

I am not using a hyperlink data type in the table, but it is in a text field and I added the Application.FollowHyperlink code to it. Is that what you mean? I have heard a lot about the negatives of hyperlink data type so I have avoided it. Earlier it was an attachment field before I knew about the "evils" of attachment fields. So it is evolving.

I would like to think I am learning but I am definitely a slow learner so it is taking so long without any actual training or classes! Thank you so much again for your help!

 

[Pat Hartman]

If you are populating a field in code and it is not showing immediately on the form, the problem is that you probably have changed the name property of the control. So, the control is bound to field1 and the Name property of the control is txtField1. This is fine and I normally do this so I can distinguish between the data field and the control in code. However, that means that if your code is:

Me.field1 = "somevalue"

You are populating the bound field but not the control. That is why you don't see the value until you do something to force Access to repaint the screen. Instead, change your VBA to:

Me.txtfield1 = "somevalue"