Dear Jack,

Hope all is well there?

I am attaching a new version of my db, using Rob's OLE form. If you open the Item form, go to the third tab, and try to hit the open form button, you'll see how far I've gotten in making it work.

Key problems now:

1.) How to get Rob's form to populate the file location and thumbnail location FOR THE CURRENT RECORD in the tblItem.

2.) How to get the path that was selected and saved in the table to then show up in the Item form's file location /thumbnail location text box control once you close Rob's form.

Do you want to take a look and see if you have suggestions, or shall I post to Rob's thread on this OLE demo?

All my best,
Kim

Good Morning Kim!!!!

I have your database but today is my morning to volunteer at the library. I am leaving in just a few minutes and I will not be back until after noon Pacific Time. At that I time I will take a look at what you have and see if I can get Rob's code to work. NO GUARANTEES that I can solve your problems, but I will take a squiz and see what I can see....

I trust you are well, happy and only moderately busy. I am dandy and feeling pretty frisky considering everything...

Jack

Very good, Jack! I'm just glad you'll get a chance to look at it, because what Rob has done with getting the file path is really pretty cool. Enjoy the library, and I'll check for your post later today or tomorrow morning.

Kim -

See ya later as I am outta here...

Jack

Kim -

Open form "frmBrowse" in design mode and click on the Path Of Picture text box. The Control Source is =xxx!FileLocation (I deleted it so I don't remember) and what you want to do is click the down arrow at the right of the field and select FileLocation from the list. That is what I did and now it is working for me... I don't know what the thumbnail location is about as the FileLocation is the path to the thumbnail...

Keep me posted!

Jack

Ok, Jack-

Here's the good and bad news. Your suggestion definitely got us one step closer. Now the path chosen does actually show up in the tblItem under FileLocation. However, it always shows up in the first record, rather than in the record that was active in the form. I need to tell it somehow, don't I, which record we are working with - pass it an ItemID or something?

Also, and this is minor, I want the path chosen to show up in the frmItem once the browse form is closed. Perhaps I could put a "Save and close" button into the Browse form - similar to the one you helped me build in the frmItemNew? How would it pass the path chosen back to the control displayed on the form?

Now, to answer your question, since it wasn't clear the way I had it with just one button in the last version I sent you. File location and thumbnail location are actually two separate files, both of which could be associated with any given item. The file would be a large, archival quality digitized image. The thumbnail is a much smaller image file, of moderate quality to be displayed on the screen. I have put in two buttons to make this clearer, which call two different Browse forms, and I believe I have set the parameters of those two Browse forms correctly. I am attaching the new file so you can see what I mean.

Even now, both browsing buttons only store in the first record of tblItem... Sigh.

Kim -

Take a look at the attached...Slowly but surely we are getting there...

Jack

Hi Jack, thanks for the new work. I downloaded the file, and when I click on the button control to open Rob's browse form now, I get a syntax error that reads "Syntax Error (missing operator) in Query expression '[ItemID]='

Strange, huh? Do you think it needs us to specfiy the tablename!ItemID? (so, tblItem![ItemID]) Hmm... Will check in tomorrow, as I am working on this from home. Tomrrow I start to think about how to create a search form to query the item data and retrieve records containing a string the user supplies! Exciting!

Best, Kim

Have you selected a record in the Combo box at the top of the form? Is this happening for a new record only? You should only get that error if a record has not been selected from the combo box OR are you trying to enter a new record without making a selection from the combo box? If this is a new record problem then I will see what adjustments need to be made...

Jack

Aha! Leave it to me to try it without picking an item. Yes, it works quite well when you are in an item record, as everyone should be. Now I'll try to add these buttons to frmItemNew, making the necessary adjustments to the event code.

Also, guess what? (and promise not to get mad) Your suggestion for dealing with this error got me thinking about the old listbox problem... I wasn't picking any item when I tested that either! It works just fine when an item has been selected. I'll bet you would get the same error if you tried what I did, but again, no actual user would do that. It was only because I was eager to see your suggestions/code in action that I went straight to that control without picking an item.

So, I now have an awesome form that does everything we had ever hoped it would do. Thanks so much, Jack! How can I give you (and Rob and Scott) credit for your work on this?

Next stop - querying the table from a search form. I think what I've learned from you about passing strings will help - Wish me luck.

All my best, and I can't thank you enough!
-Kim

Ms. Kim -

You have put a huge smile on my face... Thank you for your kind words and on going sense of humor!

Do not give any credit to Scott and Rob, just ME! I am, of course, kidding. You can create a cool 'splash screen' that starts whenever someone starts the db and you can put our names there, in really huge text!

You might want to consider making the tab form invisible until someone make a selection from the combo box. This will guarantee that no one can select anything until they have selected an ItemNumber. If that is too drastic you can add code to the command buttons that open the picture form that will not allow the forms to open unless a selection has been made in the combo box... Your choice....

It has been fun helping you and don't think that this is the end. If you have more questions you know where to come for answers...

I wish you a pleasant evening and smooth sailing with the database...

Jack

Dear Jack,

Good idea about making the tab invisible (or maybe grayed out?) until an ItemCode is chosen. I'll look into that next week, and will also make an autexec macro to open a menu form (with credits built in!) to launch item entry, a search, or to exit the database.

I saw a demo for a search form in the queries forum, which I will also be trying out. I hope it will be fairly straightforward...

Have a great weekend, and I'll keep you posted on my progress, so you can see how this all turns out.

Best and thanks,
Kim

Dear Kim -

You do not need an autoexe macro to open a particular form. Use Tool > Startup and then in the database window you can select in the Display Form/Page you can select the form you want to open when the db opens...

If you have a problem with your search form I have an idea or two so let me know if you have a question...

Enjoy your free time and I will chat with you next week.

Jack

Dear Jack -

Greetings to sunny California from rainy Maryland!

Been working on the database again, and predictably, have hit a few snags while making some good progress as well. (note my new menu form, and the search form I've designed based on user feedback here)

Two main issues I'd appreciate some advice on:

1.) I tried to add in the special 3rd tab controls from frmItem into frmItemNew. Access doesn't seem happy with the way I've done this at all! When I try to create a new record in frmItemNew (launched from frmItem when the number is not in list), and I get to the listbox or file browse controls, it keeps asking me for an ItemNumber parameter value, as if I weren't in an actual record at all. Maybe it is as if they are counting on that record already existing, even though I'm just then creating it for the first time? Or mayb I just didn't change something important in the event procedures?

2.) My search form is currently connected to nothing, does nothing. The demo I saw gave me lots of ideas on what sorts of searches I'd like to enable, and on how to use dropdown combo boxes to limit the search parameters. But the table against which the demo ran was structred quite differently than is my tblItem. What advice can you offer on the event procedure necessary to "Get Results" from tblItem, based on the keyword string the user enters and the other limiting parameters they may specify? Is this possible?

