Hyperlinks

[Bob]

Hi I want to put a hyperlink on a form that takes you to a folder on a
shared drive. i.e. T:\ Folders\12345 which I can do but

what I want to do is for the link to work from a command button that takes
you to the folder with the same number as the record you are in

i.e. CustomerID 12777 takes you to the folded T:\ Folders\12777 when you
click the command button and so on.

Is this possible? If so how do I go about it?

Thanks Bob

 

[Joan Wild]

Me.cmdNameOfCommandBtn.HyperlinkAddress = "T:\Folders\" & Me.CustomerID

Are you certain that users will all have "T:\" mapped the same?

 

[Bob]

Joan thanks, that works really well but is it possible to expand on that, we
store all our files in blocks of 100

i.e. T:\Folders\OpenFiles\20000 To 20100\ Then in this folder are folders
20000 to 20100

Is it possible for it to search in the Open files folder to find the correct
file? If not we would have to start storing them all in one folder which
would be a pain
because we move the blocks over to a folder call ClosedFiles when all of the
files in a block are finished with. This makes it easier and quicker to
back up, also because there
are so many folders the closed ones are stored on our E:\ drive in the same
way.

Also what about if no file is found what's the best way to handle this?

And lastly yes we all seem to be mapped the same way to get to the "T"
drive.

Thanks for help Bob



Me.cmdNameOfCommandBtn.HyperlinkAddress = "T:\Folders\" & Me.CustomerID

Are you certain that users will all have "T:\" mapped the same?

 

[Joan Wild]

"we seem to be mapped" - doesn't sound certain. Instead of using the mapped drives of T: and E:, you can instead use UNC naming and then not worry about the mapping. i.e. T:\ is mapped to some location on the server -- \\servername\path\folder - use that instead.

You can build up the path based on the current ID. Also, I assume there is some flag (closed) on the record that indicates whether it is open or closed.
watch for newsreader wrap

Dim strPath as String
Dim strFolder as String
strFolder = "(Me.CustomerID\100)*100+1 & " To " & (Me.CustomerID\100)*100+100 & "\"
'by the way I assume your folders run from 20001 to 20100 not 20000 to 20100 as that would cause overlap
If Me.closed= true then
strPath = "T:\Folders\ClosedFiles\" & strFolder & Me.CustomerID
Else
strPath = "T:\Folders\OpenFiles\" & strFolder & Me.CustomerID
End If

 

[Bob]

Hi Joan that produces 2 errors the first in line

strFolder = "(Me.CustomerID\100)*100+1 & " To " &
(Me.CustomerID\100)*100+100 & "\"
it high lights the word TO the error message is expecteded end of statement

And the second in
If Me.closed= true then
this produces Method or data not found

Thanks Bob

"we seem to be mapped" - doesn't sound certain. Instead of using the mapped
drives of T: and E:, you can instead use UNC naming and then not worry about
the mapping. i.e. T:\ is mapped to some location on the server --
\\servername\path\folder - use that instead.

You can build up the path based on the current ID. Also, I assume there is
some flag (closed) on the record that indicates whether it is open or
closed.
watch for newsreader wrap

Dim strPath as String
Dim strFolder as String
strFolder = "(Me.CustomerID\100)*100+1 & " To " &
(Me.CustomerID\100)*100+100 & "\"
'by the way I assume your folders run from 20001 to 20100 not 20000 to 20100
as that would cause overlap
If Me.closed= true then
strPath = "T:\Folders\ClosedFiles\" & strFolder & Me.CustomerID
Else
strPath = "T:\Folders\OpenFiles\" & strFolder & Me.CustomerID
End If

 

[Joan Wild]

Hi Joan that produces 2 errors the first in line

strFolder = "(Me.CustomerID\100)*100+1 & " To " &
(Me.CustomerID\100)*100+100 & "\"
it high lights the word TO the error message is expecteded end of statement

That should all be on one line

And the second in
If Me.closed= true then
this produces Method or data not found

You need to replace 'closed' with the actual name of the field you use (I assumed some yes/no field to indicate whether the file is closed or not).
[/QUOTE]

 

[Bob]

Joan it is all on one line but still produces the same error.

Yes we do have a tick box for closed files I will change that to the field
name.

Bob

Hi Joan that produces 2 errors the first in line

strFolder = "(Me.CustomerID\100)*100+1 & " To " &
(Me.CustomerID\100)*100+100 & "\"
it high lights the word TO the error message is expecteded end of
statement

That should all be on one line

And the second in
If Me.closed= true then
this produces Method or data not found

You need to replace 'closed' with the actual name of the field you use (I
assumed some yes/no field to indicate whether the file is closed or not).
[/QUOTE]

 

[Joan Wild]

Sorry Bob, I've taken a closer look, it should be
strFolder = (Me.CustomerID\100)*100+1 & " To " & (Me.CustomerID\100)*100+100 & "\"

(drop the first ' " ')

--
Joan Wild
Microsoft Access MVP

Joan it is all on one line but still produces the same error.

Yes we do have a tick box for closed files I will change that to the field
name.

Bob


That should all be on one line


You need to replace 'closed' with the actual name of the field you use (I
assumed some yes/no field to indicate whether the file is closed or not).

[/QUOTE]

 

[dbl]

Joan it now sort of works if you click the command button nothing happens if
you click the first command button for the first piece of code you sent me

Private Sub Command797_Click()
Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO
38100\" & Me.CustomerID
End Sub

Nothing happens because the Command should have been 797 not 796 (But if
you correct this yes it works)
But if you then hit the second command796 button it works and goes to the
correct folder. Say 38020

If you then scroll through 2 or 3 records and re hit the command796 button
it still goes to folder 38020 in stead of say record 38023

