Commit  recordset update from form without losing position

Posted on 2004-11-07
Last Modified: 2012-06-21
I have a form that is used to add or edit  records in a table. After entering changes in 1 or more fields I want to print a report based upon the active record that will reflect the changes, but I want the form to remain open on the same record. (This is an Access database, not SQL.)
Question by:Faircarp
    LVL 12

    Expert Comment

    This shouldn't be very difficult at all.

    Create a report that includes the information about the record you want to print and put a command button on the form to open that report.  Then have code like this to open the report on the Click event of your new button:
    DoCmd.OpenReport "ReportName", acViewPreview, , "[NameOfIDFieldInReport]=" & NameOfIDFieldOnForm ,acWindowNormal

    This will open the report, filtering for the record currently open in the form, and will not change the record open on the form.  When you're done with the report, you can close it, and your form should be as you left it.
    LVL 12

    Expert Comment

    If you need more detailed information, just post here.
    LVL 39

    Accepted Solution

    1. You need to make sure the record is saved.
    2. You can pass the Primary Key field as criteria to your report.

    Private Sub cmdPreview_Click()

        'make sure current record has been saved
        If Me.Dirty = True Then
            Me.Dirty = False
        End If

        'open report with criteria of current record
        DoCmd.OpenReport ReportName:="MyReport", _
                                     WhereCondition:="MyField=" & Me.txtMyField
    End Sub

    Replace MyReport with the name of your report
    Replace MyField with the name of the PrimaryKey field for the record you want to print
    Replace txtMyField with the name of the control that is bound to your Primary Key field.

    If your Primary Key field is stored as Text in the table then surround the value with '

                                     WhereCondition:="MyField='" & Me.txtMyField & "'"


    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now