example vba code to open recordset (1 Viewer)

[Luchi]

Hi there,

I have been trying to solve a "problem" and found the following answer on an other Forum to exactly my question / problem.
But I would really need an example of such a vba code to open a recordset example code... Does anyone have something similar handy? Ideal would also be with the sendmail comand with the Excel export of the concerned records too

Answer that I found:
"You need a query based on the table which will give you all the product numbers. Opening the table or a form with a macro won't work for this.
If I understand correctly, you need to export the details for each product and to have each product's details export separately.
You can create the query that will give the needed info about the product ready to export (first query).
Create another query that lists all the product numbers that you want to use, one at a time, in the first query above (this will be the second query).
You don't mention if you are comfortable with writing vba and why you specifically need a macro. If you need help with the vba, post back.
You will need some vba code to open a recordset on the second query, grab the product number and feed it into the first query, then do your export.
With the recordset open on the second query, move to the next product number and repeat the process until you have exported info for each product.

 

[George Moore]

Hi Luchi

I had a brief look back at your other post today, so before I go any further, is my understanding of your problem correct ?

a) You want open a recordset in Access
b) Loop through the entries
c) Send data to Excel (providing there are records)
d) Email the Excel spreadsheet

 

[Luchi]

Hi Luchi

I had a brief look back at your other post today, so before I go any further, is my understanding of your problem correct ?

a) You want open a recordset in Access
b) Loop through the entries
c) Send data to Excel (providing there are records)
d) Email the Excel spreadsheet

correct and in layman's terms, I want: From a data set, send all records that concern a spesific responsible to that person.
Basically I want to have a way to run a Macro or VBA that will open up a table/query that has a list of information that I want to run a Query on. Once I get the table/query, I want to pass to the query the responsible from the open table/query and have the query complete just for that one responsible, and then I want to export the finding using the Macro or VBA also.
Once that information for that spesific responsible has been exported, I want to have the Macro skip to the next set of records and do the same thing with all the records (responsibles found) until it reaches no more records (responsibles) in the table/query.

 

[George Moore]

OK...I will send you some code tomorrow if no one else responds in the mean time

 

[pbaldy]

This might get you started in the meantime:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[George Moore]

Luchi - This is a skeleton to which you need to add flesh

It is written using Access 2010 and I have tried to explain the code as much as possible

Sub Test()
''''''''''''''''''''''''''''''''''''''
' a) This assumes outlook is your email client
'
' b) In Tools/References you may have to tick Microsoft Excel nn.0 object library
'
' c) You need to create two queries :-
'
' Query1 needs to contain details of your responsible people and their email addresses etc.
'
' Query2 will be blank
'
' You can then run this code from a macro when required
''''''''''''''''''''''''''''''''''''''
    On Error GoTo oops 'oops something went wrong
 
    Dim rs1, rs2 As Recordset ' recordset 1 is the main loop of your responsible people, recordset 2 is their order details

    Dim XL, WB, WS, OL, EM As Object ' Excel, Workbook, Worksheet, Outlook, Email

    Dim ExportFileName As String ' filename to ship data to Excel
    
    Set OL = CreateObject("Outlook.Application") 'open your email client
    
    Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Query1") ' details of responsible people

''''''''''''''''''''''''''''''''''''''
' Start of Loop through the main recordset of your responsible people(query1)
''''''''''''''''''''''''''''''''''''''
    Do Until rs1.EOF
    
''''''''''''''''''''''''''''''''''''''
' Prime query2 with Orders for this specific responsible person
''''''''''''''''''''''''''''''''''''''
    
    CurrentDb.QueryDefs("Query2").SQL = "SELECT * FROM [Your Order Table] WHERE ResponsiblePersonEmail = '" & rs1!ResponsiblePersonEmail  & '"
    
    Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM Query2") ' Open Order details and check for records
    
''''''''''''''''''''''''''''''''''''''
' If there are records then send them to Excel
''''''''''''''''''''''''''''''''''''''
    
    If rs2.RecordCount >= 1 Then
    
