Solved Selected fields from a table/query to generate Report and export to Excel & PDF in MS Access (1 Viewer)

mrk777

Member
Local time
Today, 18:14
Joined
Sep 1, 2020
Messages
60
Hi Team, Please help me build this up! I have a Master DB Table with about 34 fields and I have specific requirements filtering out the data and export the fields to Excel or PDF. I have built the queries with the requirements of the criteria. The requirement is to create a form with ListBox of all the field Headers from Master DB Table and whatever the fields I select, only those fields have to be opened in a table view, and then the user should be able to filter the values if needed from that table fields and click on Export button to Export to PDF or Excel.

I have created a form to get all the field header names.

1613561710348.png
 
Last edited:

Minty

AWF VIP
Local time
Today, 12:44
Joined
Jul 26, 2013
Messages
10,354

mrk777

Member
Local time
Today, 18:14
Joined
Sep 1, 2020
Messages
60
Hi @Minty I have tried the below code to get the things done, but I'm still stuck somewhere while opening the report with the selected headers.

Code:
Private Sub Command6_Click()
Dim LstBox As String
Dim CmbFunction As String
Dim CmbYear As Variant
Dim StrSql As String

'LstBox = Me.List7.Value
Dim selList As String
Dim i As Long

If LstBxSelected.ItemsSelected.Count = 0 Then
    MsgBox "No Fields Selected"
    Exit Sub
End If


For i = 0 To (LstBxSelected.ItemsSelected.Count - 1)
    If LstBxSelected.Selected(i) Then
        selList = selList & LstBxSelected.ItemsSelected.Item(i) & ";"
    End If
Next i


MsgBox selList

StrSql = "SELECT " & selList & " FROM Master_DataBase WHERE " & selList & "= True AND ((Nz([Forms]![Form1]![FilterbyFunction],(Master_DataBase.Function_ID)) AND ((Nz([Forms]![Form1]![FilterbyYear],Year((Master_DataBase.Project_Start_Date)))"

DoCmd.OpenForm "Report1", acNormal, , StrSql

End Sub

It is throwing Run-Time error 2102. It's not picking the form even though the form exists. Please Help. I can attach the file if required for reference.
 

Minty

AWF VIP
Local time
Today, 12:44
Joined
Jul 26, 2013
Messages
10,354
You can't open a Report with a SELECT Statement like that.
Personally, I would save your select query as a QueryDef, and use that to export to Excel using the TransferSpreadsheet method.

Otherwise, you will have to get into creating a dynamic report which is a whole other world of pain.

EDIT - I don't think your SELECT statement is formatted correctly either, I would add a debug.print StrSql and examine it before applying it anywhere.
 

mrk777

Member
Local time
Today, 18:14
Joined
Sep 1, 2020
Messages
60
@Minty I have tried checking the StrSql, it is throwing the positions of it like 2;3;4;5; etc., for the selected items. I'm very new to access and facing these issues. please help!!
 

Attachments

  • PPM.zip
    1.5 MB · Views: 399

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:44
Joined
May 21, 2018
Messages
8,463
Echoing everything @Minty said. You could create a querydef and export that as stated. But even if you select the fields to show in the report you would have to format the columns. This means you would have to first have all possible columns columns on the report. Then move, size, and show only the ones you want. Hide and move out of the way all non used. Not saying this cannot be done, because it can but the formatting will take a lot of code. I tried the latter and it works fine.
 

Isaac

Lifelong Learner
Local time
Today, 05:44
Joined
Mar 14, 2017
Messages
8,738
I agree. If the output is approved to be Excel, don't even hurt yourself on the Report object, which will make it many orders of magnitude more difficult to be dynamic.
 

Minty

AWF VIP
Local time
Today, 12:44
Joined
Jul 26, 2013
Messages
10,354
@Minty I have tried checking the StrSql, it is throwing the positions of it like 2;3;4;5; etc., for the selected items. I'm very new to access and facing these issues. please help!!

I can't login, although I can get around that - your VBA code is password locked, and although I can get around that as well, I'd rather not waste the time.

Unlock it all and remove the login form, and tell me where the issue is and I'll have a look later.
 

