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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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