Solved

Validating User's entry before Update in datasheet

Posted on 2006-11-18
48
289 Views
Last Modified: 2008-02-26
I am trying to create validation function around the working code below. I need this experiment to be used as a "backdoor" entrance for the Administrator...in case of db lock out.
The code loads default dates of date(now) any time user open frmFilingDate. This new popup form is in a datasheet format. The form is also bounded to tblFilingDates.

I am attaching a sample db on the topic as a visual aid.
http://www.geocities.com/bombastikbill/SampleDatasheet.zip

Here is the new additional logic preferred:
Grace period for each quarter terminates on the 14th of the month...this date is constant for all quarters.

for example:

'************
Qtr 1 - Qtr ends on 03/31/06 - Friday) and grace period terminates on 04/14/06
        (a). so if user opens frmfilingDate before 03/31/06 or user enters date before
             03/31/06 then give msgbox "invalid Date"
        (b). If user opens frmFilingDate after 04/14/06 then default date to 04/14/06
        (c). If user opens frmFilingDate btw 03/31/06 thru 04/14/06 then default to date(now)
              that is date frmFilingDate was opened.

Qtr 2 - Qtr ends on 06/30/06 - Friday) and grace period terminates on 07/14/06.
        (a). so if user opens frmfilingDate before 06/30/06 or user enters date before
             then 06/30/06 then give msgbox "invalid Date"
        (b). If user opens frmFilingDate after 07/14/06 then default date to 07/14/06
        (c). If user opens frmFilingDate btw 06/30/06 thru 07/14/06 then default to date(now)
              that is date frmFilingDate was opened.

Qtr 3 - Qtr ends on 09/29/06 - Friday) and grace period terminates on 10/14/06
        (a). so if user opens frmfilingDate before 09/29/06 or user enters date before
             then 09/29/06 then give msgbox "invalid Date"
        (b). If user opens frmFilingDate after 10/14/06 then default date to 10/14/06
        (c). If user opens frmFilingDate btw 09/29/06 thru 10/14/06 then default to date(now)
              that is date frmFilingDate was opened.

Qtr 4 - Qtr ends on 12/29/06 - Friday) and grace period terminates on 01/14/07
        (a). so if user opens frmfilingDate before 12/29/06 then give msgbox "invalid Date"
        (b). If user opens frmFilingDate after 01/14/07 then default date to 01/14/07
        (c). If user opens frmFilingDate btw 12/29/06 thru 01/14/07 then default to date(now)
              that is date frmFilingDate was opened.
' ***********
Here is the current On Load Event code.

The following fields are autofilled when user opens frmFilingDate:
txtFDID, txtQtr, txtYear and txtGetDateFiling. I am now trying to check the above validation
first before updates can be performed.

(a). If user opens frmFilingDate, the conditions above "must" be checked out first, then
(b). Ask user to accept default date or change it...then follow rules of checking "invalid dates"
(c). If everything is ok then update all fields otherwise, cancel and ask user to re-enter again.
(d). Allow 3 failed attempt, and shut down db after the third failed attempt.
' *************

Private Sub Form_Load()
   
    Dim iQQ As Integer
    Dim iYY As Integer
   
    'Get current quarter/year based on system date
    iQQ = DatePart("q", Date)
    iYY = DatePart("yyyy", Now())

    'If current date is not last of last quarter
    If DateIsLastQuarterDate(Now()) = False Then

        'If quarter 1 then for previous quarter, we need to subtract the  year also
        If iQQ = 1 Then
            iQQ = 4
            iYY = iYY - 1
        Else
            iQQ = iQQ - 1
        End If
    End If
' **************** Need assistance here*************
'      'Check current date is within the grace period                                     ' <<<--- This line code is currently disabled
'    If GracePeriodValidation(Now(), iQQ, iYY) = False Then                           ' <<<--- needs a new validation code
'        MsgBox "Grace period has past/expired", vbExclamation, FRM_TITLE     ' <<<--- see below for
'        DoCmd.Close                                                                                    ' <<<--- further explantion
'    Else
' *************** End of request ******************

        'Initialise number of attempts to be 1
        m_iNoAttempts = 1
        Dim rs As DAO.Recordset
       
        Set rs = Me.RecordsetClone
        rs.FindFirst "QtrNo = " & iQQ & " AND YearNum = " & iYY
        'Matching record for current qq/yy does not exist, then go in addmode
        If rs.NoMatch = True Then
            DoCmd.GoToRecord , , acNewRec
        ' **************************
         'If in addmode
        If Me.NewRecord = True Then
            'Initialise variables with defaults
            Me.txtGetDateFiling = Date
            Me.txtQtr = iQQ
            Me.txtYear = iYY
            Me.txtFDID = DMax("FDID", "tblFilingDates") + 1
            Me.txtGetDateFiling.SetFocus
            Me.Dirty = False
        Else
            'Load record
            Me.Bookmark = rs.Bookmark
        End If
        rs.Close
        Set rs = Nothing
    'Otherwise edit mode and changes made
    ElseIf Me.Dirty = True Then
        Me.Dirty = False
    Else
        MsgBox "There are no pending changes that meet update criteria", vbInformation, FRM_TITLE
    DoCmd.Close
    End If