If you hit the first button and then the second button it goes to the
correct record.

As you can see I have set it up to the server address and the code is now as
follows:

Private Sub Command796_Click()
Dim strPath As String
Dim strFolder As String
strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CutomerID \ 100)
* 100 + 100 & "\"
If Me.RecordComplete = True Then
strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
Else
strPath = "\\Dgt04y2j\T\Open Files\" & strFolder & Me.CustomerID
End If
End Sub

I hope you followed that if you hit command796 nothing happens but if you
hit the wrongly coded Command 797 and then the Command796 the Command796
works.

I was wondering if the record range is wrong the first folder is 7001 To
7100 and there is of course no maximum as new folder are added every day. We
are currently in the 39000 range.
The way I read the code it could be from 1 in groups of 100 then in would go
up to 10100 (100*100+100) is that correct? properly not what does the &
"\" bit do?

Thanks for your help Bob

Sorry Bob, I've taken a closer look, it should be
strFolder = (Me.CustomerID\100)*100+1 & " To " & (Me.CustomerID\100)*100+100
& "\"

(drop the first ' " ')

--
Joan Wild
Microsoft Access MVP

Joan it is all on one line but still produces the same error.

Yes we do have a tick box for closed files I will change that to the field
name.

Bob


That should all be on one line


You need to replace 'closed' with the actual name of the field you use (I
assumed some yes/no field to indicate whether the file is closed or not).

[/QUOTE]

 

[dbl]

Joan the bit I forgot to mention was it only works in the range set by the
first button.

Regards Bob

Sorry Bob, I've taken a closer look, it should be
strFolder = (Me.CustomerID\100)*100+1 & " To " & (Me.CustomerID\100)*100+100
& "\"

(drop the first ' " ')

--
Joan Wild
Microsoft Access MVP

Joan it is all on one line but still produces the same error.

Yes we do have a tick box for closed files I will change that to the field
name.

Bob


That should all be on one line


You need to replace 'closed' with the actual name of the field you use (I
assumed some yes/no field to indicate whether the file is closed or not).

[/QUOTE]

 

[Joan Wild]

The number and names of the command buttons is confusing. Do you actually have two command buttons on your form? If you only have one then get rid of the code for the other. I also suggest you give the command button a decent name e.g. GoToFile (open the property sheet while the Command button is selected and on the Other tab modify the name property). The code that works seems to have a different path i.e. there's an 'OPEN' in it after the Open Files\

Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO 38100\" & Me.CustomerID

You also have a typo on the strFolder line - Cutomer instead of Customer
I've added an additional line, in case the record isn't saved when you click the button

Private Sub GoToFile_Click()
Dim strPath As String
Dim strFolder As String

If Me.Dirty Then Me.Dirty = false
strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CustomerID \ 100) * 100 + 100 & "\"

If Me.RecordComplete = True Then
strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
Else
'don't know whether the OPEN should be here or not; and if so should the above have a CLOSED in it?
strPath = "\\Dgt04y2j\T\Open Files\OPEN" & strFolder & Me.CustomerID
End If
End Sub

The way I read the code it could be from 1 in groups of 100 then in would go
up to 10100 (100*100+100) is that correct? properly not what does the &
"\" bit do?

It takes the CustomerID (say 14512) and then \100 which will divide by 100 and return the integer result which is 145 then *100 to multiply by 100, then add 1 - the result is 14501
The & concatenates that with a space followed by To followed by another space, so now we have
14501 To
Then CustomerID\100 gives us 145; the *100 gives 14500; the +100 gives us 14600, so now we have
14501 To 14600
Then concatenate the \ (needed in the path)
14501 To 14600\ is the strFolder
I tested this with 7012 and it works.
The strPath builds up the path using strFolder eg.

"\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
would be
"\\Dgt04y2j\E\Closed Files\14501 To 14600\14512


--
Joan Wild
Microsoft Access MVP
: Joan it now sort of works if you click the command button nothing happens if
: you click the first command button for the first piece of code you sent me
:
: Private Sub Command797_Click()
: Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO
: 38100\" & Me.CustomerID
: End Sub
:
: Nothing happens because the Command should have been 797 not 796 (But if
: you correct this yes it works)
: But if you then hit the second command796 button it works and goes to the
: correct folder. Say 38020
:
: If you then scroll through 2 or 3 records and re hit the command796 button
: it still goes to folder 38020 in stead of say record 38023
:
: If you hit the first button and then the second button it goes to the
: correct record.
:
: As you can see I have set it up to the server address and the code is now as
: follows:
:
: Private Sub Command796_Click()
: Dim strPath As String
: Dim strFolder As String
: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CutomerID \ 100)
: * 100 + 100 & "\"
: If Me.RecordComplete = True Then
: strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
: Else
: strPath = "\\Dgt04y2j\T\Open Files\" & strFolder & Me.CustomerID
: End If
: End Sub
:
: I hope you followed that if you hit command796 nothing happens but if you
: hit the wrongly coded Command 797 and then the Command796 the Command796
: works.
:
: I was wondering if the record range is wrong the first folder is 7001 To
: 7100 and there is of course no maximum as new folder are added every day. We
: are currently in the 39000 range.
: The way I read the code it could be from 1 in groups of 100 then in would go
: up to 10100 (100*100+100) is that correct? properly not what does the &
: "\" bit do?
:
: Thanks for your help Bob
:
: : Sorry Bob, I've taken a closer look, it should be
: strFolder = (Me.CustomerID\100)*100+1 & " To " & (Me.CustomerID\100)*100+100
: & "\"
:
: (drop the first ' " ')
:
: --
: Joan Wild
: Microsoft Access MVP
: : > Joan it is all on one line but still produces the same error.
: >
: > Yes we do have a tick box for closed files I will change that to the field
: > name.
: >
: > Bob
: > : > : >> Hi Joan that produces 2 errors the first in line
: >>
: >> strFolder = "(Me.CustomerID\100)*100+1 & " To " &
: >> (Me.CustomerID\100)*100+100 & "\"
: >> it high lights the word TO the error message is expecteded end of
: >> statement
: >
: > That should all be on one line
: >
: >> And the second in
: >> If Me.closed= true then
: >> this produces Method or data not found
: >
: > You need to replace 'closed' with the actual name of the field you use (I
: > assumed some yes/no field to indicate whether the file is closed or not).
: >
: >
: > --
: > Joan Wild
: > Microsoft Access MVP
: >>
: >
: >
:
:

 

