Search from command button not working

[lewis905]

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
Jenny

[Stewart Ross]

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...

-Stewart

[lewis905]

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:

Private Sub cmdsearch_Click()
On Error GoTo Err_cmdsearch_Click

'Define variables for SQL search
    Dim used As Byte
    Dim MySQL As String

    'Define database variables
    Dim dbs As AllForms, rst As Recordset
  
    'Initialise the "used" variable which tests whether the SQL statement has been previously written
    'or not
    used = 0
    
    'First half of the SQL statement which selects every record from the "PRB Index" table
    MySQL = "Select * FROM [Compound List] Where "

    'IF fields are blank then display an error message saying so and set the focus to the first field
    If ([Name] = "" And [BMS ID] = "" And [Lot#] = "" And [Storage Location] = "") Then
        MsgBox "Please enter search data into at least one of the search fields", vbCritical, "Error!"
        [Compound Information].SetFocus
        'Exit this code
        Exit Sub
    End If

    'IF the Name field is not blank THEN
    If Forms![Compound Information]![Name] <> "" Then
        'Do a wildcard search on the Name
        MySQL = MySQL & " ([Name] like '*" & [Name] & "*') "
        'Set the used byte to 1 to indicate that the MySQL statement has been used
        used = 1
    End If

    'IF the BMSID field is not blank THEN
    If Forms![Compound Information]![BMS ID] <> "" Then
        'IF the SQL statement has already been written
        If used = 1 Then
            'Append more search criteria to the SQL statement
            MySQL = MySQL & " AND ([BMS ID] like '*" & [BMS ID] & "*') "
        'ELSE the SQL statement has not been written
        Else
            MySQL = MySQL & " ([BMS ID] like '*" & [BMS ID] & "*') "
            'Set the used byte to 1 to indicate that the MySQL statement has been used
            used = 1
        End If
    End If
    
    'IF the Lot# field is not blank THEN
    If Forms![Compound Information]![Lot#] <> "" Then
        'IF the SQL statement has already been written
        If used = 1 Then
            'Append more search criteria to the SQL statement
            MySQL = MySQL & " AND ([Lot#] like '*" & [Lot#] & "*') "
        'ELSE the SQL statement has not been written
        Else
            MySQL = MySQL & " ([Lot#] like '*" & [Lot#] & "*') "
            'Set the used byte to 1 to indicate that the MySQL statement has been used
            used = 1
        End If
    End If

    'IF the Storage Location field is not blank THEN
    If Forms![Compound Information]![Storage Location] <> "" Then
        'IF the SQL statement has already been written
        If used = 1 Then
            'Append more search criteria to the SQL statement
            MySQL = MySQL & " AND ([Storage Location] like '*" & [Storage Location] & "*') "
        'ELSE the SQL statement has not been written
        Else
            MySQL = MySQL & " ([Storage Location] like '*" & [Storage Location] & "*') "
            'Set the used byte to 1 to indicate that the MySQL statement has been used
            used = 1
        End If
    End If
    
       
    'IF the SQL statement has been written (i.e. the SQL criteria is not blank)
    If used = 1 Then
   
        'Set the recordsource of the search results form to the SQl statement generated above
        Me![Search Sub Form].Form.RecordSource = MySQL
        'Refresh the search results form to pick up the new recordsource
        Me![Search Sub Form].Form.Refresh
    
        'Once the recordsource has been set, make the table visible
        Me![Search Sub Form].Visible = True
    
        'Set up the database and recordset variables
        'dbs is set to the current database
        'rst is set to the MySQL statement criteria
        Debug.Print MySQL
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(MySQL)
 
        'IF no records have been found
        If rst.RecordCount < 1 Then
            'Make the search results form invisible again
            Me![Search Sub Form].Visible = False
        
            'Display an error message
            MsgBox "No Records Found. Please Re-Enter Your Search Criteria", vbCritical, "Error!"
        End If
    
        'Close the recordset and disassociate the dbs variable from the current database
        rst.Close
        Set dbs = Nothing
    End If

Err_cmdsearch_Click:
    MsgBox Err.Description
    Resume Exit_cmdsearch_Click
    
Exit_cmdsearch_Click:
    Exit Sub
    
End Sub

Thanks very much
Jenny x x

[Stewart Ross]

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

Forms![subformname].Form.Filter = <yourSQLWhereClauseStringWithoutWhere>
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.

-Stewart

[NeoPa]

In case it helps, Debugging in VBA tells you had to do some basic debugging (setting breakpoints, stepping through code, etc).

[lewis905]

thank you I've got it working now! all your help was very much appreciated! Thanks again
Jennyx x x

[NeoPa]

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!