By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,482 Members | 1,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,482 IT Pros & Developers. It's quick & easy.

Coding search button (was Access and VBA Question)

P: 48
Hi there

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdSearch_Click()
  3.  
  4.     Dim LSQL  As String
  5.     Dim LSearchString As String
  6.  
  7.     If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
  8.         MsgBox "You must enter a search string."
  9.  
  10.     Else
  11.  
  12.         LSearchString = txtSearchString
  13.  
  14.         'Filter results based on search string
  15.         LSQL = "select Contacts.* , Work.* from Contacts, Work"
  16.         LSQL = LSQL & "where ((Contacts.ContactsID = Work.ContactsID) AND (FirstName LIKE '*" & LSearchString & "*'))"
  17.  
  18.         Form_SearchForm_sub.RecordSource = LSQL
  19.  
  20.         lblTitle.Caption = "Customer Details:  Filtered by '" & LSearchString & "'"
  21.  
  22.         'Clear search string
  23.         txtSearchString = ""
  24.  
  25.         MsgBox "Results have been filtered.  All Company Names containing " & LSearchString & "."
  26.  
  27.     End If 
  28.  
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
Expand|Select|Wrap|Line Numbers
  1.  
  2. OR LastName LIKE '*" & LSearchString & "* OR CostCenter LIKE '*" & LSearchString & "*  
  3.  
Thanks a lot
Aug 8 '07 #1
Share this Question
Share on Google+
8 Replies


Stang02GT
Expert 100+
P: 1,208
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?
Aug 8 '07 #2

P: 48
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
Aug 8 '07 #3

Stang02GT
Expert 100+
P: 1,208
Alright thank you, that gives me a better understanding of what you are trying to do.
Aug 8 '07 #4

Expert 100+
P: 635
Hi there

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdSearch_Click()
  3.  
  4.     Dim LSQL  As String
  5.     Dim LSearchString As String
  6.  
  7.     If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
  8.         MsgBox "You must enter a search string."
  9.  
  10.     Else
  11.  
  12.         LSearchString = txtSearchString
  13.  
  14.         'Filter results based on search string
  15.         LSQL = "select Contacts.* , Work.* from Contacts, Work"
  16.         LSQL = LSQL & "where ((Contacts.ContactsID = Work.ContactsID) AND (FirstName LIKE '*" & LSearchString & "*'))"
  17.  
  18.         Form_SearchForm_sub.RecordSource = LSQL
  19.  
  20.         lblTitle.Caption = "Customer Details:  Filtered by '" & LSearchString & "'"
  21.  
  22.         'Clear search string
  23.         txtSearchString = ""
  24.  
  25.         MsgBox "Results have been filtered.  All Company Names containing " & LSearchString & "."
  26.  
  27.     End If 
  28.  
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
Expand|Select|Wrap|Line Numbers
  1.  
  2. OR LastName LIKE '*" & LSearchString & "* OR CostCenter LIKE '*" & LSearchString & "*  
  3.  
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
Aug 8 '07 #5

JKing
Expert 100+
P: 1,206
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.

Expand|Select|Wrap|Line Numbers
  1. OR LastName LIKE '*" & LSearchString & "*' OR CostCenter LIKE '*" & LSearchString & "*'"
  2.  
Aug 8 '07 #6

P: 48
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
Aug 8 '07 #7

P: 48
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
Aug 9 '07 #8

missinglinq
Expert 2.5K+
P: 3,532
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)>
Aug 9 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.