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

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

Try

rs.MoveFirst

before doing copyfromrecordset

Tim

"aardvick" <(E-Mail Removed)> wrote in message
newsDEA6CB4-2E80-4B0F-971B-(E-Mail Removed)...
> 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

Tim Williams wrote:
> Try
>
> rs.MoveFirst
>
> before doing copyfromrecordset
>
> Tim
>
> "aardvick" <(E-Mail Removed)> wrote in message
> newsDEA6CB4-2E80-4B0F-971B-(E-Mail Removed)...
>> 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

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

"Tim Williams" wrote:

> Try
>
> rs.MoveFirst
>
> before doing copyfromrecordset
>
> Tim
>
> "aardvick" <(E-Mail Removed)> wrote in message
> newsDEA6CB4-2E80-4B0F-971B-(E-Mail Removed)...
> > 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
>
>
>