Make Reference To File in Path 2

[LarryDavidow]

I am using parts of the Employee Add and Remove image found in the nwind.mdb file. The field [ImagePath] stores the path and the file name. I want to set the value of [Field1] to the file name - EXCLUDING the path using the [ImagePath] field.

Any help would be much appreciated.

 

[scriverb]

Paste this function into a database module:

Public Function InStrRight(vSearchStr As String, vTargetStr As String) As Long
Dim i As Integer
For i = (Len(vSearchStr) - Len(vTargetStr) + 1) To 1 Step -1
If InStr(i, vSearchStr, vTargetStr, 1) = i Then
InStrRight = i 'Position of vTargetStr
Exit For
End If
Next i
If InStrRight <> i Then InStrRight = 0
End Function

Now in the ControlSource of the control [Field1] put this code:

=Trim(Mid$(Me.[ImagePath], InStrRight(Me.[ImagePath],&quot;\&quot+1))

This should strip off the database name and place it in this control. You could also use this code in a query to update a field called DBName and then just change the controlsource of [Field1] to DBName for display. This would change it just one time and then you can use it from there on out.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[LarryDavidow]

Thanks for the reply, Bob. I tried your solution, however, I get a #Name? in Field1. I'm using this on a simple form based on a table.

Don't know what the problem might be...

 

[TonyJollans]

Hi Larry,

If you are using Access 2000 (or above) you can use InStrRev instead of having to codei it yourself, so just use the following amended version of Bob's code without adding the function.

=Trim(Mid$(Me.[ImagePath], InStrRev(Me.[ImagePath],&quot;\&quot+1))

Enjoy,
Tony

 

[LarryDavidow]

Thanks Tony

Still no joy. After looking at my OnCurrent event, there has got to be a way to just make a reference to the file name and make it equal to [Field1]. Here's the code...

Private Sub Form_Current()
Dim res As Boolean
Dim Fname As String

path = CurrentProject.path

On Error Resume Next
errormsg.Visible = False
If Not IsNull(Me!Photo) Then
res = IsRelative(Me!Photo)
Fname = Me![ImagePath]
If (res = True) Then
Fname = path & &quot;\&quot; & Fname
End If

Me![ImageFrame].Picture = Fname
showImageFrame
Me.PaintPalette = Me![ImageFrame].ObjectPalette
If (Me![ImageFrame].Picture <> Fname) Then
hideImageFrame
errormsg.Caption = &quot;Picture not found&quot;
errormsg.Visible = True
End If
Else
hideImageFrame
errormsg.Caption = &quot;Click Add/Change to add picture&quot;
errormsg.Visible = True
End If
End Sub

I'm sure everything I need is just sitting under my nose and I can't see it!!!!!

 

[scriverb]

Well, you really threw me a curve on this one, TonyJollans. I have both A97 and A2K loaded. I am using IE 6. In A2k I can not find a way to use the InStrRev function as you have mentioned. Is there a library reference that needs to be made to access it.

I have found a link that cross-references VBA vs VBScript functions and I found that the InStrRev function is in VBScript but not in VBA. I don't think that this function is abalable to VBA in A2k. Please correct me I am wrong. Here is the link I spoke of.

http://www.hostinglogin.com/help/vbscript/vbstoc.htm

LarryDavidow : After you have loaded the Function that I indicated above then the following would strip off your file name. This is a portion of your OnCurrent Code. Just add the red line:
If (res = True) Then
Fname = Path & &quot;\&quot; & Fname
Me.[Field1] = Trim(Mid$(Fname, InStrRight(Fname,&quot;\&quot+1))
End If

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[TonyJollans]

Hi Bob,

I have only recently upgraded to 2000 and done virtually nothing in the way of customising it. InStrRev is, according to my Object Browser a member of VBA.Strings.

I have not used it in Access so did check before posting. I first used it in Excel '97 where, I thought, it came with the Analysis ToolPak AddIn (I say I thought because I have just looked on another machine which is still on '97 and now I can't find it). My understanding was that it just came as part of 2000 and my experience to date confirms that; your experience obviously doesn't.

At the moment I cannot throw any more light on the subject, but I'm still looking.

Enjoy,
Tony

 

[scriverb]

Check out this link:

http://msdn.microsoft.com/library/d...-us/script56/html/vtorifeatureinformation.asp

Well, what I found is that it comes along with a whole bunch of other functions and opertors as part of VBScript. BBut, I can find nowhere that is shows that it is included in ACCESS 2k.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[LarryDavidow]

Hi Bob

Thanks for the help.

Getting a &quot;Expected Variable or Procedure, not Module&quot; error on the InStrRight module.

 

[scriverb]

My apologies, Tony. I found it and have now got it working with the A2k function InStrRev which provides the same function as my personally developed code above. The parameters are a little different but it gives the same results.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Larry, we have been resolving the availability of the InStrRev function and concluded that it is available in A2k. At this point let's use it and not the code that I provided. Let's use the following code to make the assignment in your Oncurrent code:

Me.[Field1] = Trim(Mid$(Me.[ImagePath], InStrRev(Me.[ImagePath],&quot;\&quot;,-1)+1))

This should strip off the file name.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[LarryDavidow]

Guys, I really appreciate your help, but feel pretty bad. My field is not updating at all. Getting no errors, but no action either. Do I have to add any references???

I just hope the problem isn't between the chair and the keyboard!!! I follow instructions pretty well, and have been able to build this database from all of your help, so I hope it's not me!!! )

 

[LarryDavidow]

OK folks, I fixed it.

Thank you so much for all of your help!! Stars all around!!

The code needs to appear before the If statement.

Private Sub Form_Current()
Dim res As Boolean
Dim Fname As String

path = CurrentProject.path

On Error Resume Next
errormsg.Visible = False
If Not IsNull(Me!Photo) Then
res = IsRelative(Me!Photo)
Fname = Me![ImagePath]
Me.[ImageFront] = Trim(Mid$(Me.[ImagePath], InStrRev(Me.[ImagePath], &quot;\&quot;, -1) + 1))
If (res = True) Then
Fname = path & &quot;\&quot; & Fname
End If

Me![ImageFrame].Picture = Fname
showImageFrame
Me.PaintPalette = Me![ImageFrame].ObjectPalette
If (Me![ImageFrame].Picture <> Fname) Then
hideImageFrame
errormsg.Caption = &quot;Picture not found&quot;
errormsg.Visible = True
End If
Else
hideImageFrame
errormsg.Caption = &quot;Click Add/Change to add picture&quot;
errormsg.Visible = True
End If

End Sub

Thanks again for all your help!!!