Data in a form's fields disappear when focus moves to subform

Question

I have a form that has several text boxes and combo boxes, followed by a subform.  After a user has populated the text boxes and combo boxes, and then moves to the subform (which is a datasheet), all the values in the previous text boxes and combo boxes disappear!  There are no events associated with the subform.

Has anyone else run into this problem, and if so, how did you solve it?  Thanks in advance for any assistance.

Answer 1

There is a Before Update event at two different levels: that of individual controls and that of the form as a whole.

The Before Update even of a control fires before the value of the control is updated. It doesn't fire before the record as a whole is updated.

The Before Update event of a form fires before the current record is updated. It doesn't fire before individual controls on the form are updated.

As you have found, undoing all changes in the Before Update event is not ideal. I'd use the On Unload event of the form and cancel the unload if not all required data have been filled in. That would allow the user to visit the subforms and return to the main form without clearing the record.

Private Sub Form_Unload(Cancel As Integer)

    ' Test whether required information has been filled in

    If ... Then

        MsgBox "Not all required information has been provided."

        Cancel = True

    End If

End Sub

Answer 2

It's the Form_BeforeUpdate event procedure. The variable blnAddDetailsButtonClicked is False unless the user clicked the btnAddDetails button, so when the user clicks in a subform it will be false.

If this variable is False, the main form's update is cancelled:

    If blnAddDetailsButtonClicked = False Then ' The user closed the form.

        Cancel = True

        Me.Undo

        GoTo Exit_Procedure

    End If

Answer 3

I am amazed how quickly you found the problem!  I keep forgetting that the Form_BeforeUpdate event fires right before a record or field is updated.

The truth be told, the code you cited in my form's Form_BeforeUpdate event was derived from a solution you recently provided me (http://answers.microsoft.com/en-us/office/forum/office_2010-customize/distinguishing-between-two-types-of-on-close/70050bea-a622-401e-8879-c608ed39162a).  :-)

I would still like to distinguish between a user clicking a button whose On Click event closes the form versus the user right-clicking on the form's tab and selecting Close from the pop-up menu.  Is that still possible?

Thanks for taking the time to look through all the code in my form.  I sincerely appreciate it and all your help.

Answer 4

I posted the file (Test.accdb) at https://skydrive.live.com/redir?resid=7E032375C285F136!591&authkey=!ACYqipU7VjT6XnM.

The problem form is AddProject.  After you have inputted data in a couple of the text boxes and/or combo boxes near the top of the form, move your cursor to one of the two embedded subforms and you'll see that all the values you inputted disappear.

What's strange is that everything worked correctly on this form until today, even though I haven't edited/modified the form in weeks.

I greatly appreciate your help and hope that you can find the problem.  Thanks.

Answer 5

Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip this copy and make it available through one of the websites that let you upload and share a file, such as Windows Live SkyDrive (https://skydrive.live.com), FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Post a link to the uploaded and shared file in a reply here.

Or register at www.eileenslounge.com (it's free) and post a message in the Access forum. You can attach files up to 250 KB to a post there.