[bob]

The code is correct as follows:

Private Sub Command796_Click()
Dim strPath As String
Dim strFolder As String
If Me.Dirty Then Me.Dirty = False

strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CustomerID \ 100)
* 100 + 100 & "\"
If Me.RecordComplete = True Then
strPath = "\\Dgt04y2j\L\Closed Files\" & strFolder & Me.CustomerID
Else
strPath = "\\Dgt04y2j\L\Open Files\" & strFolder & Me.CustomerID
End If

Other button deleted

Nothing happens when you click the button

Regards Bob

End Sub
The number and names of the command buttons is confusing. Do you actually
have two command buttons on your form? If you only have one then get rid of
the code for the other. I also suggest you give the command button a decent
name e.g. GoToFile (open the property sheet while the Command button is
selected and on the Other tab modify the name property). The code that
works seems to have a different path i.e. there's an 'OPEN' in it after the
Open Files\

Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO
38100\" & Me.CustomerID

You also have a typo on the strFolder line - Cutomer instead of Customer
I've added an additional line, in case the record isn't saved when you click
the button

Private Sub GoToFile_Click()
Dim strPath As String
Dim strFolder As String

If Me.Dirty Then Me.Dirty = false
strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CustomerID \
100) * 100 + 100 & "\"

If Me.RecordComplete = True Then
strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
Else
'don't know whether the OPEN should be here or not; and if so should the
above have a CLOSED in it?
strPath = "\\Dgt04y2j\T\Open Files\OPEN" & strFolder & Me.CustomerID
End If
End Sub

The way I read the code it could be from 1 in groups of 100 then in would
go
up to 10100 (100*100+100) is that correct? properly not what does the
&
"\" bit do?

It takes the CustomerID (say 14512) and then \100 which will divide by 100
and return the integer result which is 145 then *100 to multiply by 100,
then add 1 - the result is 14501
The & concatenates that with a space followed by To followed by another
space, so now we have
14501 To
Then CustomerID\100 gives us 145; the *100 gives 14500; the +100 gives us
14600, so now we have
14501 To 14600
Then concatenate the \ (needed in the path)
14501 To 14600\ is the strFolder
I tested this with 7012 and it works.
The strPath builds up the path using strFolder eg.

"\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
would be
"\\Dgt04y2j\E\Closed Files\14501 To 14600\14512


--
Joan Wild
Microsoft Access MVP
: Joan it now sort of works if you click the command button nothing happens
if
: you click the first command button for the first piece of code you sent me
:
: Private Sub Command797_Click()
: Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO
: 38100\" & Me.CustomerID
: End Sub
:
: Nothing happens because the Command should have been 797 not 796 (But if
: you correct this yes it works)
: But if you then hit the second command796 button it works and goes to the
: correct folder. Say 38020
:
: If you then scroll through 2 or 3 records and re hit the command796 button
: it still goes to folder 38020 in stead of say record 38023
:
: If you hit the first button and then the second button it goes to the
: correct record.
:
: As you can see I have set it up to the server address and the code is now
as
: follows:
:
: Private Sub Command796_Click()
: Dim strPath As String
: Dim strFolder As String
: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CutomerID \
100)
: * 100 + 100 & "\"
: If Me.RecordComplete = True Then
: strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
: Else
: strPath = "\\Dgt04y2j\T\Open Files\" & strFolder & Me.CustomerID
: End If
: End Sub
:
: I hope you followed that if you hit command796 nothing happens but if you
: hit the wrongly coded Command 797 and then the Command796 the Command796
: works.
:
: I was wondering if the record range is wrong the first folder is 7001 To
: 7100 and there is of course no maximum as new folder are added every day.
We
: are currently in the 39000 range.
: The way I read the code it could be from 1 in groups of 100 then in would
go
: up to 10100 (100*100+100) is that correct? properly not what does the
&
: "\" bit do?
:
: Thanks for your help Bob
:
: : Sorry Bob, I've taken a closer look, it should be
: strFolder = (Me.CustomerID\100)*100+1 & " To " &
(Me.CustomerID\100)*100+100
: & "\"
:
: (drop the first ' " ')
:
: --
: Joan Wild
: Microsoft Access MVP
: : > Joan it is all on one line but still produces the same error.
: >
: > Yes we do have a tick box for closed files I will change that to the
field
: > name.
: >
: > Bob
: > : > : >> Hi Joan that produces 2 errors the first in line
: >>
: >> strFolder = "(Me.CustomerID\100)*100+1 & " To " &
: >> (Me.CustomerID\100)*100+100 & "\"
: >> it high lights the word TO the error message is expecteded end of
: >> statement
: >
: > That should all be on one line
: >
: >> And the second in
: >> If Me.closed= true then
: >> this produces Method or data not found
: >
: > You need to replace 'closed' with the actual name of the field you use
(I
: > assumed some yes/no field to indicate whether the file is closed or
not).
: >
: >
: > --
: > Joan Wild
: > Microsoft Access MVP
: >>
: >
: >
:
:

 

[Joan Wild]

