Limit record set to 22 and then 23-end of file

[claven123]

I am exporting data from access to excel. I have a record set that is larger than the space (rows), I'd like to place the first 22 records and then place the remaining records a few columns over. I'm not sure how to limit the first set of data ? array

i = 16
With xlWks
    Do While Not rsHonorary.EOF
        .Range("AB" & i).Value = Nz(rsHonorary!FullName, "")
        i = i + 1
    rsHonorary.MoveNext
    Loop
End With

This is the query that provides the data.

SQLHonorary = "SELECT TblMembers.LastName, TblMembers.FirstName, TblMembers.Status, [FirstName] & "" "" & [LastName] AS FullName " & _
    "FROM TblMembers " & _
    "WHERE (((TblMembers.Status) = 'Honorary')) " & _
    "ORDER BY TblMembers.LastName, TblMembers.FirstName;"

Would I limit the record set at the query level or the placement in Excel?

D

 

[claven123]

I was able to get it working, sort of. I'm getting an error, but it runs.

It does send the data from records 1-19 to the first column and 20-32 to the second column.

Then I get an error. Error Number: -2147352567=No current record.

Now, if I line out one of the two loops, I don't get the error. So, it's both loops together.

Private Sub CmdOpenTrainingRecord_Click()

    Dim xlApp As Excel.Application
    Dim xlWkb As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim i As Integer
    Dim p As Integer
    Dim SQLHonorary
    SQLHonorary = "SELECT TblMembers.Position, [FirstName] & "" "" & [LastName] AS FullName " & vbCrLf & _
        "FROM TblMembers " & vbCrLf & _
        "WHERE (((TblMembers.Position) Not Like (""*Chief"") And (TblMembers.Position) Not Like (""Capt *"") " & vbCrLf & _
        "And (TblMembers.Position) Not Like (""Secretary"") And (TblMembers.Position) Not Like (""Lt *"") And " & vbCrLf & _
        "(TblMembers.Position) Not Like (""Safety *"") And (TblMembers.Position)) AND ((TblMembers.Status)=""Honorary""))" & vbCrLf & _
        "ORDER BY TblMembers.LastName, TblMembers.FirstName"
    Set rsHonorary = CurrentDb.OpenRecordset(SQLHonorary, dbOpenSnapshot)
    Set xlApp = New Excel.Application
    Set xlWkb = xlApp.Workbooks.Open(CurrentProject.Path & "\Master\Training_Record.xlsx")
    Set xlWks = xlWkb.Sheets("Sheet1")
    xlApp.Visible = True
   [COLOR=rgb(44, 130, 201)] i = 1
    p = 16
    With xlWks
    Do Until i > 19
          .Range("AB" & p).Value = Nz(rsHonorary!FullName, "")
          i = i + 1
          p = p + 1
          rsHonorary.MoveNext
    Loop
    End With[/COLOR]
   [COLOR=rgb(226, 80, 65)] i = 1
    p = 16
    With xlWks
    Do Until i > 19
          .Range("AN" & p).Value = Nz(rsHonorary!FullName, "")
          i = i + 1
          p = p + 1
          rsHonorary.MoveNext
    Loop
    End With[/COLOR]
    rsHonorary.Close
    Set rsHonorary = Nothing
End Sub

 

[welshgasman]

So if the last record is going to be the 32nd, why are you counting to 19 in the second loop?
19 in first loop and 19 in the second loop is 38, when there are only 32 records?

Reread what I initially posted as to how I would do it. Post #8

 

[claven123]

I would get the error if the second Do Until was anything 33 or more. At this time the record set was 32, but could be a bit more or less. So, after a time I forgot to add the EOF part. So, I added Or rsHonorary.EOF which worked.
I wasn't sure how to get rest of the record set placed. I assume i=1 at the start and after the first loop i=19, so the next loop starts at the end of the first loop

I'm sorry, but post #8 wasn't as clear to me as it seems to you. However, it was a good bases to work off of to come to a solution, so thanks.

   i = 1
    p = 16
    With xlWks
    Do Until i > 19
          .Range("AB" & p).Value = Nz(rsHonorary!FullName, "")
          i = i + 1
          p = p + 1
          rsHonorary.MoveNext
    Loop
    End With
    p = 16
    With xlWks
    Do Until i > 50 Or rsHonorary.EOF
          .Range("AN" & p).Value = Nz(rsHonorary!FullName, "")
          i = i + 1
          p = p + 1
          rsHonorary.MoveNext
    Loop
    End With