Kamis, 17 November 2016

Re: [MS_AccessPros] Set date type fields settings

 

John,
    I am back again.
I have changed Default View of Form "Item File" to Continuous Forms.

1-    When the form opens column MfgDate shows no values for all records in this column.
    To resolve this just now i got an idea to insert a list box in detail section of form under all controls which are all in a row. Would this be right approach then in the List box i will be displaying field "MfgDate" ?0
2-    When a date is entered in this Unbound control "txtMfgDate" for a new record, or if in any row date is changed/edited. Complete column shows the same date. In table "ItemFile" edit or addition for this control is OK for field MfgDate.
3-    When month abbreviation is mis-spelled in this control after display of custom message box focus does not moves to next control ExpDate 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

4-    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.



---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :

John,

That is good, and working. There are slight arguments perhaps those are of my form design and placement of some controls. Currently it is Single Form with a List Box at bottom. I have think over it and now changing it to Continuous form, and will also remove the List Box.

I will come again after completing this task, and if there are any questions I hope we will resolve them.

Thank you very much.
regards,
Khalid


On Tuesday, November 15, 2016 11:42 PM, "John Viescas JohnV@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
Khalid-

Yes, it's a "new" text box - and remove the text box bound to the actual field.  Give the "new" text box a name that's different from the name of the field - like txtMfgDate.

Private Sub Form_Current()
    Me.txtMfgDate = Format(Me.MfgDate, "mmm-yyyy")
    CmdSaveItemFile.Enabled = False
End Sub

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"
            Me.txtMfgDate.SetFocus
    End Select
End Sub


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 15, 2016, at 6:51 PM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

It seems pretty smart and tricky, that's why i'm little confused, is that Unbound Text box a new Text box ? Or the existing Text box "MfgDate"

Private Sub Form_Current()
==>    Me.MfgDate.Format = "mmm-yyyy"
    CmdSaveItemFile.Enabled = False
End Sub

Check in After Update event of  which new Text box or MfgDate with the code we discussed last:
    Dim strMonth As String
    strMonth = Left(Me.MfgDate.Text, 3)
    MsgBox strMonth
    
    Select Case strMonth
        Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
            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"
        Cancel = True
    End Select
Me.MfgDate.Setfocus

Is this correct what i have got ?
Regards,
Khalid


On Tuesday, November 15, 2016 3:11 PM, "John Viescas JohnV@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


Khalid-

Perhaps a solution is to use an unbound text box.  In the Current event of the form, load it with the mmm-yyyy of the underlying date/time field.  In AfterUpdate of the control, check that it's valid.  If not, display an error and put the focus back in the box.  If it is valid, update the underlying field value in the Record Source.

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Nov 15, 2016, at 09:28, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Yes i am using these both fields "MfgDate" and "ExpDate" as Date/Time Data type and further at some instances items expired and expiring within one month these are used and calculated, and displayed as Notifications on Main Menu and in reports.

Regards,
Khalid


On Tuesday, November 15, 2016 12:11 PM, "John Viescas JohnV@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
Khalid-

One solution would be to change the data type from Date/Time to Text, but that's probably not a viable solution.  As you've probably noticed, when you supply only the month and the year to update a Date/Time data type, you always get 1 as the day part value.  How are you using this field in your application?

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Nov 14, 2016, at 22:48, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Then what's the conclusion, no solution ?????

Regards,
Khalid


On Tuesday, November 15, 2016 2:36 AM, "John Viescas JohnV@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
No, Validation Text.  It's the message Access will display if there's an error.  Use Validation Rule to specify any special tests - but that won't apply in your case.

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Nov 14, 2016, at 21:47, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

What will be Validation Rule ?

Regards,
Khalid


On Tuesday, November 15, 2016 1:28 AM, "John Viescas JohnV@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
Khalid- 

That's telling me that you cannot trap the error in code.  Access is grabbing it before your code can run.  You could try setting the Validation Text property in the table to see if that makes a difference.

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 14, 2016, at 9:13 PM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

Private Sub MfgDate_BeforeUpdate(Cancel As Integer)
    Dim strMonth As String
    strMonth = Left(Me.MfgDate.Text, 3)
    MsgBox strMonth
    
    Select Case strMonth
        Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
            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"
        Cancel = True 
    End Select
End Sub
------------------------------
After entering correct month abbrv. a msgbox displays the month abbrv. like Jan, but on wrong month abbrv. entered i am still waiting to see my custom message, instead i get the Access message "The text you entered....."

Khalid


On Tuesday, November 15, 2016 12:57 AM, "John Viescas JohnV@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


Khalid-

Arrggghh.  Is your code running at all?  You could add:

MsgBox strMonth

.. after the assignment of strMonth = Left(… to find out.

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 14, 2016, at 8:48 PM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

OK, but yet now on wrong month abbrv. entered my msgbox does'nt pops up, instead still getting Access message.....

Regards,
Khalid


On Tuesday, November 15, 2016 12:39 AM, "John Viescas JohnV@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


Khalid-

My error.  It should be:

   Case Else

And yes, when you enter Jan-2016, the value stored should be 1/1/2016.

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 14, 2016, at 8:07 PM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

The code is now:

Private Sub MfgDate_BeforeUpdate(Cancel As Integer)
Dim strMonth As String
    strMonth = Left(Me.MfgDate.Text, 3)
    Select Case strMonth
        Case "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
            Exit Sub
 ==>   'Else
            MsgBox "You must enter a valid month abbreviation: " & vbCrLf & _
           "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec"
        Cancel = True
    End Select
End Sub
--------------------------------------------------
First i tried by removing the first two lines:
Dim strMonth As String
    strMonth = Left(Me.MfgDate.Text, 3)
Then it gave the error on Select Case strMonth, highlighting strMonth, then i put the first two lines and again it was pointing to Else, so made the code as above.

Now after entering Jan-2016 and focus moves to next Control "ExpDate" i see that MfgDate is showing 01/01/2016

And if i mis-spell month abbrv. Access gives the old message "The text you entered is not valid........."

Regards,
Khalid


On Monday, November 14, 2016 11:33 PM, "John Viescas JohnV@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


Khalid-

Sometimes VBA accepts an SQL predicate, but apparently it doesn't like this one.  Try this:

    SELECT CASE strMonth
        CASE "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
            Exit Sub
        Else
            MsgBox ….
    End Select


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



John,

There is some Syntax Error in this line i could not find that

If strMonth Not In ("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec") Then

Please check again.

Regards,
Khalid


On Monday, November 14, 2016 10:57 PM, "John Viescas JohnV@... [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


__._,_.___

Posted by: khalidtanweerburrah@yahoo.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (24)

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