Hyperlink field formating question

[tstew]

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

 

[tstew]

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.

 

[Klatuu]

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

 

[tstew]

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]

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

 

[tstew]

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