All best, and thanks,
Kim

Dear Kim -

I was working at the library this morning and I just got home. You may have already left, but I wanted you to know that I will be looking at your db this afternoon or first thing in the morning. I will have some help for you as soon as possible...

Jack

No problem, Jack - hope you had a good day at the library. I will check back tomorrow for your thoughts.

By the way, I have also been working on designing the basic and detailed "results" screens I would like the user to see when their results come back. Since these will simply display values stored in the fields from tblItem for all records the query deems relevant, I'm sure they will be fairly straightforward to produce (but should they be forms or reports, I wonder?). So, the real mystery to me is what drives the query in between the user's input and the output returned to them.

Anyway, all best!
-Kim

Kim -

Here is your version 8 of your db.... I did not have any problems with the form frmNewItem so from the frmItem enter a new ItemNumber in the combo box and when you get the message box select Yes and all should go well.

I have done the Keyword text box, ExhibitTitle and CampusUnit combo boxes. Please take a look at the Row Sources for the two combo boxes as you do NOT want to use a Value List but real text that the user will find in the table tblItem. This means that the combo box will always show real data and you will never have to update anything....

There is code behind the Show Results button and you will need to finish it so that the rest of the combo boxes will work. You have the examples for the two combo boxes that I did so you should be able to sort the code out.

With this method of searching if you leave all the controls on the form blank you and press Show Results you will see all the records in tblItems.

You do NOT have to use a subform as I have done to show the results of the search so you can open a form if you would prefer to do that. Let me know and I will tell you how to do it if that is what you want to do...

Let me know what you think...

Jack

Wow! Jack - I started playing with this from home since I couldn't wait until I got into the office. This is so cool! I should be able to do the additional boxes, with your examples and comments, and will work on that today. Also, I see what you mean about going from real values rather than the admin tables on those combo boxes - that makes a lot of sense. That way, the user doesn't think they're narrowing the search when they're really not hitting any results.

