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
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@yahoo.com [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@msn.com [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
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@yahoo.com [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@msn.com [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
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@yahoo.com [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@msn.com [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@yahoo.com [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
------------------------------
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@msn.com [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@yahoo.com [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@msn.com [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@yahoo.com [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
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)
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@msn.com [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@yahoo.com [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@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Khalid-
If you want to look at what the user typed, you need to look at the .Text property of the control.
Dim strMonth As String
strMonth = Left(Me.MfgDate.Text, 3)
If strMonth Not In ("jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec") Then
MsgBox "You must enter a valid month abbreviation: " & vbCRLF & _
"Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec"
Cancel = True
End If
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 6:09 PM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Form "Item File" has Record Source table "ItemFile". I cleared Format property for field MfgDate in the table and also on the Forms Control "MfgDate". Input Mask is LLL\-0000;0;
Now when i entered "Jan-2016" it returned 01/01/2016, also what condition should be written on the code
Private Sub MfgDate_BeforeUpdate(Cancel As Integer)
If MfgDate ............... Then
MsgBox ".............", vbExclamation, "..........."
End If
Me.Undo
End Sub
If MfgDate ............... Then
MsgBox ".............", vbExclamation, "..........."
End If
Me.Undo
End Sub
Regards,
Khalid
On Monday, November 14, 2016 9:41 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Khalid-
Does it work if you clear the Format property? It sounds like Access is using that setting to grab the invalid data before your code gets a chance.
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 5:25 PM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Yes i am trying Before Update event on the Control.
And just for reminder now i am using Access 2007.
One more thing my Access Help is not working i dont know what happened. I ran Microsoft Office Diagnostics and it tells everything is OK.
Regards,
Khalid
On Monday, November 14, 2016 7:31 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Khalid-
Are you trying to do the intercept in BeforeUpdate of the control? I think that should work. If you're using the Form's BeforeUpdate, that's too late.
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 2:16 PM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi All,
I have two controls on Form "Item File"
MfgDate
ExpDate
Having Input Mask--> 00/00/0000;0;_
Format--> mmm-yyyy
Currently i have to enter date like 01/01/2016. I need to enter data like Jan-2016 and set
the check if month is mis-spelled i.e apart from Jan to Dec, my custom msgbox should open.
I did tried with input mask--> LLL\-0000;0; and was able to enter like Jan-2016 but i was
unable to display my custom msgbox if month part is mis-spelled, Access opens its built in message
As below:
The value you entered isn't valid for this field.
For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.
Help required please.
Regards,
Khalid
I have two controls on Form "Item File"
MfgDate
ExpDate
Having Input Mask--> 00/00/0000;0;_
Format--> mmm-yyyy
Currently i have to enter date like 01/01/2016. I need to enter data like Jan-2016 and set
the check if month is mis-spelled i.e apart from Jan to Dec, my custom msgbox should open.
I did tried with input mask--> LLL\-0000;0; and was able to enter like Jan-2016 but i was
unable to display my custom msgbox if month part is mis-spelled, Access opens its built in message
As below:
The value you entered isn't valid for this field.
For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.
Help required please.
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 (20) |
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:
Posting Komentar