End Sub
0
Comment
Question by:billcute
  • 29
  • 19
48 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Hi Bill

Sorry, Im a little confused. What are the differences from what u currently have to what u are trying to achieve?

How do u want your backdoor method to work for the administrator?
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Thanks for responding,
In one of my previous post, frmGetDateFiling is the main form the Admininstrator will normally use to add datefiling..up till the grace period expires which is usually the 14th of the expiring month.

You also know that once the grace period expires...the Administrator can no longer open this form...it will be ;locked as from the 15th of the expiring month.

New form (see the attched sample on this post)
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
The new form is frmFilingDate (datasheet type)

What I am trying to do are:
During the period that the previous form (frmGetDateFiling) opens....the new form will "not" be accessible....

but once frmGetdatefiling locks and becomes inaccesible....usually from the 15th of the expiring month, then frmFilingDate will be accessible...

In other words when frmGetDateFiling is accessible, frmFilingdate will be inaccessible....

when frmGetdateFilin becomes inaccessible, then frmFilingDate becomes accessible.

The idea is when grace period has lapsed and the Administrator did not enter the Daefiled in tblFilingDates...the backdoor ...which is frmFiling date will still let him in even though it's passed the grace period.....against all odds...
or should in case something goes wrong somewhere.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, so if grace period has passed, and if administrator is logged in, what u want is the new form to open? otherwise open the original form?
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Yes....
and the conditions are stipulated in the listed question above....both forms are almost the same in design and conditions only minor differences.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
I guess then, a small tweak like this can be made


If GracePeriodValidation(Now(), iQQ, iYY) = False Then
    MsgBox "Grace period has past/expired", vbExclamation, FRM_TITLE
    DoCmd.Close

    'Check if admin - I cant remember the variable/method u used, but u shud be able to handle this
    if Admiuser .... then
        Docmd.openform "frmFilingDate_Datasheet"
    end if
Else
    ...
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
My current design frmFilingdate is still not working as anticipated.

frmGetDateFiling and frmFilingDates are exactly the same with few exceptions (see below).

 (1). frmGetDateFiling does not accept invalid dates  but frmFiling Dates does...(this shouldnt happen)
      it means my code in the On Load Event of frmFilingDates needs further tweaking.to function just like
      frmGetDateFiling code (that refuses invalid date entry)
      Entries like 09/28/06 or 10/15/06 should be rejected as invalid date entry.

(2). Unlike frmGetDateFiling that would not open as from 10/15/06 for Qtr 3 for example
      ......., frmFilingDates "should" open but "must" default the Adminstrator to the last date of grace
      period (which is usually on the 14th of the expiring month.
' **********
Below is the full conditions for the new datasheet form (brought forward from my post above again):
If you can take a minute on this example. then it will be easier for you to understand the requirement
************
Qtr 1 - Qtr ends on 03/31/06 - Friday) and grace period terminates on 04/14/06
        (a). so if user opens frmfilingDate before 03/31/06 or user enters date before
             03/31/06 then give msgbox "invalid Date"
        (b). If user opens frmFilingDate after 04/14/06 then default date to 04/14/06
        (c). If user opens frmFilingDate btw 03/31/06 thru 04/14/06 then default to date(now)
              that is date frmFilingDate was opened.

Qtr 2 - Qtr ends on 06/30/06 - Friday) and grace period terminates on 07/14/06.
        (a). so if user opens frmfilingDate before 06/30/06 or user enters date before
             then 06/30/06 then give msgbox "invalid Date"
        (b). If user opens frmFilingDate after 07/14/06 then default date to 07/14/06
        (c). If user opens frmFilingDate btw 06/30/06 thru 07/14/06 then default to date(now)
              that is date frmFilingDate was opened.

