auto numbering of a kind

[MalcR]

Is there a series of commands that can be placed in a macro to allow me to
resolve the following:-

My table “Tbl_Location†has three fields Phase, Section and DrawingNo among
others. I wish to record new numbers in the DrawingNo field, when a new
Section record is added using the conditions, that if it’s within an existing
Phase then assign the next available sequential number or reset to 1 if it is
a new Phase value that has been added.

Thanks Malc

 

[Steve Schapel]

Malc,

You can use a SetValue action in your macro, with these arguments:
Item: [DrawingNo]
Expression: Nz(DMax("[DrawingNo]","Tbl_Location","[Phase]='" & [Phase]
& "'"),0)+1
(assumes Phase is Text data type)

You could possible assign this macro on the After Update event property
of the Phase or Section controls on the form, and you will probably want
a Condition on the macro like this...
[DrawingNo] Is Null And [Phase] Is Not Null

 

[MalcR via AccessMonster.com]

Steve,

Thanks for taking the time out to reply, but I keep getting the "action
failed" message.

I copied and pasted the expression below after changing the field type for
[Phase] to text. I then changed it back to number type and used one of your
earlier tips namely; Nz(DMax("[DrawingNo]","Tbl_Location","[Phase]='" &
[Phase] ),0)+1 still no luck.

As you may have realised I'm new to macros, is there something fundamental
that I'm missing?

Malc,

You can use a SetValue action in your macro, with these arguments:
Item: [DrawingNo]
Expression: Nz(DMax("[DrawingNo]","Tbl_Location","[Phase]='" & [Phase]
& "'"),0)+1
(assumes Phase is Text data type)

You could possible assign this macro on the After Update event property
of the Phase or Section controls on the form, and you will probably want
a Condition on the macro like this...
[DrawingNo] Is Null And [Phase] Is Not Null

Is there a series of commands that can be placed in a macro to allow me to
resolve the following:-

[quoted text clipped - 6 lines]

Thanks Malc

 

[MalcR via AccessMonster.com]

Steve,

Thanks for taking the time out to reply, but I keep getting the "action
failed" message.

I copied and pasted the expression below after changing the field type for
[Phase] to text. I then changed it back to number type and used one of your
earlier tips namely; Nz(DMax("[DrawingNo]","Tbl_Location","[Phase]='" &
[Phase] ),0)+1 still no luck.

As you may have realised I'm new to macros, is there something fundamental
that I'm missing?

Malc,

[quoted text clipped - 14 lines]

Steve,

Now working fine, my mistake. Thanks very much for your help