WithEvents and Report Line Hiding

WithEvents and Report Line Hiding

Introduction

If you have tried out last week’s Report OnPrint() Event trapping in Access Class Module then it will be easy for you here.  What we did there was Report’s Detail Section Print  Event, when triggered, is captured in Class Module, validated the Field value and highlighted the Text Box and value of Exam passed students.  We are going to do something similar here too.

If we only need to highlight Text Boxes on the Report  with some color, change font-style Bold, Italic or Underline then we don’t need the Print Event capturing in Class Module to do it through VBA Code. 

The Conditional Formatting method will do the highlighting of text, with different Color, Font Bold or Italic or Underline.  It only needs an expression like the following in conditional formatting dialogue box:  

Expression is [Total]/[MaxMarks]*100 >= [Percentage]

But, we can do many other things, which are not available in conditional formatting,  like changing the font or font size and highlighting border of the Text Box or any other styling through the Event handling in Class Module.

Last week we captured the Detail Section OnPrint() Event, but now we will try out the onFormat() built-in Event to hide certain report lines, leaving only the required ones (either passed or failed students list) on the Report.

We will use the same Report of last week, to prepare Passed or Failed Students’ List from the same Report.

Sample Images of Report View, Print Previews

1.  Full list of Students in Report View (not in Print Preview) is given below.  Remember, on the Report View the Formatting and Printing actions takes place but those built-in Events are not fired on the Report View.  They are triggered only on the Print Preview mode.

2.  Passed Students’ List in Print Preview, is achieved by preventing the failed students report lines from appearing on the Report.

3.  Failed Students’ List in Print Preview and Passed Students report lines will not appear on the Report.

The Report option 2 and 3 are prepared without applying any filtering condition directly on the source data,  but showing or hiding the report lines in the Detail Section’s Format Event does the job.

Class Module: ClsStudentsList VBA Code

Private WithEvents Rpt As Access.Report
Private WithEvents secRpt As Access.[_SectionInReport]

Private txt As Access.TextBox
Private max As Access.TextBox
Private pct As Access.TextBox
Private i As Integer

Public Property Get mRpt() As Access.Report
Set mRpt = Rpt
End Property

Public Property Set mRpt(RptNewVal As Access.Report)
Dim msg As String
Const strEvent = "[Event Procedure]"

Set Rpt = RptNewVal

With Rpt
Set secRpt = .Section(acDetail)
secRpt.OnFormat = strEvent
End With

msg = "1. Passed List" & vbCr & "2. Failed List"
i = 0
Do While i < 1 Or i > 2
i = Val(InputBox(msg, "Report Options", 1))
Loop

Set txt = Rpt.Controls("Total")
Set max = Rpt.Controls("MaxMarks")
Set pct = Rpt.Controls("Percentage")
End Property

Private Sub secRpt_Format(Cancel As Integer, FormatCount As Integer)
Dim curval As Double
Dim m_Max As Double
Dim m_pass As Double
Dim mk As Double
Dim pf As Double
Dim yn As Boolean
Dim lbl As Access.Label

On Error GoTo secRpt_Print_Err

m_Max = max.Value
pp = pct.Value
curval = txt.Value

pf = curval / m_Max * 100

yn = (pf >= pp)

Set lbl = Rpt.Controls("lblpass")

secRpt.Visible = False

If yn Then
txt.FontBold = True
txt.FontSize = 12
txt.BorderStyle = 1
lbl.Caption = "Passed"
lbl.ForeColor = RGB(0, FF, 0)
lbl.FontBold = True
If i = 1 Then
secRpt.Visible = True
End If
Else
txt.FontBold = False
txt.FontSize = 9
txt.BorderStyle = 0
lbl.Caption = "Failed"
lbl.FontBold = False
If i = 2 Then
secRpt.Visible = True
End If
End If

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print()"
Resume secRpt_Print_Exit
End Sub

In the global declaration area of the Class Module the Access.Report Property Rpt and Report Section Property secRpt are declared.

The three Text Box Control Properties txt, max and pct  are declared and will be assigned with references of the Text Box controls on the Report.  After that we can read Students Marks (txt), the Maximum Marks (max – of all Subjects) and Percentage (pct) of Pass Marks to calculate and categorize the Students as Passed or Failed.

The Integer Type Property i will hold the Report Option 1 or 2 (i.e. 1. Passed Students List. 2. Failed Students List.) entered by the User when the Report is Run.

The Property Set mRpt() Procedure assigns the current Report Object through the Report_Load() Event Procedure and assigns the RptNewVal Parameter value to the Rpt Object.

Immediately after assigning the Report to Rpt Object the Report Detail Section is assigned to the secRpt Object.  The Report Detail Section Format Event triggering mechanism is also enabled within the following Code segment in the Set mRpt() Property Procedure.

With Rpt
Set secRpt = .Section(acDetail)
secRpt.OnFormat = strEvent
End With