mrk777

Member
Local time
Today, 18:14
Joined
Sep 1, 2020
Messages
60
@Minty I'm extremely sorry and I was completely out while sending you the file. Here's is the revised file and the Login form does not have any restrictions. I have already bypassed it. Form1 is the one I'm looking for.
 

Attachments

  • PPM.zip
    1.5 MB · Views: 399

mrk777

Member
Local time
Today, 18:14
Joined
Sep 1, 2020
Messages
60
In Form 1, I have the ListBox with the list of Field Headers taken from Master_DataBase Table. And 2 other combo boxes for the Function and Year to be filtered while producing the results.
 

Minty

AWF VIP
Local time
Today, 12:44
Joined
Jul 26, 2013
Messages
10,354
Okay - there is quite a lot of work required here.
You aren't building your SELECT string even vaguely correctly, and your list box is only set to extend multiselect - I assume you would like to pick any of the fields?
 

mrk777

Member
Local time
Today, 18:14
Joined
Sep 1, 2020
Messages
60
Okay - there is quite a lot of work required here.
You aren't building your SELECT string even vaguely correctly, and your list box is only set to extend multiselect - I assume you would like to pick any of the fields?
Yes @Minty, I would like to pick the fields based on the type of report I require. If I would like to pick Completed Projects, I would select almost all the fields and If I would like to pick Project Documentation Status, I would pick a little lesser fields as compared. It is just based on user requirements.

Earlier I have built all the reports required and getting the results from the form called "Navigator_Form", but the requirement has been completely changed. Reports have to be generated with an option to export to Excel or PDF and only selected fields have to be produced.
 

Minty

AWF VIP
Local time
Today, 12:44
Joined
Jul 26, 2013
Messages
10,354
Like I said earlier - you aren't getting a dynamic report out of this - you'll get a excel output.

Please create a sample query in the Query editor for me and post up the SQL using the Form you have built already.
Just pick a few fixed fields as an example it doesn't matter which ones.
I am struggling to see how the forms "Filter By Function" will integrate into a query, and although I could guess at your logic I probably won't get it right.
 

mrk777

Member
Local time
Today, 18:14
Joined
Sep 1, 2020
Messages
60
Like I said earlier - you aren't getting a dynamic report out of this - you'll get a excel output.