I will also be looking at the code you put into the Keyword part of the query and trying to understand it fully. I will get back to you later today with questions and ideas for expanding the search's functionality even further - I think I would like to have the "keywords" string searched in several additional fields (like caption, title, subject name, locale, etc). I wonder how complicated that would be? Also, I'm sure I'm going to have questions about the asterisks and multiple keywords, about how the query interprets capital letters, etc- but playing around with it may answer these issues.

Jack, this is just awesome - thanks for all the effort this took!

Best, Kim

Dear Kim -

You are very welcome!

Upper case letters are ignored by Access in searching so Kim or kim will produce the same results. The asterisk is merely a wild card and tells Access to find everything before the * and after the * so if you type in *ack* the code will find 'black', 'back', 'mack', etc. If you do the ack* it will find 'acknowledge' and any other word that starts with ack.

You can do a keyword search in Caption, Subject, Locale, etc, but you will have to be careful as the search is now set up to find a word in Caption AND Subject AND Title so the word you are searching for has to be in ALL those fields or it will return no records. Now you can set up the search to use OR and then it will find a word in Caption OR Subject OR Title.

Be very careful with multiple keywords as a single space, incorrect letter, missing comma will cause the keywords not to be found.

Have fun and if you have questions I 'should' be around today, but it is a mighty pretty day and I may be tempted to go look for some interesting pictures to take...

Jack

Jack,

By all means do not lose a fine weather day! We are finally having one here too, thank goodness.

As for the db, I attach the most recent version, all combo boxes in the search form now functional, thanks to your examples. Yay!

Here are things I am mulling over now:

1.) I really do think there is a problem when trying to add a picture path and/or use the exhibits listbox from the third tab of frmItemNew (and this time, I truly am going in the way a user would, from frmItem, having added a new Item Number.) Do you get no errors at all trying to manipulate this? I believe the listbox subform is geared to getting a value for ItemID from frmItem, not frmItemNew. Could this be my problem? I have no idea what the issue could be with Rob's browse form.

2.) In the keyword search, I get no results at all unless I use the asterisks. Do you?

3.) In the keyword search, can I ever make it so it assumes a Boolean "AND" between two keywords that will not necessarily be found in any particular proximity to each other in the record (or an "OR," if the "AND" is not possible)?

4.) Which fields did you say the keyword part of the search is hitting against? From the event code, it seems to only be looking at additional descriptors (which is a main field for keywords but not the only one). Also, when I tried a test search with a word I took from a caption, I got no results.

5.) I'm really warming up to your subform idea. It is fast and you're right there on the search screen if you realize you need to refine your search. Now, what if I see in the subform a record I like, and double click on that row, could it pull up another form which shows details from that record only, along with - gasp - the thumbnail for that item? I can design such a form...

All best, will check in again tomorrow,
Kim

Ms. Kim -

1.) Ah ha! A problem with the picture thingo from Rob. I did not realize from your last post that this was the problem. I think the problem has to do wiith references to frmItem instead of frmItmeNew. I will look into this.

2.) You will not get results from the keyword search unless you use the asterisks. If you type in everything in the keyword control that exits in a record in the table then it will find that record. Typing in History will find nothing if the text in AdditionDescriptors contains Black History. Since Access is looking for a match of the ENTIRE field then it expects to find Black History and does not see just plain History. Adding the asterisks lets Access look for any part of the field that matches what you have entered so *History would find Black History but not History Of England. History* would not find Black History but would find History of England. *History* would find both. Am I making sense?

3.) *concert*music* is the same as Concert AND music. To do OR is going to require some more controls on the form and code behind the Show Results! button...

4.) The keywork search is ONLY looking at the AdditionDescriptions field at the moment. If you want keywords to search in more than one field then the code will need to be modified and you may need to add check boxes so that you can determine if the user wants to search using AND or OR and for which fields. This can get very complex so do not try to cover every possibility or you may be writing a whole lot of code...

5.) What you want to do can be done. Create your form and add a Picture control to the form and you should be good to go. It may require some code, but we can sort that out..

I guess you are off now until next week....What a life you lead in Maryland!!! Hey, I say hooray for you!!!

Talk to you soon...

Jack

