Gather Information in a Dialog Box Callahan Chapter 8 The Subscription Database n n Chapters 8 9 11 Subscribers table and Payments table with a 1 Many relationship defined and Referential Integrity enforced 1 Using Dialog Boxes in Your Applications n Earlier chapters used MsgBox to communicate with user q effective but limited functionality n n a message up to 3 command buttons no other control types no processing Custom dialog boxes can q q q use more than 3 command buttons use other control types eg text box combo box option group perform processing appropriate for values the user input in the dialog Dialog Box n n An unbound modal form q Unbound q Modal Custom dialog boxes are useful for q q q presenting a more flexible customized message receiving user input before opening a form or report giving user control of application options 2 Creating a Dialog Box to Goto a Specific Record Intended to work in conjunction with Subscribers form n q Subscribers form must be open or get an error n could add code in GoToRecord form s Open event to check whether the Subscribers form is open If not we could q cancel GoToRecord form s Open event to prevent it from opening then present a message q use DoCmd to open the Subscribers form List box to display fields from the Subscribers table n q q q used List Box wizard to define contents to display includes a hidden SubscriberID column modified list box s RowSource query to sort by Last Name First Name Creating a Dialog Box to Goto a Specific Record n Show Record button q why set its Default property to Yes q why set its Enabled property to No n Add Code to Enable the Show Record button Private Sub List0 AfterUpdate once a record is chosen in the list enable ShowRecord button ShowRecord Enabled True End Sub 3 Creating a Dialog Box to Goto a Specific Record n Examine the code that makes the Subscribers form display the desired record Private Sub ShowRecord Click Dim rst As Recordset store the recordset for the Subscribers form Set rst Forms Subscribers RecordsetClone locate the record for the selected subscriber rst FindFirst SubscriberID List0 set the form s Bookmark property to move to the record Forms Subscribers Bookmark rst Bookmark close the dialog box DoCmd Close acForm GoToRecordDialog End Sub Creating a Dialog Box to Goto a Specific Record n How does this act a convenience to the user Private Sub List0 DblClick Cancel As Integer when user double clicks act as though ShowRecord was chosen If Not IsNull List0 Then ShowRecord Click End If End Sub 4 Creating a Dialog Box to Goto a Specific Record Cancel button n q q every dialog box needs one or a Close why set its Cancel property to Yes Command Button Wizard created code n Sub Cancel Click On Error GoTo Err Cancel Click DoCmd Close Exit Cancel Click Exit Sub Err Cancel Click MsgBox Err Description Resume Exit Cancel Click End Sub Set Properties for the Dialog Box n n Set several form level properties to make the form behave like a dialog box Also notice the RecordSource property is blank q dialog boxes are unbound forms 5 Add a Button to the Subscribers Form to Open the Dialog Box n Control Wizard created VBA code Private Sub GoToRecord Click On Error GoTo Err GoToRecord Click Dim stDocName As String Dim stLinkCriteria As String stDocName GoToRecordDialog DoCmd OpenForm stDocName stLinkCriteria Exit GoToRecord Click Exit Sub Err GoToRecord Click MsgBox Err Description Resume Exit GoToRecord Click End Sub Hiding a Dialog Box and Referring to Its Controls n n Rather than close a custom dialog box after user supplies information we can set its Visible property to False to hide the dialog box form Advantages of hiding q q q n can refer to the form s controls can call the form s public procedures avoids the time required to load the form over and over Advantages of closing q releases the resources the form consumes 6 Filtering Data in a Report n n A user has many filtering methods available when using a form and Access 2007 provides some report filtering in Report View Existing MailingLabelsDialog form q n a tabbed custom dialog to allow user to specify which subset of records for mailing labels report and to select which label report to use Had an option group with 3 option buttons q q q all current subscribers those expiring within next 3 months those that expired in past 3 months Add Code for the Print Labels Button Private Sub PrintLabels Click Open a mailing labels report with a filter then close the dialog Dim strFilter As String strReportName As String determine which set of subscribers user wants Select Case MailingType Case 1 Journal mailing all current subscribers strFilter PaidThrough Date Case 2 Subscriptions expiring within 3 months strFilter PaidThrough Date and PaidThrough Date 90 Case 3 Subscriptions that expired in past 3 months strFilter PaidThrough Date and PaidThrough Date 90 End Select determine which mailing labels the user wants If LabelType 1 Then strReportName SmallMailingLabels Else strReportName BigMailingLabels End If DoCmd OpenReport strReportName acViewNormal strFilter DoCmd Close acForm MailingLabelsDialog End Sub 7 Copy PrintLabels Click to Obtain Code for the Preview Labels Button Private Sub PreviewLabels Click Open a mailing labels report with a filter then close the dialog Dim strFilter As String strReportName As String determine which set of subscribers user wants Select Case MailingType Case 1 Journal mailing all current subscribers strFilter PaidThrough Date Case 2 Subscriptions expiring within 3 months strFilter PaidThrough Date and PaidThrough Date 90 Case 3 Subscriptions that expired in past 3 months strFilter PaidThrough Date and PaidThrough Date 90 End Select determine which mailing labels the user wants If LabelType 1 Then strReportName SmallMailingLabels Else strReportName BigMailingLabels End If DoCmd OpenReport strReportName acViewPreview DoCmd Close acForm MailingLabelsDialog strFilter End Sub Other Ways to Change the Records in a Form Report n Base the form report on a query q n Set the form report s Filter and FilterOn properties q n as we did in Callahan Chapter 3 Set the form report s OrderBy and OrderByOn properties q n the query s criteria control which records are presented to change the sorting for a form report Use VBA code to change form report s RecordSource property as the form report opens Private Sub Report Open Me RecordSource SELECT FROM tblInvoice WHERE Paid False End Sub 8