Often, you adda function so when a user closes your form, it automatically saves the changes to the database. By default, the MS-Access behavior is to ask the user for confirmation, which can be a huge annoyance when you open and close forms hundreds of times all day long.

So... you add a new button, you click on the Event tabs (in the properties) and add a new Code Builder and in your Sub Function you add the following two lines:

DoCmd.OpenForm "New Form Name", acFormView, ...
DoCmd.Close acForm, "Old Form Name", acSaveYes

(You'll have to complete the OpenForm line, it uses many parameters)

When you do just that and the form is independent of the previous form, you'll be just fine. That's what you have 99% of the time, I suppose.

Now. I had a problem where I use a multi-form open one after another. The user clicks on a button which:

1. Opens a new form,
2. Copies some parameters,
3. Closes the old form.

So continuing with our example above, it could be something like this:

DoCmd.OpenForm "New Form Name", acFormView, ...
[New Form Name]![Order ID] = Me![Order ID]
[New Form Name]![Customer Name] = Me![Customer Name]
[New Form Name]![Customer Address] = Me![Customer Address]
...
DoCmd.Close acForm, "Old Form Name", acSaveYes

This works great. You have multiple forms all of which reference the same customer and each having that customer data duplicated (not the best in that sense, but practical in our system.)

Again this works as expected in most cases.

Where I got a problem is with forms that manage a record and when you go from one form to another when the table of the new form has a reference back to the previous form's table. For example, I showed a Customer ID in my last example. If the new table, say it is named Repair Contract, is clearly marked as having a relationship between itself and tje Customer table, then a row with a corresponding ID must exist for the new Repair Contract to get added properly. In other words, the relationship must exist and be valid at the time you do an INSERT. (note that the INSERT does not need to be explicit.)

The problem is that the new form has a view of the MS-Access Database from the time it gets open (something like that, at least). So in the code above, you are opening the second form (Repair Contract) before the first form gets saved. This somewhat looks like a Locking Problem, but it isn't.

What you need to do is force a save before you open the new form. There is no Lock so you're good on that end, plus the current window gets closed so a lock would be released unless something goes horribly wrong.

To force a Save to happen, you can use the following command:

DoCmd.RunCommand acCmdSaveRecord

This is in replacement to the acSaveYes parameter to the Close command.

Also, if you want to change some parameters, make sure to do it before the save and as I just mentioned make sure to call the save before you open the new form. Here is a fixed version of the script above:

' Change parameters before saving
[Old Form Name]![Purchase Confirmed] = True

' Save before opening new form
DoCmd.RunCommand acCmdSaveRecord

' Now we can safely open the new form
DoCmd.OpenForm "New Form Name", acFormView, ...

' Finally close the old form
DoCmd.Close acForm, "Old Form Name"

As you may notice, I removed the acSaveYes, it is useless since we already saved the form with the acCmdSaveRecord command. It probably doesn't hurt to keep it, although the new form should not make changes to the old form.

This code fixes the problem I had, which was the new form using the CustomerID which is a reference on the Contract table. As I mentioned, the CustomerID reference must be valid. Also, if you are going to reference that specific customer, it is strongly adviced that you have it saved before opening the new form. The OnLoad event is not unlikely to happen really early on!

To avoid these problems in the first place, though, you can also program your own forms. In other words, create forms which are not connected to a table. This gives you the ability to display data from various different tables without a chance of having conflicts. Now your Save function is going to save the fields you want that form to manage with an INSERT or an UPDATE. And this will always happen cleanly and it is very easy to conceptually make it happen before you open your next form. Not only that, you can test for conflicts, missing data, etc. For example, the Customer table may include three means of contact and you want to have at least one for a Customer to be valuable in your database. This means adding some code such as:

Private Sub Save()
    If IsNull(Phone) AND IsNull(Email) AND IsNull(Skype) Then
        MsgBox("At least one mean of contact (Phone, Email, Skype) must be defined!")
        Exit Sub
    End If
    ...save here...
End Sub

On my end I create a set of helper functions to create my SQL statements to do INSERT or UPDATE. One really cool thing about this technique, I can re-use the exact same form to create or edit records. Many times, because of the way MS-Access forms work. you end up with two forms: one to create a new entry and one to edit an existing entry.