ADO Help

[James Stanich]

Can someone tell me how to retrieve records 10 at a time without numbering the records in the database (Access 2000). I want to display 10 records at a time on a form. Keep in mind that the records retrieved may not be one behind the other (ie Record1, then Record2, then Record12).

Please help.

Conceptually, what I read into what you wish to do is:

1. sort a table in some special way (possibly)
2. retrieve the first 10 records and load a list

3. while there are more records, retrieve the next 10 records .... possibly based upon something that happend in the n-1(th) set

One method
You will need 4 queries and 1 dummy table:

Potential == All the records that can conceivably be shown

UsedPotential == A table holding all the previously shown

NotInUsed == left join of Potential and UsedPotential showing the first X records in Potential and NOT in UsedPotential

AddUsedPotential == Adds to a table UsedPotential that shows the Records just shown by

KillLastUsedPotential == Deletes the LAST X records from UsedPotential

To go through the list

While NotInUsed has entries
   Fill the listbox from NotInUsed.
   wait for action
      if action is forward
          AddNotInUsed
      endif
      if action is backward
          KillLastUsedPotential
      endif
end while

This schema will allow you go back and forth through the dataset. Setting up UsedPotential with various filters will have the same effect as automatically having "already seen" the undesired records.

Good Luck
DerFarm

[James Stanich]

Can this be acheived with bookmarks and indexes? I have no idea, but I have of the terms before.

I can think of one way.

Bookmarks simply mark a record so that you could go back quickly. If you sort the Potential with no book mark to start, you will get the first 10 records from FirstTen.

Retrieve the LAST sort value from FirstTen. Now re-write FirstTen to reflect the >= value in Potential.


........code sequence from above

sql = "Select * from Potential where srtvalue>="
sql = sql & chr$(34) & lastvaluefromten & chr$(34)
sql = sql & ";"

dbs.querydefs.delete("FirstTen")
dbs.querydefs.refresh
dbs.createquerydefs("FirstTen",sql)

........code sequence continues


Good Luck
DerFarm

[James Stanich]

I will have a group of employess in a table (some which will be active, some will not(layed off)). From that list (group by 10's), the user must select whether this employee performed work today or not. I wanted to be able to show the first ten employees, choose the work type, then display the next 10. I wanted to create forward and backward buttons to randomize through all employees (could be upwards of 200). Can my result be accomplished with using two tables, one with all employees and one with the wok types?

[James Stanich]

Com on ADO GURUS. I am sure that someone has a simple solution.

I really don't have to to research this one but I will throw a line in the water for you.
I remeber reading about something like this, look in the ADO help for "Pages" When I get some time I'll look it up if nobody else gets to it first.

Best,

[James Stanich]

I cannot find a reference on ADO Pages in HELP. I've done a search on this forum and cna't find any answers. Please someone help...

This is all I'm trying to do:

I will have a group of employess in a table (some which will be active, some will not(layed off)). From that list (group by 10's), the user must select whether this employee performed work today or not. I wanted to be able to show the first ten employees, choose the work type, then display the next 10. I wanted to create forward and backward buttons to randomize through all employees (could be upwards of 200). Can my result be accomplished with using two tables, one with all employees and one with the work types?

[r0ach]

You can read all the records you want into a recordset, and display them 10 at a time.

[James Stanich]

r0ach : Can you explain how this is accomplished?

[r0ach]

OK.

Option Explicit
    Dim adoCon as ADODB.Connection
    Dim adoRS as ADODB.Recordset
    Dim TenRecs(1 to 10) as String

Private Sub Form_Load()
    Set adoCon = New ADODB.Connection
    Set adoRS = New ADODB.Recordset
    
    With adoCon
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=C:\MyDB\MyDB.mdb"
        .Open
    End With
    
    With adoRS
        .CursorType = adOpenStatic
        .Source = "SELECT User_Name FROM tblUsers WHERE User_Status = 'Active' ORDER BY User_Name ASC", adoCon
        .Open
    
        .MoveFirst
    End With

    ShowTenRecords
End Sub

Private Sub ShowTenRecords()
    Dim i as integer
    i = 0
    Do while (Not adoRS.EOF) and (i < 10)
        i = i + 1
        TenRecs(i) = adoRS!User_Name
        adoRS.MoveNext
    Loop
    'check to see if the whole array was filled
    ' if not, then we got less than 10 records.  fill the
    ' rest with blanks
    If i <> 10 then
        Do until i = 10
            TenRecs(i) = ""
            i = i + 1
        Loop
    End If
End Sub

Private Sub Command1_Click()
    ShowTenRecords
End Sub

Here we fill an array with the first 10 names from the recordset.

everytime you click the button, it will refill the array with the next ten, etc.

I haven't tested this.

I will send you an email with some proper code (tested and all)

Just explain quickly waht you want.

To display records 10 at a time, and to be able to go back and forth within that Recordset?

[James Stanich]

Hey got it. I loaded my items into an array and then added the row number this way I can distinguish sets of 10. Thanks to all that replied. Your knowledge as well as your time is appreciated.