DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Recordset help please via VBA6.3

  1. #1
    Join Date
    Apr 2007
    Posts
    8

    Recordset help please via VBA6.3

    Hello All:

    I need to Investigate how to write a Code in: Set rs = db.OpenRecordset
    ("Qry_Union_Trouble_Clock")

    I am suppose to take the select statement SQL Code (with inner Join/ TRIM, etc.) inside the paranthesis è dbOpenRecordset (“… ___…“) instead of a Query name.

    I have to rewrite this Code for being able to send 3 E-mails Reports (2wk/ 3wk/ TrblReport) such as this:


    Function Email2()

    Dim DistributionList As String

    '-- Instantiate database object
    Set db = CurrentDb

    '-- Open recordset
    Set rs = db.OpenRecordset("Qry_Union_Clock")
    rs.MoveFirst

    '-- Retrieve email list
    Do Until rs.EOF

    DistributionList = DistributionList & rs!ClockID & ","

    rs.MoveNext
    Loop

    If DistributionList = "" Then
    GoTo Exit_Email
    End If



    This way I won’t have to hold 3 separate queries just for the e-mail & instead can just recopy SQL and tweak when I have a new Report to e-mail automatically.

    I would greatly appreciate a modified Example (such as the first at the top of my message) with the SQL Code provided below. I tried, but it keeps giving me errors whenever I copy & paste the Code and break the lines up for easier read with Space & Underscore_: especially continuing the next original Query line starting “FROM…” – it keeps outputting in red as an error no matter how I try to put it together as an SQL. Is there something I am missing between “[BuyerCode] and FROM Qry_ReportTurnBack_2Wk…?


    SELECT DISTINCT [Qry_ReportTurnBack_2Wk].[SRCE], [tbl_Users].[ClockID], [tbl_Users].[Name], [tbl_Users].[BuyerCode]
    FROM Qry_ReportTurnBack_2Wk INNER JOIN tbl_Users ON Trim([Qry_ReportTurnBack_2Wk].[SRCE])=[tbl_Users].[BuyerCode];



    a. The Query basically would take the Buyer Codes of whomever this Query/ Report we’re sending to and matches them with the same TblUsers – instead of taking separate queries. Use also the same Record Count written above (it works okay), so it doesn’t give Error.

    Many thanks for your efforts in advance!

  2. #2
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    It sounds like this is all inside Access? Try creating the query in Access' query designer. Get it so it runs correctly. Then view the sql behind the query and use in your code.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  3. #3
    Join Date
    Apr 2007
    Posts
    8
    I already have the Query, which has the SQL I provided @ end' my Thread. However, I have diffuculty continuing "FROM..." of SQL. I keep getting an error when I try to do the space & _underscore. Any suggestions?

  4. #4
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Oh ok then try this...

    Code:
    Dim sSQL As String
    sSQL = "SELECT DISTINCT [Qry_ReportTurnBack_2Wk].[SRCE], "
    sSQL = sSQL & "[tbl_Users].[ClockID], [tbl_Users].[Name], [tbl_Users].[BuyerCode] "
    sSQL = sSQL & "FROM Qry_ReportTurnBack_2Wk INNER JOIN tbl_Users "
    sSQL = sSQL & "ON Trim([Qry_ReportTurnBack_2Wk].[SRCE])=[tbl_Users].[BuyerCode];"
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

Similar Threads

  1. Replies: 0
    Last Post: 11-13-2001, 12:34 PM
  2. Creating a Recordset
    By Scott in forum ASP.NET
    Replies: 0
    Last Post: 11-12-2001, 10:14 PM
  3. Requerying a reshaped recordset
    By Jonathan Gibbs in forum VB Classic
    Replies: 0
    Last Post: 08-09-2001, 02:34 PM
  4. Can't change field value in a recordset
    By Craig in forum VB Classic
    Replies: 5
    Last Post: 02-17-2001, 01:23 AM
  5. ?Build a Recordset from an Existing Recordset
    By Patrick in forum VB Classic
    Replies: 1
    Last Post: 10-11-2000, 11:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center