help! Search button

[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

[Hack]

Welcome to DevX

What code are you running in your Search button?

Typically Search buttons simply execute an SQL SELECT query with specified parameters in its WHERE and AND clauses.

[lewis905]

I tried it in SQL and I also tried the wizard on Access, but neither seem to work. we cant seem to find out whats going wrong. We thought it might have been when we declared what the dbs is (we set it to 'all forms') but that didnt seem to make a difference!

[Hack]

Well, I haven't used a wizard of any kind in any software package in over 15 years so I won't be of any help there.

However, I might be able to lend a hand or two writing some SQL. What SQL code did you try that didn't work for you?

[lewis905]

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

all of that lol. thanks for taking the time to help me!!

[Hack]

First, I added [Code]your code goes here[/Code] tags to your post as it makes reading posted code a bit easier.

Second, a few questions: Dim dbs As AllForms - what is the data type "AllForms"? Wouldn'd dbs be declared As Database?

Are you doing this in Access VBA?

At the end of your query, do you know exactly what is getting passed back to your database (i.e., prior to executing it, have you viewed it to see what the SELECT query is actually asking for?)

[Hack]

I probably should have done this before answering, but after I posted I googled "AllForms" and found this.

AllForms deals with the Access Forms collection....it, based on what I read, has nothing to do with database connectivity or executing SQL queries.

[lewis905]

im well confused! :S
so should i change dbs to database?
Im doing it on MS Access (i dont know what VBA is sorry) im only just learning it all.

[lewis905]

changing dbs to database doesn't work either :s

[Hack]

VBA stands for Visual Basic for Applications - it is the language to develop applications for Excel, Word, Access, Powerpoint and other Office products (as opposed to Visual Basic or Visual Basic dot Net)

You probably should change dbs to database, but I suspect that would be more for "housekeeping" that actually solving your current problem. If declaring it as AllForms has not caused a issue by now, it probably won't.

Anyway, the one thing I the most curious to see is the actual finished SQL query. To do that, I would like you to put the following piece of code immediately above the line: Set dbs = CurrentDB

Debug.Print MySQL

This will send your query to the Immediate Window....the Immediate Window is used in debugging. To open the Immediate Window do [Ctrl+G] or you can select it from the IDE menu under View.

Don't do this now, but when you have a moment, read through this article on error handing and debugging tips. For the moment though, add the debug.print statement and run the query. Open the immediate window, and copy and paste the results back into this thread. I want to see, and I want you to see, exactly what is getting passed back to the database as the query.

[lewis905]

With the coding that I already have, i can search for information but only by typind in the BMS ID. I cannot search by name or by any of the other fields.

When I try and run it, an empty window named "macros" appears.
this comes up in the immediate box
Select * FROM [Compound List] Where ([Name] like '*Compound Information*')

[Hack]

We are getting somewhere. One part of the search works. This I didn't know. From the initial post, I was under the impression that nothing at all worked. Run a query and enter a BMS ID, and post the results from the Immediate Window. I want to see what the results of a query that does work looks like.

Do you have any names in your database that contain the string "Compound Information"? I suspect not....that is why that portion isn't working.

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

Name is being used so often here I'm not exactly sure what is a field name and what is a variable. In the LIKE clause, is Name a text field or something?

[lewis905]

Compound Information is the name of the form.the Name referred to between the & is the name of the field in the form. The other Name refers to the database.

No no names in the database as Compound Information

[Hack]

But, you have all your data stored in the database, in tables, right?

Why would you then want to search a form?

[lewis905]

yep i do.

because Im setting up database for a seperate department in work. where they can store the compound information and search for them when needed. It will save paper and its more efficient