WHERE pri key AND some other non-null field

[knownote]

The below code works fine in the main form (frmClients) to send the current record to Excel. But in the subform
(frmContacts), it will only send the first record even if a second or third record is current.

This is the only line I changed from the main form.
Set rs = db.OpenRecordset("Select * FROM tblContacts WHERE Subgect='" & Nz(Me![Subgect], "") & "'")

With the master-child link, tblContacts.Subgect is automatically filled when any other subform field is typed or pasted, creating a subform record. tblClients.Subgect is the only required field in main form; no fields are required in subform.
tblClients.Subgect --> tblContacts.Subgect (1 to many Relationship)

Can some other non-null field be added to WHERE (Subgect AND some other non-null field)?

tblClients.Subgect is text primary key for main form;
tblContacts.ContactID is AutoNumber primary key for subform (not visible on subform);
tblContacts.CustomerID is Number (not visible on subform);
tblContacts.Subtopic is text (often used, but not if redundant (name, phone, email for example)).

I can send the current main and subform records to Word or the clipboard, but can't adapt the code differences to Excel.

Thanks.

John

----Code

Private Sub cmdExcelSend_Click()
'Get recordset from Client's table
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * FROM tblContacts WHERE Subgect='" & Nz(Me![Subgect], "") & "'")

' credit "vbslammer" - Open Excel worksheet from Access

On Error Resume Next

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim sht As Excel.Worksheet

Set xl = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xl = CreateObject("Excel.Application")
'Else: Set xl = New Excel.Application 'instantiate Excel
End If

xl.Visible = True

' use Excel's built-in open dialog

xl.Dialogs(xlDialogOpen).Show "C:\*.xls"
Set wb = xl.ActiveWorkbook

' put appropriate sheet name/index here.

Set sht = wb.Sheets(1)

'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
sht.Cells(1, i).Value = rs.Fields(i - 1).Name
Next

'Add the data starting at cell A2
sht.Range("A2").CopyFromRecordset rs

'Format the header row as bold and autofit the columns
With sht.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With
'Close the Database and Recordset
rs.Close
db.Close

End Sub

 

[JoeAtWork]

Well, I've never used the CopyFromRecordset method, but I did find this on MSDN

When this method copies the recordset to the worksheet, the results will be truncated if you do not specify a range that is large enough to hold the contents of the recordset.

So I'm wondering if your range needs to have at least as many rows as the recordset, e.g. if recordset has three records then:

sht.Range("A2:A4").CopyFromRecordset rs

The above is assuming you only have one field in the recordset.

But the first thing I would do is confirm how many records are in the recordset. Then you know if the problem is with the SQL syntax or the CopyFromRecordset method.

 

[knownote]

Thanks, but still same result of one (wrong) record.