CopyFromRecordset only returns first record

[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

 

[aardvick]

Correction! It only returns the LAST record.

 

[Tim Williams]

Try

rs.MoveFirst

before doing copyfromrecordset

Tim

 

[MikeR]

Try

rs.MoveFirst

before doing copyfromrecordset

Tim

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

 

[aardvick]

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

Try

rs.MoveFirst

before doing copyfromrecordset

Tim

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