Access error when duplicating with a filter applied

Posted on 2007-07-26
Last Modified: 2013-11-28
when I try to duplicate a record when a filter is applied to a form, I get the "Access has encountered an error" box.  It's the one where there is a checkbox that says repair and reopen the database.

I only get the error when I have applied a filter to seach for a certain customer's quotes.  When I find the one I need to duplicate, I click my button, and the error pops up.

Here is the duplicate code on the button:

Dim strField1 As String
Dim strField2 As String
Dim strField3 As String
Dim strField4 As String
Dim strField5 As String
Dim strField6 As String
Dim strField7 As String
Dim strField8 As String
Dim strField9 As String

strField1 = Nz([Combo296], "")
strField2 = Nz([QuotationAddress], "")
strField3 = Nz([Combo291], "")
strField4 = Nz([QuotationPhone], "")
strField5 = Nz([QuotationFax], "")
strField6 = Nz([ProjectName], "")
strField7 = Nz([Email], "")
strField8 = Nz([Comments], "")
strField9 = Nz([QuotationContactName], "")

DoCmd.GoToRecord , , acNewRec

Combo296 = strField1
QuotationAddress = strField2
Combo291 = strField3
QuotationPhone = strField4
QuotationFax = strField5
ProjectName = strField6
Email = strField7
Comments = strField8
QuotationContactName = strField9

Any ideas?
Question by:slthom

    Author Comment

    I also get the same error if I have applied a filter and I try to add a new record.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Well .. I'm on a roll with this - this week:

    A **DeCompile** may help here ...

    But first, if you have not already:
    Check for any **Missing References via the VBA Editor>>Tools>>References ....

    Then, follow this procedure:

    0) **Backup your MDB**
    1) Compact and Repair the MDB, as follows:
    Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
    2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
    3) Close the mdb
    4) Open the mdb and do a Compact and Repair (#1 above).
    5) Open the mdb:
        a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
        b) From the VBA Editor Menu at the top of the window:
            Note ... after the word Compile will see the name of your 'Project' - just an fyi.

    6) Close the mdb
    7) Compact and Repair one more time.

    *** Executing the DeCompile:
    Here is an example of the command line syntax  (adjust your path and file name accordingly) for executing the


    Run this from Start>>Run, enter the following command line ...

    "C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile


    For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

    LVL 13

    Expert Comment

    What about opening to the record without a filter

    DoCmd.openform "yourform", acNormal, , "[fieldtomatch]=  " & me.field

    Author Comment


    decompile does not help

    when filtering, the form is already open.  we use the "filter by form" button.  We click the button and then use a combo box to select the customer we want to filter for.
    LVL 13

    Expert Comment

    What about a pop-up form with a textbox and a button?
    LVL 13

    Accepted Solution

    I have a form where I copy fields from a record to a new record. I had never tried with a filter applied, but just did and it worked. Here is the code I use.

    Dim Ttown, Tcounty, Tdefendant, Tdefendantdob, Tcrimetype, Tcounty2, Tofficer, Tshield, Ttodaysdate, Ttime, Tlocation, Ttown2, Tcounty3

    Tcounty = Me!county
    Tdefendant = Me!Defendant
    Tdefendantdob = Me!DefendantDOB
    Tcrimetype = Me!CrimeType
    Tcounty2 = Me!County2
    Tofficer = Me!Officer
    Tshield = Me!Shield
    Ttodaysdate = Me!TodaysDate
    Ttime = Me!Time
    Tlocation = Me!Location
    Ttown2 = Me!Town2
    Tcounty3 = Me!County3
    Ttown = Me!town
    Taffirmedday = Me!affirmedday
    Taffirmedyear = Me!affirmedyear
    Taffirmedmonth = Me!affirmedmonth

    DoCmd.GoToRecord , , acNewRec

    Me!county = Tcounty
    Me!Defendant = Tdefendant
    Me!DefendantDOB = Tdefendantdob
    Me!CrimeType = Tcrimetype
    Me!County2 = Tcounty2
    Me!Officer = Tofficer
    Me!Shield = Tshield
    Me!TodaysDate = Ttodaysdate
    Me!Time = Ttime
    Me!Location = Tlocation
    Me!Town2 = Ttown2
    Me!County3 = Tcounty3
    Me!town = Ttown
    Me!affirmedday = Taffirmedday
    Me!affirmedyear = Taffirmedyear
    Me!affirmedmonth = Taffirmedmonth

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    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…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now