Mandatory field on subform seems to trip over save action of main form -> advice?

[TjabbeTjibsma]

I have a mainform with two subforms. When I change a field on the main form, then click into the subform, I get an error saying I haven't filled out a mandatory field. This pops up 3 times in a row in total. After that, I get to my subform as normal.

If I don't change anything on the mainform, or save before I click in the subform, everything is fine.

So, it looks like the save action that happens before going into the subform, also triggers a save action in the subform. That is unexpected behaviour to me.

Any advice on how to fix this would be welcome. Worst case, I can turn the mandatory field into an non-mandatory field and perform a check myself.

[jimatqsi]

How do you save before you click? Do you click a button that has code behind it? If so, what's in that code? What events are programmed for that button?

Jim

[zmbd]

also, when you click on the subform, are you attempting to start a new record or are you selecting a current record.

[TjabbeTjibsma]

Saving on the main form is just the standard Access behaviour (from my understanding that is: when the record is dirty on leaving the record, Access saves the changes).

When I click in the subform, on an empty record, an event fires that prepopulates some columns for the user (timestamp and user name), the last field on the subform, a comment field, is mandatory and needs to be manually typed in by the user. As stated before, for some reason, it looks like a save action is fired before the user gets the change to type in a comment. This only happens when the main form is dirty.

[zmbd]

You are making some assumptions here that don't hold true, look at your first post. If there were a save action that was firing and tripping you up, then this would happen every time you attempted to access the subform.

We need to see the code that you are using to pre-populate the subform records, please, click on the [CODE/] button in the toolbar and then cut-n-paste your script between the two [CODE] [/CODE] tags.

What I suspect is that when you have a dirty record and select the subform, the code is attempting to execute create and save the subform record; however, because the parent is still dirty from an update point on the related field(s) you are running afoul of the required field protocol. Once the parent table gets a chance to finish updating and clears the dirty status, then your subform code gets a chance to run to completion.

However, when you save the parent record first, there is no conflict for the required related fields

[TjabbeTjibsma]

The sub that populates the subform is linked to one of the field's 'On Click' events.

However. I just tested without this code, and that sheds a lot more light on what happens:

1) when clicking the new record the * symbol changes to the > symbol.
2) immediately after that, the record seems to refresh, and
3) the first record on the subform is selected (> in front of it).


Here's the sub the pre-populates my subform fields:

Private Sub commText_Click()
  '------------------------------------------------------------------
  ' Info commText_Click
  '------------------------------------------------------------------
  ' This sub fills out the comment fields that are already
  ' predetermined, such as the date, the user and the tool it belongs
  ' to. It only does this for:
  ' 1) a new record
  ' 2) that is not being edited (first time you click, or undo)
  
  '------------------------------------------------------------------
  ' Declare variables & initialize
  '------------------------------------------------------------------
  
  If TempVars.Item("HandleErrors").Value = 1 Then
    On Error GoTo Err_Handler
  End If
  
  Dim sProcName As String
  sProcName = "commText_Click"
  
  Dim currentUserID As Long

  currentUserID = TempVars.Item("lngUserID").Value
  
  '------------------------------------------------------------------
  ' Core
  '------------------------------------------------------------------
  
  If Me.NewRecord = True And Not Me.Dirty Then
    Me.commDate = Now
    Me.commRequestID = [Forms]![Main Menu].[NavigationSubform]![tblTools.ID]
    Me.commOwnerID = currentUserID
  End If
  
  '------------------------------------------------------------------
  ' Error & Exit Handling
  '------------------------------------------------------------------
Exit_Handler:
  Exit Sub

Err_Handler:
  glbErrorHandler (sProcName)
  Resume Exit_Handler
End Sub

[zmbd]

Of note in the code, the use of the tempvars collection could be simplified:
line 15 could be If TempVars!Handle Errors = 1 Then
Same thought in Line 24 CurrentUserId = Tempvars!lngUse rID
Why you even have Line 24 is of some question as you could remove line 24 and change line 33 to Me.commOwnerID = Tempvars!lngUse rID and remove line 22

Line 30 ( If Me.NewRecord = True And Not Me.Dirty Then ) just to be sure, you are looking at the subform being dirty and not the parent, true?

I see here, [Forms]![Main Menu].[NavigationSubfo rm]![tblTools.ID] that you are using the newer ACC2010 style nav-control and this some issues as logic in using this control is NOT the same as a late bound tab control as many have assumed. I also now need to understand your form from a slightly differnet angle as using this control adds MANY unique challenges/issues:

So let's take a look at this image (You should read this post for context: http://bytes.com/topic/access/answer...ba#post3763553 ):

It has an outer "Parent" form named "NavigationForm "
It then has a navigationcontr ol named "NavigationSubF orm"
within this subform-control ther are several forms that are loaded using the NavigationSubfo rmControl-buttons. In this image the "NavigationSubF orm" control has the form "frm_loanaccoun ts" loaded. Unfortunately "frm_loanaccoun ts" does not have a subform of its own
[imgnothumb]http://bytes.com/attachments/attachment/7384d1387458484/bytes_example_n avsubctrl.jpg[/imgnothumb]

When you say "main form" are you refering to the parent "NavigationForm " or are you refering to the form loaded within "NavigationSubF orm" (which for the above example would be "frm_loanaccoun ts")

When you say click on the subform are you clicking on one of the NavigationSubfo rmControl-buttons (such as the one that is currently highlighted in the image) or are you clicking on something within the already loaded form within the navigationsubfo rmcontrol (ie: "frm_loanaccoun ts\subform")?

Something I forgot to as you:

I get an error saying I haven't filled out a mandatory field.