Good grief; I got so caught up in getting the concatenation correct, I forgot the actual command to open the hyperlink...
After the End If
Me.Command796.HyperlinkAddress = strPath


--
Joan Wild
Microsoft Access MVP
: The code is correct as follows:
:
: Private Sub Command796_Click()
: Dim strPath As String
: Dim strFolder As String
: If Me.Dirty Then Me.Dirty = False
:
: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CustomerID \ 100)
: * 100 + 100 & "\"
: If Me.RecordComplete = True Then
: strPath = "\\Dgt04y2j\L\Closed Files\" & strFolder & Me.CustomerID
: Else
: strPath = "\\Dgt04y2j\L\Open Files\" & strFolder & Me.CustomerID
: End If
:
: Other button deleted
:
: Nothing happens when you click the button
:
: Regards Bob
:
: End Sub
: : The number and names of the command buttons is confusing. Do you actually
: have two command buttons on your form? If you only have one then get rid of
: the code for the other. I also suggest you give the command button a decent
: name e.g. GoToFile (open the property sheet while the Command button is
: selected and on the Other tab modify the name property). The code that
: works seems to have a different path i.e. there's an 'OPEN' in it after the
: Open Files\
: > Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO
: > 38100\" & Me.CustomerID
: You also have a typo on the strFolder line - Cutomer instead of Customer
: I've added an additional line, in case the record isn't saved when you click
: the button
:
: Private Sub GoToFile_Click()
: Dim strPath As String
: Dim strFolder As String
:
: If Me.Dirty Then Me.Dirty = false
: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CustomerID \
: 100) * 100 + 100 & "\"
:
: If Me.RecordComplete = True Then
: strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
: Else
: 'don't know whether the OPEN should be here or not; and if so should the
: above have a CLOSED in it?
: strPath = "\\Dgt04y2j\T\Open Files\OPEN" & strFolder & Me.CustomerID
: End If
: End Sub
:
: > The way I read the code it could be from 1 in groups of 100 then in would
: > go
: > up to 10100 (100*100+100) is that correct? properly not what does the
: > &
: > "\" bit do?
:
: It takes the CustomerID (say 14512) and then \100 which will divide by 100
: and return the integer result which is 145 then *100 to multiply by 100,
: then add 1 - the result is 14501
: The & concatenates that with a space followed by To followed by another
: space, so now we have
: 14501 To
: Then CustomerID\100 gives us 145; the *100 gives 14500; the +100 gives us
: 14600, so now we have
: 14501 To 14600
: Then concatenate the \ (needed in the path)
: 14501 To 14600\ is the strFolder
: I tested this with 7012 and it works.
: The strPath builds up the path using strFolder eg.
:
: "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
: would be
: "\\Dgt04y2j\E\Closed Files\14501 To 14600\14512
:
:
: --
: Joan Wild
: Microsoft Access MVP
: :: Joan it now sort of works if you click the command button nothing happens
: if
:: you click the first command button for the first piece of code you sent me
::
:: Private Sub Command797_Click()
:: Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO
:: 38100\" & Me.CustomerID
:: End Sub
::
:: Nothing happens because the Command should have been 797 not 796 (But if
:: you correct this yes it works)
:: But if you then hit the second command796 button it works and goes to the
:: correct folder. Say 38020
::
:: If you then scroll through 2 or 3 records and re hit the command796 button
:: it still goes to folder 38020 in stead of say record 38023
::
:: If you hit the first button and then the second button it goes to the
:: correct record.
::
:: As you can see I have set it up to the server address and the code is now
: as
:: follows:
::
:: Private Sub Command796_Click()
:: Dim strPath As String
:: Dim strFolder As String
:: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CutomerID \
: 100)
:: * 100 + 100 & "\"
:: If Me.RecordComplete = True Then
:: strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
:: Else
:: strPath = "\\Dgt04y2j\T\Open Files\" & strFolder & Me.CustomerID
:: End If
:: End Sub
::
:: I hope you followed that if you hit command796 nothing happens but if you
:: hit the wrongly coded Command 797 and then the Command796 the Command796
:: works.
::
:: I was wondering if the record range is wrong the first folder is 7001 To
:: 7100 and there is of course no maximum as new folder are added every day.
: We
:: are currently in the 39000 range.
:: The way I read the code it could be from 1 in groups of 100 then in would
: go
:: up to 10100 (100*100+100) is that correct? properly not what does the
: &
:: "\" bit do?
::
:: Thanks for your help Bob
::
:: :: Sorry Bob, I've taken a closer look, it should be
:: strFolder = (Me.CustomerID\100)*100+1 & " To " &
: (Me.CustomerID\100)*100+100
:: & "\"
::
:: (drop the first ' " ')
::
:: --
:: Joan Wild
:: Microsoft Access MVP
:: :: > Joan it is all on one line but still produces the same error.
:: >
:: > Yes we do have a tick box for closed files I will change that to the
: field
:: > name.
:: >
:: > Bob
:: > :: > :: >> Hi Joan that produces 2 errors the first in line
:: >>
:: >> strFolder = "(Me.CustomerID\100)*100+1 & " To " &
:: >> (Me.CustomerID\100)*100+100 & "\"
:: >> it high lights the word TO the error message is expecteded end of
:: >> statement
:: >
:: > That should all be on one line
:: >
:: >> And the second in
:: >> If Me.closed= true then
:: >> this produces Method or data not found
:: >
:: > You need to replace 'closed' with the actual name of the field you use
: (I
:: > assumed some yes/no field to indicate whether the file is closed or
: not).
:: >
:: >
:: > --
:: > Joan Wild
:: > Microsoft Access MVP
:: >>
:: >
:: >
::
::
:
:

 

[Bob]

Hi Joan I must say thank you for you patience

