Doing a Requery on a Form opened with acFormAdd creates a new record

Question

Lets asume a user wants to create a new record and to do so he opens a form. When opening a form with

DoCmd.OpenForm "FormName", acNormal, "", "", acFormAdd, acDialog

Access will open the form with the data pointer set on a new/empty record. The user can now fill the form but some user actions may require a Me.Requery in order to take place. If you do so, the form "looses" the data pointer on the currently created and modified record and jumps to a new, empty record. Even a Me.Dirty = False before the requery wont prevent Access from doing so. Im pretty sure this behavior results from the paramter acFormAdd.

In contrast, opening a form with

DoCmd.OpenForm "FormName", acNormal, "", "WHERE-CLAUSE", acFormEdit, acDialog

resolves the behavior but will only work for existing records.

You can imagine that this unrequested behavior is not what I'm expecting because it forces me to implement an ugly workaround with closing and reopening the from when certain conditions are fulfilled.

So, I'm wondering if there is a much easier way that helps me avoid the behevior described above. I would very much appreciate your assistance!

Answer 1

See whether requerying selected form controls can accomplish what you need without requerying the entire form.

For example to requery a combo box named cboUserID, you can do this ...

Me.cboUserID.Requery

Answer 2

Maybe a better solution might be to use .recalc instead.

DoCmd.Save
Me.recalc