autoNumber

[wdu94]

Hi, All,

I have a field in the table named "Compli_num".

Compli_num
----------------
01CF001
01CF002
01CF003
01CF004
......

I want to know how to get this number auto-increment.

Thank you for your time.
Jeannia

 

[jebry]

Hi!

You can use this in the Form_Current event procedure:

Dim strCompli As String

If IsNull(txtCompli_Num) = True Then
strCompli = DMax("Compli_Num", "YourTableName&quot
txtComli_Num = Left(strCompli, 4) & Format(CInt(Right(strCompli, 3)) + 1, "000&quot
End If

Now the text box on the form will be automatically filled in any time you go to a new record. Please note that you will need to do three things. One, you will need to make a way for the user to undo records which are started by accident. Two, you will need to make sure some information is in the table initiallly. And finally, you will need to have some sort of method for handling Compli_Num after 999 records.

hth
Jeff Bridgham

 

[wdu94]

HI, jebry,

When I add new data, the all data add in the table, but the new "Compli_num" not add in the table yet. Therefore, when I get another new form, the txtCompli_num still show that number. What happens? Any suggestion?

Thanks again.
Jeannia

 

[Nancy2]

1) Ensure the text box: txtComli_Num is bound to the field: compli_num
2) Also - you may wish to run this code on the Before Insert event vs. current to ensure you do not override existing values.

Nancy

 

[jebry]

Hi!

Nancy is correct, I assumed that the text box would be bound to Compli_Num. Checking for the Null in the text box should make sure that no information is overwritten, but it would be wise to test it on dummy data at first to make sure it is working properly.

hth
Jeff Bridgham

 

[wdu94]

Hi,

Thanks all.

I met my boss this morning. I want to update info. here. Still hope someone help me out.

My project is work now, but my boss told me:

Compli_num
------------

01CF001
01CF002
....

The first two number(01) is the current year, we want to au-increment running cycle. If the current year is 2002, the compli_num should be like that:

Compli_num
-------------
02CF001
02CF002
02CF003
...... then auto_increment. Thus, we don't care about number"999".

Any ideas.

Many Thanks.
Jeannia

 

[Nancy2]

If it worked with the original code, try this - not sure what you mean about the 999.

You may need to change dmax for strCompli to only pick up the current year.

Dim strCompli As String
Dim strFeedYear As String

strFeedYear = Format(Date, "yy&quot


If IsNull(txtCompli_Num) = True Then
strCompli = DMax("Compli_Num", "YourTableName&quot
txtComli_Num = strFeedYear & Mid(strCompli, 3,2) & Format(CInt(Right(strCompli, 3)) + 1, "000&quot
End If

 

[wdu94]

Hi, Nancy,

Thanks again. I got problem: when I open the form one time, the Compli_num auto_increament one time. If I open the form more times, the compli_num auto_increament more times.

Thanks alot.



The following are my codes:

Private Sub Form_Current()
Dim strCompli As String
Dim strFeedYear As String

strFeedYear = Format(DATE, "yy&quot
If IsNull(txtCNUM) = True Then
strCompli = DMax("COMPLI_NUM", "COMPLAINTS&quot

txtCNUM = strFeedYear & Mid(strCompli, 3, 2) & Format(CInt(Right(strCompli, 3)) + 1, "000&quot

End If

End Sub

 

[Nancy2]

The check for null should handle this -- but try putting it on the After Insert event vs the On current event - let me know if it works.

 

[Nancy2]

Sorry - meant the Before Insert Event...

 

[wdu94]

Hi, Nancy,

Still not work. The Compli_num didn't show up in the form.

 

[Nancy2]

Did you save the record and go back and view it?

Here's another option - create a custom function (module) which returns the value and use it as the default value on the field bound to the Compli_num - then you will see it right away.

So the default value of Compli will be = FeedCompli()

Function FeedCompli() as string

Dim strCompli As String
Dim strFeedYear As String

strFeedYear = Format(Date, "yy&quot

strCompli = DMax("Compli_Num", "YourTableName&quot
FeedCompli = strFeedYear & Mid(strCompli, 3,2) & Format(CInt(Right(strCompli, 3)) + 1, "000&quot

end function

 

[wdu94]

Nancy,

Jeannia, again.

The same problem as I first got. The Compli_num not really add in the table, therefore, the form still show the same compli_num, not auto_increament compli_num after I add new info.

 

[Nancy2]

1) Do you wish to fill the field Compli_num in the table?

2)Is the control source of txtComli_Num (in the form) set as Compli_num?

If so it should fill the field. If you are still stuck you can email me the form and table if you'd like

Sorry if I'm just not getting this....