Well we are getting a lot nearer it how comes up with the following error

Unable to open (then the correct folder path followed by)cannot find
specified file (yet it is displaying the correct path now every time
including the correct folder number)

I have also tried L:\Open Files instead of \\Dgt04y2j\L\Open Files but it
makes no difference both ways comes up with the correct folder path and
displays the folder number.

Yet the way we started did find and open the file with the following:

Me.Command797.HyperlinkAddress =L:\Open Files\Open 38001 To 38100" &
Me.CustomerID (this works every time in this folder group)

The only difference as I can see is we have named the last folder.

Regards and thanks Bob



Good grief; I got so caught up in getting the concatenation correct, I
forgot the actual command to open the hyperlink...
After the End If
Me.Command796.HyperlinkAddress = strPath


--
Joan Wild
Microsoft Access MVP
: The code is correct as follows:
:
: Private Sub Command796_Click()
: Dim strPath As String
: Dim strFolder As String
: If Me.Dirty Then Me.Dirty = False
:
: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CustomerID \
100)
: * 100 + 100 & "\"
: If Me.RecordComplete = True Then
: strPath = "\\Dgt04y2j\L\Closed Files\" & strFolder & Me.CustomerID
: Else
: strPath = "\\Dgt04y2j\L\Open Files\" & strFolder & Me.CustomerID
: End If
:
: Other button deleted
:
: Nothing happens when you click the button
:
: Regards Bob
:
: End Sub
: : The number and names of the command buttons is confusing. Do you actually
: have two command buttons on your form? If you only have one then get rid
of
: the code for the other. I also suggest you give the command button a
decent
: name e.g. GoToFile (open the property sheet while the Command button is
: selected and on the Other tab modify the name property). The code that
: works seems to have a different path i.e. there's an 'OPEN' in it after
the
: Open Files\
: > Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO
: > 38100\" & Me.CustomerID
: You also have a typo on the strFolder line - Cutomer instead of Customer
: I've added an additional line, in case the record isn't saved when you
click
: the button
:
: Private Sub GoToFile_Click()
: Dim strPath As String
: Dim strFolder As String
:
: If Me.Dirty Then Me.Dirty = false
: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CustomerID \
: 100) * 100 + 100 & "\"
:
: If Me.RecordComplete = True Then
: strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
: Else
: 'don't know whether the OPEN should be here or not; and if so should the
: above have a CLOSED in it?
: strPath = "\\Dgt04y2j\T\Open Files\OPEN" & strFolder & Me.CustomerID
: End If
: End Sub
:
: > The way I read the code it could be from 1 in groups of 100 then in
would
: > go
: > up to 10100 (100*100+100) is that correct? properly not what does
the
: > &
: > "\" bit do?
:
: It takes the CustomerID (say 14512) and then \100 which will divide by 100
: and return the integer result which is 145 then *100 to multiply by 100,
: then add 1 - the result is 14501
: The & concatenates that with a space followed by To followed by another
: space, so now we have
: 14501 To
: Then CustomerID\100 gives us 145; the *100 gives 14500; the +100 gives us
: 14600, so now we have
: 14501 To 14600
: Then concatenate the \ (needed in the path)
: 14501 To 14600\ is the strFolder
: I tested this with 7012 and it works.
: The strPath builds up the path using strFolder eg.
:
: "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
: would be
: "\\Dgt04y2j\E\Closed Files\14501 To 14600\14512
:
:
: --
: Joan Wild
: Microsoft Access MVP
: :: Joan it now sort of works if you click the command button nothing happens
: if
:: you click the first command button for the first piece of code you sent
me
::
:: Private Sub Command797_Click()
:: Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO
:: 38100\" & Me.CustomerID
:: End Sub
::
:: Nothing happens because the Command should have been 797 not 796 (But if
:: you correct this yes it works)
:: But if you then hit the second command796 button it works and goes to the
:: correct folder. Say 38020
::
:: If you then scroll through 2 or 3 records and re hit the command796
button
:: it still goes to folder 38020 in stead of say record 38023
::
:: If you hit the first button and then the second button it goes to the
:: correct record.
::
:: As you can see I have set it up to the server address and the code is now
: as
:: follows:
::
:: Private Sub Command796_Click()
:: Dim strPath As String
:: Dim strFolder As String
:: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CutomerID \
: 100)
:: * 100 + 100 & "\"
:: If Me.RecordComplete = True Then
:: strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
:: Else
:: strPath = "\\Dgt04y2j\T\Open Files\" & strFolder & Me.CustomerID
:: End If
:: End Sub
::
:: I hope you followed that if you hit command796 nothing happens but if you
:: hit the wrongly coded Command 797 and then the Command796 the Command796
:: works.
::
:: I was wondering if the record range is wrong the first folder is 7001 To
:: 7100 and there is of course no maximum as new folder are added every day.
: We
:: are currently in the 39000 range.
:: The way I read the code it could be from 1 in groups of 100 then in would
: go
:: up to 10100 (100*100+100) is that correct? properly not what does the
: &
:: "\" bit do?
::
:: Thanks for your help Bob
::
:: :: Sorry Bob, I've taken a closer look, it should be
:: strFolder = (Me.CustomerID\100)*100+1 & " To " &
: (Me.CustomerID\100)*100+100
:: & "\"
::
:: (drop the first ' " ')
::
:: --
:: Joan Wild
:: Microsoft Access MVP
:: :: > Joan it is all on one line but still produces the same error.
:: >
:: > Yes we do have a tick box for closed files I will change that to the
: field
:: > name.
:: >
:: > Bob
:: > :: > :: >> Hi Joan that produces 2 errors the first in line
:: >>
:: >> strFolder = "(Me.CustomerID\100)*100+1 & " To " &
:: >> (Me.CustomerID\100)*100+100 & "\"
:: >> it high lights the word TO the error message is expecteded end of
:: >> statement
:: >
:: > That should all be on one line
:: >
:: >> And the second in
:: >> If Me.closed= true then
:: >> this produces Method or data not found
:: >
:: > You need to replace 'closed' with the actual name of the field you use
: (I
:: > assumed some yes/no field to indicate whether the file is closed or
: not).
:: >
:: >
:: > --
:: > Joan Wild
:: > Microsoft Access MVP
:: >>
:: >
:: >
::
::
:
:

 

