Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    40

    Unanswered: A control capable of displaying search results?

    Hi, I am using Microsoft Access 2003 and I am trying to create a form to display search results of a find operation (the find function itself has already been written and is working). What I need is something similar to what Access itself provides, when viewing lists of forms, tables etc. in the "Details view" I basically just need functionality to add and remove columns programatically at runtime and also add rows, etc. I believe that in previous IDEs that I have used, that this was called a "grid view" control but I may be wrong.

    I have tried to use a subform in datasheet view, but I don't think it is possible to hide columns using this control.

    I am looking on the "More Controls" button and I find things like Spreadsheet 11, and so on. The closest thing to my needs which I have found so far is called the "GeoTel Grid Control" but this seems to be a bit buggy and I couldn't use it.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I've used a listbox before. It allows you to display any columns you want with as many rows as you want, and has the functionality built in to let users select which results they like and then do whatever they want with those results (given the right programming that is).
    Me.Geek = True

  3. #3
    Join Date
    Jun 2007
    Posts
    40
    Quote Originally Posted by nckdryr
    I've used a listbox before. It allows you to display any columns you want with as many rows as you want, and has the functionality built in to let users select which results they like and then do whatever they want with those results (given the right programming that is).
    Hi, thanks for the swift response.

    I have been looking at a listbox and what I can't work out is how to modify individual elements of the box. So for example, I have created a test form and a list box on it with ColumnCount set to 1, and it is sourced from a value list. Then I try to do something like:

    Code:
    List0.AddItem "goodbye" ' This works
    List0.List(0,0) = "hello" ' This results in a "member not found" error
    What am I doing wrong and what do I need to do to set the individual items in a list box?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    what do I need to set the individual items in a list box
    I think you want to change the record source of the listbox.
    Code:
    Me.List0.RecordSource = "SELECT * FROM Products"
    Me.Refresh
    Is that what you want?
    George
    Home | Blog

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    george is doing a Table/Query type list box, which I think is what you want (since it's for displaying search results). Whereas you're doing a Value list (adding one item at a time).
    Quote Originally Posted by kidburla
    the find function itself has already been written and is working
    Mind displaying this? If it's an SQL string, you should be able to use george's method after you've changed the list box to a Table/Query type.

    Also, to do something like what you were trying, you can use the Column property to read (only) what value is in row x and column y, like...
    Code:
    strItem = me.lstboxname.column(y,x)
    (you may want to double-check the help file on the column prop, can't remember if it's y,x or x,y)
    Me.Geek = True

  6. #6
    Join Date
    Jun 2007
    Posts
    40
    Quote Originally Posted by nckdryr
    george is doing a Table/Query type list box, which I think is what you want (since it's for displaying search results). Whereas you're doing a Value list (adding one item at a time).

    Mind displaying this? If it's an SQL string, you should be able to use george's method after you've changed the list box to a Table/Query type.

    Also, to do something like what you were trying, you can use the Column property to read (only) what value is in row x and column y, like...
    Code:
    strItem = me.lstboxname.column(y,x)
    (you may want to double-check the help file on the column prop, can't remember if it's y,x or x,y)
    The parameter passed to the search results window is an ADODB Recordset, which contains most, but not all, of the required information. Some information needs to be looked up separately, as the linked tables have different TOP requirements, and in any case I think there are more than 5 (the maximum number of nested JOINs which Access/Jet allows). This is what necessitates building the list box manually. The actual find function works via a fairly complicated SQL string.

    Technically, it would be possible still to create a temporary table and then set the record source of the list box to that table, but this would be computationally expensive compared to setting the individual cells.

    I have seen this Column notation before and that is exactly the kind of thing I want, it's just that I need a read/write version. This seems to be provided by the "List" property of a ListBox control (as described in the VBA help), but this property is an invalid member at runtime, and doesn't even come up in the pop-up autocomplete box which appears when you are typing the name of an object property in the VBA editor. Is there some way around this?

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    If you're adding one row at a time, can you just do something like...
    Code:
    Me.LstBoxName.AddItem (strColumn0 & ";" & strColumn1 & ";" & strColumn2)
    You can do a row at a time at least, though I think you're looking to do an element at a time. Would this work, or do you still have to do element at a time?
    Me.Geek = True

  8. #8
    Join Date
    Jun 2007
    Posts
    40
    Quote Originally Posted by nckdryr
    If you're adding one row at a time, can you just do something like...
    Code:
    Me.LstBoxName.AddItem (strColumn0 & ";" & strColumn1 & ";" & strColumn2)
    You can do a row at a time at least, though I think you're looking to do an element at a time. Would this work, or do you still have to do element at a time?
    Unfortunately this is not good for me. The problem is that I want to add different columns at different times. So for example I might want to add the first 5 columns from the recordset, and then the next 2 columns "manually" (in other words, by doing a separate SQL query, and some calculation) once I have filled the rest of the table from the recordset. If I had some way to edit the cells, I could do this quite easily, but it doesn't work by adding row-at-a-time.

Posting Permissions

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