Coding search button (was Access and VBA Question)

[alive84]

Hi there

I have wrote a little script, that is used to search specific values that the User tips in.

 
Private Sub cmdSearch_Click()
 
    Dim LSQL  As String
    Dim LSearchString As String
 
    If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
 
    Else
 
        LSearchString = txtSearchString
 
        'Filter results based on search string
        LSQL = "select Contacts.* , Work.* from Contacts, Work"
        LSQL = LSQL & "where ((Contacts.ContactsID = Work.ContactsID) AND (FirstName LIKE '*" & LSearchString & "*'))"
 
        Form_SearchForm_sub.RecordSource = LSQL
 
        lblTitle.Caption = "Customer Details:  Filtered by '" & LSearchString & "'"
 
        'Clear search string
        txtSearchString = ""
 
        MsgBox "Results have been filtered.  All Company Names containing " & LSearchString & "."
 
    End If 
 

Now I always receive "Syntax Error: FROM" when I try to use it with the command button.

Does anyone see the mistake? Also, if somebody knows how to add more "search values", such as LastName etc. I would be very happy about it. I though it should be possible just to add

 
OR LastName LIKE '*" & LSearchString & "* OR CostCenter LIKE '*" & LSearchString & "*  
 

Thanks a lot

[Stang02GT]

Hello Alive,

Just trying to get a better understanding of what you want to do here. You are trying to have a search field in a text box that is executed by a command button?

[alive84]

Hello Alive,

Just trying to get a better understanding of what you want to do here. You are trying to have a search field in a text box that is executed by a command button?

Hello Stang02GT,

I am actually trying to develop a DB-"google"-Form. So, a User tips for example a firstname, and in a subform it will show all relevant data to that user. In my my script it has to get the information from table Contacts and Work.

Thanks

[Stang02GT]

Alright thank you, that gives me a better understanding of what you are trying to do.

[MikeTheBike]

Hi there

I have wrote a little script, that is used to search specific values that the User tips in.

 
Private Sub cmdSearch_Click()
 
    Dim LSQL  As String
    Dim LSearchString As String
 
    If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
 
    Else
 
        LSearchString = txtSearchString
 
        'Filter results based on search string
        LSQL = "select Contacts.* , Work.* from Contacts, Work"
        LSQL = LSQL & "where ((Contacts.ContactsID = Work.ContactsID) AND (FirstName LIKE '*" & LSearchString & "*'))"
 
        Form_SearchForm_sub.RecordSource = LSQL
 
        lblTitle.Caption = "Customer Details:  Filtered by '" & LSearchString & "'"
 
        'Clear search string
        txtSearchString = ""
 
        MsgBox "Results have been filtered.  All Company Names containing " & LSearchString & "."
 
    End If 
 

Now I always receive "Syntax Error: FROM" when I try to use it with the command button.

Does anyone see the mistake? Also, if somebody knows how to add more "search values", such as LastName etc. I would be very happy about it. I though it should be possible just to add

 
OR LastName LIKE '*" & LSearchString & "* OR CostCenter LIKE '*" & LSearchString & "*  
 

Thanks a lot

You missed a SPACE between Work and where in the from clause, should be
LSQL = "select Contacts.* , Work.* from Contacts, Work "

although its more usual to us Contacts INNER JOIN Work ON etc.

I also think

OR LastName LIKE '*" & LSearchString & "* OR CostCenter LIKE '*" & LSearchString & "*

should work!

MTB

[JKing]

Good eye MTB!

The following string was missing closing single quotes. If it was a direct copy from your code and not just a string you typed on the fly for posting purposes, that would most likely be the source of your problem. Have a look I added them in below.

OR LastName LIKE '*" & LSearchString & "*' OR CostCenter LIKE '*" & LSearchString & "*'"
 

[alive84]

You missed a SPACE between Work and where in the from clause, should be
LSQL = "select Contacts.* , Work.* from Contacts, Work "

although its more usual to us Contacts INNER JOIN Work ON etc.

I also think

OR LastName LIKE '*" & LSearchString & "* OR CostCenter LIKE '*" & LSearchString & "*

should work!

MTB

Thanks MTB that was the problem! Thanks for finding such a minuscule mistake. Thanks

Unfortunately, I am having another problem now. When I open the search form two Parameter Value Boxes appear. It asks me for the ContactsID and WorkID. How can I avoid that.

thanks

[alive84]

Thanks MTB that was the problem! Thanks for finding such a minuscule mistake. Thanks

Unfortunately, I am having another problem now. When I open the search form two Parameter Value Boxes appear. It asks me for the ContactsID and WorkID. How can I avoid that.

thanks

The problem disappeared...:-) ... I do not know what I did, that it vanished.

Thanks to all for the help

[missinglinq]

Please remember to provide a meaningful Title for any threads you start! This helps to ensure that other members, and also the general public, will have a better chance of finding answers to any similar questions. It is difficult for the experts to answer questions when there is not enough details provided to understand the problem.

Please take the time to read the Posting Guidelines, paying particular attention to Give a clear title to your question.

http://www.thescripts.com/forum/thread559246.html


Thank you and Welcome to TheScripts!!

Linq ;0)>