[Bob]

Joan looking at it again when it displays the group folder name in the error
message

Open 38001 To 38100 it displays it as 38001 To 38100

it is leaving out the word "Open" is that where the problem lies?

Regards Bob


Good grief; I got so caught up in getting the concatenation correct, I
forgot the actual command to open the hyperlink...
After the End If
Me.Command796.HyperlinkAddress = strPath


--
Joan Wild
Microsoft Access MVP
: The code is correct as follows:
:
: Private Sub Command796_Click()
: Dim strPath As String
: Dim strFolder As String
: If Me.Dirty Then Me.Dirty = False
:
: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CustomerID \
100)
: * 100 + 100 & "\"
: If Me.RecordComplete = True Then
: strPath = "\\Dgt04y2j\L\Closed Files\" & strFolder & Me.CustomerID
: Else
: strPath = "\\Dgt04y2j\L\Open Files\" & strFolder & Me.CustomerID
: End If
:
: Other button deleted
:
: Nothing happens when you click the button
:
: Regards Bob
:
: End Sub
: : The number and names of the command buttons is confusing. Do you actually
: have two command buttons on your form? If you only have one then get rid
of
: the code for the other. I also suggest you give the command button a
decent
: name e.g. GoToFile (open the property sheet while the Command button is
: selected and on the Other tab modify the name property). The code that
: works seems to have a different path i.e. there's an 'OPEN' in it after
the
: Open Files\
: > Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO
: > 38100\" & Me.CustomerID
: You also have a typo on the strFolder line - Cutomer instead of Customer
: I've added an additional line, in case the record isn't saved when you
click
: the button
:
: Private Sub GoToFile_Click()
: Dim strPath As String
: Dim strFolder As String
:
: If Me.Dirty Then Me.Dirty = false
: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CustomerID \
: 100) * 100 + 100 & "\"
:
: If Me.RecordComplete = True Then
: strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
: Else
: 'don't know whether the OPEN should be here or not; and if so should the
: above have a CLOSED in it?
: strPath = "\\Dgt04y2j\T\Open Files\OPEN" & strFolder & Me.CustomerID
: End If
: End Sub
:
: > The way I read the code it could be from 1 in groups of 100 then in
would
: > go
: > up to 10100 (100*100+100) is that correct? properly not what does
the
: > &
: > "\" bit do?
:
: It takes the CustomerID (say 14512) and then \100 which will divide by 100
: and return the integer result which is 145 then *100 to multiply by 100,
: then add 1 - the result is 14501
: The & concatenates that with a space followed by To followed by another
: space, so now we have
: 14501 To
: Then CustomerID\100 gives us 145; the *100 gives 14500; the +100 gives us
: 14600, so now we have
: 14501 To 14600
: Then concatenate the \ (needed in the path)
: 14501 To 14600\ is the strFolder
: I tested this with 7012 and it works.
: The strPath builds up the path using strFolder eg.
:
: "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
: would be
: "\\Dgt04y2j\E\Closed Files\14501 To 14600\14512
:
:
: --
: Joan Wild
: Microsoft Access MVP
: :: Joan it now sort of works if you click the command button nothing happens
: if
:: you click the first command button for the first piece of code you sent
me
::
:: Private Sub Command797_Click()
:: Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO
:: 38100\" & Me.CustomerID
:: End Sub
::
:: Nothing happens because the Command should have been 797 not 796 (But if
:: you correct this yes it works)
:: But if you then hit the second command796 button it works and goes to the
:: correct folder. Say 38020
::
:: If you then scroll through 2 or 3 records and re hit the command796
button
:: it still goes to folder 38020 in stead of say record 38023
::
:: If you hit the first button and then the second button it goes to the
:: correct record.
::
:: As you can see I have set it up to the server address and the code is now
: as
:: follows:
::
:: Private Sub Command796_Click()
:: Dim strPath As String
:: Dim strFolder As String
:: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CutomerID \
: 100)
:: * 100 + 100 & "\"
:: If Me.RecordComplete = True Then
:: strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
:: Else
:: strPath = "\\Dgt04y2j\T\Open Files\" & strFolder & Me.CustomerID
:: End If
:: End Sub
::
:: I hope you followed that if you hit command796 nothing happens but if you
:: hit the wrongly coded Command 797 and then the Command796 the Command796
:: works.
::
:: I was wondering if the record range is wrong the first folder is 7001 To
:: 7100 and there is of course no maximum as new folder are added every day.
: We
:: are currently in the 39000 range.
:: The way I read the code it could be from 1 in groups of 100 then in would
: go
:: up to 10100 (100*100+100) is that correct? properly not what does the
: &
:: "\" bit do?
::
:: Thanks for your help Bob
::
:: :: Sorry Bob, I've taken a closer look, it should be
:: strFolder = (Me.CustomerID\100)*100+1 & " To " &
: (Me.CustomerID\100)*100+100
:: & "\"
::
:: (drop the first ' " ')
::
:: --
:: Joan Wild
:: Microsoft Access MVP
:: :: > Joan it is all on one line but still produces the same error.
:: >
:: > Yes we do have a tick box for closed files I will change that to the
: field
:: > name.
:: >
:: > Bob
:: > :: > :: >> Hi Joan that produces 2 errors the first in line
:: >>
:: >> strFolder = "(Me.CustomerID\100)*100+1 & " To " &
:: >> (Me.CustomerID\100)*100+100 & "\"
:: >> it high lights the word TO the error message is expecteded end of
:: >> statement
:: >
:: > That should all be on one line
:: >
:: >> And the second in
:: >> If Me.closed= true then
:: >> this produces Method or data not found
:: >
:: > You need to replace 'closed' with the actual name of the field you use
: (I
:: > assumed some yes/no field to indicate whether the file is closed or
: not).
:: >
:: >
:: > --
:: > Joan Wild
:: > Microsoft Access MVP
:: >>
:: >
:: >
::
::
:
:

 

