dao recordset to join

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Loop Thru Recordset Join My Tables and create one report base on

Loop Thru Recordset Join My Tables and create one report base on

resolvedResolved · Urgent Priority · Version 2010

Edited on Thu 17 Jul 2014, 02:04

Nil has attended:
No courses

Loop Thru Recordset Join My Tables and create one report base on

I have VBA in access to loop thru a recordset and join each row with my main table and create new worksheet.

I get run-time error 3296 JOIN expression not supported

can you experts look thru my code and help me OR if you even have better solution?

here is a sample of my db:
https://drive.google.com/file/d/0B980etBxqQuzTGxiS1g3eUlLcHc/edit?usp=sharing

Thank you.


Sub ExportReport()

Dim dbsReport As DAO.Database
Dim qdf As DAO.QueryDef
Dim rstSKSF As DAO.Recordset
Dim strSQL As String
Dim xlsxPath As String

On Error GoTo ErrorHandler

Set dbsReport = CurrentDb
xlsxPath = "I:\Proj\Tr_Rep " & Format(Now(), "mm-dd-yyyy hhmmss AMPM") & ".xlsx"


'Open a recordset on all records from the SkillSoft Request table that have
'a Null value in the ReportsTo field.
strSQL = "SELECT * FROM SKSF_Req WHERE Flag IS NULL"
Set rstSKSF = dbsReport.OpenRecordset(strSQL, dbOpenDynaset)

'If the recordset is empty, exit.
If rstSKSF.EOF Then Exit Sub

With rstSKSF
Do Until .EOF

'join report table with SKSF_request table's Rows
'Create newworksheet for each report joint with SKSF rows
Set qdf = dbsReport.CreateQueryDef("Training_Reportsheet", _
Set qdf = dbsReport.CreateQueryDef("Training_Reportsheet", _
"SELECT Report.Name, Report.[Employee Role], Report.[Employee Location]," & _
" Report.[Retails Region], Report.[Asset Title], Report.[Completion Date], " & _
" Report.[Completion Stat] " & _
"FROM Report LEFT JOIN SKSF_Req ON Report.[Asset Title] = '" & rstSKSF![Course Name] & "'" & _
" WHERE (Report.[Asset Title]) = '" & rstSKSF![Course Name] & "'" & _
" And '" & rstSKSF!Role & "' Like ' * ' & [Report].[Employee Role] & ' * ' " & _
" GROUP BY Report.Name, Report.[Employee Role], Report.[Employee Location], " & _
" Report.[Retails Region], Report.[Asset Title], Report.[Completion Date], " & _
" Report.[Completion Stat], Report.[EMP ID]")

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Training_Reportsheet", xlsxPath, True
DoCmd.DeleteObject acQuery, "Training_Reportsheet"


.Edit
rstSKSF![Flag] = "Y" 'Set Flag
.Update
.MoveNext

Loop
End With

rstSKSF.Close
dbsReport.Close

Set rstSKSF = Nothing
Set dbsReport = Nothing

Exit Sub
'ErrorHandler:
' MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

RE: Loop Thru Recordset Join My Tables and create one report bas

Hi Nil

Thanks for your post and sorry for the delay in getting back to you.

We can certainly have a look at your working code for you but it is worth clarifying that this sort of query takes us beyond the scope of the forum.

In these cases we look at your work and if we can identify a solution we will let you know scope, duration and associated costs.

If you would like to look into this further please do contact our enquiries team.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Loop Thru Recordset Join My Tables and create one report bas

I figured it out.
Thanks

RE: Loop Thru Recordset Join My Tables and create one report bas

Hi Nil,

I am happy to hear that find a solution.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Sun 27 Jul 2014: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Access tip:

Run with the total

Often when creating reports with lists of transactions it can be useful to have a column which totals the transactions as the report progresses.

Using the Sum function gives the total for the whole group, not a running total.



Access provides an easy method of achieving a running total.



Click on the text box containing the data you wish to contain the running total.
Select the Data tab
Click in the Running sum option
Click the down arrow to view three options

View all Access hints and tips


Server loaded in 0.11 secs.