Help with Images on Continuous Form

[orange]

How did you get the data into your table? 602,000 records

[orange]

Can you get a list of these folders and image files from the photographic program?

If you have a list of folders and files from the photo program, you could load it into a table.
Then, compare that folder and file info, with the records (pathinfo) in your table.
It seems that where there is no match between these lists, that represents an record with no image.

What is your Plan B?

Updated:

I can run a Dir command on Win 10 and get a list of jpg files from every folder/subfolder on my laptop.
Seems I have 10,710.

Here are a few random records.
C:\Users\mellon\AppData\Local\Microsoft\Windows Mail\Stationery\White_Chocolate.jpg
C:\Users\mellon\AppData\Local\Packages\FileManager _cw5n1h2txyewy\LocalState\Tile0.jpg
C:\Users\mellon\AppData\Local\Packages\FileManager _cw5n1h2txyewy\LocalState\Tile1.jpg
C:\Users\mellon\Documents\ProjManagement_BarryWill iams.jpg
C:\Users\mellon\Documents\ProjtaskOwnerX2.jpg
C:\Users\mellon\Documents\PropertyAndNotes.jpg
C:\Users\mellon\Documents\PurchaseOrdersWithVendor s.jpg

These can be loaded into a table and compared with the imagepath info you have.

Are you still working on this?

It seems the Continuous form is just your current method of displaying the images. The real issue is to identify records in your table that do not have a real "jpg" file ---that is the records without an image.

[pattrickcolin]

yesi can get a list of all the images held.
But I want to do a live comparison. The minute I add one image in the 1000 plus folders the comparison list will be out of date. So can I add a live image list to the database for checking? and can I keep this image listing live.

Defiantly, still working on this. When I nearly complete a country with images and only a few re missing is the reason for a list. Minor countries with very few images are easier to do. As I just do every record.

[orange]

You have to use something as the authoritative source--- that is this source is gospel(so to speak). In your case, that authority is the image files in the file system. So, you can work with the List from the Photo Program, or you could get a file/folder list from Dir command.

I am attaching a diagram showing the basic logic, as I understand your set up and requirement.

As for keeping your data current, you work from the authoritative source always.
That is, you would run the process in its entirety each time you want the current information.

Since you could intentionally or unintentionally erase/delete a jpg (file), and that is your authoritative source, you can't rely on the database --- Until you have updated the database by running the whole process.

You could add another field(s) to the table. You could add a LastUpdatedDate to know how old the data is; and a HasImageYN to indicate Yes or No.

The speed of this whole process will depend a lot on your PC. I have mocked up such a set up based on the original database offered for download from ExpertsExchange link in my original post. The mockup is limited to 1 subdirectory in both the database and the file system, and only uses ".jpg" images. I did not load a table of all jpgs from the file system, I linked to a text file generated from the Dir command.

With the volume of info involved, I hope you have a functioning BACK UP system.

Thoughts?

[pattrickcolin]

Reading 279,000 images prior to using the program is a NO NO
it is the DIR() approach I need. With no knowledge of VBA this task is daunting

In a Country Folder ( [2 CountryID] = 405) are many image files

One of the image files is titled ‘5’ and appears on my Continuous Form with image, using [imagepath]
Whereas file ‘5a’ does not have an image file (yet) and appears on my continuous form as a Blank record.

If I place on my Continuous Form a box labelled
[imagepath]="C:\Users\User\Documents\sync\colins Stamp Images" & [2 CountryID] & "" & [4a Prefix] & [4b Number] & [4c Suffix] & ".jpg"
Then “C:\Users\User\Documents\sync\colins Stamp Images\405\5.jpg” is the result

If I place a new box on my continuous Form =DIR(“C:\Users\User\Documents\sync\colins Stamp Images\405\5.jpg”) the result is ‘5’ (image file exists) YES ALL BOXES HAVE THE SAME DATA.

If I place a new box on my continuous Form =DIR(“C:\Users\User\Documents\sync\colins Stamp Images\405\5a.jpg”) the the result is ‘’ (image file does not exsist) YES ALL BOXES HAVE THE SAME DATA.

So how do I change the Form Query [imagepath]="C:\Users\User\Documents\sync\colins Stamp Images" & [2 CountryID] & "" & [4a Prefix] & [4b Number] & [4c Suffix] & ".jpg" into DIR() statement for visual basic to translate?

[orange]

A few questions:

Do you have a BACK UP process for your set up? Does it work--you have too much data to lose it?

Do you have a test facility to set up and test any procedure? Sample data and files sufficient to test any program/approach.
It's critical that you do not work (develop) on your production/operational data.

With respect your focus on continuous form is misplaced - in my view.
Your key issue is to determine which records in your database do not have an associated image file on the file system.
Is that correct?

It seems that continuous form is the only means you have found/experienced to visually show records with and without an image file.

As for

Reading 279,000 images prior to using the program is a NO NO

No one said you have to read/open the image. You just have to get the file coordinates/a digital list that the computer uses to manage those files. That's the authoritative source you have.

Did you see the logic diagram in previous post? Does it make sense to you?

What experience do you have with
Access?
Queries?
Forms?
vba?
Database?

Details on your plan for action please.

[pattrickcolin]

Answers to questions.Yes I have a Back Up and back up new images to this on a live basis.The database, FrontEnd and BackEnd I save after each change to the program, and can easily revert to previous editions. in case of errors when I updated the program.I accept that there are many ways that I can solve the problem, does the image exist, but i don't know how to do it.I am happy to accept the challenges of Access and although self taught still consider myself a beginner.I am confident using Queries and Queries attached to Forms.I use many types of Forms in my database and I am not fixated with the use of Continuous FormsI am happy setting up tables etcHowever I know nothing about VBA and the use of DIR() commandSo wiping aside Backup issues etc.If I have a table with four fields. Where the first field is the Folder Name and the combination of the other Three fields, make up the File name. How do I produce a digital list of files not found.?

