Input mask for separated values

Question

Hi,

I don't even know if this is achievable.  I have been asked to create an application that will track deliveries of goods.  The goods are sold in tons, but shipped in rolls on pallets.  It is useful for the user to know the various sizes of rolls on pallets.  In the past they have used Excel, but have never been able to effectively filter / analyse on the field as people would use different conventions for entering the data.  For example:
1: Range) "6.5 > 8.6"   OR   "6.5/8.6"   OR   "6.5-6"
2: Individual values) "4.0,7.2"   OR   "4.0;7.2"   OR   "4.0 7.2"

What I would like to do is use an input mask that allows decimals separated:
">" = a range
";" = will represent individual values

What would be even cleverer would be to not allow ">" if a ";" is used, and vice versa.  If that can't be done with an input mask I can do it using VBA I reckon.

Is this possible?  I have not been able to make it work so far.

Thanks.

Answer 1

If the values are always the same, just make a multi-select-combobox list, then when your users select from the list, your formatting will always be the same.

---

Chris Ward

Answer 2

Hi Chris,

I never knew about mult-select-combo boxes.  Just finished reading about them now.  The last DB I built was in Access 2000, the product sure has come along way since then.

Unfortunately they aren't always going to be the same so a list won't work.  What I can't do is create multiple fields because a) I would never know how many fields could be the maximum ever required, and b) that's just a mess.

So I was hoping to just force some kind of rule on the string.  VBA is probably the best way I guess, but imagining the lines of code in my head I see an essay.  Maybe not so bad

What I was thinking would be to force the user to prefix the string with "R:" for a range and "I:" for a list of individual values.  Then I should be able to GoTo the code in my After_update proc that checks that only decimals are used and all numbers are separated by " > " for a range and "; " for individual values.  Not sure how I do the last part just yet, will have to sit down with it.  Any pointers?

Thanks,

Charles

Answer 3

Charles,

 

    You could try sample code in form's module as given below.

 

    Txt_A is the name of text box  while OpgMask is an option group as per which the input mask for Txt_A gets toggled.

 

    You can download a copy of demo file Form_InputMaskConditional.zip (Access 2000 file format) at the following link:

https://dl.dropbox.com/u/51700735/Form_InputMaskConditional.zip

 

Best wishes,
A.D. Tejpal
------------

 

' Sample code in form's module:

=================================

Private Sub Form_Load()
    Me.Txt_A.InputMask = "\R\:\ 9\.9\ \>\ 9\.9;0;_"
End Sub

'-------------------------------------------------

 

Private Sub OpgMask_AfterUpdate()
    Me.Txt_A.SetFocus
   

    ' Toggle the mask only if no entry has yet been made
    If Me.Txt_A.Text Like "*_._ > _._" Then
        Me.Txt_A.InputMask = "\I\:\ 9\.9\ \;\ 9\.9;0;_"
    Else
        If Me.Txt_A.Text Like "*_._ ; _._" Then
            Me.Txt_A.InputMask = "\R\:\ 9\.9\ \>\ 9\.9;0;_"
        End If
    End If
   
    Me.OpgMask.SetFocus
    Me.Txt_A.SetFocus
End Sub

'-------------------------------------------------

 

Private Sub Txt_A_Click()
    Me.Txt_A.SelStart = 3
    Me.Txt_A.SelLength = 0
End Sub

'-------------------------------------------------

 

Private Sub Txt_A_Enter()
    Me.Txt_A.SelStart = 3
    Me.Txt_A.SelLength = 0
End Sub

'=================================

 

----- Original Message -----

From: Charles__Toray

Newsgroups: Msdn.en-US.accessdev

Sent: Tuesday, July 03, 2012 12:52

Subject: Re: Input mask for separated values

Hi Chris,

I never knew about mult-select-combo boxes.  Just finished reading about them now.  The last DB I built was in Access 2000, the product sure has come along way since then.

Unfortunately they aren't always going to be the same so a list won't work.  What I can't do is create multiple fields because a) I would never know how many fields could be the maximum ever required, and b) that's just a mess.

So I was hoping to just force some kind of rule on the string.  VBA is probably the best way I guess, but imagining the lines of code in my head I see an essay.  Maybe not so bad

What I was thinking would be to force the user to prefix the string with "R:" for a range and "I:" for a list of individual values.  Then I should be able to GoTo the code in my After_update proc that checks that only decimals are used and all numbers are separated by " > " for a range and "; " for individual values.  Not sure how I do the last part just yet, will have to sit down with it.  Any pointers?

Thanks,

Charles

---

A.D. Tejpal

Answer 4

Excellent!  Thanks Tejpal.