Correction! It only returns the LAST record.

"aardvick" wrote:

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