Jack, thanks for these answers and thoughts. Much to think over. Quickly, for now, as I am indeed off to other projects, etc., The problems in frmItemNew are with Rob's thing and the exhibits listbox. And yes, I think you're right - they probably both have to do with reference to frmItem instead of frmItemNew.

Now I get why the keyword search needs the asterisks. And if we can do *word1*word2* with this functioning as a Boolean AND, I don't need or care about an OR. However, I do wish it would search caption, title and mainsubjectname fields as well as additional descriptors. The user won't care where the keyword is mentioned, so no checkboxes will be necessary at all. It just needs to look in those fields too and bring records back.

Ok, will check back it to continue this conversation next week!

Best and thanks,
Kim

Dear Kim -

Just this moment figured out the problem with the the browse button on frmItemNew.

The code behind the button now reads:



It should read (change in red):



You can do a keyword search on the Caption and other fields in the table, it will just take adding some more lines of code... I will see what I can come up with between now and when you return.... I assume that you want that as OR or you will never find anything...

Jack

Dear Jack-

I'm so excited about this stuff, I can't stop working on it! Thanks for the tip on the browse feature - will fix the code up right away. Any thoughts on the exhibit listbox error, also in frmItemNew? It is as if it doesn't know I'm in a record at all (just like in my previous testing scenario, back when I was forgetting to pick an ItemNumber, only this time, I'm in the process of creating a new item record) Maybe I need to create a frmListboxNew, whose event procedures point to frmItemNew instead of frmItem?

Let me think about your other point on the search issue - the "or" you refer to would mean that if I am looking for, let's say, the word *player*, the search would bring back records where the word player was found in additional descriptors field, OR in the caption field, OR in the title field, right? Yes, that seems like just the right approach to me. In fact, those are really the three key fields where a keyword might be found, but you're right - if it is in the title field, there is a good chance I have no caption using that word. It is like all three fields supplement one another, but you would never type in the same words into all of them.

Ok, well, signing off again, but all best and hope you have a great week!
-Kim

Dear Kim -

You do put a smile on my face!

I missed the Listbox problem with tblItemNew, but I am willing to bet that has something to do with ItemID/ItemNumber... I will work on that tomorrow and I should have it working by then.

Yep, you have the OR bit correct! And I agree that it seems like the right approach. I have some of the code for that but then I got to thinking about the rest of the search fields and wondered if all of them should be OR's. Why don't you think about how you want to search for the items, other than the keywords, and let me know if you want to find; ExhibitTitle AND CampusUnits AND MainSubjectType AND ItemType AND DateRange. Or if you want; ExhibitTitle OR CampusUnits OR MainSubjectType OR ItemType OR DateRange. I am leaning towards OR, but I really don't know how your users will be using the search form...

I will have a great week and I hope you do to!!!

Jack

Dear Jack-

Hmm. This is an interesting point. The dropdown boxes are meant to really hone the search - I think they should be operating with a Boolean AND, as they seem to be now. Each of those fields really operates independently of the others - i.e. date range is totally unlike campus unit, and I may often want a certain campus unit AND a certain date range. This is why, in the first pencil sketch I drew of this screen, I actually labeled those boxes "Limit by Date," "Limit by Exhibit Title," etc. In face, I may go back to that labeling. You know, I think this part of the interface will be very intuitive for my users, as many library/archives search systems work this way, and their feedback helped me design the screen.

This quality of distinctive value-sets makes those fields qualitatively different from the caption, title, and additional descriptors fields, each of which could serve as a substitute for the other in many situations. Just imagine - if the item has a title of its own, the curator may not need to make up a caption, and vice versa. Additional descriptors are most needed when the caption or title values are vague or missing. So here, I think we want an OR, because we anticipate that our keyword data could equally well be found in any of these fields.

OK, I'm really off now, as the old paying job lurks... Talk to you next week!
Best, Kim

Kim -

Are you REALLY off this time? I suspect so as it is getting rather late on the east coast....

I am with you on the AND/OR thing... I willl do the OR for the keywords and AND for everything else. We may have to give the code a few tries before it is working perfectly, but I will give it a shot and see what I can come up with.... I hope to get this and the Listbox thing done tomorrow and whenever it is done I will post the db...just in case you happen to take a peek to see what I am up to....

Jack

Dear Kim -

This version just about sent me round the bend so I gave up and made two separate ListBox subforms for the Item and NewItem forms... I am close to the edge as it is so no sense in tempting the fates...

I have fiddled with a the search form and I think it is working the way you want. You CANNOT use more than one keyword at a time (because of the OR type) unless you are willing to add more Keyword fields. The search form should find *music* in the appropriate fields and you can selecte items from the AND combo boxes as well. Since you are much more familiar with the data you can try some selections and see if you get the results you expect.

I removed a bunch of leftover code and tried to get everything as clean as possible. Let me know what you think...

Jack

Dear Jack - Its not worth losing any marbles over - believe me!

Thanks and I will take a look and do some serious testing soon. I also suspected the easiest thing to do would be to create two listbox forms, by the way.

All best,
Kim

Dear Kim -

With my marble count I have to be especially careful!!

I had a sneaking suspicion that you might hop on UA for a quick look. Remember, NO guarantees, but I think it is working...or at least getting close....

Jack

Dear Jack-

Good morning! I have looked at the db, and here's what I see so far. The fixes in frmNewItem seem to have worked, with one minor exception (and don't worry, it is not a problem with the listbox!). It is just that when you invoke Rob's form while entering a new item, and pick a picture and then close the form (all of which works well), the path doesn't appear in frmNewItem, nor is it stored in the table.

