If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Hyperlink field formating question
Hello,
I have a field that I think would work well as a Hyperlink field but don't know how to format it correctly. Currently the field is TEXT with entries like "09150906" (no quotes) which is a reference to a picture #6 taken today stored in a folder on the hard drive. I am entering the the field contents in the field in a hurry and would like avoid adding the "C:\Pics\xxxxxxxxx.jpg" (xxxx is current field contents) and have the formatting add that. I think a mask would be good, but that doesn't appear to be an option. I would also like to avoid code as that is over my head at this point. A simple recap: enter "09150906" and have the field recognized as "C:\Pics\09150906.jpg" Any ideas? Thanks, Mark |
#2
|
|||
|
|||
Hyperlink field formating question
BTW, quick add... I don't want to use the Attachment field to avoid bloat.
The database is already 150meg and seems like Hyperlink would be faster to create and manage on the fly. THX. "tstew" wrote: Hello, I have a field that I think would work well as a Hyperlink field but don't know how to format it correctly. Currently the field is TEXT with entries like "09150906" (no quotes) which is a reference to a picture #6 taken today stored in a folder on the hard drive. I am entering the the field contents in the field in a hurry and would like avoid adding the "C:\Pics\xxxxxxxxx.jpg" (xxxx is current field contents) and have the formatting add that. I think a mask would be good, but that doesn't appear to be an option. I would also like to avoid code as that is over my head at this point. A simple recap: enter "09150906" and have the field recognized as "C:\Pics\09150906.jpg" Any ideas? Thanks, Mark |
#3
|
|||
|
|||
Hyperlink field formating question
You are not going to be able to do this without a bit of code. This is
fairly simple. You can paste the code below in the After Update event of the control. You will just have to change the names to reference your actual control name. Just replace Text9 with your control name. If Not IsNull(Me.Text9) Then Me.Text9 = Replace("C:\Pics\xxxxxxxxx.jpg", "xxxxxxxxx", Me.Text9) End If -- Dave Hargis, Microsoft Access MVP "tstew" wrote: Hello, I have a field that I think would work well as a Hyperlink field but don't know how to format it correctly. Currently the field is TEXT with entries like "09150906" (no quotes) which is a reference to a picture #6 taken today stored in a folder on the hard drive. I am entering the the field contents in the field in a hurry and would like avoid adding the "C:\Pics\xxxxxxxxx.jpg" (xxxx is current field contents) and have the formatting add that. I think a mask would be good, but that doesn't appear to be an option. I would also like to avoid code as that is over my head at this point. A simple recap: enter "09150906" and have the field recognized as "C:\Pics\09150906.jpg" Any ideas? Thanks, Mark |
#4
|
|||
|
|||
Hyperlink field formating question
Hi Dave,
Thank you. I figured it would end up needing a small bit of code. Kind of a dumb followup question. The "xxxxxx", does it have to be a specific number of placeholders, or can I use wildcards? Different people are going to be generating the picture file names and I am planning on having them append their initials to the front of the file name. ie: 09150906 for me (no identifier = me) and JL09150906 for someone else. Thanks a million, Mark "Klatuu" wrote: You are not going to be able to do this without a bit of code. This is fairly simple. You can paste the code below in the After Update event of the control. You will just have to change the names to reference your actual control name. Just replace Text9 with your control name. If Not IsNull(Me.Text9) Then Me.Text9 = Replace("C:\Pics\xxxxxxxxx.jpg", "xxxxxxxxx", Me.Text9) End If -- Dave Hargis, Microsoft Access MVP "tstew" wrote: Hello, I have a field that I think would work well as a Hyperlink field but don't know how to format it correctly. Currently the field is TEXT with entries like "09150906" (no quotes) which is a reference to a picture #6 taken today stored in a folder on the hard drive. I am entering the the field contents in the field in a hurry and would like avoid adding the "C:\Pics\xxxxxxxxx.jpg" (xxxx is current field contents) and have the formatting add that. I think a mask would be good, but that doesn't appear to be an option. I would also like to avoid code as that is over my head at this point. A simple recap: enter "09150906" and have the field recognized as "C:\Pics\09150906.jpg" Any ideas? Thanks, Mark |
#5
|
|||
|
|||
Hyperlink field formating question
The xxxxx does not have to be a specific number of characters nor does it
have to be a specific character. But, it does need to be unique to the string involved so it will be replaced correctly. you could even use something like "Name Goes Here" as long as you specify it in the Find argument of the Replace function. As to users entering their initials, there is a minor problem in that a user may forget or may enter an incorrect value. To correct that, you could use a table with each user's name and initials and use that to add the value to the table. This may stretch you a bit, but it will be a good learning exercise, so I will give you some detailed instructions. Please feel free to post back with questions. First, copy the following code into a standard module. Never name a module the same as any Sub or Function in the module. My module is named modGetUserAPI. The mod tells me it is a module object, and the API tells me it is an API call module. 'Start of Code Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String, ByRef intN As Long) As Long Public Function GetUserID() Dim Buffer As String * 20 Dim Length As Long Dim lngresult As Long, userid As String Length = 20 lngresult = GetUserNameA(Buffer, Length) If lngresult 0 Then userid = Left(Buffer, Length - 1) Else userid = "xxxxxxx" End If GetUserID = UCase(userid) End Function 'End of Code Now you need a table with two text fields. I will call the table tblUserInit First field is text and I will call it User_Name Second field is text and will be User_Initials Create the table and put an entry for each user. Note the User Name should be the user's Windows login name. Now you can modify the code I passed earlier to pick up the user initials without the user having to even be aware of it: If Not IsNull(Me.Text9) Then Me.Text9 = Replace("C:\Pics\xxxxxxxxx.jpg", "xxxxxxxxx", DLookup("User_Initials", "tblUserInit", "User_Name = """ & GetUserID & """" & Me.Text9) End If -- Dave Hargis, Microsoft Access MVP "tstew" wrote: Hi Dave, Thank you. I figured it would end up needing a small bit of code. Kind of a dumb followup question. The "xxxxxx", does it have to be a specific number of placeholders, or can I use wildcards? Different people are going to be generating the picture file names and I am planning on having them append their initials to the front of the file name. ie: 09150906 for me (no identifier = me) and JL09150906 for someone else. Thanks a million, Mark "Klatuu" wrote: You are not going to be able to do this without a bit of code. This is fairly simple. You can paste the code below in the After Update event of the control. You will just have to change the names to reference your actual control name. Just replace Text9 with your control name. If Not IsNull(Me.Text9) Then Me.Text9 = Replace("C:\Pics\xxxxxxxxx.jpg", "xxxxxxxxx", Me.Text9) End If -- Dave Hargis, Microsoft Access MVP "tstew" wrote: Hello, I have a field that I think would work well as a Hyperlink field but don't know how to format it correctly. Currently the field is TEXT with entries like "09150906" (no quotes) which is a reference to a picture #6 taken today stored in a folder on the hard drive. I am entering the the field contents in the field in a hurry and would like avoid adding the "C:\Pics\xxxxxxxxx.jpg" (xxxx is current field contents) and have the formatting add that. I think a mask would be good, but that doesn't appear to be an option. I would also like to avoid code as that is over my head at this point. A simple recap: enter "09150906" and have the field recognized as "C:\Pics\09150906.jpg" Any ideas? Thanks, Mark |
#6
|
|||
|
|||
Hyperlink field formating question
Thanks Dave,
You say "stretch a little". HA! That looks like leaping the Grand Canyon. I am working through a couple of Access books at the moment while trying to live with my current database design and still get work done. I'll play with these ideas an see what I get. Thank you so much for your time! Mark "Klatuu" wrote: The xxxxx does not have to be a specific number of characters nor does it have to be a specific character. But, it does need to be unique to the string involved so it will be replaced correctly. you could even use something like "Name Goes Here" as long as you specify it in the Find argument of the Replace function. As to users entering their initials, there is a minor problem in that a user may forget or may enter an incorrect value. To correct that, you could use a table with each user's name and initials and use that to add the value to the table. This may stretch you a bit, but it will be a good learning exercise, so I will give you some detailed instructions. Please feel free to post back with questions. First, copy the following code into a standard module. Never name a module the same as any Sub or Function in the module. My module is named modGetUserAPI. The mod tells me it is a module object, and the API tells me it is an API call module. 'Start of Code Private Declare Function GetUserNameA Lib "Advapi32" (ByVal strN As String, ByRef intN As Long) As Long Public Function GetUserID() Dim Buffer As String * 20 Dim Length As Long Dim lngresult As Long, userid As String Length = 20 lngresult = GetUserNameA(Buffer, Length) If lngresult 0 Then userid = Left(Buffer, Length - 1) Else userid = "xxxxxxx" End If GetUserID = UCase(userid) End Function 'End of Code Now you need a table with two text fields. I will call the table tblUserInit First field is text and I will call it User_Name Second field is text and will be User_Initials Create the table and put an entry for each user. Note the User Name should be the user's Windows login name. Now you can modify the code I passed earlier to pick up the user initials without the user having to even be aware of it: If Not IsNull(Me.Text9) Then Me.Text9 = Replace("C:\Pics\xxxxxxxxx.jpg", "xxxxxxxxx", DLookup("User_Initials", "tblUserInit", "User_Name = """ & GetUserID & """" & Me.Text9) End If -- Dave Hargis, Microsoft Access MVP "tstew" wrote: Hi Dave, Thank you. I figured it would end up needing a small bit of code. Kind of a dumb followup question. The "xxxxxx", does it have to be a specific number of placeholders, or can I use wildcards? Different people are going to be generating the picture file names and I am planning on having them append their initials to the front of the file name. ie: 09150906 for me (no identifier = me) and JL09150906 for someone else. Thanks a million, Mark "Klatuu" wrote: You are not going to be able to do this without a bit of code. This is fairly simple. You can paste the code below in the After Update event of the control. You will just have to change the names to reference your actual control name. Just replace Text9 with your control name. If Not IsNull(Me.Text9) Then Me.Text9 = Replace("C:\Pics\xxxxxxxxx.jpg", "xxxxxxxxx", Me.Text9) End If -- Dave Hargis, Microsoft Access MVP "tstew" wrote: Hello, I have a field that I think would work well as a Hyperlink field but don't know how to format it correctly. Currently the field is TEXT with entries like "09150906" (no quotes) which is a reference to a picture #6 taken today stored in a folder on the hard drive. I am entering the the field contents in the field in a hurry and would like avoid adding the "C:\Pics\xxxxxxxxx.jpg" (xxxx is current field contents) and have the formatting add that. I think a mask would be good, but that doesn't appear to be an option. I would also like to avoid code as that is over my head at this point. A simple recap: enter "09150906" and have the field recognized as "C:\Pics\09150906.jpg" Any ideas? Thanks, Mark |
Thread Tools | |
Display Modes | |
|
|