Qtr 3 - Qtr ends on 09/29/06 - Friday) and grace period terminates on 10/14/06
        (a). so if user opens frmfilingDate before 09/29/06 or user enters date before
             then 09/29/06 then give msgbox "invalid Date"
        (b). If user opens frmFilingDate after 10/14/06 then default date to 10/14/06
        (c). If user opens frmFilingDate btw 09/29/06 thru 10/14/06 then default to date(now)
              that is date frmFilingDate was opened.

Qtr 4 - Qtr ends on 12/29/06 - Friday) and grace period terminates on 01/14/07
        (a). so if user opens frmfilingDate before 12/29/06 then give msgbox "invalid Date"
        (b). If user opens frmFilingDate after 01/14/07 then default date to 01/14/07
        (c). If user opens frmFilingDate btw 12/29/06 thru 01/14/07 then default to date(now)
              that is date frmFilingDate was opened.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
Do you see my last comment?

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
The example I brought forward from my post should guide you towards the new tweaking request.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
I know you are very busy, I will appreciate your final look at this topic.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
If you are confused with this post. Is it okay if I close the post, refund points and ten re-open with a new listing that would be more clearer?

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Hi Bill
Not confused. I didnt use EE all of today until now and all weekend, and limited use latter end of last week.
I got very busy with work and had a busy weekend with the family also, so sorry about that.

Gonna look at this now


0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Bill I have one question for you

regarding this

Qtr 1 - Qtr ends on 03/31/06 - Friday) and grace period terminates on 04/14/06
        (b). If user opens frmFilingDate after 04/14/06 then default date to 04/14/06

Qtr 2 - Qtr ends on 06/30/06 - Friday) and grace period terminates on 07/14/06.
        (a). so if user opens frmfilingDate before 06/30/06 or user enters date before
             then 06/30/06 then give msgbox "invalid Date"


In q1. if u open after the 14th, the default date is 14th
but if q2, if u open before quarter end, show invalid date


doesnt the 2nd contradict with the 1st?

0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockroads,
There is no contradiction....

frmGetDateFiling always open during grace period of each quarter

...however, I want to make it in such away that frmFilingDate (the new form does not open when the previous form (frmGetDateFiling) opens

...as such the new form frmFilingDate will be closed for example in QtrNo 3 btw 09/29/06 thru 10/14/06, the new form will be closed because frmGetDateFiling will be opened during this period....as from 10/15/06 when frmGetDateFiling is locked and can no longer open,...

....then the new form frmFilingDate can be opened EXCEPT that when it opens as from 10/15/06, the admin wiill be able to perform date filing on emergency however, the default date will always be 10/14/06 - the default date for the end of the 3rd quarter for 2006 for example.

.....this will be so for all other quarter.

I hope this is clearer.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockroads,
Is my above explanation sufficient?
If not what the remaining issues that require clarification?

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Bill, sorry.
Ive had problems last few days with the net at home and yesterday it just didnt work. I think I have a problem with the router.
And also unable to concentrate on this (in fact, EE in general) as Im busy at work.
Once I get my net sorted, I'll tackle this first

Its bloody frustrating not having net, I tell u
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
no worries

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
I will appreciate further help on this topic at your earliest convenience

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
Please dont forget me on this post. I know that you are very busy.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Hi Bill

I must admit, I did get confused based on this

Qtr 1 - Qtr ends on 03/31/06 - Friday) and grace period terminates on 04/14/06
        (a). so if user opens frmfilingDate before 03/31/06 or user enters date before
             03/31/06 then give msgbox "invalid Date"
        (b). If user opens frmFilingDate after 04/14/06 then default date to 04/14/06
        (c). If user opens frmFilingDate btw 03/31/06 thru 04/14/06 then default to date(now)
              that is date frmFilingDate was opened.

Qtr 2 - Qtr ends on 06/30/06 - Friday) and grace period terminates on 07/14/06.
        (a). so if user opens frmfilingDate before 06/30/06 or user enters date before
             then 06/30/06 then give msgbox "invalid Date"
        (b). If user opens frmFilingDate after 07/14/06 then default date to 07/14/06
        (c). If user opens frmFilingDate btw 06/30/06 thru 07/14/06 then default to date(now)
              that is date frmFilingDate was opened.


and looking at the comment

Qtr 2 - Qtr ends on 06/30/06 - Friday) and grace period terminates on 07/14/06.
        (a). so if user opens frmfilingDate before 06/30/06 or user enters date before
             then 06/30/06 then give msgbox "invalid Date"


SO I ENTER A DATE FOR EXAMPLE  06/10/06 - going by this line, it means I throw a msg "Invalid Date"

but then for Qtr1 u say

