How to Transfer Data from Form to Report




VB: Transfer Text from Form to Report

(Macro: Transfer Text from Form to Report)

In this How To, I will show you how to transfer data from textbox on form to report. Data on the textbox is not stored in the table. For example below, I have two textboxes on the form for start and end date. I want to open a report by the date range from the start date and end date that I enter on the form. I also want the date range to show on the report as well.

Step 1 Add two textboxes on form (Main Menu form)

  • Add the first unbound textbox and name it “txtStartDate” for a start date.
  • Add the second unbound textbox and name it “txtEndDate” for the end date.

add two text box

Step 2 Add code to open report

Under the design view mode, click on an Account Detail button and select Event Procedure under the On Click event.

click button

VB Code:

Private Sub Command19_Click()

On Error GoTo Err_Command19_Click

 Dim stDocName As String

     If IsNull(txtStartDate) Or IsNull(txtEnddate) Then

        MsgBox “Please enter a Start Date and End Date.”, vbInformation, “Start and End Date Needed”

        Me.txtStartDate.SetFocus

    Else

        stDocName = “TransByDate”

        DoCmd.OpenReport stDocName, acPreview, , “[Entry Date] >= #” & txtStartDate & “#” & “and [Entry Date] <= #” & txtEnddate & “#”

    End If

Exit_Command19_Click:

    Exit Sub

Err_Command19_Click:

    MsgBox Err.Description

    Resume Exit_Command19_Click

End Sub



Step 3  Add two textboxes on report

  • Add the first unbound textbox for a start date.
  • Add the second unbound textbox for the end date.

report design

Step 4  Link Start Date textbox on report

  • Double Click on the From Date textbox (or Start date)
  • Click on a Data tab of Property Sheet
  • Update Control Source = “Forms![Main Menu].form!txtStartdate”

from Date field

Step 5  Add link End Date textbox on report

  • Double Click on the To Date textbox (or End date)
  • Click on a Data tab of Property Sheet
  • Update Control Source = “Forms![Main Menu].form!txtEnddate”

To fireld date

Step 6 Complete and Testing

  • Entering two dates on the Start Date and End Date boxes
  • Click on Account Detail button

date on form

As Result: the Start Date and End Date on form will show on the report of From Date and To Date as shown below.

report

Related posts