Solved Dynamicly changing the format of text box to show leading zeros

[LanaR]

I'm populating an unbound text box from a column in a combo box. This combo is selecting a suburb from a rowsource dependent on the country of origin.

For each country, the post code is a fixed numeric length. Each of the tables is formatted to show the leading zero, and this displays just fine in the combos dropdown list. This formatting gets lost, however, when the value is passed to the unbound text box.

Based on the formatting in the table, I figured the following code in the On Current event should do the trick, but I seem to be missing something as it's not having the desired effect

Dim PCOdeFmt As String
    
        If IsNull(Me.CountryID) Then
            Exit Sub
        End If
    
        PCOdeFmt = Nz(DLookup("PCodeFrmt", "TBL_UNCountry", "CountryID = " & Me.CountryID), "Null")

        Select Case PCOdeFmt
        
        Case "Null"
        
        Case "NNN"
            Me.Text21.Format = "000"
        
        Case "NNN NN"
            Me.Text21.Format = "000 00"
        
        Case "NNN NNN"
            Me.Text21.Format = "000 000"
        
        Case "NNN NNNN"
            Me.Text21.Format = "000 0000"
                
        Case "NNNN"
            Me.Text21.Format = "0000"
        
        Case "NNNNN"
            Me.Text21.Format = "00000"
        
        Case "NN-NNN"
            Me.Text21.Format = "00-000"
        
        Case "NNNNNN"
            Me.Text21.Format = "000000"
        
        Case "NNN-NNN"
            Me.Text21.Format = "000-000"
            
        End Select

 

[Gasman]

Why not just store the format in the table?
Have you walked through that code and inspected variables?

 

[theDBguy]

Just curious, why use a Format? Why not just store the actual values with leading zeroes?

 

[CJ_London]

you could have simpler, shorter code

Dim PCOdeFmt As String

PCOdeFmt = Nz(DLookup("PCodeFrmt", "TBL_UNCountry", "CountryID = " & Me.CountryID), "Null")
if  PCOdeFmt <>"Null" then Text21.Format=replace(PCOdeFmt,"N","0")

This formatting gets lost, however, when the value is passed to the unbound text box.

as it's not having the desired effect

which means what?

To answer your question please answer these:

• how are you passing the value to the unbound textbox?
• provide some examples of the unformatted data, the outcome you are seeing and the outcome you require.
• Is this on a single form or a continuous form?

 

[LanaR]

Why not just store the format in the table?
Have you walked through that code and inspected variables?

Yep, the table is formatted to store leading zeros.

Yes, the variables are picking up the values I'm expecting

 

[LanaR]

Just curious, why use a Format? Why not just store the actual values with leading zeroes?

Because Access will drop off the leading zero in numeric fields. I'm going to try storing the codes as Short Text which should fix the problem

 

[LanaR]

you could have simpler, shorter code

Dim PCOdeFmt As String

PCOdeFmt = Nz(DLookup("PCodeFrmt", "TBL_UNCountry", "CountryID = " & Me.CountryID), "Null")
if  PCOdeFmt <>"Null" then Text21.Format=replace(PCOdeFmt,"N","0")

which means what?


To answer your question please answer these:

• how are you passing the value to the unbound textbox?
• provide some examples of the unformatted data, the outcome you are seeing and the outcome you require.
• Is this on a single form or a continuous form?

Thanks that code is a whole lot simpler

In answer to your questions;

1. Me.Text21 = Combo11.Column(2)
2. 01234 stored appears as 1234
3. Single form

 

[LanaR]

Storing the number as short text and adding the leading zero back on, does the trick.

However, I'm still interested on an academic level whether or not this can be achieved using code

 

[theDBguy]

Storing the number as short text and adding the leading zero back on does the trick.

However, I'm still interested on an academic level whether or not this can be achieved using code

Glad to hear you got it sorted out. Technically, numbers with leading zeroes are not really numbers at all. So, using a Text data type makes more sense. If you wanted to convert the number text into real numbers in queries or code, it wouldn't be a problem either.

 

[Gasman]

Yep, the table is formatted to store leading zeros.

Yes, the variables are picking up the values I'm expecting

No, I meant instead of storing NNNN in any format, just store the 0000 and apply that.

 

[LanaR]

No, I meant instead of storing NNNN in any format, just store the 0000 and apply that.

My bad