Search 3 fields from one unbound field

[ohi]

Basically what I'm trying to do is make a search form that will open another form from the search form that will match the records if the data was from any of the three fields.

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Main Case Details"
    
    stLinkCriteria = "[Serial Number]=" & "'" & Me![Search] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Is the code I use to match (via onclick) from one to another

is there a simple way, to add the following to the code, so it will search the other two fields as well?

    stLinkCriteria = "[Received by Agency Inventory #]=" & "'" &  Me![Search] & "'"
     stLinkCriteria = "[CFU Inventory ID]=" & "'" & Me![Search]  & "'"

Thanks!

 

[Brianwarnock]

I've never done it but I would be trying someting like

stLinkCriteria = "[Serial Number]=" & "'" & Me![Search] & "'" & or & "[Received by Agency Inventory #]=" & "'" & Me![Search] & "'" & or & "[CFU Inventory ID]=" & "'" & Me![Search] & "'"

but not at all sure of the syntax

Brian

 

[Brianwarnock]

Having done a quick search of the forum it looks like it might be

stLinkCriteria = "[Serial Number]=" & "'" & Me![Search] & "'" " Or [Received by Agency Inventory #]=" & "'" & Me![Search] & "'" " Or [CFU Inventory ID]=" & "'" & Me![Search] & "'"

Brian

 

[ohi]

Thanks for the reply Brian. I tried that out, it's giving me an Error 13

 

[vbaInet]

You can write it like this:

stLinkCriteria = "[Serial Number]='" & [COLOR=Blue]Me![Search][/COLOR] & "' OR [Received by Agency Inventory [COLOR=Red][B]#[/B][/COLOR]]='" & [COLOR=Blue]Me![Search][/COLOR] & "' OR [CFU Inventory ID]='" & [COLOR=Blue]Me![Search][/COLOR] & "'"

Or this:

stLinkCriteria = "[Serial Number]='" & [COLOR=Blue]Me![Search][/COLOR] & "' " & _
                 "OR [Received by Agency Inventory [B][COLOR=Red]#[/COLOR][/B]]='" & [COLOR=Blue]Me![Search][/COLOR] & "' " & _
                 "OR [CFU Inventory ID]='" & [COLOR=Blue]Me![Search][/COLOR] & "'"

Or this:

stLinkCriteria = "[Serial Number]='" & [COLOR=Blue]Me![Search][/COLOR] & "' "
stLinkCriteria = stLinkCriteria & "OR [Received by Agency Inventory [B][COLOR=Red]#[/COLOR][/B]]='" & [COLOR=Blue]Me![Search][/COLOR] & "' "
stLinkCriteria = stLinkCriteria & "OR [CFU Inventory ID]='" & [COLOR=Blue]Me![Search][/COLOR] & "'"

However, you've got '#' as part of your field name so that may be causing problems too. Also, don't use spaces as well when naming anything in Access.

Why are your three fields feeding off from the same field, Me![Search]?

 

[ohi]

I know I am using non standard naming methods, and unfortunately this project is at it's end stages for me to start the entire thing over again. Trust me when I say i will be following more normalized naming methods in the future.

The thing is I have a form, it's just an unbound field with a command button to search and produce the matching record.

I want the end user just to be able to type in what they're looking for if it's SN, Inventory # or inventory id (they may have just one and not know which it is) and find the matching record to edit the data.

I didn't realize the # could cause such issues. It was the only field in any table that had one. I'm so used to typing # instead of the word Number I probably did it out of habit and never realized it. I changed it to No, so there won't be any confusion on that in the future.

Your code worked like a charm btw, thank you so very much, I can't tell you how much I appreciate your assistance.

 

[vbaInet]

Ah, I see what you're doing.

Glad it worked for you. You're welcome!

 

[ohi]

One more question if you're still there. I just can't figure it out. This is the entire code for the above

Private Sub searchrecs_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Search Evidence Sub"
    
    stLinkCriteria = "[Serial Number]='" & Me![Search] & "' "
    stLinkCriteria = stLinkCriteria & "OR [Received by Agency Inventory No]='" & Me![Search] & "' "
    stLinkCriteria = stLinkCriteria & "OR [CFU Inventory ID]='" & Me![Search] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "Search Form"
End Sub

How would I go about not closing the search form, and telling the user that no records were found, if infact, no records were found?

I've done numerous searches, and everything I'm finding just doesn't seem to work..

 

[vbaInet]

You wouldn't open the form if no records are found.

Use a DCount function to count how many records are returned, if it's Not Equal to 0 then you open the form. Else prompt with an Msgbox that no match was found.

http://www.techonthenet.com/access/functions/domain/dcount.php

 

[ohi]

I'm definitely doing something wrong and my head hurts

Private Sub searchrecs_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Search Evidence Sub"
    
    stLinkCriteria = "[Serial Number]='" & Me![Search] & "' "
    stLinkCriteria = stLinkCriteria & "OR [Received by Agency Inventory No]='" & Me![Search] & "' "
    stLinkCriteria = stLinkCriteria & "OR [CFU Inventory ID]='" & Me![Search] & "'"

If DCount("*", "Evidence Search", [Case Number]) < 1 Then
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.close acForm, "Search Form"

Else
    MsgBox "No records ... Try again"
End If
End Sub

Can ya give me a hint as to what i'm doing wrong?

I'm getting the error msg even when there is data.

 

[vbaInet]

Here you go:

If DCount("*", "[[COLOR=Red][B]Name of Table or query[/B][/COLOR]]", stLinkCriteria) <> 0 Then
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.close acForm, "Search Form"
Else
    MsgBox "No records ... Try again"
End If

See how it works now? Replace the red part with the name of the table or query that "Search Evidence Form" form is using.

 

[ohi]

I was almost there at my last edit!!

I see what I was doing wrong. Thank you again so very much

 

[vbaInet]

Exactly! Your last edit was close. Good job.

Don't use "< 1" though. Use "<> 0" as I wrote it.

You're welcome.

 

[ohi]

I did change it and tyvm again for the coaching!

 

[vbaInet]

No problemo ohi.

 

[ColinEssex]

Do a search for "Quicksearch" under my name, I posted a great search method that searches on 3 or 4 fields (or as many as you like by tweaking) at the same time.

I don't take the credit for it, but I've used it loads of times.

Col