help with copying to excel

[sambucaman]

Hi everyone. This is my first post on here, so before I begin, hello, and thanks in advance!

I am struggling with some VBA, which is sooooo nearly working, and I'd be really grateful of some help.

I have a form, which displays a single customers information; Name, Address, Post Code, order history etc etc. I'm trying to run some VBA that will pull the name and address info, and copy it into a xls document. The code I am using is shown below.

Private Sub invoice_Click()
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean


blnEXCEL = False
blnHeaderRow = False


On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\users\admin\desktop\invoice.xls")
Set xls = xlw.Worksheets("Invoice")
Set xlc = xls.Range("E2")
Set dbs = CurrentDb()




Set rst = dbs.OpenRecordset("Customers", dbOpenDynaset, dbReadOnly)


If rst.EOF = False And rst.BOF = False Then
      rst.MoveFirst


      If blnHeaderRow = True Then
            For lngColumn = 0 To rst.Fields.Count - 1
                  xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
            Next lngColumn
            Set xlc = xlc.Offset(1, 0)
      End If




      Do While rst.EOF = False
            For lngColumn = 0 To rst.Fields.Count - 1
                  xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
            Next lngColumn
            rst.MoveNext
            Set xlc = xlc.Offset(1, 0)
      Loop
End If


rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
    
End Sub

Now, this works in as much as it opens the XLS, copies and pastes the info. BUT it copies ALL my customer records. How could I re-gig it so that It pulls the data from the loaded form, and not the table?

I have tried this

Private Sub Command11_Click()


Dim DataObj As New MSForms.DataObject
    Dim S As String
    S = Me.Customer.Name
    DataObj.SetText S
    DataObj.PutInClipboard


End Sub

which will get loaded form data, but I cant seem to combine the two.



Any help on the above would be really gratefully received.
Many thanks in advance, Andy

[pbaldy]

You can change this:

Set rst = dbs.OpenRecordset("Customers", dbOpenDynaset, dbReadOnly)

to

Dim strSQL As String
strSQL = "SELECT * FROM Customers WHERE CustomerID = " & Me.CustomerID
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

changing the name of the key field as appropriate. That presumes the field is numeric.