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 1002864800006538.tif in a folder on the local machine
6508025786084 - This should then link to the file 1003484800007778.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 "frmWarrantSelection" 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!!

[nico5038]

The idea is to use the right-click pop-up to do the filtering. The right-click and putting the ID# in the "Filter for" field will do the same as your input-form triggered by the query.
But also a search for the Surname and/or court can be achieved.

Entering the ID# manually will always hold the error of typo's, using the data sheet will allow some control.

Check out the Right-click Instruction !

Getting the idea ?

Nic;o)

[riaane]

Ahh ok gotcha.

Problem is that the use of this is on a random query of the population to see if there are any matches on the recordset in the person's ID Number. Therefore a typed in search criteria will always have to be used as the operator is tasked with finding out of the random person in front of them has any records as listed in the WOA Table.

So the operator can right-click and select "Text Filters - Equals...", but how do you then return to the full record set data sheet view after your search?

I will try and build a button that will return the form to fully populated view.

[nico5038]

After right-clicking in the Surname and entering e.g. "*hans*" (without quotes but with the *'s) Access will fill the data-sheet with all rows holding "hans" anywhere in the Surname.

Just try yourself.

Nic;o)

[nico5038]

You cold also right-click the ID# and use your sample value "7608245057081".
You'll see three rows that hold this value...

Nic;o)

[riaane]

Nico, we are talking past each other here :-)

I get the results you point out. However, the searches are repeated many times per hour, each on different ID#'s so the operator must be able to return to the full record set to enable him/her to do the next search on the full record set, not only on the previous match list.

I can click on the "Filtered / Unfiltered" button on the bottom taskbar but I would like to have a "Return Full Record Set" button below the "Show Warrant Scan" button for ease of use.

Sorry for the confusion. I will try and dream up a button that does this.

[nico5038]

Hmm, something like this attachment perhaps ?

I've build in the past a solution where the filter is stored in a table, so allowing the user to re-use them...

Nic;o)

[riaane]

Nico, you are BRILLIANT! Thanks so much for your help and patience. Your beer is in the mail ;-)

[nico5038]

Glad I could help.

Some tips:
. There's some VBA code "behind the forms", perhaps you'll now be able to understand it. It's better to use VBA instead of the macro's you were using.
. Try not to use spaces in table and field names.
. Use (like I did with the table and forms) prefixes like tbl / qry and frm to identify the database objects.

Success with your application!

Nic;o)
PS I hope it's a "Trappist" that's being mailed <LOL>

[riaane]

Nico, 1 last question: The operators that will use this system are complete technology Philistines and many of them can literally barely drive a mouse. Please can you tell me how I can replace the Right’click-Search process with a button that pops up a little form allowing you to enter the ID Number and the “Search” to execute the query. The result should still be displayed in the data sheet view.

I'm a little lost without my trusty old Macro's.....
PS: My 1st installment is attached. I selected one of the Belgium versions as the ones in your native Netherlands may be too well-known to you ;-)

[nico5038]

You've selected one of my favorites. There's only one Dutch Trappist and some 6 Belgian.

I've added a solution that's using a combo box to select the (distinct) ID's.
A combo box has an auto-append feature, thus typing an existing code will show the "final" part, but when a typo is made the trailing part will be empty...
Just try it yourself. I prefer this way as it's supporting the user.

Nic;o)

[riaane]

"You have done well, young Skywalker. Your training is complete" ;-)