DoCmd OpenForm with filter opens in "Add" mode

Posted on 2007-08-03
Last Modified: 2013-12-20

I have a simple VBA application.  On the primary form is a list of equipment in the "Equipment" table.  I have a secondary form that displays information on each piece of equipment.  

If a user is viewing the equipment list, they double click on a line to view the form that displays details on the equipment.

This is the line of code I am using:
  DoCmd.OpenForm "Equipment", acNormal, , strWhere
strWhere is my filter, looking at it in debug shows
  [Equipment_ID] = 47,
which is the correct ID of the piece of equipment I clicked on.

The subform opens in "Add" mode, all the fields are blank.  If I type in any of the fields it creates a new equipment record.  I have a button which intentionally invokes this functionality, but in that case I am calling:
DoCmd.OpenForm "Equipment", , , , acFormAdd

Here is a another piece of the puzzle, I created a new form named "Equipment1" and dropped all of the "Equipment" fields from the table onto the form layout.  If I change the text of my OpenForm command to use "Equipment1" instead of "Equipment", the form works fine.  

In fact I don't think there are any differences at this point between Equipment and Equipment1, so why would one work and one not.  

I did a compact and repair on the database, but that did not make any difference.  Any ideas why this would happen?

Question by:compsol1993
    LVL 119

    Accepted Solution

    use the openargs to pass the Equipment_ID to form Equipment.

    DoCmd.OpenForm "Equipment", , , , acFormAdd, , OpenArgs = Me.[equipment_id]

    in the load or open event of form Equipment.

    private sub form_load()
    if len(me.openargs)>0 then


    end sub
    LVL 13

    Expert Comment

    <The subform opens in "Add" mode>
    Did you check the DataEntry property of the subform?


    Author Comment

    Thanks for the feedback  

    capricorn1:  I had already tried your suggestions.  Since the primary key of the table is an autonumber, trying to change it in form load() produces an error saying that the field cannot be edited.  It seems that it is already in add mode at that point.

    LVL 119

    Expert Comment

    by:Rey Obrero
    is equipment_id an autonumber ?
    if it is opening the form in addmode will automatically create a equipment_id.
    LVL 119

    Expert Comment

    by:Rey Obrero
    what is it you are trying to do?
    LVL 13

    Assisted Solution

    DoCmd.OpenForm "Equipment", ,, "[equipment_id]='" &me.equipment_id

    Check to see if you have any code in the on open of the form that is causing it to open with a new record.

    LVL 10

    Assisted Solution

    Hi compsol1993,

    you say you are using the command line

     DoCmd.OpenForm "Equipment", acNormal, , strWhere

    Have you tried using

     DoCmd.OpenForm "Equipment", acFormReadOnly, , strWhere

    to force the form to open in read only mode?

    Hope this helps,

    LVL 13

    Expert Comment

    did you check the dataentry property?

    Featured Post

    IT, Stop Being Called Into Every Meeting

    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!

    Join & Write a Comment

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    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…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now