Input Mask and Autonumber Issue

[ralphralph07]

Hi

I have inherited a database that needed to be updated from 97 to 2000/2003.

I have table that has a field set with the following input mask:

Logging Number: >CC0/\ 000

This creates a unique number based on the month and transaction - for example JY8/003 I'm now getting duplicate errors as the database has been running since 98
so need to change the code so that it stores 2008 after the JY but i'm unsure where to start on the code?

Dim current_month As Integer, current_number As Integer, current_year As String
Dim month_code As String
                                                'set values of system variables
current_month = DLookup("c_month", "system values")
current_number = DLookup("last_num", "system values")
current_year = Right$(DatePart("YYYY", Now), 1)

                                                'Check to see if month is correct
If DatePart("M", Now) <> current_month Then
    Call reset_system_month                     'reset month to correct value
    current_month = DatePart("M", Now)
    current_number = 0
End If
                                                
month_code = DLookup("month", "month codes", "[month_num]=" & current_month)


'MsgBox "Last number is " + month_code + current_year + "/" + Format((current_number), "000")

Forms![logging screen].[Lab Ref No] = month_code + current_year + "/" + Format((current_number + 1), "000")

Any guidance greatly received as always

Ralph

 

[HarleyQuinn]

The code does store the 4 digit year after the month_code in the textbox...

You could do away with the input mask as the code generates the value you want to see in the box anyway doesn't it?

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[ralphralph07]

Hi

It looks like it should be what it actually stores is:

JY8/001

What I need it to do is JY2008/001 etc?

Thanks

Ralph

 

[ralphralph07]

Hi
I've resolved this problem now just have a new problem that i will post separately

Thanks for help

Ralph