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!HandleErrors = 1 Then
Same thought in Line 24 CurrentUserId = Tempvars!lngUserID
Why you even have Line 24 is of some question as you could remove line 24 and change line 33 to Me.commOwnerID = Tempvars!lngUserID 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].[NavigationSubform]![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 navigationcontrol named "NavigationSubForm"
within this subform-control ther are several forms that are loaded using the NavigationSubformControl-buttons. In this image the "NavigationSubForm" control has the form "frm_loanaccounts" loaded. Unfortunately "frm_loanaccounts" does not have a subform of its own

When you say "main form" are you refering to the parent "NavigationForm" or are you refering to the form loaded within "NavigationSubForm" (which for the above example would be "frm_loanaccounts")

When you say click on the subform are you clicking on one of the NavigationSubformControl-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 navigationsubformcontrol (ie: "frm_loanaccounts\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 NavigationSubForm. 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 NavigationSubform.
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.commText' 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 NavigationSubformControl-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 navigationsubformcontrol (ie: "frm_loanaccounts\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_loanaccounts. So once you've clicked on the button 'frmEdit', again 'frm_loanaccounts' 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 NavigationSubForm 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_AfterUpdate event of [frmEdit] I have a CurrentDb.Execute 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 navigationsubform 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 NavigationSubform 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_LoanAccounts (say FK_CustomerNumber)
2) NavigationSubForm>Frm_LoanAccounts

(in this example) How are you linking the Master Fields in the parent (NavigationForm) to the Child Fields in the child (Frm_LoanAccounts)
Link Master Fields := Text29
Link Child Fields := FK_CustomerNumber

[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.

[zmbd]

Then this is not a true Parent/Sub relationship as would be established here:

Which is not something one can do with the ACC2010:NavigationSubForm control that I am aware of.

[TjabbeTjibsma]

Mine looks similar, with the exception that my version of 'Employee ID' is not a control, but a field in my version of 'sbfActiveOrder's source. From your screenshot I can't see if that's the case in your example as well.

What my subform does is show all comments (in a continous form) for the record that's open in the mainform.

I'm confused now as to why it would not be a true parent/sub relationship. How can the records link properly if the relationship is not as it should be?

[zmbd]

That last shot is from the Northwinds Traders Template

Open your Main form that looks like the one in Post #7 in design view.
Now, Show the properties dialog box.
In the dropdown list of this box, select the "NavigationSubForm" (this is assuming that you have not changed the name of the ACC2010:NavigationSubForm control)
Select the data tab.

Compare what you see there to what you see in Post #16

See the difference?

Thus even though the Parent may be the outer form, there's no true Parent-Child established between the two.
This is a quirk in the ACC2010:NavigationSubFormControl.

Now there has to be something else going on here if your loaded subform is only showing the related records in the subform to the record selected in the parent... and that might be part of the reason your required field error was tripping three times.

[TjabbeTjibsma]

Thanks again ZMBD,

These NavigationSubforms are very tricky.. I'm starting to regret a little that I've used them. Let me summarize:

My highest level possible form, contains the title, and all the buttons. I've called this [Main Menu] On this form there is a [NavigationSubform]. This is where each button automatically loads/sends whatever you link to with the [Navigation Target Name] field, that you'll find under the Data tab of each Navigation Button.

Then on the NavigationSubForm a form is loaded, as said, depending on which button the user clicked. This form contains subforms exactly the way you normally would set them up with the screenshot you showed in post 16.

Apart from the code in the parent form that triggered the problem, I can't think of anyting out of the ordinary here.

[zmbd]

ok,
Took me a moment to follow (^_^)

MainForm->
...NavgationSubformControl->
......LoadedForm->
.........SubformToLoadedform

It is the SubformToLoadedform that is linked back to MainForm. This makes sense now and is a nice trick to linking the subform to the mainform from within the NavigationSubFormControl as you can not do this with the loadedform in the NavigationSubformControl

If this is correct, then I'll attempt this setup in my test database and see if there's something going on that might also lead to the issues you were seeing.

(Yes, the navigation forms have some regreatable quirks. I've found one where if the database is split, and the mainform has VBA script, then many bad things happen. The solution was to use a control with a Macro that called the VBA script - took me three days to figure that out! (@_@) )

[TjabbeTjibsma]

MainForm-> <<--- this is called [Main Menu]
...NavgationSubformControl->
......LoadedForm-> <<-- this is called [frmEdit]
.........SubformToLoadedform <<-- this called [sfrmComments]

I have a 'classic' subform link between the latter two. In other words, the subform is placed on [frmEdit]. The master and childfields are fields in [frmEdit] and [sfrmComments] respectively.

Hope that clears it up! This navigation stuff is slick if it works, but it's very confusing to set up. But then again, this is my first elaborate Access DB. My other experience is mainly in MatLab and MySQL.