Number Format in Select Case

[tajuddin]

Hi,

1. I have one unbound textbox on a form (Text62).
2.The another textbox on a form is (Text60) & the control source is =Left([List22].[Column](0),2)
3. The third textbox on a form is (Text24) & the control source is =Left([List22].[Column](0),5) & [Text62] & Right([List22].[Column](0),4)+1

Now, I am using below code to call the number in 4 digit format from Text62 in VBA.I need number format which I think I am not able to give the expression after (me.text62= ?????)

If I give "00" it is working fine starting from 0100 but when I change the start value 0001 the answer is 00-01 & giving VB error.

Can anyone advice.

Private Sub List22_DblClick(Cancel As Integer)

Select Case Text60

    Case "BD"
        Me.Text62 = "00"
End Select

Me.BVCode = Me.Text24

[zmbd]

Which version of Access are you using.

What is the EXACT error NUMBER and TEXT. Please no generic, post the EXACT number and text of the error.

The code you posted is not really doing anything at all, other than setting the value of the control "Text62" to the string value of "00" if and only if the value of "Text60" is the exact value of "BD" - note case matters.
As for what this code does, as compared to what you describe in your post, there is no correlation. If this is some generic code snip you are trying to use as an example, please do not do that.... we can only provide relevant help if you provide the actual code.

[tajuddin]

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"

[zmbd]

- Lines 3 thru 12 make no sense at this stage of your coding.
- "“Run time error, the value you entered is not valid for this field” Is this the entire error? If the second posted code is where the error is occurring, then check the control source property for "Text62." I suspect that this is bound to a table field that is typecast as a numeric and what you are entering is a string value ("0" - the quotes make this a string) hence the error.

What you should be doing is explicitly typecasting all of your variables, I prefer at the start of the code; however, others have different viewpoints.

Your first step to accomplishing this will be to follow the setup and troubleshooting section for VBA found here: > Before Posting (VBA or SQL) Code

Once you set your Option Explicit in the VBE options you will then need to add this to each of your existing forms and modules.

Until you type cast/define all of your variables the compiler will complain about this.... this may take you some time; however, it is the best practice.

Once you can compile your code without error, we can take the next steps.