Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries & listbox row source (Access 97)

    Hi

    I have a number of forms each with a listbox. Each listbox has the same information, but the selection criteria for each differs. Currently, I have a separate query for each rowsource property for each listbox in each form. Thus I have a number of very similar queries. So is there a way to use the one query where the selection criteria changes depending on which form and listbox is opened.

    For example (using dummy queries)

    Query 1 = SELECT name, surname, address, city FROM contact WHERE City ='New York'
    Query 2 = SELECT name, surname, address, city FROM contact WHERE Name = 'Smith'

    where the different rowsource for each listbox would equal Query1, or Query2 etc.

    What I would like is the one query looking something like

    Global Query = SELECT name, surname, address, city FROM contact WHERE varitem

    Where the selection criteria for varitem is determined by which form with listbox is opened. I was thinking that perhaps a function could be used in the rowsource eg =functionname(varitem), but not sure whether a) this is the right appropriate and [img]/forums/images/smilies/cool.gif[/img] if so, how.

    Hopefully, I have explained myself ok on this question.

    Thanks & Regards
    WTH

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Queries & listbox row source (Access 97)

    You can start by creating a single query, let's call it qryListBox:
    SELECT name, surname, address, city FROM contact

    Then in the rowsource for each listbox, you put in an SQL statement something like this:
    SELECT * FROM qryListBox WHERE City='New York'
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries & listbox row source (Access 97)

    Hi

    Thanks for the input, although I'm not sure whether it answers my question. I resolved the problem, by using the following:

    1 Creating a function to pass the sql variable bit into a public variable declaring the sql statement.
    eg
    Public Pvaritem as string
    Public Function Sqlvariable(varitem as string)
    Pvaritem = "SELECT name, address, city FROM organisation WHERE & varitem & ";"
    end function
    2 In the form call the function passing the appropriate variable and setting the listbox rowsource to the name of the public variable.
    eg Open_form
    Call Sqlvariable("City = 'New York'")
    Me.listbox.rowsource = Pvaritem
    end sub

    Regards
    WTH

Posting Permissions

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