As for the keyword search, my test searches now pull up the expected results from the three fields we discussed - thank you so very much! This is really a major improvement. I do want to understand better, though about a couple of issues. I'm still a bit concerned that we can't search on multiple keywords (for instance *singers*concert*), but if this would require a lot of complicated coding, it is probably just not worth it. They can just search twice and try to manually see the overlap. But is there a way to make the keyword search look more "seamless" to the user? I mean, could we somehow add the leading and tailing asterisks to the search word automatically, so they don't have to know this is even happening? That might make it less of a training issue. What do you think?

I also have a "detail" result form to connect somehow to the subform records, as we discussed, but thought I would wait to show it to you until the last kinks are worked out elsewhere. If you have any advice on these points above, I'll implement your suggstions from here if possible. Thne I'll reattach a new version with the new form, and we can talk about the programming to connect it all up.

All best,
Kim

Hiya Kim -

I am about to leave for the library so this will be short... Dang! I thought I had the picture thing working in frmNewITem! I will have to go back and see what is going on with that...

We can add the asterisks before and after a keyword and that can be done in the code. If you look at it you can probably see what you need to do to make it work. To search for Singers AND Concerts we would need to use different code (we are using OR now) and that can be done. You will need to add an unbound Checkbox to the form so that when it is check that means the user wants to use AND and you can add new code that uses AND. The code will look at the checkbox and if it is true run the AND code or if it is false run the OR code... Now I am doing this in my head, but I think it should work!

The details form should not be too hard, if I correctly remember what it is you want to do.

Now I must dash and I will check in with you sometime after 1pm Pacific Daylight time...

Jack

Dear Jack,

Hope the library work went well!

As for getting the asterisks to read in, you have entirely too much confidence in me. I've looked at the code behind the Get Results! button, and I can certainly see where the code tells it to pick up the string (If *string* then ..., else ...) But what I couldn't see is how to automatically assign asterisks to an unmarked keyword string entered into that control as we read it in. It seems like something you would want to do before you begin the if,then. Or, do we just take out the part of the expression that looks for asterisks around the string? But then, if I understood correctly, Access wouldn't find anything, because it woud be looking for the entire cotents of that field and not just a portion of it... Sigh.

As for the other point, these ANDs and ORs are really beginning to get to me! So let me make sure I undertand something very basic:

In the keyword control as things stand now (let us just say that we are only doing a keyword search, to keep things simple), under what, if any, circumstances can I enter more than one word?

- For an exact phrase? (i.e. "madrigal singers")
- for two words that may or may not be adjacent to one another? (i.e. madrigal mansion)

Then, are you saying that in the second instance, it would look for all records where either madrigal OR mansion appears in any of our 3 keyword fields? That would probably be just fine, if we knew that was how it worked. I don't think that search brings back any reocrds right now, though. Maybe because of the asterisks?

Ok, out for now, will check back later tonight or tomorrow! All best, Kim

Ms. Kim -

So the user does not have to put in the asterisks you can change the first line of code that now reads:

If Left(Me![Keywords], 1) = "*" Or Right(Me![Keywords], 1) = "*" Then

To:

