• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3168
  • Last Modified:

DoCmd OpenForm with filter opens in "Add" mode


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?

3 Solutions
Rey Obrero (Capricorn1)Commented:
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
<The subform opens in "Add" mode>
Did you check the DataEntry property of the subform?

compsol1993Author Commented:
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.

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Rey Obrero (Capricorn1)Commented:
is equipment_id an autonumber ?
if it is opening the form in addmode will automatically create a equipment_id.
Rey Obrero (Capricorn1)Commented:
what is it you are trying to do?
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.

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,

did you check the dataentry property?

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now