User Input Control Beyond Input Masks (1 Viewer)

DeanFran

Registered User.
Local time
Today, 10:04
Joined
Jan 10, 2014
Messages
111
I have an Access application that requires a date input in this format, YYMMDD with the day being the last day of the month. This date becomes part of a bar code as an expiry date code, that gets printed on a label. I have guidance text right above the textbox that explains how to input the proper sequence, a little grid showing the last day of the month for all 12 months and a mask that requires 6 numbers. This application has been trucking along for over a year now, and we just had our first screw up. The *only* user of the system, who has successfully input hundreds of date codes, input the date as MMDDYY, and a bushel of labels were printed before anyone caught the mistake. Is it possible to lock down the user input more completely using VBA? I'm thinking parse the input to ensure the first two digits are greater than the present year, next two are 1 through 12 inclusive, and the last two automatically populate according to the month number. I'm not looking for a how to, but any guidance on what concepts I should start investigating are appreciated.
 

plog

Banishment Pending
Local time
Today, 09:04
Joined
May 11, 2011
Messages
11,613
To reduce user errors, you reduce the opportunities the users have to do so. Make getting the data you actually need from them dead nuts simple. From what I hear you simply need 2 drop downs for them to enter--One for the month (1-12) and one for the year (populated with all valid years). So make that the user interface.

With just those 2 numbers you can then caclulated the last day of that month and then format that date any way you want it for use wherever. Store just those 2 numbers and the user will never make a formatting error again...

...just factual errors about not entering the correct data. Good luck preempting those.
 

Mark_

Longboard on the internet
Local time
Today, 07:04
Joined
Sep 12, 2017
Messages
2,111
For myself, if it is a date, treat it as a DATE.

You do not need to force a specific format for entry. You simply need to show the end user the DATE they entered in a way that makes them very sure it is proper.

My validation would be
if ME.[YourDATEfiled] < NOW() THEN
msgbox "You put in a date before today!"
End If

I would also have a label that is filled in when they enter the new date.
lbl_ShowDate.Caption = format( YourDateField,"YYYY MMM DD") so they can see it also.

In your code for creating your barcode, just use the DATE field but format(YourDateField,"YYMMDD").

From the description, you are treating a DATE as a string. I'd treat is as a DATE and worry little about how the user enters said date, just validate that the results are reasonable.
 

Solo712

Registered User.
Local time
Today, 10:04
Joined
Oct 19, 2012
Messages
828
I'm thinking parse the input to ensure the first two digits are greater than the present year, next two are 1 through 12 inclusive, and the last two automatically populate according to the month number. I'm not looking for a how to, but any guidance on what concepts I should start investigating are appreciated.

It is possible, sure, an it's a good idea. But if an expiry date can be calculated by a formula (the life of the product seems to be a constant from what you write), and it is say the last day of the month that is 12 months from today, then you don't need to enter anything. The date could be calculated in whatever form you choose, with no opportunity for user data entry errors.

Best,
Jiri
 

DeanFran

Registered User.
Local time
Today, 10:04
Joined
Jan 10, 2014
Messages
111
1. I hadn't even thought of the idea of drop down boxes, that would help.
2. Yes I am using a string for this. My own lack of skills led to this. My original intention was to just use a calculation of the present date plus the expiry interval, but there were a couple problems. First, the requirement of the last day of the month threw me a curve ball I didn't know how to handle. Second, The data entry person wanted to be able to enter a bunch of entries for future runs at the same time, so it was possible that a set of data could be entered in Dec. 2017, and not actually manufactured until January 2018. I could have added the requirement for a date of manufacture and used that in my calculations, but this a property that is subject to a lot of possible change, and would require the data entry person to keep it up to date. I just saw this as a source of errors in and of itself, so I abandoned that idea.
 

Minty

AWF VIP
Local time
Today, 14:04
Joined
Jul 26, 2013
Messages
10,355
Taking the suggestions a stage further - we have a default warranty period, that can be adjusted by either a customer specific or job type override.

It's calculated from todays date and we simply add a number of days or months depending on the requirements.

So you could probably automate the complete process including the decision making.
 

Mark_

Longboard on the internet
Local time
Today, 07:04
Joined
Sep 12, 2017
Messages
2,111
First piece of advise, never store a DATE in a STRING. Not horrible but it leads to the issues you are encountering.

Second, if you have a base date that you are working with, say "Dt_Mfgr" for "Date Manufactured", you can use the following to create and expiration date that is the end of the month six months from now:

Dt_Exp = DateSerial( Year( [Dt_Mfgt]), Month [Dt_Mfgt]) + 7, 1) -1

I'd then let a user edit this, if needed.
 

MarkK

bit cruncher
Local time
Today, 07:04
Joined
Mar 17, 2004
Messages
8,178
If I had a small set of fixed text responses that I could calculate I would not allow the user to type them in, rather, I would calculate them in advance and only allow the user to select them from a list or combo.

Also, the last day of this month is the 0th day of next month...
Code:
Function LastDayOfMonth(d1 As Date) As Date
   LastDayOfMonth = DateSerial(Year(d1), Month(d1) + 1, 0)
End Function
...and you could populate a combo as simply as...
Code:
Function GetComboRowSource() As String
    Dim i As Integer
    Dim tmp As String
    Dim d1 As Date
    
    For i = -6 To 6                                  [COLOR="Green"]'from 6 months ago to 6 months hence[/COLOR]
        d1 = DateAdd("m", i, Date)                   [COLOR="green"]'get date offset from today[/COLOR]
        d1 = DateSerial(Year(d1), Month(d1) + 1, 0)  [COLOR="green"]'get the last day of that month[/COLOR]
        tmp = tmp & ";" & Format(d1, "YYMMDD")       [COLOR="green"]'format and accumulate[/COLOR]
    Next
    
    If Len(tmp) Then GetComboRowSource = Mid(tmp, 2) [COLOR="green"]'drop leading ";"[/COLOR]
End Function

hth
Mark
 

Mark_

Longboard on the internet
Local time
Today, 07:04
Joined
Sep 12, 2017
Messages
2,111
Hm... I've always subtracted 1... Should have been using 0 all along... Have to remember that trick!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:04
Joined
May 7, 2009
Messages
19,175
supposed that your textbox is named Text0,
write code to the Keydown Event of this textbox:
Code:
Private Sub Text0_KeyDown(KeyCode As Integer, Shift As Integer)
    Dim intLength As Integer
    Dim strText As String
    Dim intDays As Integer
    If Len(Me.Text0.Text & "") = 0 Then strText = ""
    If InStr("abcdefghijklmnopqrstuvwyz`~!@#$%^&*()-_=+.""""?/.", UCase(Chr(KeyCode))) > 0 Then
        KeyCode = 0
        Exit Sub
    End If
    If (KeyCode > 47 And KeyCode < 58) Or (KeyCode > 95 And KeyCode < 106) Then
        
        strText = Val(Me.Text0.Text & Chr(KeyCode)) & ""
        intLength = Len(strText)
        Select Case intLength
        Case Is = 2
            If Val(strText) <= Right(Year(Date), 2) Then
                Call showAlert
                KeyCode = 0
            End If
        Case Is = 4
            If Val(Right(strText, 2)) < 1 Or Val(Right(strText, 2)) > 12 Then
                Call showAlert
                KeyCode = 0
            End If
        Case Is = 6
            intDays = Day(DateSerial(Val("20" & Left(strText, 2)), Val(Mid(strText, 3, 2)) + 1, 0))
            If Val(Right(strText, 2)) <> intDays Then
                Call showAlert
                KeyCode = 0
            End If
        Case Is > 6
            Call showAlert
            KeyCode = 0
        End Select
    End If
End Sub


Private Sub showAlert()
    Dim lngBackColor As Long
    Beep
    With Me.Text0
        lngBackColor = .BackColor
        .BackColor = vbYellow
        Pause (2)
        .BackColor = lngBackColor
        Pause (1)
        .BackColor = vbYellow
        Pause (2)
        .BackColor = lngBackColor
    End With
End Sub


Private Sub Pause(intPause As Integer)
    Dim lngtimer As Double
    lngtimer = Timer
    Do While Timer < lngtimer + (0.075 * intPause)
        DoEvents
    Loop
End Sub
 

Users who are viewing this thread

Top Bottom