If Not IsNull(Me.Keywords) Then

And then directly below that and before the next line of code add:

Me![Keywords] = "*" & Me![Keywords] & "*"

So your first 3 lines of code should read:

If Not IsNull(Me.Keywords) Then
Me![Keywords] = "*" & Me![Keywords] & "*"
where = where & " AND [Caption] Like '" + Me![Keywords] + "'"

About Keywords as the code is currently written:

If your Keywords are "Madrigal Singers" the code will find that exact phrase in the 3 fields you are currently searching. If you want to find "Madrigal" and "Mansion" the current code will NOT find both instances. To find "Madrigal" AND "Mansion" we need to use code that uses AND so we can search for *Madrigal*Mansion* (which works out to be Madrigal AND Concert in the code).

I realize that this is confusing and I am not sure that I have made it any less so...

If you make the changes to the code that I mentioned above you should be able to enter "madrigal singers" (without the quotes) in the Keywords control and the db should return one record...

Let me know how you are doing!

Jack

Ok, Jack, thanks! I will try it out first thing in the morning and let you know - now it is time to get out into the sun, which has finally made a real appearance here.

And the sun is shining here too, so I am conflicted about working or catching a few rays... Choices...

Ok Jack, hopefully you did get some sun yesterday and are feeling well-rested, because it will need all our collective brainpower to make sense of what comes next!

I am attaching - yes it is true - version 11....

Your code tips worked just fine on the search form with the asterisks, and I say, let's leave a working keyword search alone. They can type in a keyword phrase or a single keyword in combination with the other limiting fields - that's enough for now (and maybe for always!).

Here are the current puzzles:

1.) Old problem - FrmItemNew, Rob's browse form - no image path recorded to table or displayed to frmItemNew once picked.

2.) I just realized on frmSearch that the dropdown, limiting search on the Exhibit Title field should be a bit more powerful than it is. Remember all that work you did creating a listbox for additional exhibits? And the table we generated from those choices? Well, theoretically, a user might remember an item from a later exhibit, having no idea what its "original" exhibit was. Can we make the search on this field look for matching values in either tblItem's exhibit field OR tblExhibitsSelected2? If a matching exhibitID is found in tblExhibitsSelected2, then maybe a query has to backtrack to tblItem to get the corresponding record to display on the sfrmResults. All this is even trickier than it sounds, I believe, because in the code for frmSearch's Get Results! control, I noticed that you have had to repeat the code for searching each of those limiting fields over and over to cover every possible combination of keywords/limiting fields a user might choose. In fact, the whole prospect seems quite daunting and may not be worth it...

3.) However, some of the complexity from Item 2 follows us into these final points about the frmDetailedResult, which I have created and which is included in this version for you to play with, if you will be so kind. Right now, the form connects to nothing. Ultimately, I believe it will have to draw values from 3 tables - yuck! Most of the fields at the top are familiar to you from tblItem. But down in the bottom, under "Exhibits," some of the information can be found in tblExhibit, and some in tblxhibitsSelected2... Yes, here would be a good chance to display your listbox contents again. Also, please note the way I hope to call up the thumbnail at this point, somehow by calling the path noted in the record in tblItem. Last but not least, if the user wants to print this, can they? Is this really better off as a form or as a report?

Is this enough to keep you busy for a while, or shall I start a new project?

All best, and will check in on and off throughout the week, as I feel we are truly getting close to a working product!
-Kim

Kim -

I have downloaded your db and I have printed out your questions/requests. I have another iron in the fire so I will be looking at the latest version just as soon as I can. Hopefully in about an hour... Dang! I just realized that I may have to leave for an hour so I may not get to this as soon as I thougth... Anyway, you will not be forgotten!!!

Jack

Kim -

Hmmm. The Picture thing works for me in the from frmNewItem. I just open version 11, select a new ItemNumber, to to the new form and I can select the photograph and the path is saved in the proper place. I did nothing to make this start working...it just did its thing when I first opened the new db...

In regards to question 2 - At the moment the table tblItem is storing the Text of the Exhibit and not the ExhibitID. That will need to be changed and will be easy as there are only a couple of items in the whole table. Also, I do not understand exactly what it is you want to do, but I think that if there are exhibits in tblExhibitsSelected2 that are NOT in the ExhibitsTitle field in table "tblItem" that you want those Exhibits to be shown in the combo box as well. For example if there is ExhibitTitle "Ralphie" and "Butch" in table "tbleItem" and ExhibitTitle "Ruthie", "Ralphie" and "Butch" in table "tblExhibitsSelected2" that you want the combo box on the search fomr to show "Ruthie", "Ralphie" and "Butch". Is that correct?


