CopyFromRecordset only returns first record

A

aardvick

Below is my code. The message boxes confirm that the query looks good and
returns 4 records. But only the first record is copied onto the worksheet.
What am I doing wrong?
tia,
Aardvick

Sub test()
Dim db As DAO.Database, rs As DAO.Recordset, ws As Worksheet
Dim lName As String, hits As Integer

lName = "Anderson"
strQry = "SELECT * FROM [MyTable] WHERE [Last Name] = """ & lName & """"
MsgBox "Query looks like: " & strQry

Set db = OpenDatabase("C:\myDB.mdb")
Set rs = db.OpenRecordset(strQry, dbOpenDynaset)

rs.MoveLast
hits = rs.RecordCount
MsgBox "Number of records returned =" & hits

If hits > 0 Then
Set ws = Worksheets.Add
Count = rs.Fields.Count
For I = 0 To Count - 1
ws.Cells(1, I + 1).Value = rs.Fields(I).Name
Next
ws.Range("A2").CopyFromRecordset rs
End If
End Sub
 
M

MikeR

Tim said:
Try

rs.MoveFirst

before doing copyfromrecordset

Tim

aardvick said:
Below is my code. The message boxes confirm that the query looks good and
returns 4 records. But only the first record is copied onto the
worksheet.
What am I doing wrong?
tia,
Aardvick

Sub test()
Dim db As DAO.Database, rs As DAO.Recordset, ws As Worksheet
Dim lName As String, hits As Integer

lName = "Anderson"
strQry = "SELECT * FROM [MyTable] WHERE [Last Name] = """ & lName & """"
MsgBox "Query looks like: " & strQry

Set db = OpenDatabase("C:\myDB.mdb")
Set rs = db.OpenRecordset(strQry, dbOpenDynaset)

rs.MoveLast
hits = rs.RecordCount
MsgBox "Number of records returned =" & hits

If hits > 0 Then
Set ws = Worksheets.Add
Count = rs.Fields.Count
For I = 0 To Count - 1
ws.Cells(1, I + 1).Value = rs.Fields(I).Name rs.Movenext
Next
ws.Range("A2").CopyFromRecordset rs
End If
End Sub
What Tim said plus rs.MoveNext

Mike
 
A

aardvick

Thanks a million! I knew it must be something obvious : )

Tim Williams said:
Try

rs.MoveFirst

before doing copyfromrecordset

Tim

aardvick said:
Below is my code. The message boxes confirm that the query looks good and
returns 4 records. But only the first record is copied onto the
worksheet.
What am I doing wrong?
tia,
Aardvick

Sub test()
Dim db As DAO.Database, rs As DAO.Recordset, ws As Worksheet
Dim lName As String, hits As Integer

lName = "Anderson"
strQry = "SELECT * FROM [MyTable] WHERE [Last Name] = """ & lName & """"
MsgBox "Query looks like: " & strQry

Set db = OpenDatabase("C:\myDB.mdb")
Set rs = db.OpenRecordset(strQry, dbOpenDynaset)

rs.MoveLast
hits = rs.RecordCount
MsgBox "Number of records returned =" & hits

If hits > 0 Then
Set ws = Worksheets.Add
Count = rs.Fields.Count
For I = 0 To Count - 1
ws.Cells(1, I + 1).Value = rs.Fields(I).Name
Next
ws.Range("A2").CopyFromRecordset rs
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top