Follow on question - if file exists

[Wayne-I-M]

Hi

I got a good answer from both TopJB and Steve Schaple about a butotn click
to see if a file exists. Works fine on a few records.

Small problem - we have 139,487 photos from 187,498 records (so 48,000
people don't have a linking photo.

************
Is there a method to loop through 187,000 records to see if a file exists
and check a tickbox if it does ?
************


When a new record is created the default for the field with the path to the
photo is loaded as soon as the form is opened.
This is Path \ ID.jpeg (well something like that anyway).

This way when a photo come in the admin staff only have to name the photo
the same as the ID and is it already linked. Very easy to do for users.

If we don't get a photo we send out letter to clients a few weeks prior to
the departure. This is just a filtered report auto run 3 weeks prior. Again
dead easy.

But the filter is taken off a tickbox (= -1) ie. Photoreceived

Some people have been with us before and we already have a photo other
haven't.

Many thanks

 

[Wayne-I-M]

Its OK - I worked it out

List full of ID numbers (PhotoCheckList), a button and a tick box

May take a while run though 180K records - oh well its running now so
coffee break.

Dim varItem As Variant
With Me.PhotoCheckList
For lngx = Abs(.ColumnHeads) To (.ListCount - 1)
..Selected(lngx) = True
Next
End With
For Each varItem In Me.PhotoCheckList.ItemsSelected
If Dir("P:\ski_2009_photos\" & [CDClientID] & ".jpg") <> "" Then
Me.PhotoReceived = -1
Next varItem

 

[Dirk Goldgar]

Hi

I got a good answer from both TopJB and Steve Schaple about a butotn click
to see if a file exists. Works fine on a few records.

Small problem - we have 139,487 photos from 187,498 records (so 48,000
people don't have a linking photo.

************
Is there a method to loop through 187,000 records to see if a file exists
and check a tickbox if it does ?
************


When a new record is created the default for the field with the path to
the
photo is loaded as soon as the form is opened.
This is Path \ ID.jpeg (well something like that anyway).

This way when a photo come in the admin staff only have to name the photo
the same as the ID and is it already linked. Very easy to do for users.

If we don't get a photo we send out letter to clients a few weeks prior to
the departure. This is just a filtered report auto run 3 weeks prior.
Again
dead easy.

But the filter is taken off a tickbox (= -1) ie. Photoreceived

Some people have been with us before and we already have a photo other
haven't.

I'm pretty sure that you can use an update query along these lines:

UPDATE MyTable SET PhotoReceived = True
WHERE PicturePath Is Not Null
AND Len(Dir(PicturePath)) > 0

PROVIDED THAT Macro Security is set to Low and Jet Sandbox Mode is disabled.

 

[Steve Sanford]

Sounds slow..

Seems to me you if could get all of the picture names from the directory in
a table, you could use update queries to set/ unset the PhotoReceived.

My idea:

In a command window (DOS), switch to the directory of the photos. Send the
file names to a text (.txt or .csv) file

Dir *.jpg /b > PicNames.txt


Create a new blank MDB. Import the text file (PicNames.jpg).
Modify the table structure - add a new field for file name without the ".jpg".
Run an update query to populate the new field (name it "FileID"??): if file
name = ID10010.jpg, new field would be = "ID10010"

In the prod database, link to the new table in the new MDB.

Run an update query to set [PhotoReceived] to FALSE.
Run an update query to set [PhotoReceived] to TRUE Where [CDClientID] =
[FileID]


....try this on a test MDB, of course

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Its OK - I worked it out

List full of ID numbers (PhotoCheckList), a button and a tick box

May take a while run though 180K records - oh well its running now so
coffee break.

Dim varItem As Variant
With Me.PhotoCheckList
For lngx = Abs(.ColumnHeads) To (.ListCount - 1)
.Selected(lngx) = True
Next
End With
For Each varItem In Me.PhotoCheckList.ItemsSelected
If Dir("P:\ski_2009_photos\" & [CDClientID] & ".jpg") <> "" Then
Me.PhotoReceived = -1
Next varItem


--
Wayne
Manchester, England.

Hi

I got a good answer from both TopJB and Steve Schaple about a butotn click
to see if a file exists. Works fine on a few records.

Small problem - we have 139,487 photos from 187,498 records (so 48,000
people don't have a linking photo.

************
Is there a method to loop through 187,000 records to see if a file exists
and check a tickbox if it does ?
************


When a new record is created the default for the field with the path to the
photo is loaded as soon as the form is opened.
This is Path \ ID.jpeg (well something like that anyway).

This way when a photo come in the admin staff only have to name the photo
the same as the ID and is it already linked. Very easy to do for users.

If we don't get a photo we send out letter to clients a few weeks prior to
the departure. This is just a filtered report auto run 3 weeks prior. Again
dead easy.

But the filter is taken off a tickbox (= -1) ie. Photoreceived

Some people have been with us before and we already have a photo other
haven't.

Many thanks

 

[Wayne-I-M]

Thanks Dirk

I ran my silly bit of code and it worked fine (but VERY slow).

I tried your update query on a tester and it also works with a bt of tweeking.

Thanks again

 

[Dirk Goldgar]

Sounds slow..

Seems to me you if could get all of the picture names from the directory
in
a table, you could use update queries to set/ unset the PhotoReceived.

Great idea, Steve. I was just thinking along those lines, and saw that you
had already posted it.

 

[Wayne-I-M]

Hi Steve

Thanks for that. Good idea but not really what I was looking for.

I have a table with some fields ID, other stuff, PhotoLink, etc
The ID field is an autonumber
The PhotoLink is set to default blank (as you can't reference the ID until
the record is created.
When the form is used (to create a record) the PhotoLink value is set to
Path to folder\me.ID.jpeg

So the table always has the link to the corrct photo

As the photo come in (from an online form I have set up) they are called 123
or 456 or etc etc. This is the same as the ID of the record and is really
simple (which is the sort of "stuff" I like).

All this work great BUT what if someone does not send in a photo. We need
to send them a letter. This is just a filter report taken from a tickbox
(=0).

The problem I was trying to do was to see if the jpeg folder was there and
if it was then (=-1). This would be a probems for a few record but I am
doing a favour for the ski lift company (they run the lifts in 12 resort =
around 30,000 people per week pre-book). Some people has skied here before
so we have the photos already (the new records is just a linked table showing
this year's, week's, etc booking.

Anyway I have sorted it now with a looping checker (see code below) that
will run each week from scheduler (auto excec - macro - run public module -
print reports - exit). I have tested it a few times - but it take over 3
hours to loop all the way through. I think this will be scheuled for a night
run.

Thanks for the input though, learn all the time from the ideas i see here.

Not thought yet (well I have a bit) about deleting photo links for people
under say 12 years old - as their photo will not look like them after 2 year
and under 8's each year (OMG more "stuff" to write .

It's "almost" ready to go live - still 8 weeks before the slopes open though

Thanks again to you and Dirk

--
Wayne
Manchester, England.

Sounds slow..

Seems to me you if could get all of the picture names from the directory in
a table, you could use update queries to set/ unset the PhotoReceived.

My idea:

In a command window (DOS), switch to the directory of the photos. Send the
file names to a text (.txt or .csv) file

Dir *.jpg /b > PicNames.txt


Create a new blank MDB. Import the text file (PicNames.jpg).
Modify the table structure - add a new field for file name without the ".jpg".
Run an update query to populate the new field (name it "FileID"??): if file
name = ID10010.jpg, new field would be = "ID10010"

In the prod database, link to the new table in the new MDB.

Run an update query to set [PhotoReceived] to FALSE.
Run an update query to set [PhotoReceived] to TRUE Where [CDClientID] =
[FileID]


...try this on a test MDB, of course

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Its OK - I worked it out

List full of ID numbers (PhotoCheckList), a button and a tick box

May take a while run though 180K records - oh well its running now so
coffee break.

Dim varItem As Variant
With Me.PhotoCheckList
For lngx = Abs(.ColumnHeads) To (.ListCount - 1)
.Selected(lngx) = True
Next
End With
For Each varItem In Me.PhotoCheckList.ItemsSelected
If Dir("P:\ski_2009_photos\" & [CDClientID] & ".jpg") <> "" Then
Me.PhotoReceived = -1
Next varItem


--
Wayne
Manchester, England.

Hi

I got a good answer from both TopJB and Steve Schaple about a butotn click
to see if a file exists. Works fine on a few records.

Small problem - we have 139,487 photos from 187,498 records (so 48,000
people don't have a linking photo.

************
Is there a method to loop through 187,000 records to see if a file exists
and check a tickbox if it does ?
************


When a new record is created the default for the field with the path to the
photo is loaded as soon as the form is opened.
This is Path \ ID.jpeg (well something like that anyway).

This way when a photo come in the admin staff only have to name the photo
the same as the ID and is it already linked. Very easy to do for users.

If we don't get a photo we send out letter to clients a few weeks prior to
the departure. This is just a filtered report auto run 3 weeks prior. Again
dead easy.

But the filter is taken off a tickbox (= -1) ie. Photoreceived

Some people have been with us before and we already have a photo other
haven't.

Many thanks