If statement conflict

[paulhenley]

I have a form (header) that has a subform (items). I have some code that takes information from the header and populates the item table as default values when a new item is added>

Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next 'It should never occur, just to be sure...
Me!itctlordno.DefaultValue = "Forms![CTL]![ctlordno]"
Me!itctlquoteno.DefaultValue = "Forms![CTL]![ctlquoteno]"
Me!itcustordno.DefaultValue = "Forms![CTL]![custordno]"
Me!itentby.DefaultValue = "Forms![CTL]![entby]"
Me!ititemno.DefaultValue = Nz(DMax("[ititemno]", "Soitem", "[itconum]=" & Forms![CTL]![conum]), 0) + 1
End If
End Sub

This coding works well, but if I refer to any of the item fields in If statements elsewhere in the form, the value does not default.

Please can anyone help?

 

[jimbrooking]

Paul,

I've had similar mysterious happenings with the OnCurrent event. As I recall my problems finally resolved when I realized that changing stuff on my form kicked off other events which I was not expecting. I wonder if your problem might go away if you tried to insert your header-specific defaults at the BeforeInsert event instead. (And remove the Me.NewRecord test, as it shouldn't be needed.)

If this resolves the problem could you let me/us know?

Thanks,
Jim

 

[paulhenley]

Jim,

Many thanks for your suggestion, it solved the problem and all "If" statements now work. The only down side is that the default data (including the calculation of the new item number) does not appear until the user starts to enter data. I have tried putting commands in both the OnCurrent and BeforeInsert events but I get errors. Ideally I would like the form to calculate the new/next item number immediately (OnCurrent) and the other default values are fine if they populate once the users start entering data(BeforeInsert).

Any further advice would be greatly appreciated.

Many thanks once again.

Paul

 

[jimbrooking]

Paul,

Just returned from a 2-week holiday, so my inbox is greatly full. If you want to ZIP and email me your database (assuming it comes in at under a megabyte) I'll take a look at it when time permits.

Jim

 

[paulhenley]

Jim

Many thanks for your prompt response and kind offer. I have actually managed to fix the problem by getting the data to default as you suggested by using the BeforeInsert and I put the code to calculate the next item number on a subform control GotFocus event.

Many thanks for all your help.

Paul.

 

[jimbrooking]

Paul,

Great! One less thing to do, even though this looked like more fun than the rest of it!

Jim