Opening a Dialog Form When Printing a Report
Before printing a report, you normally would want to enter report parameters in a dialog box such as the following:
The dialog form will popup when you open the Payroll Register report shown below:
To do this, we just need to put a code in the Open event of the report to open the dialog form as follows:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmReportCriteria", acNormal, , , _
acFormEdit, acDialog, "rptPayrollRegisterPerPeriod"
If Not IsLoaded("frmReportCriteria") Then
Cancel = True
End If
End SubIn the Close event of the report, we’ll need to close the form:
Private Sub Report_Close() Forms![frmReportCriteria].Visible = True DoCmd.Close acForm, "frmReportCriteria" End Sub
In the Print button of the form, we can enter the following code:
Private Sub btnPrint_Click() On Error GoTo Err_btnPrint_Click Dim strDocName As String Dim strFilter As String ' OpenArgs contains the report name strDocName = Me.OpenArgs If fmeForEmployee = 1 Then ' All employees strFilter = "" Else ' Or individual employees strFilter = "[EmployeeID] = " & EmployeeID & " AND " End If strFilter = strFilter & "[PayPeriodID] Between " _ & FromPayPeriodID & " And " & ToPayPeriodID Reports.Item(strDocName).Filter = strFilter Reports.Item(strDocName).FilterOn = True Me.Visible = False Exit_btnPrint_Click: Exit Sub Err_btnPrint_Click: MsgBox Err.Description Resume Exit_btnPrint_Click End Sub
The code gets the report name from the OpenArgs passed by the report when it opens the dialog form. It then set the Filter property of the report to show only the records we selected in the form.
Read More...
Convert Numbers into String with Ordinal Suffix
Labels:
Visual Basic
The strOrdinal function uses the Mod operator to get the last two digits of the number. For all values between 11 and 19, apply “th” as the suffix. Otherwise, it again uses Mod operator to get the last digit of the number and apply the following rules:
Here’s the code for the function that converts numbers into a string with ordinal suffix:
Read More...
The VBA code below allows you to represent a numeric value as its ordinal position in a set. The VBA function takes a numeric value and returns the number with the correct ordinal suffix as a string.
For example:
- strOrdinal(1)
- strOrdinal(42)
The strOrdinal function uses the Mod operator to get the last two digits of the number. For all values between 11 and 19, apply “th” as the suffix. Otherwise, it again uses Mod operator to get the last digit of the number and apply the following rules:
- Numbers that end in 1, use “st”
- Numbers that end in 2, use "nd"
- Numbers that end in 3, use "rd"
- Otherwise, use "th"
Here’s the code for the function that converts numbers into a string with ordinal suffix:
Public Function strOrdinal(lngVal As Long) As String
Dim intDigit As Integer
Dim intTeen As Integer
Dim strSuffix As String
' Check for the "teens" first
intTeen = lngVal Mod 100
If intTeen >= 11 And intTeen <= 19 Then
' if teens, use "th"
strSuffix = "th"
Else
' Get the last digit
intDigit = lngVal Mod 10
Select Case intDigit
Case 1
strSuffix = "st"
Case 2
strSuffix = "nd"
Case 3
strSuffix = "rd"
Case Else
strSuffix = "th"
End Select
End If
strOrdinal = lngVal & strSuffix
End Function
Read More...
Subscribe to:
Posts (Atom)