[Bob]

Joan I have just re named the folder without the word "Open" and it works
fine is there a fix for that or do I have to rename all the folders?

Bob

 

[Joan Wild]

Sounds like it. If Open is supposed to be part of the path then you have to build it in. I don't know if the Closed should be there, but you should be able to figure it out based on the following.

If Me.RecordComplete = True Then
strPath = "\\Dgt04y2j\L\Closed Files\Closed " & strFolder & Me.CustomerID
Else
strPath = "\\Dgt04y2j\L\Open Files\Open " & strFolder & Me.CustomerID
End If

--
Joan Wild
Microsoft Access MVP
: Joan looking at it again when it displays the group folder name in the error
: message
:
: Open 38001 To 38100 it displays it as 38001 To 38100
:
: it is leaving out the word "Open" is that where the problem lies?
:
: Regards Bob
:
:
: : Good grief; I got so caught up in getting the concatenation correct, I
: forgot the actual command to open the hyperlink...
: After the End If
: Me.Command796.HyperlinkAddress = strPath
:
:
: --
: Joan Wild
: Microsoft Access MVP
: :: The code is correct as follows:
::
:: Private Sub Command796_Click()
:: Dim strPath As String
:: Dim strFolder As String
:: If Me.Dirty Then Me.Dirty = False
::
:: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CustomerID \
: 100)
:: * 100 + 100 & "\"
:: If Me.RecordComplete = True Then
:: strPath = "\\Dgt04y2j\L\Closed Files\" & strFolder & Me.CustomerID
:: Else
:: strPath = "\\Dgt04y2j\L\Open Files\" & strFolder & Me.CustomerID
:: End If
::
:: Other button deleted
::
:: Nothing happens when you click the button
::
:: Regards Bob
::
:: End Sub
:: :: The number and names of the command buttons is confusing. Do you actually
:: have two command buttons on your form? If you only have one then get rid
: of
:: the code for the other. I also suggest you give the command button a
: decent
:: name e.g. GoToFile (open the property sheet while the Command button is
:: selected and on the Other tab modify the name property). The code that
:: works seems to have a different path i.e. there's an 'OPEN' in it after
: the
:: Open Files\
:: > Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO
:: > 38100\" & Me.CustomerID
:: You also have a typo on the strFolder line - Cutomer instead of Customer
:: I've added an additional line, in case the record isn't saved when you
: click
:: the button
::
:: Private Sub GoToFile_Click()
:: Dim strPath As String
:: Dim strFolder As String
::
:: If Me.Dirty Then Me.Dirty = false
:: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CustomerID \
:: 100) * 100 + 100 & "\"
::
:: If Me.RecordComplete = True Then
:: strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
:: Else
:: 'don't know whether the OPEN should be here or not; and if so should the
:: above have a CLOSED in it?
:: strPath = "\\Dgt04y2j\T\Open Files\OPEN" & strFolder & Me.CustomerID
:: End If
:: End Sub
::
:: > The way I read the code it could be from 1 in groups of 100 then in
: would
:: > go
:: > up to 10100 (100*100+100) is that correct? properly not what does
: the
:: > &
:: > "\" bit do?
::
:: It takes the CustomerID (say 14512) and then \100 which will divide by 100
:: and return the integer result which is 145 then *100 to multiply by 100,
:: then add 1 - the result is 14501
:: The & concatenates that with a space followed by To followed by another
:: space, so now we have
:: 14501 To
:: Then CustomerID\100 gives us 145; the *100 gives 14500; the +100 gives us
:: 14600, so now we have
:: 14501 To 14600
:: Then concatenate the \ (needed in the path)
:: 14501 To 14600\ is the strFolder
:: I tested this with 7012 and it works.
:: The strPath builds up the path using strFolder eg.
::
:: "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
:: would be
:: "\\Dgt04y2j\E\Closed Files\14501 To 14600\14512
::
::
:: --
:: Joan Wild
:: Microsoft Access MVP
:: ::: Joan it now sort of works if you click the command button nothing happens
:: if
::: you click the first command button for the first piece of code you sent
: me
:::
::: Private Sub Command797_Click()
::: Me.Command796.HyperlinkAddress = "\\Dgt04y2j\T\Open Files\OPEN 38001 TO
::: 38100\" & Me.CustomerID
::: End Sub
:::
::: Nothing happens because the Command should have been 797 not 796 (But if
::: you correct this yes it works)
::: But if you then hit the second command796 button it works and goes to the
::: correct folder. Say 38020
:::
::: If you then scroll through 2 or 3 records and re hit the command796
: button
::: it still goes to folder 38020 in stead of say record 38023
:::
::: If you hit the first button and then the second button it goes to the
::: correct record.
:::
::: As you can see I have set it up to the server address and the code is now
:: as
::: follows:
:::
::: Private Sub Command796_Click()
::: Dim strPath As String
::: Dim strFolder As String
::: strFolder = (Me.CustomerID \ 100) * 100 + 1 & " To " & (Me.CutomerID \
:: 100)
::: * 100 + 100 & "\"
::: If Me.RecordComplete = True Then
::: strPath = "\\Dgt04y2j\E\Closed Files\" & strFolder & Me.CustomerID
::: Else
::: strPath = "\\Dgt04y2j\T\Open Files\" & strFolder & Me.CustomerID
::: End If
::: End Sub
:::
::: I hope you followed that if you hit command796 nothing happens but if you
::: hit the wrongly coded Command 797 and then the Command796 the Command796
::: works.
:::
::: I was wondering if the record range is wrong the first folder is 7001 To
::: 7100 and there is of course no maximum as new folder are added every day.
:: We
::: are currently in the 39000 range.
::: The way I read the code it could be from 1 in groups of 100 then in would
:: go
::: up to 10100 (100*100+100) is that correct? properly not what does the
:: &
::: "\" bit do?
:::
::: Thanks for your help Bob
:::
::: ::: Sorry Bob, I've taken a closer look, it should be
::: strFolder = (Me.CustomerID\100)*100+1 & " To " &
:: (Me.CustomerID\100)*100+100
::: & "\"
:::
::: (drop the first ' " ')
:::
::: --
::: Joan Wild
::: Microsoft Access MVP
::: ::: > Joan it is all on one line but still produces the same error.
::: >
::: > Yes we do have a tick box for closed files I will change that to the
:: field
::: > name.
::: >
::: > Bob
::: > ::: > ::: >> Hi Joan that produces 2 errors the first in line
::: >>
::: >> strFolder = "(Me.CustomerID\100)*100+1 & " To " &
::: >> (Me.CustomerID\100)*100+100 & "\"
::: >> it high lights the word TO the error message is expecteded end of
::: >> statement
::: >
::: > That should all be on one line
::: >
::: >> And the second in
::: >> If Me.closed= true then
::: >> this produces Method or data not found
::: >
::: > You need to replace 'closed' with the actual name of the field you use
:: (I
::: > assumed some yes/no field to indicate whether the file is closed or
:: not).
::: >
::: >
::: > --
::: > Joan Wild
::: > Microsoft Access MVP
::: >>
::: >
::: >
:::
:::
::
::
:
:

 

