DWUTKA at marlow.com
DWUTKA at marlow.com
Fri Jul 30 11:40:58 CDT 2004
You didn't download the 'last' version. That not only wrapped the table names in brackets (ran into the same problem with linked Oracle tables), but it also breaks the searches down, so on tables with a lot of text and memo fields, you won't get a Query Too Complex error. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mitsules, Mark S. (Newport News) Sent: Friday, July 30, 2004 9:07 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Multiple Table Search Drew, Thanks for working on this. It worked great. Though no fault of yours, the only comment I had is that due to the non-standard naming conventions used in this db, I had to modify the code to place brackets around the table names when building the SQL string...just as you did for the field names. So I ended up changing this: strSQL = "SELECT * FROM " & tbl.Name & " WHERE " & strWHERE To this: strSQL = "SELECT * FROM [" & tbl.Name & "] WHERE " & strWHERE Mark -----Original Message----- From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] Sent: Tuesday, July 27, 2004 11:33 AM To: AccessD at databaseadvisors.com Subject: RE: [AccessD] Multiple Table Search (Here we go, posting this under the right subject this time!) Okay, here ya go. I built this into a form, because it uses a lot of 'techniques' to get things done. (Class modules, collections, callback functions (for the list box)), along with all of the associated properties that need to be set to get things done. So instead of trying to explain how to build the form, I'm just posting a link to download it. Right now, my DSL is down (in case anyone wondered why the archives on my site weren't there.....), it will be back up Thursday...so within a few days of that, I will try to remember to put this file on my website. (http://www.wolfwares.com) Until then, I put it on my company's website, for download. http://www.marlow.com/FindAllData.zip The zipped database has a form, query, and class module. Just import all three into whatever database you want. Open the form, enter the search criteria, and click the button. All applicable hits will be displayed in the listbox. You can then double click the listbox to display either the particular record (if a primary key for that table exists), or for all of the results off of that table. Boy, that was actually fun to build! Drew FYI: This code only searches for text, in text and memo fields. It also automatically puts astericks around the search criteria, so if you want to put astericks inside the criteria, go ahead, but they're not necessary on the outside. If you want to search number fields, the code could be modified to do that. Actually, if there is enough of a request for it, I could modify the search form to have different search boxes (text, currency, date/time, etc). Finally, and this is IMPORTANT, the code uses ADO. The sample I built is in 97, which has DAO set by default, but if you use A2k, make sure you have DAO selected. -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com