How to search entire database from one field 1

[frosty90]

I have built a database with about 12-13 tables. The program is user friendly as I can make it. What I cannot get it to do is I would like from the main form to be able to look up or search entire database (all tables) for information in the user is looking for. I can use the search option on every form that links back to the table but I cannot get it to have one search do all. My question is can I have one command click, search all tables based on the criteria the user enters? Most of the time my users would be entering a number. Any help in solving this would be greatly appreciated.
Thanks

 

[willir]

frosty90

How this works will depend greatly on your schema. With 12+ tables, the search could become pretty complex. Also, where does this make sense, and where does it not. If you have a book store database, does it make sense to find phone numbers of the librarians?

A couple of ideas come to mind...

Under the command button, you can use a series of recordsets. Each recordset searchs a table or related tables for your value. Displaying the information should be interesting.

Another approach is to build your index table. For example, if you have a database for tracking computers, the index table could store the serial number, asset tag number, device name all in one field. The second field would contain the internal ID number for the device. By using the one index field, you could retrieve the PC regardless of using the serial number, asset tag, etc.

Richard

 

[frosty90]

Thank you for the information. I'll give you a rundown on what the database does. It is used to track phone inquires. Each type of inquiry may be pushed to a different table. The operator sees the "main form" from here they have the options to open 6 other forms. The secondary forms that are opened allow the user to put in information such as reference number, date , or any notes that they have about the call. This information is then pushed to the coinsiding tables. Eviron is used so each operator only sees his/her inquiries and not the total record set of the table. Each "sub form" gives the operator the option of searching for the reference number of the call. What I would like to do is to be able for the user to search the database on the "main form" for this reference number. The problem is I can do a record search from any form that is linked to a specific table. I cannot figure out how to do a record search that will search on all tables for this specific reference number.
Again I thank you for your help.

 

[willir]

frosty90

I think I understand -- the operator enters a number, and you want to find the information using the reference number. The problem is that the information can be on any one of six tables.

I see ways to appraoch this.

First on the main form that accepts the reference number, for after update event for the reference number have a bit of code that does the following...

dim dbs as dao.database, rst as dao.recordset
dim strSQL1 as string, strSQL2 as string, strSQL as string
dim strRefNum as string
dim intTest as integer
dim strDoc as string

strSQL1 = "select * from "
strSQL2 = " where refNum = "
strRefNum = me.RefNum


set dbs = currentdb()

strSQL = strSQL1 & "tblMyTable1" & strSQL2 & strRefNum

set rst = dbs.openrecordset(strSQL1)

rst.movefirst

intTest = rst.recordcount

if intTest > 0 then
stdDoc frmMyForm1
end if

rst.close

strSQL = strSQL1 & "tblMyTable2" & strSQL2 & strRefNum

and repeat...

Then...

DoCmd.OpenForm stDoc, , , stRefNum

The nice thing is that if I understand your setup correctly, the operator only has to press one button and the logic should open the correct form.

Two problems with this -- repeat the code six times. Assume that the reference number will be found only in one table.


SECOND approach.

Create an index table...

tblRefNumIndx

RefNum (primary key)
RefTable (optional, indicates name of table info stored on)
RefForm (form to be used)

Then the logic for above is replaced with
an SQL statement to retrieve the name of the form.

...
strSQL = "select * from tblRefNumIndx where refnum = " & refnum

Grab from name

rst.movefirst

inttest = rst.recordcount

if inttest > 0 then
strDoc = rst!RefForm
DoCmd.OpenForm stDoc, , , stRefNum
else
msgbox "Error ...
end if

On the other end, you will need to create the logic to update tblRefNumIndx

(Sorry, I have to run so I am going to cut this short)

This solution is more efficient at the front end.


BUT, and this is a farily big BUT...
Your problem is that your database design is a little messed. I would have designed it a little differently where the reference number is only in one table.

Let me give you an example. I designed a database for tracking computer devices -- all sorts of devices -- servers, switches, PC's, printers, monitors, etc.

The problem was that each object, each device type had different properties. When I look at the specs for a PC and don't want to see monitor resolution or screen size. Likewise, when I look at a printer, I don't want to see number of fiber ports.

I went back to the drawing board several times on this one - every design came up with problems, similar to your issues. I finally came up with the following which I find works well...

tblMasterDevice
DevID
AssetTag
SerialNumber
DevGroup
- other info common to all devices

Then for each device group

tblServer
DevID
- specs needed for servers

tblPrinter
DevID
- specs needed for printers

etc

Then I would query the Master Device using the serial number or asset tag and voila, I would have my device information and the information for the device group.

Likewise, I can run queries to say find all PC's with less than 300 MHz CPU's -- works backwards and forwards.

This sound a bit similar to your needs. But I am not sure you are ready to go back to the drawing board.

Good luck on this one.
Richard

 

[frosty90]

Thanks alot for the information. I do (at some point need to do a complete rebuild). I did get the problem resolved this morning. I came up with somewhat of the same solution. Although quite time consuming. I created a new form and did a record search on each table. As you explained above. I then made 6 command options for each form that coinsides to each table. The commands are only visible if the reference number is found. Once found The command "button" that the reference number is in becomes visible indicating whch form or table that the reference number is located in. I then ran a code on the now visible command to open form that. I have also set environ "username" to capture id so the form will only display the entries from each specific user. I did try to get this displayed on the main form as a sub form but with no results. Kept getting error that it could not find form. I just made a simple option to open the "search" form. I appreciate all the information you have given me and will implement on a new build.
Thanks again

 

[willir]

frosty90

Yea, I kind of avoided the user / environ issue since it is another can of worms.

The immediate thing that came to my mind is to implement security for Access.

Next, I have to assume you are storing the operator's name in the data. Does the operator enter this name or do you grab it from their environment or other mechanism?

You can use the debug mode to test your environment.

"...as a sub form but with no results. Kept getting error that it could not find form" sugests that the name someing in the main form is not correctly referencing the subform -- typo or somehting.

And yea, great going on resolving your problem, and I can understand why you don't want to rebuild.

Richard