Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: Referencing fields and rows in a RecordSet!

    I create a RecordSet and now I want to retrieve specific values from it!

    For example I want to know what is in Field 3 of the 5th row (record)?

    Is there a better way than...

    Create RecordSet

    Count number of records

    MoveFirst

    Iterate through recordset with MoveNext, autoincrement Counter and if I'm on 5th row (record) than myVariable is RecordSet![Field3].value


    I know there is, I just can't find it, that's why I'm asking!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    depends...

    possibilities include SQL, DLookup(), FindXxxx, Seek, Move N, list/combo, and probably several that i didn't think of: what's best depends on the situation
    ...hence some questions:

    do you use the recordset anyway for some other purpose? (what?)
    where is the data? (Jet on c: or ??)
    if Jet/c: what is the source of the recordset? (local table, linked table, query)
    how many rows?
    how big is the recordset (bytes - take a guess: 2kB 20kB 200kB)?
    how do you know you need the 5th row?
    how often are you going to do this? (once on _Click, 100,000 times in a loop, or ?)
    what are you going to do with myVariable when you 've got it?

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2007
    Posts
    108
    Well the idea is to use recordset to retrieve names of people and put those names on form's buttons!

    So the form has 21 button divided in 3 columns, all with the same Caption = "Commnad0".

    3 columns represent 3 departments and the idea is to populate Captions of those buttons with names of people working in those 3 departments order by hierarchy - both of these data are stored in People table. This would serve as visual picker for people taking part or performing certain tasks.

    People can change departments, retire or new people can be hired to work in those 3 departments. So that table would be source of button's caption each time the form loads.

    do you use the recordset anyway for some other purpose? (what?)
    To retrieve data needed to put on those buttons.

    where is the data? (Jet on c: or ??)
    In the same Access Database!

    if Jet/c: what is the source of the recordset? (local table, linked table, query)
    Table

    how many rows?
    21 at most.

    how big is the recordset (bytes - take a guess: 2kB 20kB 200kB)?
    Very small < 2kB

    how do you know you need the 5th row?
    I don't, I need to order recordset by department, then by hierarchy number, go to Button 1 and set its caption to RecordSet![FirstRow].[Name]+[LastName]+[Title]

    how often are you going to do this? (once on _Click, 100,000 times in a loop, or ?)
    Every time the form loads.

    what are you going to do with myVariable when you 've got it?
    Put it as Button's Caption

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your interface seems doomed ...the fourth department, the eighth person in one department.
    anyhow, going with your plan as described, how about this (is DAO code, but ADO equivalent exists):

    Departments are 1,2,3
    HierarchyNumbers are 1,2,3,4,5,6,7
    button names are myButton11...myButton17, myButton21...myButton27, myButton31...myButton37
    ...actually should work with any HierNo/Dept scheme as long as the buttons are named myButtonHierNoDept

    dim strSQL as string
    dim recs as DAO.recordset
    strSQL = "SELECT Department, Hierarchy, Name & " " & LastName & " - " & Title AS Blah FROM People"
    set recs = currentdb.openrecordset(strSQL, dbopensnapshot)
    with recs
    if not (.bof and .eof) then
    .movefirst 'not necessary, but do it anyway
    do while not .eof
    me.Controls("myButton" & !Department & !Hierarchy).Caption = !Blah
    .movenext
    loop
    endif
    end with
    recs.close
    set recs = nothing

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2007
    Posts
    108
    Thanks izy, very neat solution!

    I managed to solve it myself with approx. 4 times more code and I used the same method of button naming and referencing only with For...Next loops. Your solution is so much nicer.

    I knew I would solve it, that wasn't really the question how to assign caption to button, the question was more RecordSet oriented - like can you avoid MoveNext in cycling through recordset!

    I was thinking if I got the idea of changing 5th button in 2nd department could I use something like (in pseudo):

    CommandButton25.Caption = RecordSet.Filter(by Department No).GetRow(5).[Fields Name+LastName].value

    but nevermind, this is fine too.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...to answer your original question yes! you can avoid .movenext with .move n
    it is a relative move

    .move 5
    takes you + 5 records from wherever you are (or .EOF)

    .move 5, someBookmark
    takes you +5 records from the bookmarked record (or .EOF)
    e.g. if your SQL is sorted ... ORDER BY Dept, Hier
    and you bookmark the first record
    then 3rd Hier in Dept 2 is
    move 9, yourBookmark
    ((Dept -1) * 7) + (Hier -1) LATER: assuming each dept is fully populated with 7 people!

    to complete the discussion: since your data is in a local table, you could use the Seek method on the HierarchyNo & Department (use a compound index of both fields and open a table recordset, not snapshot) to go direct to the record you want. if linked Jet table, all you have is the Find method which is radically slower (...but you would not notice in such a small recordset). Seek & Find both quite well described in help.

    other possibilities are to use a list (visible or not) or to put the data into a 2D aray of strings. they don't really bring anything to the party unless you have some other use for the list/array

    izy

    LATER: i don't use filters - cannot comment on your proposed use of filter in the pseudo
    Last edited by izyrider; 12-24-07 at 05:16.
    currently using SS 2008R2

  7. #7
    Join Date
    Apr 2007
    Posts
    108
    Thanksk izy!

    Anyway going back to the original idea (now that the solutions is here) I suppose you cannot create buttons (or other form controls) during run-time which would be the most generic solution?

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i believe that you can run-time design-open a(nother) form and add controls etc in .MDB (since i only distribute .MDE it is a non-solution for me and i never tried it).

    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
  •