Qtr 1 - Qtr ends on 03/31/06 - Friday) and grace period terminates on 04/14/06
        (b). If user opens frmFilingDate after 04/14/06 then default date to 04/14/06


SO 06/10/06 is after 04/14/06, I default date to 04/14/06, no error

this is why I got confused




Ok, let me try understand what u want here

Does frmFilingDate ALWAYS opens for Administrator ?

What do u define as a invalid date considering what Ive just said


>> however, I want to make it in such away that frmFilingDate (the new form does not open when the previous form (frmGetDateFiling) opens

What do u mean by this? when previous form opens




Is this new form a way to set dates outside the grace period (when u can open up frmGetDateFiling only)


0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
I will amend this question a little to make it simple.

For example,
#1.
Qtr 2 - Qtr ends on 06/30/06 - Friday) and grace period terminates on 07/14/06.

 If user opens frmFilingDate after 04/14/06 (i.e as from 04/15/06, 04/16/06 etc..), then default date to 04/14/06

(b).
...So if form (frmFilingDate) defaults date to 04/14/06 after the  "grace period"...
but the Admin can still overide this date by entering any date between the end of the quarter thru the grace period...in this case the Admin can enter any date between 03/31/06 thru 04/14/06.

If the ADMIN "manually" ENTERs A DATE FOR EXAMPLE  03/10/06  or 03/15/06 - going by this line, it means I throw a msg "Invalid Date".
This means that the ADMIN can only enter date btw 03/31/06 thru 04/14/06.

The alternative is for the ADMIN to accept the form's default date of 04/14/04 when frmFilingDate opens and this will be okay.
' *******

CONTROL:
The only control I placed on frmFilingDate is that this form will not open
during the grace period ...

for example.....

Qtr 1's end of quarter is from 03/31/06 thru 04/14/06.

If ADMIN tries to open this form, he should get a message: "Please use the other form "frmGetDateFiling" to enter your filing date...

NOTE:
Please note that frmGetDateFiling is already programmed only to open between 03/31/06 thru 04/14/06 and it locks out as from 04/15/06.

The idea is that if frmGetDateFiling is locked as from 04/15/06, then frmFilinDate can open for "backdoor" use only.

While it is expected that the ADMIN will file the filing date timely, should there be an emergency that the grace period had expired and the filing Date was not filed, then frmFilingDate will "now" come in as a backdoor for the ADMIN.

The above is true for all quarters:

RECAP FOR GRACE PERIOD
Qtr 1 ... btw 03/31/06 thru 04/14/06.  
Qtr 2 ... btw 06/30/06 thru 07/14/06.
Qtr 3 ... btw 09/30/06 thru 10/14/06.
Qtr 4 ... btw 12/31/06 thru 01/14/07.

I hope this is clearer.

Regards
Bill



Regards



0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, gotcha I think

How do u control this form opening? can anyone open it or is it just for admin
if just for admin, have u controlled this somewhere?

One way is to use Form_Open event
eg

Private Sub Form_Open(Cancel As Integer)
    If userlevel <> admin Then Cancel = True
End Sub


where   userlevel   is your variable that holds the user type (or name or whatever identified admin)

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
I assume this form never allows an "add", u have disabled it in form_current

0
 
LVL 4

Author Comment

by:billcute
Comment Utility
(a). This form is for the admin only.
(b). When form opens it automatically add the Quarter info and it's expected
      to default date to the 14th of the expiring month as described above

... form_current locks evrything except the ldate field of the current quarter being edited.

If you could assist using my explanation above, I will be grateful.

Regards
Bill
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, here is the mod to Form_Load
pls read the comments to try understand



Private Sub Form_Load()
   
    Dim iQQ As Integer
    Dim iYY As Integer
    Dim rs As DAO.Recordset
   
   
    'Get current quarter/year based on system date
    iQQ = DatePart("q", Date)
    iYY = DatePart("yyyy", Now())

    'If current date is not last of last quarter
    If DateIsLastQuarterDate(Now()) = False Then

        'If quarter 1 then for previous quarter, we need to subtract the  year also
        If iQQ = 1 Then
            iQQ = 4
            iYY = iYY - 1
        Else
            iQQ = iQQ - 1
        End If
    End If
   
    'Check current date is within the grace period
    If GracePeriodValidation(Now(), iQQ, iYY) = True Then
        MsgBox "Please use the other form " & Chr$(34) & "frmGetDateFiling" & Chr$(34) & " to enter your filing date", vbInformation
        DoCmd.Close
    Else
        'Otherwise the date must be after the grace period date
        'Just double check anyway
        If Date > GetLastGracePeriodDate(iQQ, iYY) Then
           
            'Find last record, and update it with the 14th
            Set rs = Me.Recordset
            rs.MoveLast
            rs.Edit
            rs!DateFiled = GetLastGracePeriodDate(iQQ, iYY)
            rs.Update
           
        'Should never get here
        Else
            MsgBox "Cannot verify date"
            DoCmd.Close
        End If
    End If
