Generate Custom ID instead of using AutoNumber 1

[delontan]

I need to generate a custom unique value for my primary key field (i.e order id). so far, i had little success by using functions in the default value but i still couln't get the other part to work.(add the other 4 auto numbers.)

my primary key
Order ID is a combination of 1st letter of the month, followed by year (4 digits), then 3rd letter of current month, then 4 digits (from 0000-9999) [this is where i got stucked].

for i.e. A2004R0001
A2004R0002 and so on.....

this is what i have in my general field properties - default value,

=Mid(MonthName(Month(Date()),True),1,1) & DatePart("yyyy",Date()) & UCase(Mid(MonthName(Month(Date()),True),3,1))

Example of my table
field data type
======== =========
Order ID Text
Customer ID Text
Date Date/Time

i am thinking maybe i could use 2 field, one for the already made default value, and one for the autonumber and then combine them together in the 3rd field. but the problem is for different month, it will start from 0001.
(btw, i dunno how to combine 2 fields into one field).

please help. thanks!

 

[jksmi]

Hi, you wont be able to use this function as a default value or update query but it will work from a form and might give you some ideas to try...

Public Function IDGen() As String
Dim strMonth As String
Dim strRetVal As String
Dim strCount As String

strMonth = UCase(MonthName(Month(Date), True))

strRetVal = Mid(strMonth, 1, 1) & DatePart("yyyy", Date) & Mid(strMonth, 3, 1)

strCount = Nz(DMax("DUMMY_ID", "TEST_IDS", "[DUMMY_ID] Like '" & strRetVal & "*'"), 1)
strCount = Right("000" & CLng(Right(strCount, 4)) + 1, 4)

strRetVal = strRetVal & strCount
IDGen = strRetVal

End Function

HTH, Jamie

 

[delontan]

i am trying to create the order table with the custom id generator as primary key to be used in a website (will use front page to create the query and asp pages). i am new to implement database in a website (that's why i am using frontpage), so, how and where do i put the function? i can't use the function in the dafault value (obviously), and that the function will automatically update my order table primary key with every new record? what do u mean by it will work on a form? plz advise..thanks!

 

[BNPMike]

what do u mean by it will work on a form? plz advise..thanks!

This is an Access forum. We didn't realise you were actually asking a question about Frontpage working with the Jet Database.

In Access you can put process code in form modules. Presumably with Frontpage you need to do this in an ASP back-end script or write Javascript scripts on your HTML page.

 

[delontan]

well, just forget about the frontpage thing. i just need the order field value to be unique and have the data format and auto values that i mentioned earlier. once that value can be automatically generated, then i can manipulate the field values in many different ways. i am going to try to put the process code in form modules. (never done that before) and see how the value can be generated. thanks!

 

[delontan]

alrite, i create the module with the IDGen() function and saved it. then i created a form that includes all the table fields. then i open up the form in design view and i enter =IDGen() in the default value in the properties of my Order ID field(text box). It gave me errors on the TEST_ID whenever i try to input new record in the form.

 

[jksmi]

Hi,

in the Form_Current event add

If IsNull(Me.TXT_ID) Then
    Me.TXT_ID = IDGen
End If

where TXT_ID is your (enabled but locked) bound control to ID...

HTH, Jamie