Thanks ZMBD for taking time to write to me.
I am trying to have increment number on my form from a list. I have
I think the below points will give you clear picture of the database.
1. I have 3 tables:
a. tblBV
fields: BVID / BVCode / BVDate / CoNum / Preform
b. tblCoName
fields: CoID / PrefixCocode / CoNameCode
c. tblPreForm
fields: Preform
2. I have 3 Queries:
a. q-BV
fields: BVID / BVCode / BVDate / CoNum / Preform / PrefixCocode
b. q-CoCode
fields: CoID and another field as CoCode: [PrefixCocode] & "-" & [CoNameCode]
c. q-BVLkp
fields: BVCode with total as MAX and another field as Left([BVCode],2)with total as Group By
I am trying to have increment numbers together with company code.
I have 4 companies:
1. BCI 2. CMDC 3. HAPC 4.NAL
Short codes of the company:
1. BCI = BD 2. CMDC = CD 3. HAPC = AD 4. NAL = ND
I wanted to have increment numbers with short code of the company whenever we click on a list.
Example:
1. BD13-0001 2. BD13-0002 3. BD13-0003 and so on.
2. CD13-0001 and so on.
3. AD13-0001 and so on.
4. ND13-0001 and so on.
I am using below code on Double Click of list called list22 as given below.
- Private Sub List22_DblClick(Cancel As Integer)
-
-
Select Case Text60
-
Case "AD"
-
Me.Text62 = "0"
-
Case "BD"
-
Me.Text62 = "0"
-
Case "CD"
-
Me.Text62 = "0"
-
Case "ND"
-
Me.Text62 = "0"
-
End Select
-
-
Me.BVCode = Me.Text24
-
-
Select Case BVCodePrefix
-
Case "BD"
-
Me.CoNum = 1
-
Case "CD"
-
Me.CoNum = 2
-
Case "AD"
-
Me.CoNum = 3
-
Case "ND"
-
Me.CoNum = 4
-
End Select
-
-
Me.Recalc
-
-
End Sub
-
-
-
Private Sub List22_GotFocus()
-
Me.Refresh
-
DoCmd.GoToRecord , , acNewRec
-
-
End Sub
-
-
-
Private Sub BVCode_AfterUpdate()
-
-
Select Case BVCodePrefix
-
-
Case "BD"
-
Me.CoNum = 1
-
Case "CD"
-
Me.CoNum = 2
-
Case "AD"
-
Me.CoNum = 3
-
Case "ND"
-
Me.CoNum = 4
-
End Select
-
-
End Sub
Now, when we give “0” in select case of Text60, the numeric portion is incrementing fine after 0100 and so on. But if we start from 0001 it is giving error as “Run time error, the value you entered is not valid for this field”
- Select Case Text60
-
Case "BD"
-
Me.Text62 = "0"