End Sub

0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 500 total points
Comment Utility
Now the validation for the date entered, we make use of the function GracePeriodValidation
Remember, this function has all the logic in there already

eg


Private Sub txtGetDateFiling_BeforeUpdate(Cancel As Integer)
   
    Dim iQQ As Integer
    Dim iYY As Integer
   
   
    'Get current quarter/year based on system date
    iQQ = DatePart("q", Date)
    iYY = DatePart("yyyy", Now())

    'If current date is not last of last quarter
    If DateIsLastQuarterDate(Now()) = False Then

        'If quarter 1 then for previous quarter, we need to subtract the  year also
        If iQQ = 1 Then
            iQQ = 4
            iYY = iYY - 1
        Else
            iQQ = iQQ - 1
        End If
    End If
   
    'Date entered must be in grace period
    If GracePeriodValidation(Me.DateFiled, iQQ, iYY) = False Then
        MsgBox "Invalid Date"
        Cancel = True
    End If
End Sub
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
I am getting "compile" error:

Sub or Function not defined

on...

GetLastGracePeriodDate

Did you define a function for "GetLastGracePeriodDate" that you forgot to post here?
' ********

(2).
Me.DateFiled is in your code but does "not exist" in the sample I placed on this post....it should be Me.txtGetDateFiling (same as your other same in the concluded post).


(3). I placed your code at the Before Update Event of txtGetDateFiling as suggested. Is it utilizing the "GracePeriodValidation function" ?
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 500 total points
Comment Utility
Sorry Bill,. I forgot to post that code



Public Function GetLastGracePeriodDate(Optional ByVal iQQ As Integer = 0, Optional ByVal iYY As Integer = 0) As Date
   
    Dim iUseQQ As Integer
    Dim iUseYY As Integer
   
    If iQQ = 0 Then iUseQQ = Format(Now(), "q") Else iUseQQ = iQQ
    If iYY = 0 Then iUseYY = Year(Now()) Else iUseYY = iYY
   
    If iUseQQ = 4 Then
        GetLastGracePeriodDate = DateSerial(iUseYY + 1, 1, GRACE_PERIOD)
    Else
        GetLastGracePeriodDate = DateSerial(iUseYY, Month(GetLastQuarterDate(iUseQQ, iUseYY)) + 1, GRACE_PERIOD)
    End If
End Function


put it in ModDateFiling

0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
Your code did not provide provisions that would update tblFilingDates fields.

If you look at my original On Load Event code...the following codes was there
but missing in your updated On Load Event code.

   'If in addmode
        If Me.NewRecord = True Then
            'Initialise variables with defaults
            Me.txtGetDateFiling = Date
            Me.txtQtr = iQQ
            Me.txtYear = iYY
            Me.txtFDID = DMax("FDID", "tblFilingDates") + 1
            Me.txtGetDateFiling.SetFocus
            Me.Dirty = False

So when frmFilingDate opens it does not load the default date for example 04/14/06 for Quarter 1.

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, sorry, I just did the Edit. Here it is again with add



Private Sub Form_Load()
   
    Dim iQQ As Integer
    Dim iYY As Integer
    Dim rs As DAO.Recordset
   
   
    'Get current quarter/year based on system date
    iQQ = DatePart("q", Date)
    iYY = DatePart("yyyy", Now())

    'If current date is not last of last quarter
    If DateIsLastQuarterDate(Now()) = False Then

        'If quarter 1 then for previous quarter, we need to subtract the  year also
        If iQQ = 1 Then
            iQQ = 4
            iYY = iYY - 1
        Else
            iQQ = iQQ - 1
        End If
    End If
   
    'Check current date is within the grace period
    If GracePeriodValidation(Now(), iQQ, iYY) = True Then
        MsgBox "Please use the other form " & Chr$(34) & "frmGetDateFiling" & Chr$(34) & " to enter your filing date", vbInformation
        DoCmd.Close
    Else
        'Otherwise the date must be after the grace period date
        'Just double check anyway
        If Date > GetLastGracePeriodDate(iQQ, iYY) Then
           
            Set rs = Me.RecordsetClone
            rs.FindFirst "QtrNo = " & iQQ & " AND YearNum = " & iYY
           
            'Matching record for current qq/yy does not exist, then go in addmode
            If rs.NoMatch = True Then
                DoCmd.GoToRecord , , acNewRec
                'Initialise variables with defaults
                Me.txtGetDateFiling = GetLastGracePeriodDate(iQQ, iYY)
                Me.txtQtr = iQQ
                Me.txtYear = iYY
                Me.txtFDID = DMax("FDID", "tblFilingDates") + 1
                Me.txtGetDateFiling.SetFocus
                Me.Dirty = False
            Else
                rs.MoveLast
                rs.Edit
                rs!DateFiled = GetLastGracePeriodDate(iQQ, iYY)
                rs.Update
                Me.Bookmark = rs.Bookmark
            End If
            rs.Close
            Set rs = Nothing
           
        'Should never get here
        Else
            MsgBox "Cannot verify date"
            DoCmd.Close
        End If
    End If