Why we can’t use last week’s OnPrint() Event here, to hide the required Report lines, because Print Event is the final phase of other actions like Formatting phase. Once the Formatting phase is complete and Printing starts we cannot hide individual report lines.  If the statement secRpt.Visible = False is executed at this point. The entire Detail Section will not be visible.

Report Events before Viewing/Printing.

  1. In the first pass of Report Formatting the Format Event fires and determines which data belongs to the Report Page.  This is where our programmed activities takes place.
  2. In the second formatting pass the Report lines are formatted for printing/previewing.  
  3. The Print Event fires immediately after the second formatting pass but before the Report lines are printed on Detail Section.

Within the Do while . . . Loop we have inserted an Inputbox() statement to get the user response (1 or 2) for report option: 1. Passed Students List, 2. Failed Students List.

The User is forced to enter the value 1 or 2. Any value outside this range is not accepted and the Do while . . . Loop keep repeating itself till a valid value is received in the Variable i.

Next three statement assigns the Text Box controls to their respective declared Properties.

When the Report is open in the Print Preview Mode the Detail Section Format Event is fired and getting captured in the secRpt_Format() sub-routine in the Class Module: ClsStudentLine. Few local variables are declared at the beginning of the sub-routine.

The Maximum Marks and Pass Percentage Text Box values are read into max and pct. The curval = txt.value statement reads the students obtained Total marks and assigns it to the Variable curval.

In the next line the statement pf = curval / m_Max * 100 calculates the percentage of marks obtained by the student.

yn = (pf >= pp) – compares the obtained percentage of marks with the Pass Percentage and checks whether the obtained percentage is greater than or equal to Pass Percentage, if so the result in Variable yn = TRUE (Passed) otherwise yn = FALSE (Failed).

The lbl Control is set with the Label control reference, of the Label to the right of the Total Text Box control.

Next Line the Report Detail Section is first kept in hidden state. When the Student is found in Passed category (yn = TRUE) then the Total TextBox is formatted to highlight it and the Label Control’s Caption is set with the text Passed. If yn = FALSE then the Formatting is reset to normal and the label control is set with the text Failed, depends on what type of Report you have opted for.

Based on the Report Option selected by the user in response to the InputBox() Function.

The statement i=Inputbox() within the Do While . . . Loop  gives the user two choices to select: 1.  Passed Students List, 2.  Failed Students List.

How it works.

Option 1: If the student, in the current line on Detail Section is found passed the Exam then the Detail Section is made visible.  That particular report line appears on the Report.  This check is done on each line of Report and passed students only are made visible on the Report. 

Option 2: Detail Section is made visible for Failed student data lines, hiding others.

Report Class Module Code.

The Report Class Module Code is given below:

Option Compare Database
Option Explicit

Private R As New ClsStudentsList

Private Sub Report_Open(Cancel As Integer)
Set R.mRpt = Me
End Sub

On the Report Code Module the Class Module ClsStudentList  is instantiated in Object R.  In the Form_Load() Event Procedure the current Report Object is passed to the Class Object’s R.mRpt() Set Property Procedure. These are the only Code required on the Report’s Class Module.

All the other actions are happening behind the curtain in the Class Module ClsStudentsList.

Note: Always open the Report in Print Preview Mode (not in Report View) to trigger the Format Event in the Detail Section of the report.

You may Download the Demo Database from the Link given below and try out the Report and Code.

  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
  8. WithEvents in Class Module and Data Entry
  9. WithEvents and Access Report Event Sink

Posted on

Having 2 users use 2 separate forms in the same DB?

So I have a database and 2 forms. 1 for logging tickets, 1 for closing. I know how to save it so that it opens as a form (Options). BUT, is there any way so that if I open say file X, I get form 1 and if I open file Y, I get form 2?

Or do they need to be the very same file? I know this could be solved with a login, but my skills are not quite there yet lol.

submitted by /u/Principfist
[link] [comments]

Posted on

Bypassing the UI for a MS access database

I use an old access database for work. Part my job involves data entry (sometimes huge volumes) of numerical values. It is a construction company tool. Essentially, I am typing in areas of floors and windows and walls etc. This is incredibly tedious as the database interface only allows you enter so many number values at one time and each requires the use of an obnoxious drop down menu.

My question:

Is there anyway I can bypass the user interface and plug the values straight into the tables myself? or use the code and build a new user interface that is less miserable? or any other possible solution?

I did not design the database or the UI but I have been able to get into the code by removing the password but I don’t know how to go about making it more user friendly.

P.s If I have not included the enough relevant information for you to help me, please let me know what I am missing.

Any help would be hugely appreciated, data entry is mind numbing torture.

submitted by /u/OwenyD
[link] [comments]

Posted on

Converting a Table to Excel List – Have I Screwed Up?

