Sabtu, 19 November 2016

Re: [MS_AccessPros] Set date type fields settings

 

John,

Thank you very much for the guidance. While i am raising my questions in the group and getting help from and you amend or give new code, that improves my knowledge more & more and i learn the logic's.

After entering month and date in "TxtMfgDate" i have to press Enter twice to move to next control "TxtExpDate", and "TxtExpDate" will have same checks as "TxtMfgDate. One additional check will be on "TxtExpDate" something like:

-------------
    If Me.txtExpDate < Me.txtMfgDate Then
            MsgBox "Expiry Year cannot be before Manufacturing Year." _
        , vbCritical, "Invalid Expiry Year"
        Cancel = True
    End If
------------

In table "ItemFile" for fields "MfgDate" and "ExpDate" i have put input mask LLL\-0000;0;
Should there be this input mask or i remove it . OR should input mask be of Short Date.

regards,
Khalid


On Saturday, November 19, 2016 10:01 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
Khalid-

Oh, I thought you had removed that because Access was intercepting with its own message, and you wanted to provide your own customized message.  You could change the mask to:

LLL\-AAAA;0;

That lets the user enter letters or numbers in the second part, and then you can check for numbers with your code and display your error message.

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 5:46 PM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

Got the first instance, but for second instance feb-abcd. Let me remind you that i have set input mask LLL\-0000;0; so it does not accepts alphabats. Therefore in this condition we must have to enter numbers.

Have i set input mask correct ?

regards,
Khalid


On Saturday, November 19, 2016 9:38 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


Khalid-

Type in something like:

jan-9999

or

feb-abcd

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 5:19 PM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

Yes! It is working now. GOOD.

By the way, just for example what would be the invalid year part to get second MsgBox ?

Rest its OK. Thanks a lot.

Please keep in touch i have some other to discuss with you for this database.

Regards,
Khalid


On Saturday, November 19, 2016 8:53 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


Khalid-

You won't get that error if you REPLACE ALL of the:

Me.txtMfgDate.SetFocus

with:

Cancel = True

You need to do the replace in three places in the code.

BeforeUpdate is the way to go.

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 4:45 PM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

My apologies i still can't describe clearly.

1- When i entered invalid data and press Enter/Tab key, i get the MsgBox (I am not sure if the focus stays on txtMfgDate because when MsgBox is active we cannot select anything on Form only the MsgBox).

2- But when i click OK on MsgBox the focus does not stay on txMfgDate.

I hope now i am clear to  convey the situation.

If i put the code to the Before Update event of the control i get Run-time error '2108': You must save the field before you execute the GoToControl action,.......

So i am still on the After Update event of the control.

Regards,
Khalid


On Saturday, November 19, 2016 8:16 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


Khalid-

So, when you press Enter, that moves you to the next control.  When you have entered invalid data, you get the MsgBox, but when you click OK, the focus does not stay on txtMfgDate.  Correct?  I suspect that because you're doing the test in the After Update of the text box where you are trying to keep the focus, Access isn't letting you.  Or perhaps it is honoring the code, but then honoring your Enter key to move out.  You could try moving the code to the Before Update event of the control - but I'm not sure that Before Update will fire for an unbound control.  If it does work, replace the SetFocus with Cancel = True to keep the focus on the control.

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 4:00 PM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

Sorry that i could not convey properly, simply to your question. Perhaps you are asking about "Tab Stop" property of the control, in my case it is set to Yes.

Secondly I was trying to say that i normally  use Enter key to move to next desired control and also i did not use the navigation button to move to the next record.

Regards,
Khalid



On Saturday, November 19, 2016 7:39 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


Khalid-

Your English is normally very understandable, but I'm afraid I do not understand what you are trying to say in your last response.

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 3:24 PM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

This happens either i press enter key or tabbed upto here the focus is still on that control but after display of msgbox pressing enter key or clicking on OK button of msgbox.

regards,
Khalid


On Saturday, November 19, 2016 5:00 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


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 
(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: Khalid Tanweer <khalidtanweerburrah@yahoo.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (40)

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.


.

__,_._,___

Tidak ada komentar:

Poskan Komentar