End Sub

0
 
LVL 4

Author Comment

by:billcute
Comment Utility
I tested your last suggestion by setting PC Clock to 10/15/06, there was no update for Qtr3.
(1).
FDID, QtrNo, YearNum and Datefiled did not add any record for that quarter

(2).
Also there was no suggested default date of 10/14/06 .. am I doing something wrong?

(3). There was no reference to tblFilingDates in your code. How will updates be performed if no table reference?

Do you have a working sample at your end? If so, can you post it?.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
Did you see my last comment?

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Hi Bill, I just tried that and it appears to be working fine for me

Unless I have mistaken your requirements, which may be a possibility

I deleted rec for Q3 2006, set date to 15th Oct, it created record
I changed date from 14th oct to 12th Oct, restarted form, it changed to 14th


updated db is here
http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=1670

0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
It's very good, I dont know what I did wrong from my end..I'll retrace my steps.

I have one question. While the information for "AutoNumber", QrtNo and YearNum can be filled in automatically -- Is it possible "not" to autopaste the  date in the DateFiled field but rather present the default date like a msgbox such as:
msgbox: "Accept the default date of.... 10.14/06 click "Yes" otherwise click "No" to enter a new date."

In this case, if Admin clicks the "Yes" button to accepts the "default date" then at this time date will be pasted in the DateFiled.

If a "No" response is clicked, then clear the DateFiled field such that user can enter a valid date.

Is this possible?

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Also when Admin enters an Invalid date and gets an Invalid Date msgbox...there is an extra msgbox that comes up immediately 'Property not found"

Do you know why this mesgbox is popping up?

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Im not sure what this extra msgbox is, Im not getting that, I entered 11/11/06 and all I got was "Invalid Date". U will need to debug and step thru the code to see where it is coming from.


Here is the updated Form_Load with prompts


Private Sub Form_Load()
   
    Dim iQQ As Integer
    Dim iYY As Integer
    Dim rs As dao.Recordset
    Dim dDate As Date
    Dim sMsg As String
   
    'Get current quarter/year based on system date
    iQQ = DatePart("q", Date)
    iYY = DatePart("yyyy", Now())

    'If current date is not last of last quarter
    If DateIsLastQuarterDate(Now()) = False Then

        'If quarter 1 then for previous quarter, we need to subtract the  year also
        If iQQ = 1 Then
            iQQ = 4
            iYY = iYY - 1
        Else
            iQQ = iQQ - 1
        End If
    End If
   
    'Check current date is within the grace period
    If GracePeriodValidation(Now(), iQQ, iYY) = True Then
        MsgBox "Please use the other form " & Chr$(34) & "frmGetDateFiling" & Chr$(34) & " to enter your filing date", vbInformation
        DoCmd.Close
    Else
        'Otherwise the date must be after the grace period date
        'Just double check anyway
        If Date > GetLastGracePeriodDate(iQQ, iYY) Then
           
            Set rs = Me.RecordsetClone
            rs.FindFirst "QtrNo = " & iQQ & " AND YearNum = " & iYY
           
            'Matching record for current qq/yy does not exist, then go in addmode
            dDate = GetLastGracePeriodDate(iQQ, iYY)
            sMsg = "Accept the default date of " & dDate & vbCrLf & "Click Yes to accept otherwise No to enter a new date"
            If rs.NoMatch = True Then
                DoCmd.GoToRecord , , acNewRec
                'Initialise variables with defaults
                If MsgBox(sMsg, vbYesNo + vbQuestion, "New Date Confirmation") = vbYes Then Me.txtGetDateFiling = dDate
                Me.txtQtr = iQQ
                Me.txtYear = iYY
                Me.txtFDID = DMax("FDID", "tblFilingDates") + 1
                Me.Dirty = False
            Else
                rs.MoveLast
                rs.Edit
                If MsgBox(sMsg, vbYesNo + vbQuestion, "New Date Confirmation") = vbYes Then rs!DateFiled = dDate
                rs.Update
                Me.Bookmark = rs.Bookmark
            End If
            Me.txtGetDateFiling.SetFocus
            rs.Close
            Set rs = Nothing
           
        'Should never get here
        Else
            MsgBox "Cannot verify date"
            DoCmd.Close
        End If
    End If