Question 3 - I will see what I can come up with on this form, but I suspect I will have questions for you. It is better to print the new form using a report as reports are intended to be printed and forms are not.

Are we having fun yet?

Jack

Dear Jack-

It's a miracle! Don't you just love it when things spontaneously resolve themselves? Yes, the Browse thing now works for me too. Maybe I just was in an older version when I had the problem, I don't know.

As for point 2, yes you're right about the dropdown list - it should draw from exhibit values present in either table (and yes, we'll need exhibit ID, not title, in both - I see that now). But there is another implication as well, about which I'm sure you're already thinking. Just in case, here it is - the search, limited to let's say "Ralphie," needs to pull back all result records where either the original exhibit or an additional exhibit selected was "Ralphie". So I'll see all the items that ever appeared in the Ralphie exhibit if I choose it in the dropdown, regardless of whether that was their first time on display, or their eighth. I realize now that in a way, this functionality also gives the user a way to see a complete inventory of a given exhibit, so that is quite useful.

As for question 3, I was afraid of that - about it needing to be a report, I mean. Is that a big deal in terms of the coding that goes behind it all? Creating a report similar to that form should be easy for me to do here - shall I do that and send you version 12? Or shall I wait because you are in process with version 11?

All best,
Kim

Ms. Kim -

Thank you for the clarificatiion on "Ralphie"!!! I will take a look at getting this to work...

Go ahead and start on your report as whatever I do with the "Ralphie Code" (similar to the "Da Vinci Code") will be easy to migrate to your latest, greatest version...

Jack

Ok, Jack, before I take off, I will attach version 12 (all the same except with rptDetailedResults added).

Don't let the Ralphie Code get out of control, like the Da Vinci Code! Will check in sooner rather than later.

All best, Kim

Ms. Kim -

I have been pondering on the thingo about exhibit(s) in the table "tblSelectedItems2". This is presenting a small quandary... The table "tblSelectedItems2" can have 5 records for the exhibit titled "Bruce" as all of these will be for different ItemID's (records) in the table "tblItem". To be able to select exhibits that are listed in "tblSelectedItems2" it will be necessary to have a list box on the search form for the list of exhibits in the table "tblSelectedItems2" (there will not be any duplicates so "Bruce" will only be in there once). This will have to be a multi-select list box so the user can select "Bruce", "Faber" and "Byrd" so that those records can be displayed in the results (remember that there could be 5 records just for "Bruce"). Now I THINK I can do this, but before I go there I want to be sure that we are on the same page with this. Is a multi-select list box OK with you to select exhibits that are not stored in the table "tblItem" directly?

Selecting an Exhibit Title from the current search for will not need to be changed.

Let me know what you think and I hope I made myself clear....

Jack

Hmm Jack....

I'm almost understanding what you mean, but if I do, it seems more complicated for the user (not to mention the generous programmer!) to me than it should have to be. Do you mean that there would have to be two search boxes in which exhibits could be selected, one that would search tblItem and another that is a multi-select listbox that would search tblExhibitsSelected2? That really seems confusing to me from the user's point-of-view - I mean with just one box to choose from, they don't have to know or understand how or why we have stored these items seprarately.

