auto numbering of a kind

  • Thread starter Thread starter MalcR
  • Start date Start date
M

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
 
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
 
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?




Steve said:
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:-
Click to expand...
[quoted text clipped - 6 lines]
Thanks Malc
Click to expand...
Click to expand...
 
MalcR said:
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,
Click to expand...
[quoted text clipped - 14 lines]
Click to expand...

Steve,

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