End Sub

0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
I noticed you added a Small Calenda in your posted sample. That was neat ..thanks.
I am grateful for the newly added msgbox...

In your last posted code, I think we still need a small tweaking in these two areas.

First, When Admin opens the form (frmFilingDates):

(a). allow the form to open first and set focus on DateFiled field, them
      ....also note that at this time, all other fields have already been filled in EXCEPT the DateFiled field...

After setting focus on the DateField field, then...
the new msgbox "Accept the default......" ...should come up....

then the rest of your code can function as designed once the dateFiled field is in focus.

Secondly, When Admin opens frmFilingdate
.....and detects that Datefiled is already filled in for the last quarter,
...then set focus again on DateFiled field and then present another msgbox ,
...DELETE the current default date? Yes/No..
...a "Yes" response deletes it and still set focus on DateFiled field for Admin
   to set a new date.
...a "No" response does not delete the default date but still set focus for Admin to change or leave the DateFiled alone...

If these new tweaks added, I will be ok...and extremely grateful

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
setfocus should be working

the only thing different is now if date exists, u want to clear it, thats all



Private Sub Form_Load()
   
    Dim iQQ As Integer
    Dim iYY As Integer
    Dim rs As dao.Recordset
    Dim dDate As Date
    Dim sMsg As String
   
    'Get current quarter/year based on system date
    iQQ = DatePart("q", Date)
    iYY = DatePart("yyyy", Now())

    'If current date is not last of last quarter
    If DateIsLastQuarterDate(Now()) = False Then

        'If quarter 1 then for previous quarter, we need to subtract the  year also
        If iQQ = 1 Then
            iQQ = 4
            iYY = iYY - 1
        Else
            iQQ = iQQ - 1
        End If
    End If
   
    'Check current date is within the grace period
    If GracePeriodValidation(Now(), iQQ, iYY) = True Then
        MsgBox "Please use the other form " & Chr$(34) & "frmGetDateFiling" & Chr$(34) & " to enter your filing date", vbInformation
        DoCmd.Close
    Else
        'Otherwise the date must be after the grace period date
        'Just double check anyway
        If Date > GetLastGracePeriodDate(iQQ, iYY) Then
           
            Set rs = Me.RecordsetClone
            rs.FindFirst "QtrNo = " & iQQ & " AND YearNum = " & iYY
           
            'Matching record for current qq/yy does not exist, then go in addmode
            dDate = GetLastGracePeriodDate(iQQ, iYY)
            If rs.NoMatch = True Then
                DoCmd.GoToRecord , , acNewRec
                'Initialise variables with defaults
                sMsg = "Accept the default date of " & dDate & vbCrLf & "Click Yes to accept otherwise No to enter a new date"
                If MsgBox(sMsg, vbYesNo + vbQuestion, "New Date Confirmation") = vbYes Then Me.txtGetDateFiling = dDate
                Me.txtQtr = iQQ
                Me.txtYear = iYY
                Me.txtFDID = DMax("FDID", "tblFilingDates") + 1
                Me.Dirty = False
            Else
                rs.MoveLast
                rs.Edit
                Me.Bookmark = rs.Bookmark
                If IsNull(Me.txtGetDateFiling) = False Then
                    sMsg = "Delete the current default date of " & Me.txtGetDateFiling
                    If MsgBox(sMsg, vbYesNo + vbQuestion, "New Date Confirmation") = vbYes Then Me.txtGetDateFiling = ""
                End If
            End If
            Me.txtGetDateFiling.SetFocus
            rs.Close
            Set rs = Nothing
           
        'Should never get here
        Else
            MsgBox "Cannot verify date"
            DoCmd.Close
        End If
    End If
End Sub

0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
Everything seems to work fine except that msgboxes comes up before frmFilingDates open.

For example, when Admin clicks on frmFilingDate, the msgboxes opens without frmFilingDate in the background.

