Access error when duplicating with a filter applied

Posted on 2007-07-26
Medium Priority
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
  • 3
  • 2

Author Comment

ID: 19577404
I also get the same error if I have applied a filter and I try to add a new record.
LVL 75
ID: 19577486
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 ...you 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

ID: 19577655
What about opening to the record without a filter

DoCmd.openform "yourform", acNormal, , "[fieldtomatch]=  " & me.field
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.


Author Comment

ID: 19577854

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

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

Accepted Solution

wiswalld earned 2000 total points
ID: 19577980
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

840 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