Recordset help please via VBA6.3

[vik02]

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!

[RobDog888]

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.

[vik02]

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?

[RobDog888]

Oh ok then try this...

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];"