In Form, String Field cant display leading zero

[ChocQu]

Hello everybody,

I have problem please someone can solved my problem

I want to create a table, the primary key(Name : No Invoice] is string with 10 length.
(use for numerical string : 4 for number, 2 for month, 4 for year)
Example 0001042017; 0032042017

In my form i create modul that can create the number automatically
-number increment automatically when new record is saved

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim MyForm As Form
Dim intnewrec As Integer
Set MyForm = Screen.ActiveForm
intnewrec = MyForm.NewRecord

If intnewrec = True Then
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Penomoran")
'This Table save my current [No Urut]
rst.MoveFirst
rst.Edit
rst![No Urut] = rst![No Urut] + 1
rst.Update
rst.Close
dbs.Close
End If
End Sub

Private Sub Form_Current()
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Penomoran")
Dim Nomor As String
If IsNull(Me.No_Invoice) Then
rst.MoveFirst
Nomor = Right("0000" & rst![No Urut], 4) + _
Right("00" & rst!Bulan, 2) + _
Right("0000" & rst!Tahun, 4)

MsgBox Len(Nomor) & vbCr & Nomor
' Len(nomor) = 10 - correct already
'Nomor = "0007042017" - c0rrect already"

'Set default value in field noInvoice
Me.No_Invoice.DefaultValue = Nomor
'its show "7042017"
'Even try this, its also the same occur leading zero is disappear
'Me.No_Invoice.DefaultValue =Right("0000000000" & Nomor, 10)

'if i direct to value its show "0007042017"
'with
me.No_Invoice = Nomor
, correct but mycurrent record are in add new record mode/editing mode

End If
rst.Close
dbs.Close
End Sub

Need any help to solve

 

[The_Doc_Man]

If the leading zeroes disappear, the field in question has to be numeric, because that trick of concatenating with a string of zeros to the left should do the job for you if you are working with a string.

 

[gemma-the-husky]

Me.No_Invoice.DefaultValue = Nomor

this field no_Invoice must be numeric in your table/form

Note that if so, then even without the leading zeroes, you would probably get an over flow problem above 2 billion or so, as you would be exceeding the maximum value for a long integer.

You are much better not to concatenate the parts into a compound field, to be honest. Just have a compound key with the 3 elements.