[orange]

Do you have a test facility to set up and test any procedure? Sample data and files sufficient to test any program/approach
A test database such that you could set up some tables and routines to set up and test the logic.

Did you see the logic diagram in previous post?
Do you understand the diagram?

What do you want to do at this point?
We can work on HOW to do it, once we know WHAT we're trying to solve.

[pattrickcolin]

I want to follow the DIR() approach please.

I see and understand the logic diagram.

It is the HOW to do it.

The database is ready to test.However to simplify matters I have stripped it down to a four main fields in the database.

Country	Prefix	Number	Suffix
2251		1
2251		2
2251	B	12
2251		12	a
2251	B	13

Where [Country] and [Number] are number fields and [Prefix] and [Suffix] are text fields
I enter some test data, (as shown)
I create a Query where Image path is created.

Country	Prefix	Number	Suffix	ImagePath
2251		1		C:\Users\User\Documents\sync\colins Stamp Images\2251\1.jpg
2251		2		C:\Users\User\Documents\sync\colins Stamp Images\2251\2.jpg
2251	B	12		C:\Users\User\Documents\sync\colins Stamp Images\2251\B12.jpg
2251		12	a	C:\Users\User\Documents\sync\colins Stamp Images\2251\12a.jpg
2251	B	13		C:\Users\User\Documents\sync\colins Stamp Images\2251\B13.jpg

Outside my database I have a Folder Called '2251' This contains 4 images files but is missing the image file '12a'

If I run my currant database I can see all the Images correctly except '12a' which is blank, (I currently use a continuous Form to display these four images and one Blank record.)
So HOW do I either LIST the one image that the database cannot find, or Conversely LIST the four images found.
I think I need VBA code Dir() to create my goal but am willing to be guided by someone with greater knowledge.

[orange]

Great stuff.
Let me mock something up here and get back to you shortly.

[orange]

Can you create a link to the list file that is outside your database?
What is the format of the list? Is it a .txt file?
What version of Access do you have?

I was trying to set something up. I have something on my laptop, but I am having difficulty getting the Dir command to work from within Access. I have some questions out to others.

I am assuming you are not familiar with DOS prompts and commands.

[pattrickcolin]

Linking to a list file is not a course I wish to proceed. I need to get the DIR() command up and running.
I have an Office 365 subscription with automatic updates.
My version of MS Access is "2016MSO(16.0.7766.7080) 32 bit"
Have a very small understanding of DOS prompts and commands if this helps.

Thankyou for your attention so far.

I will now ramble.
The DIR("xxxx") command works on a Single Form if you place an Unbound Box = [imagePath] on the form and copy the generated TEXT into a second box & do a replacement of the "xxxx" of DIR("xxxx"). It is the HOW can I do this replacement in Code.
Just thought it might help.

[orange]

Is it Colin or Pattrick?
I have Access 10 on windows 10.

Have been busy on other things.

Linking to a list file is not a course I wish to proceed. I need to get the DIR() command up and running.

I just got a function that allows me to get a list of image files on the fileSystem via Dir within Access.
Basically, a Dir command is submitted from Access(button click) and a file -is created with a known name in a known Folder.

Access query(s) allow you to see:

-Records in your database Table
-Images(jpg) from the FileSystem
-Database records that don't have an Image file
-Images(jpg) on the fIleSystem that are not in your database Table

The continuous form is same as discussed in previous posts.

In my limited mockup, using the database from ExpertsExchange,

-created some records simulating image records in a database table
-added some jpgs to a test folder ( some in some not in the database table)
-created some queries as per the list above
-used the continuous form to display records in database table

Here is sample output from query showing -Database records that don't have an Image file.

This would be much easier than visual checking from my perspective.

ID	ImagePath	RunDateTime
1	C:\users\mellon\documents\TestImages\A.tif	12-Mar-2017 8:49:26 PM
3	C:\users\mellon\documents\TestImages\C.png	12-Mar-2017 8:49:26 PM
4	C:\users\mellon\documents\TestImages\NotAvailable. jpg	12-Mar-2017 8:49:26 PM
6	C:\users\mellon\documents\TestImages\mycat.jpg	12-Mar-2017 8:49:26 PM
7	C:\users\mellon\documents\TestImages\record6.jpg	12-Mar-2017 8:49:26 PM
8	C:\users\mellon\documents\TestImages\NoSuchImage.j pg	12-Mar-2017 8:49:26 PM

Here is a sample of the form.

[pattrickcolin]

That looks to be ideal. I need to do some investigation. Could you please send me the (info/database) direct. My email address is colin@colinpattrick.com
Big Big thankyou for the amount of time you have spent on this, a problem that has driven me nuts.
Regards Colin

[orange]

Colin,

I'm not trying to be hesitant, but cautious. I don't know your environment and don't want you to corrupt anything at your end.

The mockup uses a .bat file to gather Info on imageFiles on the filesystem.
I have a function LaunchApp32 which executes the bat file and has internal waits/pauses to ensure all image files are processed before turning control back to Access. This is initiated from a button click.

At this point, I have to manually relink the imageFile List in order for the queries to work. I'm looking a t a means to automate the relink.

The real issue now is you test environment and mine --different folders, different names etc.
There are number of procedures that involve vba.

Also, my test environment data consists of only a few records. I'd like to do a little more "tests" to ensure it will work with more volume.

It's late here so I'll do a little more tomorrow.

orange