Search an Excel Database and display results w/ pictures.

Hi

Have a look at the attached.

1) I've only used the category as an example.
2) Data sheet now has 4 entries for a category of test2.
3) Category is loaded at the form initialization stage
4) I've used an advanced filter to extract the relevant information
5) The output is now a 7 column listbox, that shows the results of the filter.

Open the form, select search for a part, then select test2 from the Category dropdown, press search and the relevant items should show up in the output form.

Hope this gives you some ideas on how to proceed.


rylo

Okay this really does need to be done tonite.

So if anyone can have it working tonite I'll paypal you 20 dollars once i confirm it works at work tomorrow.

Hi

The code needs to go into the form initialization event.
Or are you calling the subs from there?


rylo

I'm not quite sure what you mean by that, so probably not...

Hi

If you go back to my original code, you will see that the code to fill the combobox is in a sub called userform_initialize in the form_search code area. Basically, this is event code that will run when the form is loaded.

So if you don't have the code there, then it is not being called.

Do a bit of a test and put the code for the category combobox into the initialize area, and get that part working. Then expand for the rest.


rylo

Rylo,

1.
I seem to have everything the same as yours however my listbox in search results does not seem to populate. Also by your earlier comments I got the impression that your search results are set to a maximum, x. Can, x, be automatically set to the number of occupied rows in the data worksheet.

2.
How can I have the search results omit the 'picture' column and actually try to pull the picture file up based on the file path?


-L

Hi

1) On the results sheet, I have a defined name called resultss that will dynamically adjust for the number of entries returned from the advanced filter. You will have to insert that into your file.

You will then have to change the row source on your output listbox to refer to that name, change the column count to 7, the column widths to 60 and perhaps alter the headings to true.

2) If you want to exclude the picture address, then you will have to change the output from the filter to exclude that item. Be careful if you just delete the column as it will impact the crtieria range. You will also have to adjust the number of columns in the listbox.


rylo

Hello,

1) When I try to set the Row Source under properties for the list box to 'resultss' I get the error message: "Could not set the rowsource property. Invalid property value."

2) If you want to exclude the picture address, then you will have to change the output from the filter to exclude that item. Be careful if you just delete the column as it will impact the crtieria range. You will also have to adjust the number of columns in the listbox.

How do I change the "output from the filter"?

Ok i'll be awake for another hour and a half.

Don't forget the 20 bucks folks!!!!!

Hi

Assuming that results!C still contains the picture path name, then in the search_results code block insert the following code

Please Login or Register  to view this content.

When the listbox shows with the results, click on an entry and the picture should load.

If you have removed the picture path from column C, then adjust the code accordingly.


rylo

So close......................

Hi

1) Change Search_Results.listbox1 columnheads to TRUE
2) Ajust the defined name refers to formula to be:
=OFFSET(Results!$A$2,0,0,COUNTA(Results!$A:$A),7)
3) Change the listbox1 code to be

Please Login or Register  to view this content.

I'll have a fiddle with the resize issue.


rylo

Hi

Try changing the image1.picturesizemode to 1 (stretch). The help file explains that this may cause some distortion and I didn't try what would happen if the image is too large for the window.

Try each of the options and evaluate the results to see which would be best for your situation. You may have to resize the picture window, or use a best for the most type arrangement.


rylo

I'm a little confused about "=OFFSET(Results!$A$2,0,0,COUNTA(Results!$A:$A),7)" where do I put it?

And turning column heads on just adds Column A, B, C, D etc but doesn't get rid of the Hanel_No, Man_Man_No etc in the search results.

No the strech seems to work perfetly, I'd really just like to get rid of both of those column headers, i thin that will fix the numbering proble ie #1 showing #2 picture

Nevermind, the last thing that needs to be done is to get rid of the Header that was in the search results list box before setting the columnheader to true

Just need to get rid of this and thats it!!!!!!!!!!

Hi

If you have the defined name resultss starting from A2, then set listbox1.columnheadings to false and it should get rid of them.


rylo

When column heads was set to true it actually added another set of headings. How do i set the defined name resultss to start at A2?

Hmmmmm it also doesn't seem to work when you have two fields with an input in the search userform:

Please Login or Register  to view this content.

Hi

Worked on the last example file you sent up. Category a, Manufacturer u brings up results....

If you most recent file doesn't work, can you put it up with the parameters used to bring back no result.


rylo

One big problem and then I think I can handle the rest of it.


The picture file paths are generated by the =CONCATENATE function. So a particular part may not actually have a picture. In this event I would like it to default to a picture that displays "File Not Found" or fnf.bmp (located on Sheets: "data" and range: "I1".

So heres what I have:

Please Login or Register  to view this content.

However instead of just displaying the fnf.bmp it comes up with a Run-time error "53": File not found. With end or debug as options.



I tried playing around with error handling but couldn't get that to work either:

Please Login or Register  to view this content.

Thanks in advance!





Rylo, thanks so much for taking the time to go through and start debugging the program. It was a big help.

Hi

What is in

Sheets("data").Range("I1")

On the example file it is blank.

If you have a path and the file is definitely in that location, then try changing it to

Sheets("data").Range("I1").value and see if that loads


rylo

Maybe this will help clear some stuff up. I've actually been trying to figure this stuff out.


One problem though: When I was trying to get rid of the column header in the listbox on the Search_Results form I messed something up and can't seem to fix it. But if you can fix that:

Please Login or Register  to view this content.

Then you should have the same problem with pictures that I'm having.



Thanks Rylo!

Hi

1) Change the code from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

