Noob needing help with search box (1 Viewer)

[nickbarnes]

Hi guys, Im pretty new to Access and am learning as I go! I've got all my tables, queries and forms set up but Im really struggling to get a working search function which suits my needs. I've searched for weeks and tried so many examples but nothing does what I'm looking for so I think it's time I came and asked the experts!

I have two main tables, "Exchanges" and "PCPs". The "ExchangesView" form shows the data from the "Exchanges" table one record at a time. Also on this form is a subform which shows the related data for each Exchange from the "PCPs" table.

I'd like a single text box in which the user can search by any of the following values and press enter or click a search button to find the records:

FIELD NAME / FROM TABLE / DATA TYPE
1141 / Exchanges / Text
ExchangeName / Exchanges / Text
Phase / Exchanges / Text
Region / Exchanges / Text
BuildDate / Exchanges / Date
PurchaseOrder / Exchanges / Text
Requisition / Exchanges / Text
Estimate / Exchanges / Text

And if possible:

DSLAM / PCPs / Text
OrderRef / PCPs / Text
GatewayRef / PCPs / Text

Entering any one of these field in the search box will then change all of the other fields on the "ExchangesView" form so that the relevant data can be viewed and edited. I'd really like to include the fields from the PCPs table (highlighted in blue) but I have no idea if it is even possible to search for data in the subform and then bring up the relevant data from the Exchanges table (the main form). I really dont want to use any combo boxes or any list type affair as there will eventually be too many records to make this feasible.

Im really sorry if this makes no sense, Im really struggling to put my needs into words! I hope someone can help, I'd be so very grateful!! Please remember, like I said above, Im really new to Access so please be gentle!

Thanks all you Access Masters out there!

PS... I forgot to mention that Im using Access 2010 and this is a web database which will be uploaded to SharePoint 2010. Thanks guys and girls!

 

[John Big Booty]

Welcome to the forum.

Perhaps the sample posted here, will give you some pointers.

 

[nickbarnes]

Hi John Big Booty, thanks for getting back to me and for welcoming me to the forum!

I tried to get my head around how I could customise your code so that it worked with my pre-existing form but I just couldnt work it out. From what I read in the comments, it seems like this creates an instantly changing combo box type thing? Which isnt really what I want to do.

I've attached a dummy version of my database in the hope that you'll be able to see what Im trying to acheive. On the "Exchange View" tab, I've highlighted the simple search box and button in red. I'd like users to enter one of the fields I mentioned in my last post into this box and then the resulting records show up in the same form which the search box is in.

The users who will be updating the data arent the most computer literate people and I want the search to be like this as they already use a database with a similar search function and this method would be easy for them to pick up. It also saves them a few mouseclicks!

Let me know what you think and how I might tailor your solution to meet my needs. Thanks very much for taking the time to help me!

Nick

 

[John Big Booty]

Sorry I don't have Access '10 so am unable to open your DB