On Open - increment a form value 2

[LDP]

Hello:

I have a text box on a form who's control source is the primary key field of a table. I would like the form to open to the next value (to add a new record). The following expression sets the default value for the Data of this text box.

(DMax('[DTNumber]','[Sypher Drawing Transmittal Table]')+1,1)

The form properties are set as follows:

Allow Edits Yes
Allow Delections Yes
Allow Additions Yes
Date Entry No

The form does not open to the next available value and when I add a Add Record control I get a #error message in the form text box.
When I set the Data Entry property of the form to Yes it opens with "0" as the value not the next incremented value in the table. Any ideas on how to set the incremental value of this form on open?

Thanks in advance.

 

[hymn]

Try

= DMax("[DTNumber]", "[Sypher Drawing Transmittal Table]") + 1

Hope this helps
Hymn

 

[LDP]

Thank you Hymn

I corrected the code as you suggested but unfortunately there was no change. The form opened to the first DTNumber.

I'm trying to open the form ready for new data with the next available DTNumber showing in the form.

Thanks again for your help.

 

[hymn]

I must admit that I use it in the after update event of the control prior to the control that needs the incremental number

Hope this helps
Hymn

 

[randy700]

How about....
In the OnCurrent event....
If Me.NewRecord Then
Me.ControlName = DMax("[DTNumber]", "[Sypher Drawing Transmittal Table]") + 1
End If



Randy

 

[LDP]

Thanks folks:

Hymn:
Thanks
Do you mean if you have a form control that opens the form you want to increment you place the code in the "after update" event of the first form? Think I'm confused?

Randy700
Thanks
I replaced controlname with "DTNumber" (correct control)in the code .
I placed the code in the On Current event of the form and I receive a Visual Basic compile error "End If without block If". I'm not sure what a block If is? So I'm not at the point where I can test the expression. Any further help you can provide?

 

[randy700]

Check your code carefully. It sounds like you have one more End If than you have Ifs.


Randy

 

[hymn]

In the form if the first control/field is Surname and the second is Member_No then I would put the code in the after update event of the control/field Surname

Hope this helps
Hymn

 

[LDP]

Randy

I'll check my code and get back.

My Add Record control now picks up the incremented value
with the corrected Dmax expression but I'm going to check your code.

thanks

Hymn:
The control for DTnumber is the first in the form. I'm not sure where to apply the afterdate event procedure

thanks

 

[hymn]

OK try randy's in the on open event

Hope this helps
Hymn