How to search multiple fields for a text string

[Reynastus]

Hi all

I was wondering how to set a command button to search multiple fields on a single table of data for user entered data.

The database is for documents and provides links to relevant documents.

ie. Bob has a fall at work. He knows theres a slip trip fall checklist somewhere and not quite sure where. He opens up the Access database for document control and enters "slip trip fall" into the search text box.

He clicks a button

A new form loads with any form that has "Slip trip fall" in the either the [DocumentName] field or the [DocumentDescription] field.

i figure its a VBA thing and will be a variation on

Private Sub FindDoc_Click()
    If Len(Me.SearchBox & "") <> 0 Then
    DoCmd.OpenForm "SearchForm", , , "[DocumentName]= " & Me.SearchBox
End Sub

Any ideas? am I thinking down the right track?

Cheers
Rey

 

[John Big Booty]

Perhaps the sample posted here will give you some pointers.

 

[Reynastus]

Was actually just looking through that

 

[Reynastus]

After having a go at that I found its not quite what I was after.

I was hoping to display the records instead of just the name of the record if that makes sense.

Thanks for the pointers though... a way to click on the record name and see the full record on a separate page or something like that could work

 

[John Big Booty]

You could use the On Double Click event of the List Box to open the selected record, the code might look something like;

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "YourFormName"
    
    stLinkCriteria = "[YourRecordID]=" & Me![YourRecordID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , ,

 

[Reynastus]

I got the dbl click event idea but my VBA is really poor... to the point where its pretty much non-existant and the use of variables (although its not so bad that i do not recognise the idea of variables) really throws me.

Any chance you could either have a look at the database and see where I'm going wrong or break down the code you posted so that I could get a better idea of how to point the code to the right fields as I am getting an error of such.

Cheers for the help

Rey

 

[John Big Booty]

Throw me a bone. If you want me to look at your DB I will need at least a couple of dummy records in it

 

[Reynastus]

Sorry posted that before my morning coffee and just went and did the mass delete (not the the original has much in it ~10 records in total so far)

Here's the dummy one with 3 dummy records, a few categories (which I realise are under the department table) and some committee's included.

I've also turned the menu bars on as well

Hope this helps

Rey

 

[John Big Booty]

OK, that's better

I've made a few changes to your search form, I've added a couple of extra columns to the List Box, chief among them the DocID field, which is column zero and hidden, you On Double Click event now looks like;

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FoundRecord"
    stLinkCriteria = "[docid]=" & Me![SearchResults]
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Reynastus]

Ahhh ok I can see what I was missing there. Thanks for the help btw its muchly appreciated and sorry about the wasting of time with the empty database.

That solution worked perfect (once I rebuilt the query in the original database). So thanks heaps

 

[SpentGeezer]

Throw me a freaking bone here, I've been frozen for thirty freaking years....

 

[John Big Booty]

Ahhh ok I can see what I was missing there. Thanks for the help btw its muchly appreciated and sorry about the wasting of time with the empty database.

That solution worked perfect (once I rebuilt the query in the original database). So thanks heaps

No problem, glad we got it sorted

 

[Reynastus]

The solution was surprisingly simple but eluded my non-programmer mind ... funny how when you ask a computer to look something up you have to tell it what you want it to look up .... derp

 

[Khalid_Afridi]

Throw me a freaking bone here, I've been frozen for thirty freaking years....

What's the freaking bone