maybe some kind of Window Mode code to open frmFilingDate, then msgboxes can appear afterwards...

Is this clearer?

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
Would you mind testing this code at your end to see what I was saying?

Regards
Bill
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Its a feature of Access that I havent figured out yet

I went down and put msgboxes on all events that are called whe a form opens
eg
form_open
form_load
form_current
etc

and msg's always appear before form is fully opened

There have been other questions like this, it might be worth a search on EE for alternatives.
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
I will do a new question on "Window mode" later on.
..but there are some other issues that came after pasting your last suggested On Load Event code.

(1). The code below in the before Update event of frmFilingDate is not working..
...... 'Date entered must be in grace period
    If GracePeriodValidation(Me.DateFiled, iQQ, iYY) = False Then
        MsgBox "Invalid Date"
        Cancel = True
    End If
' *******

Whenever I enter wrong date in the txtGetDateFiling field, I expect to see Invalid Entry msgbox, but it's no longer showing that msgbox because of the latest On Load Code...in frmFilingdate.
' ***********
(2). When I re-downloaded your sample db posted above just to compare
        .... I started to receive
          Run-time error 94 : "Invalid use of Null"

on...

If GracePeriodValidation(Me.DateFiled, iQQ, iYY) = False Then


...and this usually happen when user deleted the date input of the txtGetDateFiling without adding new date...then try shut off the frmFilingDate.

If you can assist in correcting the two errors above, I will close this post.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
If you can fix the probelm associated with the Invalid Date entry errors provided above, I'll work with the code for now and post the Window mode question in the future.

Regards
Bill
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
Ok, I entered an invalid date  21st Sep 2006
and it came up with Invalid Date

regarding your 2nd error, its a null date which we havent catered for. I think perhaps this is also related to your 1st problem

What do u want to happen if the date field is blank, do u want it as a mandatory field? throw error or leave alone? Ive coded both, u can tell by the comments


Private Sub txtGetDateFiling_BeforeUpdate(Cancel As Integer)
   
    Dim iQQ As Integer
    Dim iYY As Integer
    Dim rs As dao.Recordset
   

'BILL PICK ONE OF TWO HERE    
    'If date is blank and u dont want anything to happen then do this
    If IsNull(Me.txtGetDateFiling) = True Then Exit Sub
   
    'If date is blank and u want to throw an error then to this
    If IsNull(Me.txtGetDateFiling) = True Then
        MsgBox "Date is mandatory"
        Cancel = True
        Exit Sub
    End If
   
    If IsDate(Me.txtGetDateFiling) = False Then
        MsgBox "Invalid date entered"
        Cancel = True
        Exit Sub
    End If
   
    'Get current quarter/year based on system date
    iQQ = DatePart("q", Date)
    iYY = DatePart("yyyy", Now())

    'If current date is not last of last quarter
    If DateIsLastQuarterDate(Now()) = False Then

        'If quarter 1 then for previous quarter, we need to subtract the  year also
        If iQQ = 1 Then
            iQQ = 4
            iYY = iYY - 1
        Else
            iQQ = iQQ - 1
        End If
    End If
   
    'Date entered must be in grace period
    If GracePeriodValidation(Me.DateFiled, iQQ, iYY) = False Then
        MsgBox "Invalid Date"
        Cancel = True
    End If
End Sub
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
rockiroads,
Thanks for the effort. I have now opened a new thread to deal with the acDialog command. I was able to find the command from my current db in the office. Additional assistance will be appreciated at.

http://www.experts-exchange.com/Databases/MS_Access/Q_22093112.html
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
just a quick question, in one of your past paq post, you made correction to the code below..and it works fine....

 ' *******correction made on 11/17/06**Q_22061316)*****
from..

    'Build the 14th of the next month
    'dLastDateOfMonth = DateSerial(Year(dValDate), (iQQ * 3) + 1, 0)
   
   to...
 
    'Build the 14th of the next month
    dLastDateOfMonth = DateSerial(iYY, (iQQ * 3) + 1, 0)

The sample db you provided above, is still using the old dLastDateofMonth......
'dLastDateOfMonth = DateSerial(Year(dValDate), (iQQ * 3) + 1, 0)

Would you mind checking this out just to be sure...if error found, please send correction.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Billl, to verify just look at all the places where that funciton is called

Its in GracePeriodValidation right?

If its working now with a date then it should be fine, Im sure I have picked up the right one
Just double check
0
 
LVL 4

Author Comment

by:billcute
Comment Utility
Thanks for the efforts. It's appreciated

Regards
Bill
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now