Search Like key words

[ultra5219]

hi guys


i did try create search data using search Text box find. my question is, how to add search keyword function?
For example if i type keyword: "JAVA" it will shows any keyword related "JAVA" on the table record for membertbl.
i try to modified my code, it seems many syntax error.

Thanks

[CJ_London]

no syntax error error as such - it you are saying this is what you want the code to do
i

f i type keyword: "JAVA" it will shows any keyword related "JAVA"

you are not using wildcards which you require for this statement to work, and have some code which is never used

you also don't need the tmp variable, just use '

varwhere will never be null because you are assign it "ID like " etc

and as you have constructed it, varwhere will never end in ' AND '

Also helps if you learn to use indenting - makes your code easier to read and debug. try

Private Function BuildFilter() As String
Dim varWhere As String
If Nz(Me.txtSearch, "") <> "" Then
    varWhere = "WHERE [ID] like '*" & Me.txtSearch & "*' OR  [Membership_ID] Like '*" & Me.txtSearch & "*' OR [Membership_name] Like '*" & Me.txtSearch & "*' OR  [Book_ID] Like '*" & Me.txtSearch & "*' OR  [Book_Name] Like '*" & Me.txtSearch & "*' OR  [Book_location] Like '*" & Me.txtSearch & "*'"
Else
    varWhere = ""
End If
BuildFilter = varWhere
End Function

[CJ_London]

no syntax error error as such - it you are saying this is what you want the code to do
i

f i type keyword: "JAVA" it will shows any keyword related "JAVA"

you are not using wildcards which you require for this statement to work, and have some code which is never used

you also don't need the tmp variable, just use '

varwhere will never be null because you are assign it "ID like " etc

and as you have constructed it, varwhere will never end in ' AND '

Also helps if you learn to use indenting - makes your code easier to read and debug. try

Private Function BuildFilter() As String
Dim varWhere As String
If Nz(Me.txtSearch, "") <> "" Then
    varWhere = "WHERE [ID] like '*" & Me.txtSearch & "*' OR  [Membership_ID] Like '*" & Me.txtSearch & "*' OR [Membership_name] Like '*" & Me.txtSearch & "*' OR  [Book_ID] Like '*" & Me.txtSearch & "*' OR  [Book_Name] Like '*" & Me.txtSearch & "*' OR  [Book_location] Like '*" & Me.txtSearch & "*'"
Else
    varWhere = ""
End If
BuildFilter = varWhere
End Function

[CJ_London]

cross posted here

http://www.access-programmers.co.uk/...d.php?t=288447

@ultra - please read this thread about cross posting http://www.excelguru.ca/content.php?184

[ultra5219]

no syntax error error as such - it you are saying this is what you want the code to do
i
you are not using wildcards which you require for this statement to work, and have some code which is never used

you also don't need the tmp variable, just use '

varwhere will never be null because you are assign it "ID like " etc

and as you have constructed it, varwhere will never end in ' AND '

Also helps if you learn to use indenting - makes your code easier to read and debug. try

Private Function BuildFilter() As String
Dim varWhere As String
If Nz(Me.txtSearch, "") <> "" Then
    varWhere = "WHERE [ID] like '*" & Me.txtSearch & "*' OR  [Membership_ID] Like '*" & Me.txtSearch & "*' OR [Membership_name] Like '*" & Me.txtSearch & "*' OR  [Book_ID] Like '*" & Me.txtSearch & "*' OR  [Book_Name] Like '*" & Me.txtSearch & "*' OR  [Book_location] Like '*" & Me.txtSearch & "*'"
Else
    varWhere = ""
End If
BuildFilter = varWhere
End Function

Cool.. Thanks for your advise. Your code very easy and simple and it helps me out too
anyway, one more, what if lets say, if i type any keyword which not meet any data from the subform, can show Error Message. "Search KEyword for: "ufrrf" Not Found.

how to modify that code in VB ya?

Thanks again

[ultra5219]

Thank you..i appologies for that noted

[CJ_London]

possibly - but would be obvious if no records were returned anyway.

You would have to do a recordcount of the subform once it has had the new controlsource assigned. If it was zero then generate the message

[ultra5219]

possibly - but would be obvious if no records were returned anyway.

You would have to do a recordcount of the subform once it has had the new controlsource assigned. If it was zero then generate the message

I see. Well i just asking if possible to do that
Well here is another one i ask, If i create Search Combo Box, Which are 3 field category: MemberID, Member name & IdentityCard No.
If i type any related keyword on combo box, it will show record on form. how to do that? i did manage to do only search by memberID.
how to add in VB code search keyword on combo box for: memberID,name & identity card no in one combo box, then it will display if keyword match found.
if i type any keyword or wrong memberID, or member name or identity card no wrong will show error message: "search error not found." how to add this error message in VB? maybe u can check my code problem.

Thank again

[CJ_London]

how to add in VB code search keyword on combo box for: memberID,name & identity card no in one combo box, then it will display if keyword match found.

I don't understand - isn't that is what the code already does?

if i type any keyword or wrong memberID, or member name or identity card no wrong will show error message: "search error not found." how to add this error message in VB? maybe u can check my code problem.

Already explained - I can see you have changed your code, but cannot see how this relates.

Suggest provide a full description in simple english of what you are trying to do

[ultra5219]

I don't understand - isn't that is what the code already does?

Already explained - I can see you have changed your code, but cannot see how this relates.

Suggest provide a full description in simple english of what you are trying to do

I mean, this one is another method. Search record on combo box. usually people search record using text box field to type correct?
this one is combo box method. My question is, how to add multi-field search instead memberID search only.
thats mean, i can search by MemberID or identity card no or member name keyword?


PS: the file i attach is only search by memberID only. if i type member name or Identity card will generate error. im not sure why is that?

Sorry bad english thanks

[CJ_London]

OK so you want a combobox to provide a value to select a specific record to display and after you have made a selection, you want to display that record? Is that correct?

[ultra5219]

OK so you want a combobox to provide a value to select a specific record to display and after you have made a selection, you want to display that record? Is that correct?

yep correct. Or the user can type any keyword from combo box, then click search will display the record.

[CJ_London]

confused again

Or the user can type any keyword from combo box

do you mean combobox or textbox?

[ultra5219]

confused againdo you mean combobox or textbox?

The user type in Combobox. not textbox

[CJ_London]

I'm guessing this is what you want

in the combobox rowsource put something like 'SELECT ID, FirstName, LastName FROM myTable'

in the combobox after update event put

me.filter="[ID]=" & comboboxnamehere
me.filteron=true