Normally this error will have the name of the field in it, would you please post the EXACT error messages you are receiving - you only need to this once for each different message; however, please post the EXACT title, number, and text of each of the messages if different.

[TjabbeTjibsma]

ZMBD,

First of all, thanks for the very extended and informative reply.

Let me try and go over the points you raised.

1) TempVars Notation
You are right about the TempVars notation. This particular sub was written when I was not aware yet of that shorthand notation.

2) Line 30 ( If Me.NewRecord = True And Not Me.Dirty Then ):
Correct, I'm checking to see if the comment field the user clicked on is empty or existing, so it indeed refers to the subform (see explanation below).

3) Form Layout
Yes, I am using the 2010 Navigation Control. I have a main navigation form called [Main Menu]. This is the parent form. On this form I have my controls that load the different menus in my database. For example I have an 'Add' page and an 'Edit' page ([frmEdit]). These get loaded in the NavigationSubFo rm. The [frmEdit] form contains the subform ([sfrmComments]) that's giving me trouble.

So when I say 'main form' I'm referring to the [frmEdit] that's been loaded in the NavigationSubfo rm.
When I say 'sub form', I'm referring to the subform [sfrmComments] that's placed on the Edit form.

This subform contains the following fields:
[commText], [commDate], [FullName]

4) The exact error message:

'You must enter a value in the 'tblComments.co mmText' field'

(No title or error number given, it's a warning)

From observing the behaviour without the pre-populating, (see my previous post) it looks like for some reason Access is immediately leaving the field / tries to refresh. With the commText field empty, but the others filled in, this gives a dirty record, and being a mandatory field, a warning message.

4) Sorry for not informing everyone more clearly on the use of the navigation controls.

[TjabbeTjibsma]

I've been trying to work around / fix the problem, but I keep coming up empty handed.

On a dirty record on the mainform, a subform apparently always requeries. Standard MS Access requery behaviour results in losing your position in the set (i.e., it moves to the first record).

So the only way I currently see of fixing this, is making sure you never click in the subform while the parent form is still dirty. This would require something like

If Me.dirty = True Then Me.dirty = false

statement in every control's _AfterUpdate event.

Hopefully, someone on this forum can come up with something better.

[zmbd]

You still haven't answered:

When you say click on the subform are you clicking on one of the NavigationSubfo rmControl-buttons (such as the one that is currently highlighted in the image) or are you clicking on something within the already loaded form within the navigationsubfo rmcontrol (ie: "frm_loanaccoun ts\subform")?

Also, please be aware that the navigation form tosses in some really unique issues; however, I have not been able to replicate your issue in my test database. Thus, I suspect you have code somewhere else or you are clicking on one of the navigation buttons that is forcing the subform to either reload or requiery.

[TjabbeTjibsma]

I click on the bound text field in the already loaded subform. Note that my subform is located in what in your screenshot is called the frm_loanaccount s. So once you've clicked on the button 'frmEdit', again 'frm_loanaccoun ts' in you example, you'll see the edit page, which contains a subform.

Let my try explain the behaviour in another way:

If I change something on the frmEdit, and NOT save (either by code or via the save button) and then click inside my sfrmComments, placed on frmEdit, it triggers a requery of sfrmComments.

When the data on frmEdit remains the same OR is manually saved, for example by hitting the save button, and then click into the subform, the requery does not appear.

Silly question: how do I quote a previous post?

[zmbd]

OK,
Just how are you linking the Parent form to the form in the NavigationSubFo rm Control?

[TjabbeTjibsma]

ZMBD,

Fresh new day, fresh new start. I decided to approach it from a different angle and take your suggestion and look for code somewhere that could set of a reload / requery.

I've found the culprit: in my Form_AfterUpdat e event of [frmEdit] I have a CurrentDb.Execu te statement. This statement saves the username + time stamp for logging purposes to the same table that is in [frmEdit]'s source. I assume since the form's source has changed, that's what's triggering a requery, including the subform.

I just verified and saved this data to a different table and that solves the reloading. Preferably I would like to keep saving to the original table, but right now, I don't see a way of making that happen.

[zmbd]

I'm glad you finally found the culprit.
Now instead of changing where you are storing the information, store something in the missing comments during that execute method.

I assume since the form's source has changed, that's what's triggering a requery, including the subform.

I don't think that is the case; instead, what I think you are seeing is the error from the execute method as it has stored the value and there's missing information, you then attempt to move on and finally the correct information is entered/stored. Forms do not normally requery unless told to do so; HOWEVER, the navigationsubfo rm control has some very unusual quirks and it may have caused a requery

I am however, very curious about how you related the fields on the master/parent form to the subform in the NavigationSubfo rm control.

Just to be clear, referring to the image in post#7

Assume (I know (^.-) ) for a moment that:
1) NavigationForm is bound to a record-set with Text29 bound to a field in that record-set. Furthermore the field is related to the Frm_LoanAccount s (say FK_CustomerNumb er)
2) NavigationSubFo rm>Frm_LoanAcco unts

(in this example) How are you linking the Master Fields in the parent (NavigationForm ) to the Child Fields in the child (Frm_LoanAccoun ts)
Link Master Fields := Text29
Link Child Fields := FK_CustomerNumb er

[TjabbeTjibsma]

Thanks ZMBD,

On my parent form (frmEdit), I created a (hidden) field called 'masterfield'. This field contains simply the PK of the data behind the form. Exactly analogue to your description of your NavigationForm.

The child field is set to a field in my subforms' recordsource that is the Foreign key to the PK of the main form. Again very analogue, with the difference that the child field is not a control on the subform, just a field in the recordsource.