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

Search from command button not working

P: 3
Hi, Ive been working on an Access database for over a month now and I have had some trouble with creating a search button, and making it work. I can create the button but for some reason it stil does not work. Both me and my boss have been through the coding numerous times and cannot find what is wrong.

Does anybody know the coding to create a successful search button in Access 2003???

Any information will be greatly appreciated thank you
Sep 15 '08 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 2.5K+
P: 2,545
Hi, and Welcome to Bytes!

It would help us if you could post the code you have at present, and advise us of the names of the fields and controls concerned - we know nothing about your database other than what you tell us, and since you have not told us anything about what it is you are searching upon it is very difficult to advise you.

I have retitled your post to a more meaningful title - 'Help!!!' does not direct readers to what you want help with...

Sep 15 '08 #2

P: 3
Ok thanks for replying, below is the code I have for the search button. There are feilds named, BMS ID, Name, Lot#, Storage Location, Date Recieved, amount Recieved, Measure, Expiry date all in the Compound List table. I want a search button on the Compound Information form searching the first four feilds of all compounds in Compound List table.

I hope this helps you understand more

The code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdsearch_Click()
  2. On Error GoTo Err_cmdsearch_Click
  4. 'Define variables for SQL search
  5.     Dim used As Byte
  6.     Dim MySQL As String
  8.     'Define database variables
  9.     Dim dbs As AllForms, rst As Recordset
  11.     'Initialise the "used" variable which tests whether the SQL statement has been previously written
  12.     'or not
  13.     used = 0
  15.     'First half of the SQL statement which selects every record from the "PRB Index" table
  16.     MySQL = "Select * FROM [Compound List] Where "
  18.     'IF fields are blank then display an error message saying so and set the focus to the first field
  19.     If ([Name] = "" And [BMS ID] = "" And [Lot#] = "" And [Storage Location] = "") Then
  20.         MsgBox "Please enter search data into at least one of the search fields", vbCritical, "Error!"
  21.         [Compound Information].SetFocus
  22.         'Exit this code
  23.         Exit Sub
  24.     End If
  26.     'IF the Name field is not blank THEN
  27.     If Forms![Compound Information]![Name] <> "" Then
  28.         'Do a wildcard search on the Name
  29.         MySQL = MySQL & " ([Name] like '*" & [Name] & "*') "
  30.         'Set the used byte to 1 to indicate that the MySQL statement has been used
  31.         used = 1
  32.     End If
  34.     'IF the BMSID field is not blank THEN
  35.     If Forms![Compound Information]![BMS ID] <> "" Then
  36.         'IF the SQL statement has already been written
  37.         If used = 1 Then
  38.             'Append more search criteria to the SQL statement
  39.             MySQL = MySQL & " AND ([BMS ID] like '*" & [BMS ID] & "*') "
  40.         'ELSE the SQL statement has not been written
  41.         Else
  42.             MySQL = MySQL & " ([BMS ID] like '*" & [BMS ID] & "*') "
  43.             'Set the used byte to 1 to indicate that the MySQL statement has been used
  44.             used = 1
  45.         End If
  46.     End If
  48.     'IF the Lot# field is not blank THEN
  49.     If Forms![Compound Information]![Lot#] <> "" Then
  50.         'IF the SQL statement has already been written
  51.         If used = 1 Then
  52.             'Append more search criteria to the SQL statement
  53.             MySQL = MySQL & " AND ([Lot#] like '*" & [Lot#] & "*') "
  54.         'ELSE the SQL statement has not been written
  55.         Else
  56.             MySQL = MySQL & " ([Lot#] like '*" & [Lot#] & "*') "
  57.             'Set the used byte to 1 to indicate that the MySQL statement has been used
  58.             used = 1
  59.         End If
  60.     End If
  62.     'IF the Storage Location field is not blank THEN
  63.     If Forms![Compound Information]![Storage Location] <> "" Then
  64.         'IF the SQL statement has already been written
  65.         If used = 1 Then
  66.             'Append more search criteria to the SQL statement
  67.             MySQL = MySQL & " AND ([Storage Location] like '*" & [Storage Location] & "*') "
  68.         'ELSE the SQL statement has not been written
  69.         Else
  70.             MySQL = MySQL & " ([Storage Location] like '*" & [Storage Location] & "*') "
  71.             'Set the used byte to 1 to indicate that the MySQL statement has been used
  72.             used = 1
  73.         End If
  74.     End If
  77.     'IF the SQL statement has been written (i.e. the SQL criteria is not blank)
  78.     If used = 1 Then
  80.         'Set the recordsource of the search results form to the SQl statement generated above
  81.         Me![Search Sub Form].Form.RecordSource = MySQL
  82.         'Refresh the search results form to pick up the new recordsource
  83.         Me![Search Sub Form].Form.Refresh
  85.         'Once the recordsource has been set, make the table visible
  86.         Me![Search Sub Form].Visible = True
  88.         'Set up the database and recordset variables
  89.         'dbs is set to the current database
  90.         'rst is set to the MySQL statement criteria
  91.         Debug.Print MySQL
  92.         Set dbs = CurrentDb
  93.         Set rst = dbs.OpenRecordset(MySQL)
  95.         'IF no records have been found
  96.         If rst.RecordCount < 1 Then
  97.             'Make the search results form invisible again
  98.             Me![Search Sub Form].Visible = False
  100.             'Display an error message
  101.             MsgBox "No Records Found. Please Re-Enter Your Search Criteria", vbCritical, "Error!"
  102.         End If
  104.         'Close the recordset and disassociate the dbs variable from the current database
  105.         rst.Close
  106.         Set dbs = Nothing
  107.     End If
  109. Err_cmdsearch_Click:
  110.     MsgBox Err.Description
  111.     Resume Exit_cmdsearch_Click
  113. Exit_cmdsearch_Click:
  114.     Exit Sub
  116. End Sub
Thanks very much
Jenny x x
Sep 15 '08 #3

Expert Mod 2.5K+
P: 2,545
Hard to tell in 116 lines of code. Anyways, the first comment I have is that I think it's a complicated way to do something relatively simple - and the simplest way is to apply a filter to the form concerned. A filter comprises the WHERE clause of an SQL statement (just as you have at present) but without the WHERE. You could then just apply the filter instead of changing the recordsource for your subform - using
Expand|Select|Wrap|Line Numbers
  1. Forms![subformname].Form.Filter = <yourSQLWhereClauseStringWithoutWhere>
  2. Forms![subformname].Form.Filteron = True
I am not sure where you are currently going wrong. One error is that the Refresh method is not the correct one to apply to update the recordsource of a form; use the Requery method instead.

In debugging what you currently have, if you continue to have problems you should set a breakpoint at the start of the code at step through it a line at a time, viewing the values of all local variables as you go. In particular, check that the SQL statement is correctly formed - you should be able to copy it into an Access query to check that the SQL delivers the results you expect.

Sep 16 '08 #4

Expert Mod 15k+
P: 31,662
In case it helps, Debugging in VBA tells you had to do some basic debugging (setting breakpoints, stepping through code, etc).
Sep 16 '08 #5

P: 3
thank you I've got it working now! all your help was very much appreciated! Thanks again
Jennyx x x
Sep 29 '08 #6

Expert Mod 15k+
P: 31,662
Good for you Jenny.

I hope you managed to get into some debugging. That can be such a useful tool in your arsenal.

Welcome to Bytes!
Sep 29 '08 #7

Post your reply

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