Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a computer inventory db created with Access XP, SP3. I often need to search for a specific record based on one specific known field. For example, I know the user name and need to find the machine name. I can use the built-in find function, but it gets annoying having to manually go back to the first record, and then click on the specific field by which I want to search when I need to perform a specific search multiple times.

    I decided to just create my own custom search form, which should give me more flexibility. The form consists of an option group, a text box and a list box. I have the following coded for the text box:

    Code:
    Private Sub Text0_AfterUpdate()
    
    Select Case frame1
        Case 1 'Search by Machine Name
            List2.RowSource = "SELECT tblMain.Machine_Name, tblMain.Model, tblMain.User, tblMain.Location, tblMain.WarrantyExpDate, tblMain.Asset_Tag FROM tblMain WHERE (((tblMain.Machine_Name) Like " * " & [Forms]![frmSearch]![Text0] & " * "));"
        Case 2  'Search by Model Number
            Me.List2.RowSource = qrysrchmodel
        Case 3  'Search by User
            Me.List2.RowSource = qrysrchname
        Case 4  'Search by tag
            Me.List2.RowSource = qrysrchtag
        End Select
    
    List2.Requery
    When executing the code I have 2 issues (but probably only need a solution to one of them, since essentially the problem is that I'm not getting any results):
    1) I get a "type mismatch" error for Case 1
    2) For Cases 2 - 4, the list box remains empty. However, if I just open the respective queries while I have the search form active with data in Text0, I get the expected results.



    I know it's got to be something simple and stupid that I'm overlooking, but I just can't find it.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Chico,

    For Case 1 it looks like you just forgot the "ME."

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by ChicoV View Post
    I have a computer inventory db created with Access XP, SP3. I often need to search for a specific record based on one specific known field. For example, I know the user name and need to find the machine name. I can use the built-in find function, but it gets annoying having to manually go back to the first record, and then click on the specific field by which I want to search when I need to perform a specific search multiple times.

    I decided to just create my own custom search form, which should give me more flexibility. The form consists of an option group, a text box and a list box. I have the following coded for the text box:

    Code:
    Private Sub Text0_AfterUpdate()
    
    Select Case frame1
        Case 1 'Search by Machine Name
            List2.RowSource = "SELECT tblMain.Machine_Name, tblMain.Model, tblMain.User, tblMain.Location, tblMain.WarrantyExpDate, tblMain.Asset_Tag FROM tblMain WHERE (((tblMain.Machine_Name) Like " * " & [Forms]![frmSearch]![Text0] & " * "));"
        Case 2  'Search by Model Number
            Me.List2.RowSource = qrysrchmodel
        Case 3  'Search by User
            Me.List2.RowSource = qrysrchname
        Case 4  'Search by tag
            Me.List2.RowSource = qrysrchtag
        End Select
    
    List2.Requery
    When executing the code I have 2 issues (but probably only need a solution to one of them, since essentially the problem is that I'm not getting any results):
    1) I get a "type mismatch" error for Case 1
    2) For Cases 2 - 4, the list box remains empty. However, if I just open the respective queries while I have the search form active with data in Text0, I get the expected results.



    I know it's got to be something simple and stupid that I'm overlooking, but I just can't find it.
    I think you have a problem with quotes, try this:
    List2.RowSource = "SELECT tblMain.Machine_Name, tblMain.Model, tblMain.User, tblMain.Location, tblMain.WarrantyExpDate, tblMain.Asset_Tag FROM tblMain WHERE (((tblMain.Machine_Name) Like *" & [Forms]![frmSearch]![Text0] & "*));"

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    For Case 1 the Mismatch is because you cannot have "*" included in the assignment string.
    You need to insert the " Characters into the string.

    You can either use a single ' instead of a ""

    e.g. '*' or concatenate in additional chr(34)

    Also IF there are no field ambiguities you can omit the tablenames from the query

    Code:
    "SELECT Machine_Name, Model, User, Location, WarrantyExpDate, Asset_Tag FROM tblMain WHERE  ((Machine_Name) Like " & chr(34) & "*" & [Forms]![frmSearch]![Text0] & "*" & chr(34) & "));"
    OR with Single Quotes instead of chr(34)

    Code:
    "SELECT Machine_Name, Model, User, Location, WarrantyExpDate, Asset_Tag FROM tblMain WHERE  ((Machine_Name) Like '*"  & [Forms]![frmSearch]![Text0] & "*'));"
    In the Other cases you need to enclose the Query Names in double quotes
    e.g.
    Code:
    Case 3  'Search by User
            Me.List2.RowSource = "qrysrchname"
    Andrew

  5. #5
    New Lounger
    Join Date
    Jun 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    AWK -- I tried both versions of the SQL screen, but still get no results in the list box. Not a big deal though, it works perfectly when I put double quotes around the query name and, oddly enough spell the query name correctly

    Now, what would be really cool is to put some code in the OnDoubleClick event to go to that record on my main form. Here's how I have it as of now, procedurally:

    1) frmForm opens when the db opens. This is the main form that I use to do data entry/manipulation.
    2) I put a command button on this form that opens frmSearch (the form with which this thread deals).
    3) As of now, I have a simple close button on frmSearch.

    It just occurred to me that I still may need the default Access record search capability. So, once I find the record I'm looking for with the search form, it could save a lot of headache to just be able to say something like

    Code:
    forms!frmForm.DoCmd.GoToRecord List2.Column (4)
    I know that's probably nowhere near the correct syntax, but that's kind of the functionality I'm looking for. Sorry for the "noob" questions...I've been out of the dev game for a few years, so I'm a bit rusty.

  6. #6
    New Lounger
    Join Date
    Jun 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, wait...I think I just found the answer myself. I put the following in the OnDoubleClick event of the list box:

    Code:
    DoCmd.OpenForm "frmForm", , , "Machine_Name=" & Chr(34) & List2.Column(0) & Chr(34)
    Then, on the frmForm (main form) I added a command button to turn off the filter passed to this form from the search form. That way, when I'm done making changes to the data, I can go back to "normal" view.

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Not sure why you get data on the SQL

    Try adding the following couple of lines just to test it

    Code:
    Private Sub Text0_AfterUpdate()
    
    Dim strSQL as String
    
    Select Case frame1
        Case 1 'Search by Machine Name
            strSQL="SELECT Machine_Name, Model, User, Location, WarrantyExpDate, Asset_Tag FROM tblMain WHERE  ((Machine_Name) Like " & chr(34) & "*" & [Forms]![frmSearch]![Text0] & "*" & chr(34) & "));"
            List2.RowSource = strSQL
            'Comment out or delete the lines below later
            Msgbox strSQL
            Debug.Print strSQL  'Need to see Immediate Window to see this
        Case 2  'Search by Model Number
            Me.List2.RowSource = "qrysrchmodel"
        Case 3  'Search by User
            Me.List2.RowSource = "qrysrchname"
        Case 4  'Search by tag
            Me.List2.RowSource = "qrysrchtag"
        End Select
    End Select
    
    List2.Requery    
        
    End Sub
    See what is generated in the Message Box

    Or copy and paste the results of the variable strSQL placed into the immediate window into a Query and see what it gets.
    Might give you some insight into why you get no rows.

    On the other hand, might not....
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •