Any way to retrun Unique Code ?

[Guest_imported]

I am having a customer database while adding a new customer information i want a way by which Vb-6 can assign a unique code to this new customer.

 

[Andy0625]

This will work, although I'm sure there are many more elegant solutions:

Public Function NewCode() as long
dim intFileNo as integer
dim lngCode as long
intFileNo = freefile
open app.path & "\CodeNoFile.DAT" for random as #intFileNo len = 4
if LOF(intFileNo) = 0 then 'first code number
lngCode = 1000
Put #intFileNo,1,lngCode
else 'get a new code number
Get #intFileNo,1,lngCode
lngCode = lngCode + 1
Put #intFileNo,1,lngCode
End If
close #intFileNo
NewCode = lngCode
End Function

(I just typed this and haven't put it into VB to test for typos, etc, so can't guarantee all details! The idea should work, though.)

 

[dpaulson]

If you are use a database, couldn't you just use a field set to a long integer with auto increment?
David Paulson

 

[MickTheBelgian]

watch out for collisions: several people creating a new customer Id at the same time...
You might simply define a Long and assign a random value to it. Probability to use the same number twice = number of customers / 4,000,000,000

 

[swilliams]

Where do you get that probability from?? Isn't the maximum long value somewhere in the region of 2,147,483,000??

Anyway, I would not recommend doing it this way. I would either
1. have an AutoNumber field in Access, or if you are using SQL Server 7, have an int field and set the Identity Increment property to 1 (increments 1 each record, as with AutoNumber).
OR
2. Have a routine to get the previous highest customer number and add 1 to this JUST BEFORE WRITING THE NEW CUSTOMER INFORMATION TO THE DATABASE.
This should be done just before writing the customer to the database to minimise the likelihood of getting two customers with the same customer id.
OR
3. Have a control table in your database which looks like this:

ItemDescription ItemValue InUse
Customer Number 15 0

When you are about to write the customer record to the database, set the InUse field to 1 if is not already 1. Read the ItemValue and increment by 1. Write the customer record using this new value as the customer id, and if this is successful, update the record in the control table to hold the new highest customer number and 0 in the InUse field.
If the InUse field is already 1 when you check to see if you can use the ItemValue field to get the next customer id, go into a loop reading the InUse field 1000 times. It is fairly safe to say that if the InUse field is 1 each time the record is read then there was a crash somewhere and the field has not been reset to 0, so go ahead and use the ItemValue anyway.

I would recommend #1 if you are happy using AutoNumber type fields - the disadvantage of this is that you have to requery the database to get the customer id of the current customer if you want to write order information at the same time using the customer id - , otherwise I would recommend #2 as #3 involves a lot of work and there is still a chance of getting duplicated customer id's (although I think this chance will be less than #2).
Simon

 

[chiph]

You could call the GetGUID function that's part of OLE. This function creates a Globally Unique ID that is a 15-byte string that is based on diverse things such as your network adapter address, the date and time, hard drive size, and phase of the moon . Here's some sample code from the knowledgebase:

[tt]Option Explicit

' All this stuff is based on code found in Q176790.

Private Declare Function CoCreateGuid Lib "OLE32.DLL" (pGuid As guid) As Long
Const S_OK = 0 ' Api call went off OK

Private Type guid ' UDTs must be private in class module, no problem
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(7) As Byte
End Type

Public Function GetGuid() As String
Dim lResult As Long
Dim lGuid As guid
Dim strEndGuid As String
Dim strGuid(3) As String
Dim iDataLen As Integer
Dim iStringLen As Integer
Dim i As Integer

lResult = CoCreateGuid(lGuid)

' Format this pup

If lResult = S_OK Then
' First 4 bytes
strGuid(1) = Hex$(lGuid.Data1)
iStringLen = Len(strGuid(1))
iDataLen = Len(lGuid.Data1)
strGuid(1) = LeadingZeros(2 * iDataLen, iStringLen) & strGuid(1)

' Next 2 bytes
strGuid(2) = Hex$(lGuid.Data2)
iStringLen = Len(strGuid(2))
iDataLen = Len(lGuid.Data2)
strGuid(2) = LeadingZeros(2 * iDataLen, iStringLen) & Trim$(strGuid(2))

' First 2 bytes
strGuid(3) = Hex$(lGuid.Data3)
iStringLen = Len(strGuid(3))
iDataLen = Len(lGuid.Data3)
strGuid(3) = LeadingZeros(2 * iDataLen, iStringLen) & Trim$(strGuid(3))

GetGuid = strGuid(1) & "-" & strGuid(2) & "-" & strGuid(3) & "-"

For i = 0 To 7
strEndGuid = strEndGuid & Format$(Hex$(lGuid.Data4(i)), "00&quot
Next i

strEndGuid = Left$(strEndGuid, 4) & "-" & Mid$(strEndGuid, 4, 12)

GetGuid = "{" & GetGuid & strEndGuid & "}"

End If

End Function

Private Function LeadingZeros(ExpectedLen As Integer, ActualLen As Integer) As String
LeadingZeros = String$(ExpectedLen - ActualLen, "0&quot
End Function
[/tt]

Hope this helps.
Chip H.

 

[RayMan]

If using Oracle use a sequence counter. Never a replicated key.

Rayman