help! Search button


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: help! Search button

  1. #1
    Join Date
    Sep 2008
    Posts
    9

    help! Search button

    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

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Sep 2008
    Posts
    9
    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!

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  5. #5
    Join Date
    Sep 2008
    Posts
    9
    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
            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!!
    Last edited by Hack; 09-04-2008 at 10:19 AM. Reason: Added Code Tags

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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?)
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  8. #8
    Join Date
    Sep 2008
    Posts
    9
    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.

  9. #9
    Join Date
    Sep 2008
    Posts
    9
    changing dbs to database doesn't work either :s

  10. #10
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  11. #11
    Join Date
    Sep 2008
    Posts
    9
    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*')

  12. #12
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    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.
    Code:
    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?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  13. #13
    Join Date
    Sep 2008
    Posts
    9
    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

  14. #14
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    But, you have all your data stored in the database, in tables, right?

    Why would you then want to search a form?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  15. #15
    Join Date
    Sep 2008
    Posts
    9
    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

Similar Threads

  1. search button & access database
    By oscarjaime in forum .NET
    Replies: 0
    Last Post: 04-14-2008, 02:02 PM
  2. Pseudocode Problem
    By zobi316 in forum VB Classic
    Replies: 3
    Last Post: 03-10-2008, 08:05 AM
  3. Replies: 0
    Last Post: 10-11-2006, 05:10 PM
  4. Need help with calculator...
    By 2eXtreme in forum Java
    Replies: 0
    Last Post: 04-05-2006, 08:38 AM
  5. Search button not getting displayed in PALM
    By Prakash in forum Mobile
    Replies: 0
    Last Post: 01-23-2001, 03:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center