If this was the idea you were explaining to me (and maybe I just didn't get it), let me suggest an alternative. Soon we hope to have all the exhibits entered in there, and a new one would be added only as it occurs and all its items were being inventoried. Unlike the other limiting fields, then, this one really should recall records for all possible values listsed in the dropdown box on frmItem that draws from tblExhibit. So how about this: what if the source for the search form's "limit by exhibit" dropdown IS tblExhibit, rather than the actual values in both tblItem abd tblExhibitsSelected2. That solves the problem of covering all the bases in offering a choice of search terms. Now here's the rub: the search still has to go against those two tables to pull in results, and still has to bring back all records where either the first or any subsequent exhibit value matches the item chosen in the dropdown...

What do you think? By the way,if I did misunderstand and your idea means just one multi-select listbox for the exhibit search, period, that would be just fine with me!

Hope all is well with you, will check in again soon,
Kim

Kim -

You were correct in my description of what I had in mind. My idea was for two separate things to choose from, the existing combo AND a multi-select list box. I have just started to try to figure out a way to do what you want with a single list box or combo and I have yet to come up with a solution because of the fact that the data is in two tables using two different fields to search on and the data is not mutually exclusive. I am going to mull on this today as I am pretty well caught up on another db that I have been working on and I will see what I can come up with....

I hope this makes sense! I am starting to confuse myself as I think about the db as I write this, but I will ponder the possibilities and see what I can come up with...

Jack

Kim -

I have been trying to find a solution to the problem of selecting ExhibitID's and ItemID's from a multi-select list box and I cannot find a solution that will work. I have tried everything that I can think of and all to no avail. If you have an idea of how to do this I am open to your suggestions. I can do it where the user selects the ExhibitTitle, as is done now in the search form, but I cannot do the items that are saved in the table "tlbExhibitsSelected2" without using a second list box. Maybe I am too far into the forest and can't see the trees, but I have been working on this for a long time and I just keep running into dead ends.

I will not give up on this as something may come to me, but for the time being I am dead in the water....

Jack

Dear Jack-

Would my idea - from my last post -work? Just draw the values from tblExhibit to populate the exhibit filter dropdown in frmSearch? This would truly be just fine. Then we could take the Exhibit ID chosen and go look for it in the two target tables?

If not, please don't worry, Jack. Much more important is how to link the report to the subform in frmSearch so that when a user sleected a record of interest, they get the detailed results report for that record.

What do you think on that point?

All best,
Kim

Dear Kim -

I don't see how I can make your suggestion work because we still need to find data from two different sources, the ExhibitID and ItemID. As an example you can select Calvert's Creation (I think that is the name) and it has a ExhibitID of 16 and there are a LOT of records in table tblItem with an ExhibitID of 16. Now it is very possible that Byrd was used at one time in the tblExhibitsSelected2 table and it has an ItemID of 179. ItemID 179 is not one of the records selected if the user selects Calvert's Creation (16) so this record needs to be selected as well and I cannot find a way to search both tables and come up with a common solution that will work for all possible selections.

You ask, "Then we could take the Exhibit ID chosen and go look for it in the two target tables?" I don't know if I can explain this, but if you open form frmItem and select ItemNumber RC0001 you can add any four items to the listbox on the right. Now if you look at table ExhibitsSelected you will see that all of those ExhibitID's are different but they all belong to ItemID 179. What this means is that if you select any one of those items you in the search form need to find recordID 179. Now any of these exhibits can be in any of the records so if we select ExhibitID 34 that ExhibitID could be in 12 different ItemID records. So if we select ExhibitID 34 in our search form we need to find 12 records by ItemID, not by ExhibitID. To search for ExhibitID for Exhibit Title in the tblItem we only need to put the ExhibitID number in the Criteria line of our query but with the items in ExhibiitsSelected we may need to look for 12 different ItemID's...

I am sure that I have NOT explained this well, but there is no direct relationship between the ExhibiitID in tbExhibitsSelected2 and the tblIem but there is a direct relationship between ExhibitID's in the tblItem and the Exhiibits table. In other words, selecting an ExhibitID from the Exhibits table will not find the records in tblItem via the tblExhibitsSelected2 table....

Now I am so confused that I am going to the wine cellar and pick a good strong wine!!!

Some good news - Base your report on the Dynamic_Query (Dynamic_Query will be the Record Source for the report) and you will be good to go. Everytime you do a new search you can have a button on your search form that previews your report and it will always show the latest search results...

Kim, I hope I have not driven you over the edge! I continue to mull on the search thingy, but so far I am unable to find a way to kill two birds with a single list box. My only solution might be to use code to make a temporary table and then some queries and then code to sort it all out, but at this point I have not gone that far, but that seems to be the only possible answer because I have not been able to find one using a query or even 3 queries...

By the way, it may be easier for us to communicate via email. You can contact me directly at [email=jpcowley1@earthlink.net.]jpcowley1@earthlink.net.[/email]

Hang in there kid!!!

Jack

Dear Kim -

After much mulling, pondering and trying of code, queries and other stuff I think I may have solved the riddle... Take a look at the attached and see what you think. You know the exhibits better than I do so you can probably tell if the correct records are being returned. I think they are, but NO guarantees.

Let me know what you think...

Jack