Results 1 to 4 of 4
  1. #1
    joecamel9166 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    85

    Creating a record lookup form

    Ok, so I am just about done with the DB! A big thanks to all from here that helped me on this journey!



    I am creating a lookup form.

    I have a combo box that includes the "filters" I want to search by.
    Example:
    RSO#
    PO#
    Company Name
    Company State
    Company Zipcode
    Serial Number
    Model Number
    etc.

    Then I have an unbound text box to type in the criteria.

    I want the search results to open another form with a listbox to display the results. (It would be better if the listbox could also be on the same form.)

    The only way I know to do this is to have a query for each "filter" and have it open to its own form with a listbox of all the records that match the criteria.

    This seems to be very redundant and I am looking for a better way to achieve this.

    Thanks!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you can have the listbox on the same form - actually easier

    and you need a sub to build your criteria - not quite clear from your description, but assuming you have multiple combos then something like


    Code:
    sub filterList()
    dim s as string
    dim Source as string
    
       Source="SELECT * FROM myTable"
       s=""
       if not isnull(cboRSO) then  s=s & "[RSO]=" & cboRSO & " AND "
       if not isnull(cboPO) then  s=s & "[PO]=" & cboPO & " AND "
       if not isnull(cboCompanyName) then  s=s & "[CompanyName]='" & cboCompanyName & "' AND "
       ...
       ...
    
       if s<>"" then s="WHERE " & left(s,len(s)-6)
       myListbox.Rowsource=Source & s
    
    end sub
    it may be your combos are not null but have a zero length string in which case change

    if not isnull(cboRSO)

    to

    if nz(cboRSO,"")<>""

    call this sub as required - might be on a button event or the after update event of each combo

  3. #3
    joecamel9166 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    85
    Thanks.
    Its only one combo box with a value list of:
    RSO#
    PO#
    Zip
    Etc.

    so the code would have to be in the click event of a button.

    the records I am looking to retrieve are from "tblUnits"

    the criteria of Company Zip would search "tblCustInv" and pull the related record(s) from "tblUnits"

    would your code above handle that? I'm not too familiar with SQL. Almost everything I did so far was through a query.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I'm not too familiar with SQL. Almost everything I did so far was through a query.
    queries are SQL - I presume you mean you only use the query window. If so click on the sql window and you can see what your query looks like in sql.

    OK, misunderstood what you were trying to do. Assuming your unbound textbox is called txtCriteria, your combo is called cboFilter and the contents of the combo are your actual field names plus another column to indicated it is looking for a text or number (because the criteria is set up differently), use the following code in its after update event

    Private Sub txtCriteria()
    dim Source as string

    Source="SELECT * FROM tblUnits WHERE [" & cboFilter & "]=""
    on error resume next
    if cbofilter.column(1)="Number" then
    myListbox.Rowsource=Source & txtCriteria 'for numbers
    Else
    myListbox.Rowsource=Source & "'" & txtCriteria & "'" 'for text
    end if

    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 12-08-2014, 08:51 AM
  2. Replies: 3
    Last Post: 04-18-2014, 12:20 PM
  3. Replies: 3
    Last Post: 04-09-2014, 01:45 PM
  4. Replies: 21
    Last Post: 08-05-2013, 06:23 AM
  5. Lookup List in a Form for Table Record entry...
    By noaccessguru in forum Forms
    Replies: 1
    Last Post: 05-05-2011, 12:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums