Autogenerated AlphaNumeric Text

  • Thread starter Thread starter cktia
  • Start date Start date
C

cktia

Hello. I have a database and form (for work orders) in which I'd like to
create an auto-generated alphanumeric text box that will contain unique work
order numbers - ideally (2007-S001) and will increment by one automatically
when a new work order is entered into the form (ie: 2007-S002, 2007-S003).
There is the slight possibility that more than one person will be creating a
work order at the same time - so I thought perhaps making it a "Before
Update" event would help duplications not to occur. I'm code challenged so
any help would be greatly appreciated. Thanks ahead of time! :)
 
Strongly recommend against creating a single field like this. Use two fields
(WO_Year, WO_Num). You don't want to use the BeforeUpdate event because you
don't want to change this value on updates, you want to set it just before
you insert the record. Assuming you have a bound textbox (I'd lock it so it
cannot be edited by the user) called txt_WO_num.

Private Sub Form_BeforeInsert( )

dim strCriteria as string

strCriteria = "[WO_Year] = " & Year(Date)
me.txt_WO_Num = NZ(DMAX("WO_Num", "tbl_WorkOrders", strCriteria), 0) + 1

End Sub

HTH
Dale
 
Thanks for the quick response. I had been wondering if I should use two
fields and then combine them somehow.

I've still got a couple of questions. One, I do not have a "BeforeInsert"
option (fyi - using Access 2002). Two, are both the WO_Year & WO_Num fields
text fields?

Thanks again.

Dale Fye said:
Strongly recommend against creating a single field like this. Use two fields
(WO_Year, WO_Num). You don't want to use the BeforeUpdate event because you
don't want to change this value on updates, you want to set it just before
you insert the record. Assuming you have a bound textbox (I'd lock it so it
cannot be edited by the user) called txt_WO_num.

Private Sub Form_BeforeInsert( )

dim strCriteria as string

strCriteria = "[WO_Year] = " & Year(Date)
me.txt_WO_Num = NZ(DMAX("WO_Num", "tbl_WorkOrders", strCriteria), 0) + 1

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



cktia said:
Hello. I have a database and form (for work orders) in which I'd like to
create an auto-generated alphanumeric text box that will contain unique work
order numbers - ideally (2007-S001) and will increment by one automatically
when a new work order is entered into the form (ie: 2007-S002, 2007-S003).
There is the slight possibility that more than one person will be creating a
work order at the same time - so I thought perhaps making it a "Before
Update" event would help duplications not to occur. I'm code challenged so
any help would be greatly appreciated. Thanks ahead of time! :)
Click to expand...
Click to expand...
 
1. Your forms should have a Before Insert event, but I'll check my copy of
2002 at home to make sure.

2. No, I would make both of those two fields numeric (Integer for WO_Year,
Integer maybe Long for the WO_Num field.

I would retain them as separate fields in all of your forms, but in reports,
I'd concatenate them as:

WO: Format(WO_Year, "yyyy") & " - " & Format(WO_Num, "0000")

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



cktia said:
Thanks for the quick response. I had been wondering if I should use two
fields and then combine them somehow.

I've still got a couple of questions. One, I do not have a "BeforeInsert"
option (fyi - using Access 2002). Two, are both the WO_Year & WO_Num fields
text fields?

Thanks again.

Dale Fye said:
Strongly recommend against creating a single field like this. Use two fields
(WO_Year, WO_Num). You don't want to use the BeforeUpdate event because you
don't want to change this value on updates, you want to set it just before
you insert the record. Assuming you have a bound textbox (I'd lock it so it
cannot be edited by the user) called txt_WO_num.

Private Sub Form_BeforeInsert( )

dim strCriteria as string

strCriteria = "[WO_Year] = " & Year(Date)
me.txt_WO_Num = NZ(DMAX("WO_Num", "tbl_WorkOrders", strCriteria), 0) + 1

End Sub

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



cktia said:
Hello. I have a database and form (for work orders) in which I'd like to
create an auto-generated alphanumeric text box that will contain unique work
order numbers - ideally (2007-S001) and will increment by one automatically
when a new work order is entered into the form (ie: 2007-S002, 2007-S003).
There is the slight possibility that more than one person will be creating a
work order at the same time - so I thought perhaps making it a "Before
Update" event would help duplications not to occur. I'm code challenged so
any help would be greatly appreciated. Thanks ahead of time! :)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top