Search in Access

[riaane]

Me again. Please tell me how I can do the following: I have a report listing the results from a number of fields from a query result. The 1 spesific fileld results must be displayed as a hyperlink on my report (this I have done). When you click on the hyperlink, the following should happen:
The file name in a specific directory on the local machine must be searched and the file name that corresponds with the name of the hyperlink must be opened.
Please, if possible (but I fear it is not possible) no VB coding as my VB sucks

[Stewart Ross]

Sorry, Riaane, but if (as I suspect) you need assistance with some form of coursework or assignment question we really cannot do the work for you. If you have a specific problem, one which you have already made an attempt to resolve, please feel free to ask us for advice. In any event, you will undoubtedly need to use code to achieve what you ask, regardless of whether or not you wish to do so.

I will close this thread at present to prevent other posters unwittingly assisting you with plagiarism of coursework. Please feel free to PM me if I've got this wrong so I can reopen the thread.

MODERATOR

[Stewart Ross]

Riaane has contacted me to advise that she is not a student and this post is not a homework question. Accordingly I have re-opened this thread as promised in reply #2.

MODERATOR

[riaane]

Thank you for understanding Stewart. My query is not covered in any of the online Access Help documentation I could find so help on this issue will really be appreciated.

[nico5038]

You should first consider to change from using a report (that should only be used for printing output) to using a form.

Nic;o)

[riaane]

Hi Nico, I have changed my search result to display on a Form as a Hyperlink. I can only assume I need VB code to now go and search for the matching record with the file name of the Hyperliunk in a directory on my local machine? Thie matching file will have a .tif extension. Can you or anyone help me with the VB coding to pount to
1. a spesific directory or
2. just the Common Dialogue to Browse to the directory where the file with the name of the Hyperlink is situated? Note that there are anything from 200,000 to 800,000 files in the directory that has to be seached.

[riaane]

Maybe I must rephrase my question to ensure that I do not limit the scope of possible solutions to my problem:
How do I link to a file (with .tif extension, therfore an image file) from within the result set of a Select Query in Access 2007? The name of the file I need to open is listed in the result of my Select Query as a number only (without the .tif extension) and the .tif files are all situated in 1 directory on the local machine. In other words, a Query result lists a number of records and one of the fields of each record has a numeric string that has to be matched with a file with the same "numeric string.tif" name in a folder on the locam mahine. When clicking on the Hyperlink of the the field containing the numeric string, the click Event should open the relevant .tif file. I will gladly send the ACCESS DB and some sample .tif image files to anyone that is willing to help me on this and needs some sample data.

[nico5038]

Hmm, just wondering when you have a "fixed" name (being the number) and filepath, why you don't use an unbound image and show the image on the form.
The "De Luxe" version would check or a file does exist and display a default image showing "No Image".

Idea ?

Nic;o)

[riaane]

Please note that the result of my Query (from an original table consisting of >250,000 records) contains 39 fields, only 1 of which contains a list of all the file names in the folder consisting of >250,000 .tif image files. I therefore must search for the correct .tif image file relating specifically to the record relating to that image. I currently have no way to access the correct 'tif file in my folder from the record listed on my Form.

Does that make sense? Thanks Mate for taking the trouble of helping me :-)

[nico5038]

Can you post a small sample of your .mdb ?
Can I deduct that you use one or more multiple-value fields to hold the file names ?

Nic;o)

[riaane]

Here you go.

The relevant search (on the main Form) is "Find Warrant". Use the following 2 ID Numbers as examples:

7608245057081 - This should then link to the file 100286480000653 8.tif in a folder on the local machine
6508025786084 - This should then link to the file 100348480000777 8.tif in a folder on the local machine

Excuse my humble way of making the DB work but I have never done an Access course and time is tight at this stage so I'm hacking my way around my challanges *blush

[nico5038]

OK riaane, I've added a sample how I would "attack" this problem.
I always start with a "selection" form so users can use the right-click popup menu to do the filtering and the action buttons to manage the selected row.
You'll find that entering the ID# is easily giving false results because of typo's.
I also spotted a search on Surname and with my selection form the user can even enter a partial name to search by entering e.g. "*han*".

As the location of the .tif files can differ, I've added a table (tblSystem) to hold the path and added the active path to the form. By pressing the folder button you can dynamically set the Path to another location/folder.

I've added my sample and for the helpfile I give my users to work with the right-click pop-up menu see my last comment in http://bytes.com/topic/access/answer...-access-2007-a.

Nic;o)

[riaane]

Looks promising but I get the attached error when selecting "Show Warrant scan" - maybe a VB class missing on my puter?

[nico5038]

Sorry, forgot to remove a statement, check this file.

Nic;o)

[riaane]

GREAT! this works.

Only problem is, there is no way the operator can select the relevant record from a record set of 200,000 to 800,000 records on data sheet view. My original search function must still remain and the result of that search (Find Warrant) must result in your form "frmWarrantSele ction" with the matches found in the WOA record set in the Data Sheet view. I will play with what you gave me as I don't want to take up more of your time.

Thanks so much for your help!!