''''''''''''''''''''''''''''''''''''''
' Create Export FileName & transfer
''''''''''''''''''''''''''''''''''''''
        
    ExportFileName = CurrentProject.Path & "\Export " & Format(Now, "ddmmyy hhmmss") & ".xlsx"
        
    DoCmd.TransferSpreadsheet acExport, , "Query2", ExportFileName
    
''''''''''''''''''''''''''''''''''''''
' Allocate object variables
''''''''''''''''''''''''''''''''''''''
    
    Set WB = GetObject(ExportFileName)
    Set WS = WB.Worksheets(1)
    Set XL = WB.Application
    
''''''''''''''''''''''''''''''''''''''
' Make sure Excel is visible and the Workbook window as they sometimes open hidden
''''''''''''''''''''''''''''''''''''''
        
    XL.Visible = True
    
    XL.Windows(WB.Name).Visible = True
            
''''''''''''''''''''''''''''''''''''''
' Format Worksheet as required this is just an example
''''''''''''''''''''''''''''''''''''''
    
   With WS
        .Cells.Font.Name = "Calibri"
        .Cells.Font.Size = 10
        .Range("A:B,F:H,J:J,L:M").ColumnWidth = 6
        .Range("E:E,K:K").ColumnWidth = 12
        .Range("C:C,I:I,N:N").ColumnWidth = 26
        .Range("D:D").ColumnWidth = 28
        .Range("B:B,H:H,L:L").NumberFormat = "hh:mm"
        .Range("A:A,C:G,I:K,M:N").NumberFormat = "@"
        .Range("A1:N1").Interior.ColorIndex = 20
        .Range("A1:N1").AutoFilter
        .Range("1:1").RowHeight = 26
        .Name = "Orders"
    End With
    
''''''''''''''''''''''''''''''''''''''
' Save Workbook
''''''''''''''''''''''''''''''''''''''
  
    XL.DisplayAlerts = False
    WB.SaveAs CurrentProject.Path & "\" & rs1!ResponsiblePersonName & ".xlsx"
    XL.DisplayAlerts = True

''''''''''''''''''''''''''''''''''''''
' Email Workbook
''''''''''''''''''''''''''''''''''''''
    Set EM = OL.createitem(0)
    
    With EM
        .to = rs1!ResponsiblePersonEmail
        .CC = ""
        .BCC = ""
        .Subject = "Order List"
        .Body = "These Are Your Orders"
        .Attachments.Add WB.FullName
        .display   'or use .send to automatically send without reviewing first
    End With
    
''''''''''''''''''''''''''''''''''''''
' Close Workbook and delete temp export file
''''''''''''''''''''''''''''''''''''''
     
   WB.Close
     
   Kill ExportFileName ' kill temporary file
   
''''''''''''''''''''''''''''''''''''''
' end If there are records send them to Excel
''''''''''''''''''''''''''''''''''''''
    
    End If
    
    rs2.Close ' close recorset of this persons orders
    
''''''''''''''''''''''''''''''''''''''
' End of loop through the responsible person recordset
''''''''''''''''''''''''''''''''''''''
    
    rs1.MoveNext
    
    Loop
    
''''''''''''''''''''''''''''''''''''''
' House Keep
''''''''''''''''''''''''''''''''''''''
    
    rs1.Close
    
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set XL = Nothing
    Set WB = Nothing
    Set WS = Nothing
    Set OL = Nothing
    Set EM = Nothing
    
    Exit Sub ' everything OK

oops: 'something has gone wrong

    MsgBox Error$

End Sub

 

[isladogs]

Hi George

I've not read your code in detail but are you aware that when you do this

 Dim XL, WB, WS, OL, EM As Object

The result is that only EM is defined as an object.
The rest become variants

 

[George Moore]

Thanks Colin - I usually list variables vertically but thought I'd try and save some space given the verbosity of the code

 

[pbaldy]

You can do it, you just have to specify each:

Dim XL As Object, WB As Object, WS As Object, OL As Object, EM As Object

 

[isladogs]

I also used to do it like that to save space until someone pointed this out to me ...
Now I do it properly as in pbaldy's post...

 

[Luchi]

Thank you ever so much all!!! Very helpfull!
I'll get working on it today... not sure I'll grasp it all as I'm really new to VBA, but I'm eager to learn it

I'll inform on here how it all went.