Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unanswered: searching in a rowsource

    i have an SQL-Server stored procedure that fills 0, 1, or >1 rows in myCombo in an A front-end.

    my task is to search for myValue in myCombo.Column(N) and return the rownumber if found, else some value indicating notfound.

    re-running the sp is not an option.

    - grateful for any suggestions, my mind is blank!

    izy
    currently using SS 2008R2

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Izy,

    The combobox is sourced to a table? Is there either an upper bound on the # of columns or a set # of columns?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Izy,

    If the combobox has a ValueList, finding the value is trivial ... Run InStr on the RowSource. Finding the row is a little more tricky ... You'll need the # of columns and then count the # of delimiters to determine which row number ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it's not a value list. the .rowsource is an sp equivalent to:
    SELECT idThis, blahThis, hmmmThis, ummThis FROM aTable WHERE ((blahThis = 'X') OR (blahThis = 'Y'));

    now i want to go into the combo and find hmmmThis = "Fred"
    ("Fred" is guaranteed unique)

    i don't want to re-run the sp 'cos i don't have X & Y any more

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what i'm actually looking for is the notfound return when "fred" is not in .Column(N) of my combo (so i know i need to update the combo)

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Izy,

    This is what I'm kicking around ... I do nothing but disconnected recordsets and as such, all my forms and controls that are based on tables use a "template" table to bind to which allows me to make a temp table to populate and then bind to the form or control... This makes for easier access to the data for a given form or control (outside of the form or control itself)...

    Now, your combobox is bound to a SP right? So in essence, you're bound to a recordset meaning that you're only window into it is thru the combobox ... Hmmm. What about trying to access the recordset thru a collection?
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    tried looking at collections as you suggested but got a headache instead of results. i'm beginning to believe that the .rowsource cannot be attacked.

    you are right: make that one additional step to populate a local table and then feed the combo from the table: the rest is trivial from the local table.

    thanks for lending some brain cells to the problem

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Some times I just get lucky ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Dec 2003
    Posts
    268

    Different approach

    Do yo uhave access to the SQL that is dynamically building the rowsource for the control?

    If so use that and store the information in a recordset.

    Once it is in a record set cycle throught the list of results until myvalue = the rowsource value
    dim iIndex as integer
    set rs = db.openrecordset(sql)
    if rs.eof and rs.bof then
    'no records found
    end if
    do while not rs.eof
    if rs.fields(0).value = myvalue then
    msgbox "The index of the selected item is " & i
    exit loop
    end if
    rs.movenext
    end while

    set rs=nothing

    this should get you headed in the right direction.

    HTH

    I deal with the same issues quite frequenty with forms that are not bound to any table or query etc.

  10. #10
    Join Date
    Dec 2003
    Posts
    268

    Different approach

    Do yo uhave access to the SQL that is dynamically building the rowsource for the control?

    If so use that and store the information in a recordset.

    Once it is in a record set cycle throught the list of results until myvalue = the rowsource value
    dim iIndex as integer
    set rs = db.openrecordset(sql)
    if rs.eof and rs.bof then
    'no records found
    end if
    do while not rs.eof
    if rs.fields(0).value = myvalue then
    msgbox "The index of the selected item is " & i
    exit loop
    end if
    i = i+1
    rs.movenext
    end while

    set rs=nothing

    this should get you headed in the right direction.

    HTH

    I deal with the same issues quite frequenty with forms that are not bound to any table or query etc.

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yes - that would work also, but the .rowsource is (i'm ashamed to say) rather larger than i would normally want in client/server: 15kB in one mode, 80kB in another mode --- that is exactly why i'm reluctant to requery unless i really really need to.

    my feeble excuse for this over-large .rowsource is that it is pretty stable (maybe it changes once or twice a day) and the one-time hit to download it makes the rest of the application appear much more responsive.

    i'm guessing that a saved A query ploughing thru a table will be faster than looping thru a recordset. in any case, both will be radically faster than a revisit to the server.

    besides, with the local table approach, maybe i don't need to download the full data lump again, probably i can simply append the missing record.

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Dec 2003
    Posts
    268

    RowSource as a string

    One thing I have found myself doing is storing large recordsets that may need to be reaccessed as a clone of the initial recordset and letting it live in some object in the application.

    It is a lot faster to access some variable of an object than to hit the DB and should not increase network traffic at all.

    Just a thought. I am still thinking about a way to dissect the rowsource information on a control.

    MW

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    MW

    ?? this probably works:
    for Y = 0 to .listcount -1
    if .column(X, Y) = "Fred" then
    but i was hoping for something like a .findfirst to avoid code looping thru several hundred string-compares.

    one other frustration here is that i can determine what the .boundcolumn should be for "Fred". armed with this i've tried triggering _notinlist() or error with:
    myCombo = IDofFred
    ...and failed. (anyone know why this doesn't trigger _notinlist() when Fred is not in the combo ??)

    last night i tried:
    myCombo = IDofFred
    if myCombo.Column(X) <> "Fred" then
    i will play some more with this as it seems an attractive idea.

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this works:
    myCombo = IDofFred
    if isnull(myCombo.Column(N)) then ' "Fred" is notinlist

    i overlooked the fact that i know the bound column value that goes with the text value i'm looking for in .column(N) - duhhh!

    izy
    currently using SS 2008R2

Posting Permissions

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