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

C

claven123

Board Regular
Joined
Sep 2, 2010
Messages
83
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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



VBA Code: 
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.
SQL: 
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
 
Sort by date Sort by votes
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.


VBA Code: 
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
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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.

VBA Code: 
   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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Access UNION query won't run, data limits with a small file and almost nothing in it???
2
Replies
13
Views
4K
xenou
xenou
C
  • Question
This is an Excel to Access porting issue - Is it possible to convert an Excel VBA FIND method to work correctly in and with Access's VBA ?
4 5 6
Replies
50
Views
1K
Trebor76
T
C
  • Question
compile error... method or data member not found
Replies
4
Views
1K
Norie
Norie
J
  • Question
Run the “ready” query in ACCESS from VBA Excel with the parameter
Replies
1
Views
2K
stumac
S
C
  • Question
export fields to excel from access
Replies
4
Views
660
Joe4
Joe4

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top