Please create a sample query in the Query editor for me and post up the SQL using the Form you have built already.
Just pick a few fixed fields as an example it doesn't matter which ones.
I am struggling to see how the forms "Filter By Function" will integrate into a query, and although I could guess at your logic I probably won't get it right.
Code:
SELECT Master_DataBase.Function, Master_DataBase.Project_Lead, Master_DataBase.Project_Name, Master_DataBase.Project_Type, Master_DataBase.Certification, Master_DataBase.Manager_Name, Master_DataBase.Sponsor, Master_DataBase.Champion, Master_DataBase.Project_Start_Date, Master_DataBase.Proposed_Project_End_Date, Master_DataBase.Training_Completed, Master_DataBase.Cost_Included, Master_DataBase.Project_Approval, Master_DataBase.Cost_Approval, Master_DataBase.Dependencies, Master_DataBase.Foresight, Master_DataBase.Status_of_Project, Master_DataBase.Business_Impact, Master_DataBase.Customer_Impact, Master_DataBase.Process_Impact, Master_DataBase.SCORE, Master_DataBase.Activity_Frequency, Master_DataBase.Activity_Frequency_in_No_PerAnnum, Master_DataBase.Current_Activity_Time_InMins, Master_DataBase.Current_Activity_Time_InHrs_PerAnnum, Master_DataBase.Total_Development_Time_InHrs, Master_DataBase.ActivityTime_AfterDevelopment_InHrs_PerAnnum, Master_DataBase.Time_Savings_PerAnnum_InHours, Master_DataBase.Cost_Savings_PerAnnum, Master_DataBase.Test_Result, Master_DataBase.Final_Comments, Master_DataBase.Project_Charter_Status, Master_DataBase.Final_Project_Charter_Saved_in_MSTeams, Master_DataBase.Project_Historical_Received_Status, *
FROM Master_DataBase
GROUP BY Master_DataBase.Function, Master_DataBase.Project_Lead, Master_DataBase.Project_Name, Master_DataBase.Project_Type, Master_DataBase.Certification, Master_DataBase.Manager_Name, Master_DataBase.Sponsor, Master_DataBase.Champion, Master_DataBase.Project_Start_Date, Master_DataBase.Proposed_Project_End_Date, Master_DataBase.Training_Completed, Master_DataBase.Cost_Included, Master_DataBase.Project_Approval, Master_DataBase.Cost_Approval, Master_DataBase.Dependencies, Master_DataBase.Foresight, Master_DataBase.Status_of_Project, Master_DataBase.Business_Impact, Master_DataBase.Customer_Impact, Master_DataBase.Process_Impact, Master_DataBase.SCORE, Master_DataBase.Activity_Frequency, Master_DataBase.Activity_Frequency_in_No_PerAnnum, Master_DataBase.Current_Activity_Time_InMins, Master_DataBase.Current_Activity_Time_InHrs_PerAnnum, Master_DataBase.Total_Development_Time_InHrs, Master_DataBase.ActivityTime_AfterDevelopment_InHrs_PerAnnum, Master_DataBase.Time_Savings_PerAnnum_InHours, Master_DataBase.Cost_Savings_PerAnnum, Master_DataBase.Test_Result, Master_DataBase.Final_Comments, Master_DataBase.Project_Charter_Status, Master_DataBase.Final_Project_Charter_Saved_in_MSTeams, Master_DataBase.Project_Historical_Received_Status
HAVING (((Master_DataBase.Function)=Nz([Forms]![Navigator_Form]![FilterbyFunction],[Master_DataBase].[Function])) AND ((Master_DataBase.Status_of_Project)="Completed") AND ((Year([Master_DataBase].[Project_Start_Date]))=Nz([Forms]![Navigator_Form]![FilterbyYear],Year([Master_DataBase].[Project_Start_Date]))));

@Minty This is just an example to pick up the fields for Completed Projects. And If I want Pending Projects details, I might select only few fields to generate results. And you can check the queries for Completed Projects with the required fields and filtering the data based on Function and Year Combo boxes from the form "Navigator_Form".
 

Minty

AWF VIP
Local time
Today, 12:44
Joined
Jul 26, 2013
Messages
10,354
Okay - I sort of figured that out, but I was mystified by your use of the NZ function.
I can see that it simply ignores a null entry, but probably very inefficiently, I'll adjust the code to accommodate that option.

Also, that query is grouped - for no reason.
 

mrk777

Member
Local time
Today, 18:14
Joined
Sep 1, 2020
Messages
60
Okay - I sort of figured that out, but I was mystified by your use of the NZ function.
I can see that it simply ignores a null entry, but probably very inefficiently, I'll adjust the code to accommodate that option.

Also, that query is grouped - for no reason.
@Minty I have provided Nz function because, if the Combo Boxes are selected with some values, the records get filtered based on them and of the combo boxes are left empty, then all the records to be displayed without any filters based on Combo boxes
 
Last edited:

mrk777

Member
Local time
Today, 18:14
Joined
Sep 1, 2020
Messages
60
Okay - I sort of figured that out, but I was mystified by your use of the NZ function.
I can see that it simply ignores a null entry, but probably very inefficiently, I'll adjust the code to accommodate that option.

Also, that query is grouped - for no reason.
@Minty , Is there any update on the PPM file? Meanwhile, I have tried a bit on it. But I ended up getting some weird errors like Run-time error 3075 and etc., Thank you :)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:44
Joined
May 21, 2018
Messages
8,463
PM me if interested. Not sure if I want to post this code. I put this in most of my DBs.

This allows me to
1. Pick any query (I only want to show those preceded by VIEW_)
2. Pick and remove fields to include
3. Sort fields by moving up and down
4. Sort the data
5. Filter the data
6. Export to excel

It is pretty freaking powerful.
qryViewer.jpg
 
Last edited:

Users who are viewing this thread

Top Bottom