Incrementing PART NUMBER on form

[kealaz]

Hi,

I have a form [frmPRTODO]. When the user enters information into this form,
I would like a part number to be automatically created. I don't need the
user to see this part number, so I'm setting the visble for the control to
"No". Can I use the after update of another control to enter the value in
this control?

Me!PART_NO =

I would like the part number to be 9999-xxxx such that xxxx increments each
time.

ie.
9999-0001
9999-0002
9999-0003... etc.

Any help is greatly appreciated. THANKS!

 

[John W. Vinson]

Hi,

I have a form [frmPRTODO]. When the user enters information into this form,
I would like a part number to be automatically created. I don't need the
user to see this part number, so I'm setting the visble for the control to
"No". Can I use the after update of another control to enter the value in
this control?

Me!PART_NO =

I would like the part number to be 9999-xxxx such that xxxx increments each
time.

ie.
9999-0001
9999-0002
9999-0003... etc.

Any help is greatly appreciated. THANKS!

If the 9999- is constant and will be present in all records, why store it AT
ALL? You can just display it using a Format:

"\9\9\9\9-0000"

on an integer or Long Integer field.

If the user won't see the value you can use the form's BeforeUpdate event.
With an integer just use

If IsNull(Me!PartNo) Then
Me!PartNo = DMax("[Partno]", "[tablename]") + 1
End If

If you have some good reason to store the 9999- prefix post back, the code can
be tweaked to handle it.

 

[kealaz]

Hi John,

Thank you so much for helping me with this. I do not really need the 9999,
I just need a unique number, so what you gave me works for that..... EXCEPT,
when i tab into the next record, to enter another part, it assigns the same
number. Is there a way to get it to give it a new part number every time?

Thanks!

 

[John W. Vinson]

Hi John,

Thank you so much for helping me with this. I do not really need the 9999,
I just need a unique number, so what you gave me works for that..... EXCEPT,
when i tab into the next record, to enter another part, it assigns the same
number. Is there a way to get it to give it a new part number every time?

Thanks!

If the code is in the *FORM'S* BeforeUpdate event this should work: you will
not see the part number because it will be assigned after the user takes
action to save the record (e.g. move to a new record) but before it's written
to disk.

Please post your actual code and indicate the datatype of the ID field.

 

[kealaz]

Hi John,

Thank you very much for your help. I got it working! Thanks so much!!!