[mswiernik]

I have a database of our inventory that will be shared with technicians and support staff. I already have it set up with permissions, login and such. What I am looking to do is after login, it would go to a form (or a report if that's more appropriate) where the user who is not able to determine the part number can type in any value (a text marking on a part) and the program would return a list of data that contains the value they entered. It would be like the 'Find' function in Access, but more of a 'Find All' function. Any help on this would be much appreciated!

[ScottGem]

Assuming you are searching one field this should be fairly simple. Create a form bound to your table or query and put an unbound text box in the header. Then add a button with code like:

Me.Filter = "PartNum LIKE *" & Me.txtboxname & "*"
DoCmd.RunCommand acCMDApplyFilterSort

[mswiernik]

I have a form and subform for search results with multiple tables of inventory. I was wondering if there is a way to link all the tables together(9 of them, all with the same headers in the table) in the control source for the subform so that when a search is done, it will return data from any table that has the matching info in the appropriate field in the search text box

[ScottGem]

Why would you have multiple tables with the same structure? This is not good design. One table with a field to identify what makes the difference.

That would then solve your problem.

[mswiernik]

I thought about that last night, which makes more sense. The setup is that we have 7 project sites, and each site has its own login and search to view only information in the associated table that has inventory specific for their machines since we don't want any information being shared between the projects as it could cause conflicts with material revisions or design, hence the separate tables. We wanted to create an admin search so that we can search all the information and not worry about the separation, but it would work with one table. My only concern is that by combining all that information into another table, which includes a picture for each part, will create a very large file size since we are looking at roughly 8,000 records in each table.

[ScottGem]

Access can handle it. The way this should work, is each record should have a SiteID. Depending on how you have setup the logins, you can either filter the forms so they only show the current site or create queries that only show the current site.

The way I would do it is to have a local table (lets call it tblCurrentSite). This table would have one record with the SiteID of the current Site populated at login. All your forms and reports would be based on a query that joins that table to your data table so it only shows records for the current site. For the home office, this table would be populated with ALL the SiteIDs, so the home office can see all records.

However, the alternative is a UNION query to combine all the individual tables. Using one data table is a better choice for a variety of reasons.