Solved

Use Input Mask to autofill zeros in Client ID field

Posted on 2003-12-02
3
586 Views
Last Modified: 2011-10-03
Hi Guys,
I am wondering if I can use an input mask to achive the following:

I have a client ID field in a Customers table in my database.

The Client ID is of the following form(all ID's have to be 10 chars long for uniformity):
03/0000123

03 is the year and then a literal forward slash separates year part from unique number part which must be 7 digits filled left with zeroes.

So with the above example in mind I would like to set up an input mask so all the user has to tap on the keyboard is 0 3 / 1 2 3
and what is displayed in field and saved is 03/0000123
i.e. the system automatically left fills the 4 zeroes before 123 to make sure the Clients ID total length is 10 characters  

Is this possible using Input Mask


Cheers


________
B Cunney

0
Comment
Question by:Barry Cunney
  • 2
3 Comments
 
LVL 5

Accepted Solution

by:
DarkMor earned 100 total points
ID: 9884449
Instead of an Imput Mask use VB Code to accomplish this on the afterupdate event of the cell where they enter 03/123.

Private Sub txMyText_AfterUpdate()
Dim DelimPos
Dim strText As String

    strText = txMyText.Value
    'Verify Delimiter in the string
    DelimPos = GetFirstOccurance(strText, "/")
    If DelimPos = 0 Then
        MsgBox ("Invalid Value")
        Exit Sub
    Else
    'Fill Before Delimiter
        If DelimPos < 3 Then
             strText = PadLeft(Left(strText, DelimPos), "0", 3 - DelimPos) & Right(strText, Len(strText) - DelimPos)
             DelimPos = 3
        End If
    'Fill After Delimiter to Value
        If Len(strText) < 10 Then
            strText = Left(strText, 3) & PadLeft(Mid(strText, DelimPos + 1, Len(strText) - 3), "0", 10 - Len(strText))
        End If
    'set new value
    txMyText.Value = strText
    End If
   
End Sub
Function GetFirstOccurance(str As String, char As String) As Integer
'Returns first occuance of char within str
Dim pos
Dim Found As Boolean
    pos = 1
   
    Do While pos <= Len(str) And Not Found
        If Mid(str, pos, 1) = char Then
            Found = True
        Else
            pos = pos + 1
        End If
    Loop
   
    If Found Then
        GetFirstOccurance = pos
    Else
        GetFirstOccurance = 0
    End If
End Function
Function PadLeft(str As String, char As String, buffer As Integer) As String
'Pad left of str with char buffer times
    For padding = 1 To buffer
        str = char & str
    Next padding
    PadLeft = str
End Function


0
 
LVL 5

Expert Comment

by:DarkMor
ID: 9983072
BCunney,
     Did not hear back and just wanted to know if the example worked for you

Enjoy the Holidays

D
0
 
LVL 17

Author Comment

by:Barry Cunney
ID: 10034875
Hi DarkMor,
Sorry for the delay in getting back to you but I was busy with other things.

Thank you very much for the solution above - it worked perfectly.
That's the best help I 've seen anyone giving on EE.

Hope to touch bases with you again


Many Thanks


B Cunney
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question