New Record Problem

[The Professor]

Hi All,

I'm having a slightly tricky problem when creating a New Record. I have a Main Form (Customers) with a SubForm (for Invoices) linked by the CustomerID field on a One to Many relationship.

The Main Customers Form, has a Command Button to create a New Record. This Control will create a new customer record for the Main Form, and a new blank record on the SubForm (Invoice). This is working well for the most part, but in certain circumstances there is a hiccup, which is causing problems. When the Command Button is clicked on the Main Customer Form, the code behind the Control includes the line:

DoCmd.GoToRecord , , acNewRec



This will then trigger the Form_Current event for the Main Form - the code runs - and everything is fine.

However, the hiccup is, that sometimes, after the Command Button code is run (which is on the Main Form) the Form_Current event for the SubForm is run first - before the Form_Current event for the Main Form. And this is causing me real problems, because I need the code in Form_Current for the Main Form to first, before the Form_Current event for the SubForm - because if it doesn't run first, errors occur.

I don't know why, DoCmd.GoToRecord , , acNewRec, (which is on the Main Form) should sometimes trigger the Form_Current event on SubForm first, but I need to ensure the correct sequence of events, but I'm at a bit of a loss at the moment.

Prof.

[June7]

What is the code in the subform Current event?

[Demerit]

What is it you Need when you press the command button? Both on the subform and the main form. Post the codes if possible.

[The Professor]

Hi June,

Thanks for your reply, you've helped me out in the past, hope you can help here too.

There's a fair bit of code on the SubForm Current event, most of it deals with activating or deactivating various Controls, according to certain criteria. This code is working fine, provided the Current event for the Main Form (Customers), runs first. The reason for this is because of the CustID Field, which is the Key Field linking the two Tables (Customers and Invoice). The value of this Field is set in the Current event of the Main Form and is automatically entered, it's not set by the user.

A section of code on the SubForm Current event, however, requires the value of the Field CustID, so when the SubForm Current event runs first, the value of CustID is Null - because it hasn't been set yet. This then generates the "Invalid use of Null" error and the Invoice SubForm doesn't load properly and is missing the Key CustID Field.

I haven't quite got to the bottom of just why, when I create a new customer record (as per my original posting) that sometimes, the SubForm Current event runs before the Main Form Current event - that has me baffled as the moment.

Prof.

[Missinglinq]

...why, when I create a new customer record...sometimes, the SubForm Current event runs before the Main Form Current event - that has me baffled as the moment...

What baffles me is why the SubForm OnCurrent event doesn't always run before the Main Form Current event! This is the way it is supposed to run! In a Main Form/Subform scenario, the OnLoad and OnCurrent events of the Subform are always supposed to execute before those of the Main Form!

My guess is that you're going to have to relocate the code currently in the Subform OnCurrent event to somewhere else. I'd try that code in the OnEnter event of the Subform Control.

Alternatively, you could place the code to diddle with the Subform Controls in the Main Form's OnCurrent event, after the code that generates the CustID, but you'd have to adjust all of that code to reference the Controls as being Subform Controls. Personally, I'd choose the first approach!

Linq ;0)>

[June7]

This behavior occurs when moving to new record row on the Customers main form? It does not happen every time?
Since events of subform and its controls occur before the main form events, it should.

Akin to what linq suggests, try this.

Keep the code behind the subform but as a general Sub and call that procedure from the main form.

Sub FixControls()
...
End Sub
________________

Call Me.subformcontainername.Form.FixControls

[The Professor]

Hi,

Many thanks, June, Link - both replies very helpful.
Will look at moving the code in the ways suggested. I'll let you know how I get on.

Thanks again,

Prof.