2) You will have to correct the formula for the defined name resultss to
=OFFSET(Results!$A$2,0,0,COUNTA(Results!$A:$A),7)
It currently has a #REF! error

3) The default load picture details are in data!I2 not data!I1. Change that reference in the listbox1_click event.

4) Also change the listbox1_click event to comment out the error handler, and bring back the loadpicture command.

Things worked for me from there.


rylo

Originally Posted by rylo

2) You will have to correct the formula for the defined name resultss to
=OFFSET(Results!$A$2,0,0,COUNTA(Results!$A:$A),7)
It currently has a #REF! error

I'm not sure what you mean by this.

Sorry this is my second week working with VBA

Hi

Go to sheet Results. Then go Insert, name, define and you will see that there is a name called resultss. Select the name, and bottom is a refers to: box that contains a formula. The formula currently contains a #REF!. This will have to be replaced as per the formula I gave you.

This defined name is dynamic and will select the results of the filter. The form listbox refers to this defined name to obtain its data.


rylo

Hi

Try updating the code to

Please Login or Register  to view this content.

Wasn't thinking of a missing file / incorrect file path / name when looking at this, just the default file or existing file.


rylo

Yeah I probabally should of made that more clear.

In anyevent at least the end/debug screen has stopped coming up.

A shortcoming of the example I gave you is that the incorrect filepaths are not intermitten, they are all at the end. So on your example all the inccorrect file paths would show up with the fnf.bmp.

However when I put the real data in what happens is when the file path isn't valid it just doesn't update the picture box. For instance if #3 file path was incorrect it would just show the #2 picture if you were scrolling down through the list. I hope that clear. Try deleteing on of the 3.bmp and you'll see what I mean.

So I was thinking :

Please Login or Register  to view this content.

But that doesn't seem to work either.

Thanks for the help Rylo!

And a second, much less important question then the one above. Why doesn't this work?

Please Login or Register  to view this content.

Its under the 'part_maintenance' code. What seems odd to me is that if you leave it blank it works, but all other entries recieve "incorrect password" message boxes, even correct passwords.

Hi

1) Pictures:
I think there is a workflow issue coming in here. Lets see if this is the correct order of events.
a) A valid path and picture exists it is loaded.
b) The path is a "-", then the default is loaded.
c) An invalid path / picture as the first selection then .... what do you want to happen???
d) An invalid path / picture is selected after either (a) or (b) have already been selected then ...... what do you want to happen.

When you select an invalid item after selecting a valid one, the existing picture is not removed. This is where you have to specify any action. If you have other scenarios, then can you determine what you want to happen for each and every one.

2) Your code seems to be missing a couple of things.
(a) is p a global variable? If it is not, then setting it in Set_Password will not have any impact on the variable p in btnAddPart_Click
(b) pa55w0rd is a variable, not a string. Again, is this global, and has it been set somewhere else. From the code below, pa55w0rd would be blank, and so p will be blank. Nothing will be carried over to the other sub.


rylo

Hi,

Picture:
If the picture file path is valid load the picture. In all other instance load the defualt (assuming the defualt is the file not found picture). This includes after either a (after) or b (before) has already been loaded.

So does that mean I need two more if/then/else stements?





Password:

Please Login or Register  to view this content.

I think this is the right idea....

But why if I call set_password do I need to have 'p' set a public variable. Won't this set it in each sub that it is called in.

In any event my code still doesn't work. Any suggestions

Hi


Picture: try

Please Login or Register  to view this content.

Password:

Have a look at the help file on the scope of variables. Basically, a local variable can only be used within the sub it is set. So in your case p is set in set_password, but the moment that sub finishes, the variable p disappears. If p was global, then setting it in set_password would make it available for use in btnAddPart_Click. Same with the variable pa55w0rd. I can't see where that is being set, or the type of variable. If you mean that the password is actually the string "pa55w0rd", then you have to set it as a string

Please Login or Register  to view this content.

rylo

Please Login or Register  to view this content.

Is there anyway to do this without setting a limit on the "Dim ManArray(2 To 1800) As String". Right now if the worksheet data was to grow larger than 1800 rows the combo boxes would just stop. Can the array be made to make itself as large as the data?

Or is there a more efficent way to do this?

Hi

Have a look at the REDIM PRESERVE approach. Basically you have a counter, and every time you want to add a new record to the array, you increment the counter, and redim preserve the array using the new counter variable.

Another approach would be to work out the number of rows of data, and use that to initialize the array.


rylo

Hmmmmmm it still takes a while to load the comboboxes when the database hits about 2500 rows and just plain stops working at 3000.

I'd like this database to be expandable, is there another, perhaps more efficent way to sort the comboboxes?

Hi

Create a sheet that is hidden. Copy the data that you want to put into the comboboxes to that sheet, then use the inbuilt sort functions to sort the data however you want. You could then either (a) build your unique array for the combobox or (b) use an advanced filter to extract the unique data to a specified range on that hidden sheet that can be used as the source of your comboboxes.


HTH

rylo