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"
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
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
==> 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
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
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
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
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
------------------------------
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
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@... [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:
Posting Komentar