Listing missing external files 1

[CaptainBob007]

Hi -

I have a form used for lookiup up properties. It displays the owner name, phone number, address, and a photo of the house at that address. The photos are in external files, called by a text field called

ImagePath

, which is made up of the street and address numbers. If the photo is not there, it displays a "photo not found" graphic, also external.

Anyway all of the above works fine. My problem is that I have many many properties that I'm lacking a photo for. I'm trying to figure out how to write a query or script or something so it can go through my entire table

tblProperty

and get me all of the addresses of the properties I don't have photos for.

Any assistance would be appreciated.

~Bob

 

[jrbarnett]

Bob,

Go to tools -> references and put a tick against the Microsoft DAO 3.6 Object library, then open a new module and paste this in:

Public Sub ListHouses()

Dim Db As DAO.Database
Dim rs As DAO.Recordset

Set db = Currentdb
Set rs = db.OpenRecordset ("select address, imagepath from tblproperties")

Do While Not rs.EOF
  if Dir (rs!ImagePath) = "" Then
   MsgBox "Photo not found for " & rs!address
  End If
  rs.MoveNext
Loop
rs.Close
Set db = Nothing
End Sub

Then go to the debug window (press ctrl g) and type
ListHouses <RETURN>
at the bottom and you will get messages up for each property.
If you need help to adapt it to write the addresses out to a file, post back here.

John

 

[CaptainBob007]

I'm trying it but its not working. My properties table

tblProperty

contains the fields

AddNum

(the address number)
and

AddStreet

(the address street)

The image path is generated on the form itself (and hence is not stored in the table). There's a text box in the form called

ImagePath

which contains the formula:

=P:\......\"& [AddStreet] & " " & [AddNum]

and the form just calls the path located in

ImagePath

.

And yes, if we could output this to a file so I'd have a list of the houses I need to get pictures of, that would be wonderful. Thank you so much.

~Bob

 

[jrbarnett]

Public Sub ListHouses(strOutputfile as string)

Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim intHandle As Integer

Set db = Currentdb
Set rs = db.OpenRecordset ("select rs!AddStreet, rs!AddNum from tblproperties")
intHandle = FreeFile

Open strOutputfile For Output As #IntHandle

Do While Not rs.EOF
  if Dir ("P:\......\" & rs![AddStreet] & " " & rs![AddNum]) = "" Then
       Print #intHandle, rs!AddStreet & " " & rs!AddNum
     End If
  rs.MoveNext
Loop
rs.Close
Close #intHandle
Set db = Nothing
End Sub

Find the amended code above. Pass the output location across as a parameter - eg
ListHouses c:\output.txt

you can then load the file into Notepad or similar afterwards.

 

[CaptainBob007]

I'm trying it but it keeps on giving me an error:

&quot;too few parameters - expected 2&quot;.

When I click on 'debug' it points to the following line:

Set rs = db.OpenRecordset (&quot;select rs!AddStreet, rs!AddNum from tblProperty&quot

There are definitely 2 parameters there, I dont get why it couldnt be getting them. Any ideas?

~Bob

 

[jrbarnett]

Bob

Big big apologies. I don't know what came over me when I did that. The contents of the quote marks should read:

select AddStreet, AddNum from tblproperties


John

 

[CaptainBob007]

Thank you so much!