Auto Increment To dbText Field

[alwsid]

Hello...

I've field name InvoiceID which as dbtext field.
So how to format my InvoiceID auto Increment which can store
string and number.

Ex:
A 1 until A 1000 then change to B 1 until B 10000 and so on.

so how to do that.

waiting response from you all...

[HunterMcCray]

I would suggest for ease of coding that you create three fields: LineRef as AutoNumber , Letter as Text, LetterSeq as Number. In code you can select the Letter using an SQL Query that orders by LetterSeq. MoveLast and add one to the letterSeq and then insert the record. Using LineRef as a primary key will help the speed of the table. Make Letter and LetterSeq a unique Index to prevent accidental duplication.

Hope that this helps

Hunter

[alwsid]

Thanks for reply... HunterMcCray

But for nice to me, hope you can show more detail like
code snippet to very clear.

Still waiting response...

[HunterMcCray]

In Access in the Table where this needs to be create three fields:


LineRef as AutoNumber 'Primary Key
Letter as String * (however many characters you need)
Number as Number 'Whatever type of number you need
Field1
Field2
Field3
.
.
.
.
FieldX

In Visual Basic When you want to add a record with a certain letter prefix use the following

Private Function AddRecord(Let as String)

Dim txtSQL as String
Dim rs as Recordset
Dim WS as WorkSpace
Dim db AS DataBase
Dim DbDir as String
Dim LastNum as Number Type You are Using

DbDir= DataBase Path and Name

Set Ws = DBEngine.Workspaces(0)
Set db = Ws.OpenDatabase(DbDir)

txtSQL="SELECT [TableName].* FROM TableName WHERE [TableName].[Letter] LIKE " & Chr(39) & Let & Chr(39) & "ORDER BY [TableName].[Number];"

Set rs=db.OpenRecordset(txtSQL, dbOpenDynaset)

if rs.recordcount>0 then
'Code to add new record with Letter=Let and Number=1
else
rs.movelast
LastNum=rs!Number + 1
rs.AddNew
rs!Letter = Let
rs!Number = LastNum
rs!Field1=Something
rs!Field2=Something Else
.
.
.
rs!FieldX=SomeThing LAST
rs.Update
end if

set rs=nothing
AddRecord=Let & LastNum
End Function


When you want to view the letter and the number as a single unit then simply compile them with StringVariable=Let & Num. You can do this in code or in your SQL statement.


Hunter

[alwsid]

Thanks HunterMcCray for your Idea. Hope this can help me...

[HunterMcCray]

Originally posted by alwsid
Thanks HunterMcCray for your Idea. Hope this can help me...

Sorry, I put the wrong code in the wrong if condition, it should have read:

if rs.recordcount>0 then
rs.movelast
LastNum=rs!Number + 1
rs.AddNew
rs!Letter = Let
rs!Number = LastNum
rs!Field1=Something
rs!Field2=Something Else
.
.
.
rs!FieldX=SomeThing LAST
rs.Update
else
'Code to add new record with Letter=Let and Number=1
end if

Hope it helps, sorry for the mistake,

Hunter