Help with the Dir function 1

[docin]

Hi all,
I've run into the problem of the Dir function not wanting to work on certain machines. I've searched on here and read where some have made a user defined function containing the Dir function and then used it in their update query. My problem is I can't seem to get the user defined function (some call it a wrapper function) to work. In my update query which runs before the database fully launches...I have the following:

IIf(Dir("c:\Section_Cor_pictures\" & [Township] & [Range] & [Field29] & "_p" & ".jpg",3)<>"","c:\Section_Cor_pictures\" & [Township] & [Range] & [Field29] & "_p" & ".jpg","c:\ocsection\ocdef.bmp")

Which checks the above directory for a matching picture and puts that in my image field, otherwise it puts in a default bitmap (ocdef.bmp)

This query runs fine on the development machine...but I'm runing into error messages on some machines...so I need to have this redone somehow. Can anyone tell me how to make this update query into a "wrapper function" that I can call on its own or use in the query? Thanks in advance for any help.

 

[PCLewis]

What error message? It is relevant because any wrapper function will have the same issue to deal with.

You are calling Dir with ,3 so you are looking for Normal, Hidden and Read Only files.

Depending on the error, which can only be related to permissions to read a directory I assume (maybe the directory does not exist), you would write a wrapper like so:

' In a Module
Public Function AssertFilename(sFilename as String, sDefault as String) as String
  On Error resume next
  if dir(sFilename,3) = "" then sFilename="" 
  if Err.Number <> 0 or sFilename = "" then
    AssertFilename=sDefault
  else
    AssertFilename=sFilename
  end if
End function

Then call it:

AssertFilename("c:\Section_Cor_pictures\" & [Township] & [Range] & [Field29] & "_p" & ".jpg", "c:\ocsection\ocdef.bmp")

Or something like that

 

[docin]

Thanks PCLewis,
The error wasn't due to any file permissions. It was something like "unrecognized Dir function". I had read here in my searching that sometimes the Dir function wouldn't work on some machines inside a query...just a access 2000 thing I guess...but the workaround was to "wrap" the dir function. I think your code will do what I need. I am going to try it and let you know. I'm not sure how to place it in my update query...does it go inside of the IIF statement? If the file exists...I want to update the table with the name..if it doesn't I want to put the default "ocdef.bmp" picture in the field. Again, thanks for the reply.

 

[PCLewis]

No, you relpace the entire IIF statement with my second example code. You place the first example code (the function) in a Module.

 

[docin]

PCLewis,
Thanks so much...and have a star on me...that little bit of code worked great. I still (along with some others) haven't figured out why on some machines the Dir function won't work within a query, but will work when "wrapped up" in a user function. By using the "wrapped" function you created, I can avoid the "don't recognize Dir function" error.
It probably has something to do with references, but I can't seem to find it. My research indicated that I'm not the only one to run into this and everyone else was recommending using a wrapper function to do what the Dir function in the query was doing. Writing that wrapper function was where I was having the problem..again thanks for the help....you saved me hours..and some headaches. Thanks