I have staff who are employed to perform their duties in various specific locations each day. I created a table to record their arrival and departure times at each location – essentially the following:

  1. date column
  2. staff member’s name
  3. location 1
  4. location 1 arrival time
  5. location 1 departure time

I repeated the location columns for a further 5 locations so we can record up to 6 location visits per day.

Our admin is updating the table via a form – the form records the details for all 6 locations against the name of the staff member for 1 day.

It all works fine as a record of the day’s activities, but I have hit a stumbling block when I want to analyse in Excel – how to convert a multi column table with 2 fixed values (date and name of staff) to a list. Is there a simple way to do this? Thanks!

submitted by /u/spoonfed99
[link] [comments]

Posted on

[Can Access help?] Availability of Functions based on Systems Availability made of Components Availability (with Logic and "1 out of 2", "2 out of 4" kind of dependencies)?

It is basically Logical relationships.
For instance: Function_A is available if System_A OR System_B is Available; while System_A is Available if ComponentA_1 AND ComponentA_2 AND (2 out of 3 ComponentA_3, ComponentA_4, ComponentA_5) OR …

And a System or Component could depend on one or more other Systems too, and Component Availability could also depend on Component, etc.

Once the relationships are built, I would need to run some “scenarios”: what if this Component_X is unavailable, how does that trickle down to the rest? Ideally interrogative scenarios: what do I need to loose to loose the Function?

I’ve managed an ugly Excel example but quickly reached Excel limits.

Sorry if it’s a “duh, of course yes/no!” kind of question.

submitted by /u/233C
[link] [comments]

Get the best Microsoft Access conultant

Get the best Microsoft Access programmer

Posted on

How To Fix “Access Database Cannot Be Opened Because The Vba Project Contained In It Cannot Be Read” Error?

Find out more about >> Oakland CA Microsoft Access programmer services

Encountering error message “The database cannot be opened because the VBA project contained in it cannot be read” at the time of compiling the Microsoft Access ACCDE, MDE, or ADE file or trying to open these access database files on…

[Read More …]

Find out more if you need a Microsoft Access programmer near me in Oakland CA

Posted on

Bulk Rename Tables?

Is there a way to bulk rename common strings in Access table names?

Here is what I’m doing:

I do a monthly system audit where I use Access to query/compare imported XLS documents from my various systems. Each month I use a consistent naming convention starting with [YYYY-MM] of the month that the raw data is from. I’m tinkering with my process to make it so that I don’t have to tweak my queries each month that are looking at the [YYYY-MM] and instead using [Current] and [Last].

My theory being that if I can use Current and Last in the comparison queries, I won’t have to go through them all each month and saving myself a lot of time. As I’m building a proof of concept from my previous month’s audit and manually renaming the [YYYY-MM] to Last/Current in table names, I’m thinking that even though I can live with doing this each month, if there was a way that I could just bulk change all of the table names that start with [Last] to [Current] it would again save me lots of time.

Aside from doing this monthly audit, I really haven’t done much development work in access in years which is why I’m asking if there may be a way to do this. Thanks!

Edit: I’m also finding that changing the table names automatically updates the table’s name in the associated queries, is there a way to prevent this from happening? It is making it so that if I rename [Current] to [Last] that I’m still having to go into the query to rename it, it does not “break” the query until I import a new [Current] table that I was expecting it to do.

submitted by /u/beerdini
[link] [comments]

Posted on

PSA: KB4484119 may break your Access Application

KB4484119 broke our Access application. Upon launching our accde we would get “Query “query name” is corrupt.”.

I had to uninstall the KB on affected systems and decline it in WSUS.

Apparently a new patch to fix this will likely be released Dec 10:

Here’s more info: https://support.office.com/en-us/article/Fixes-or-workarounds-for-recent-issues-in-Access-54962069-14f4-4474-823a-ff7e5974a570

submitted by /u/Mindflux
[link] [comments]

Posted on

A confusing question about rulers

A confusing question about rulers

“In Design view, ensure that the left edge of the field is at the 8.5-inch mark on the ruler, and then return to Layout view. “

I do not know what the left edge of the field is or how to change the ruler. If I could be pointed in the right direction please, I can likely solve this on my own afterwards. Any help is appreciated.

https://preview.redd.it/kxrzjmo7sjy31.png?width=1920&format=png&auto=webp&s=7b44b3b0b7dae0bff0da94ac1cac186406fb3fbe

submitted by /u/ScriptGenius12
[link] [comments]

Posted on

Writing to database with Form from another database?

Access Newbie, I’d just “google” this, but I’m not really sure how to frame the search/question

The situation is I want users to be able to write to my access database with a form, but don’t want them actually in the database. I figure this a common way to interact with a database, no? You want people to write or access the data, but not inside tampering with the data.

How do I create an interface to write to an MS database like an Access form, but that is separate from the database it is writing to? Thank you!

submitted by /u/nyct0phile
[link] [comments]

Posted on