[Joan Wild]

See my other reply; don't rename your folders.

--
Joan Wild
Microsoft Access MVP
: Joan I have just re named the folder without the word "Open" and it works
: fine is there a fix for that or do I have to rename all the folders?
:
: Bob
: : > Joan the bit I forgot to mention was it only works in the range set by the
: > first button.
: >
: > Regards Bob
: >
: > : > Sorry Bob, I've taken a closer look, it should be
: > strFolder = (Me.CustomerID\100)*100+1 & " To " &
: > (Me.CustomerID\100)*100+100 & "\"
: >
: > (drop the first ' " ')
: >
: > --
: > Joan Wild
: > Microsoft Access MVP
: > : >> Joan it is all on one line but still produces the same error.
: >>
: >> Yes we do have a tick box for closed files I will change that to the
: >> field
: >> name.
: >>
: >> Bob
: >> : >> : >>> Hi Joan that produces 2 errors the first in line
: >>>
: >>> strFolder = "(Me.CustomerID\100)*100+1 & " To " &
: >>> (Me.CustomerID\100)*100+100 & "\"
: >>> it high lights the word TO the error message is expecteded end of
: >>> statement
: >>
: >> That should all be on one line
: >>
: >>> And the second in
: >>> If Me.closed= true then
: >>> this produces Method or data not found
: >>
: >> You need to replace 'closed' with the actual name of the field you use (I
: >> assumed some yes/no field to indicate whether the file is closed or not).
: >>
: >>
: >> --
: >> Joan Wild
: >> Microsoft Access MVP
: >>>
: >>
: >>
: >
: >
:
:

 

[Bob]

Joan that all works fine now.

I cannot thank you enough for you time and patience.

Regards and thanks again Bob

See my other reply; don't rename your folders.

--
Joan Wild
Microsoft Access MVP
: Joan I have just re named the folder without the word "Open" and it works
: fine is there a fix for that or do I have to rename all the folders?
:
: Bob
: : > Joan the bit I forgot to mention was it only works in the range set by
the
: > first button.
: >
: > Regards Bob
: >
: > : > Sorry Bob, I've taken a closer look, it should be
: > strFolder = (Me.CustomerID\100)*100+1 & " To " &
: > (Me.CustomerID\100)*100+100 & "\"
: >
: > (drop the first ' " ')
: >
: > --
: > Joan Wild
: > Microsoft Access MVP
: > : >> Joan it is all on one line but still produces the same error.
: >>
: >> Yes we do have a tick box for closed files I will change that to the
: >> field
: >> name.
: >>
: >> Bob
: >> : >> : >>> Hi Joan that produces 2 errors the first in line
: >>>
: >>> strFolder = "(Me.CustomerID\100)*100+1 & " To " &
: >>> (Me.CustomerID\100)*100+100 & "\"
: >>> it high lights the word TO the error message is expecteded end of
: >>> statement
: >>
: >> That should all be on one line
: >>
: >>> And the second in
: >>> If Me.closed= true then
: >>> this produces Method or data not found
: >>
: >> You need to replace 'closed' with the actual name of the field you use
(I
: >> assumed some yes/no field to indicate whether the file is closed or
not).
: >>
: >>
: >> --
: >> Joan Wild
: >> Microsoft Access MVP
: >>>
: >>
: >>
: >
: >
:
:

 

[Joan Wild]

You're welcome, Bob; all the best.

--
Joan Wild
Microsoft Access MVP
: Joan that all works fine now.
:
: I cannot thank you enough for you time and patience.
:
: Regards and thanks again Bob
: