Sabtu, 19 November 2016

Re: [MS_AccessPros] Set date type fields settings

 

Khalid-


You may not be able to move back to the control depending on how the After Update was triggered.  Did you tab out of the control?  Did you use a navigation button to move to the next record?

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Nov 19, 2016, at 11:50 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

Sorry for late replying i was engaged in some work.

Now its fine, except in Case Else after msgbox closes control moves to next control it doesn't  stays in txtMfgDate.

Private Sub txtMfgDate_AfterUpdate()
Dim strMonth As String, lngYear As Long
    strMonth = Left(Me.txtMfgDate.Text, 3)
    'MsgBox strMonth
    
    Select Case strMonth
        Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
            ' Month name OK - now check year
            strMonth = Right(Me.txtMfgDate.Text, 4)
            If IsNumeric(strMonth) Then
                ' Have numbers - now check for a valid year
                lngYear = CLng(strMonth)
                If lngYear >= 1900 And lngYear <= 2300 Then
                    ' All OK - save the value
                    Me.MfgDate = CDate(Me.txtMfgDate)
                    Exit Sub
                Else
                    MsgBox "You entered a valid month, but the year must be between 2015 and 2099." & vbCrLf _
                    , vbInformation, "Year range is 2015-2099"
                    Me.txtMfgDate.SetFocus
                End If
            Else
                MsgBox "You entered a valid month, but the year part is invalid.", vbInformation, "Invalid year"
                Me.txtMfgDate.SetFocus
            End If
        Case Else
            MsgBox "You must enter a valid month abbreviation: " & vbCrLf & _
           "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec", vbExclamation, "Month abbreviation mis-spelled"
            Me.txtMfgDate.SetFocus ' <=====
    End Select
End Sub


On Friday, November 18, 2016 4:59 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


Khalid-

Then add a test for numbers:

Private Sub txtMfgDate_AfterUpdate()
Dim strMonth As String, lngYear As Long
    strMonth = Left(Me.txtMfgDate.Text, 3)
    'MsgBox strMonth
    
    Select Case strMonth
        Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
            ' Month name OK - now check year
            strMonth = Right(Me.txtMfgDate.Text, 4)
            If IsNumeric(strMonth) Then
                ' Have numbers - now check for a valid year
                lngYear = CLng(strMonth)
                If lngYear >= 1900 And lngYear <= 2300 Then
                    ' All OK - save the value
                    Me.MfgDate = CDate(Me.txtMfgDate)
                    Exit Sub
                Else
                    MsgBox "You entered a valid month, but the year must be between 1900 and 2300."
                    Me.txtMfgDate.SetFocus
                End If
            Else
                MsgBox "You entered a valid month, but the year part is invalid."
                Me.txtMfgDate.SetFocus
            End If
        Case Else
            MsgBox "You must enter a valid month abbreviation: " & vbCrLf & _
           "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec", vbExclamation, "Month abbreviation mis-spelled"
            Me.txtMfgDate.SetFocus
    End Select
End Sub

Note that you can change the 1900 and 2300 values to whatever range you think is useful.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
http://www.viescas.com/ 
(Paris, France)




On Nov 18, 2016, at 12:40 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,
    I am back again.
I have changed Default View of Form "Item File" to Continuous Forms and put List box on the detail section under the controls which are in a row and list box is populated with all above controls under txtMfgDate in List box i am showing field MfgDate.

When month abbreviation is mis-spelled in "txtMfgDate" after display of custom message box focus moves to next control ExpDate and does not returns focus to "txtMfgDate"
    
Private Sub txtMfgDate_AfterUpdate()
Dim strMonth As String
    strMonth = Left(Me.txtMfgDate.Text, 3)
    'MsgBox strMonth
    
    Select Case strMonth
        Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
            ' Month name OK - update the underlying field
            Me.MfgDate = CDate(Me.txtMfgDate)
            Exit Sub
        Case Else
            MsgBox "You must enter a valid month abbreviation: " & vbCrLf & _
           "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec", vbExclamation, "Month abbreviation mis-spelled"
            Me.txtMfgDate.SetFocus
    End Select
End Sub

What if user only enters month abbrv. and leaves dash and year, then we get message "Type mismatch" and on clicking Debug and on vb editor it highlights the line ==> Me.MfgDate = CDate(Me.txtMfgDate).
    There should be some restriction to enter complete pattern or some sort of custom msgbox.
    I tried to put input mask of this control with LLL\-0000;0; if year part is missing then i get message "The value you entered isn't appropriate for the input mask 'LLL\0000;' specified for this field.

Regards,
Khalid








__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (28)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)