Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hello

    We have a small new Database that is keeping track of Students. Certain Fields are incumbent on other Fields, and I'd like to create an Event Procedure so that when 1 Date Field is entered, another Date Field automatically updates with a new Value.

    Example, If Referral_Date is 1-February-2010, then End_Date = Referral_Date + 180 days.

    Similarly, 5 Follow-up Reviews need to take place following the Referral, so I'd like to Insert into a corresponding Table 5 new Records.

    Example. Start Date = 1-March-2010, then 5 new Records are created in tblMontlyForms.DateDue
    1st New Record = StartDate + 28 days
    1st New Record = StartDate + 56 days
    1st New Record = StartDate + 84 days
    1st New Record = StartDate + 112 days
    1st New Record = StartDate + 140 days

    Are these things feasible?

    Many thanks in advance.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    What is the database engine? Access, SQL?

    cheers, Paul

  3. #3
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If those add on days are always going to be the same you should not be storing the values in fields. You should calculate them as and when you need them in queries.

    You are violating the rules of normalisation by doing what you propose.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If the End Date is determined by the Referral Date, and can not be changed you don't need a field in the table. Instead base the form on a query where you have a calculated field:

    EndDate: ReferralDate + 180

    If however you need to option of manually adjusting the EndDate, then it does need to be a separate field in the table.

    In the After Update event of ReferralDate put this code:
    Code:
    if not isnull(me.ReferralDate) then
     Me.Enddate = Me.ReferralDate + 180
    else
     Me.EndDate = NULL
    end if
    For the other Dates, if all you need are the 5 dates, and they are totally determined by the Start Date then you could also use a query. but it sounds to me like you need 5 records created. Each will have a ReviewDate, and a number of other fields to record whether the Review happened etc.

    For this you would use a sub procedure to create the new records, and pass it the key field of the current table (StudentID?) and the StartDate.
    Something like this.

    Code:
    Public Sub sbCreateReviews(lngStudentID As Long, StartDate as Date)
    	Dim sql As String
    	Dim intcounter As Integer
    	On Error GoTo sbCreateReviews_Error
    	Dim REviewDate as Date
    	For intcounter = 1 To 5
     	ReviewDate =StartDate + intcounter *28
     	sql = "INSERT INTO tblMontlyForms (StudentID, REviewDate) Values (" & lngStudentID & ", " & ReviewDate &")"
     	
     	CurrentDb.Execute sql
    	Next intcounter
    	Exit Sub
    
    sbCreateReviews_Error:
    	If Err.Number = 3022 Then
     	Resume Next
    	Else
    
     	MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure sbCreateReviews "
    	End If
    End Sub
    You would then trigger that by putting in a StartDate, so in the afterUpdate for StartDate.

    Code:
    sbCreateReviews Me.StudentID, Me.StartDate
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks so much for your help John. It is excellent.

    Both of those Code snippets worked great except that the Dates that are inserted into the tblMonthlyForms are all 30 December 1899?

    Maybe I messed up the Code? This is what I changed it to in order to reflect the exact Field Names:

    Code:
    Public Sub sbCreateReviews(lngGAPID As Long, StartDate As Date)
        Dim sql As String
        Dim intcounter As Integer
        On Error GoTo sbCreateReviews_Error
        Dim DateDue As Date
        For intcounter = 1 To 5
        DateDue = ConfirmStartDate + intcounter * 28
        sql = "INSERT INTO tblMonthlyForms (GAPID, DateDue) Values (" & lngGAPID & ", " & DateDue & ")"
        
        CurrentDb.Execute sql
        Next intcounter
        Exit Sub
    
    sbCreateReviews_Error:
        If Err.Number = 3022 Then
        Resume Next
        Else
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure sbCreateReviews "
        End If
    End Sub
    Many thanks for your ongoing help and advice.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by farscape_fan View Post
    Both of those Code snippets worked great except that the Dates that are inserted into the tblMonthlyForms are all 30 December 1899?



    Code:
    Public Sub sbCreateReviews(lngGAPID As Long, StartDate As Date)
     Dim sql As String
     Dim intcounter As Integer
     On Error GoTo sbCreateReviews_Error
     Dim DateDue As Date
     For intcounter = 1 To 5
     DateDue = ConfirmStartDate + intcounter * 28
     sql = "INSERT INTO tblMonthlyForms (GAPID, DateDue) Values (" & lngGAPID & ", " & DateDue & ")"
     
     CurrentDb.Execute sql
     Next intcounter
     Exit Sub
    
    sbCreateReviews_Error:
     If Err.Number = 3022 Then
     Resume Next
     Else
    
     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure sbCreateReviews "
     End If
    End Sub
    You have changed the variable to ConfirmStartDate in one place, but not the other. (I have tried to show this in Red, but it seems you can't apply colour to a Code block within a Quote block.

    Variable names within a procedure don't need to correspond to actual field names, but they do need to be used consistently.

    PS Good practice is to begin each module with:
    Code:
    Option Compare Database
    Option Explicit
    Option Explicit requires variables to be declared. Without that, undeclared variables (as with ConfirmStartDate above) do not produce errors, but just give the wrong results.
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks. I changed the name at the beginning too, but it's still coming in as 30 December 1899?

    Could this be a bug in Access, or would there be another method that would force Access to recognize the correct Dates?

    Again, thanks so much for your help and advice.

    Brian



    Code:
    Public Sub sbCreateReviews(lngGAPID As Long, ConfirmStartDate As Date)
        Dim sql As String
        Dim intcounter As Integer
        On Error GoTo sbCreateReviews_Error
        Dim DateDue As Date
        For intcounter = 1 To 5
        DateDue = ConfirmStartDate + (intcounter * 28)
        sql = "INSERT INTO tblMonthlyForms (GAPID, DateDue) Values (" & lngGAPID & ", " & DateDue & ")"
        
        CurrentDb.Execute sql
        Next intcounter
        Exit Sub
    
    sbCreateReviews_Error:
        If Err.Number = 3022 Then
        Resume Next
        Else
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure sbCreateReviews "
        End If
    End Sub

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I don't think it is a bug in Access.

    Dates are stored as numbers in Acsess. If a Date field stores the number 0, you see that displayed as 30 December 1899.

    So the process for passing the date and incrementing it is not working here, and they are all ending up as 0.

    To test what is happening with dates I have added these two debug.print lines to the code below.
    Another way to try to sort out problems is to set a break point then step through the code. When code is in break mode, you can tell the value of variables by holding your mouse over them.

    Code:
    Public Sub sbCreateReviews(lngGAPID As Long, ConfirmStartDate As Date)
     Debug.print "Start Date: " & ConfirmStartDate
     Dim sql As String
     Dim intcounter As Integer
     On Error GoTo sbCreateReviews_Error
     Dim DateDue As Date
     For intcounter = 1 To 5
     DateDue = ConfirmStartDate + (intcounter * 28)
     Debug.print "Date " & intcounter & ": "& DateDue
    ps You have put the Option Explicit in I hope. Without that I would suspect that you have an undeclared variable through a typo.
    Regards
    John



  9. #9
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks again John

    I added the Debug line and ran it again.

    In the Immediate Window, I got the following:
    Start Date: 16/02/2010
    Date 1: 16/03/2010
    Date 2: 13/04/2010
    Date 3: 11/05/2010
    Date 4: 08/06/2010
    Date 5: 06/07/2010

    But in the actual Table, it's the 1899 dates????


  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I suspect your issue is with the construction of the SQL string, not the date value. Try debugging the SQL string and print what your execute line is running. For example you may need the # character around the date value.
    Wendell

  11. #11
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for your reply, Wendell, but could you advise what line of code I'd need to add at which point, please?

    I tried adding # to the sql line, eg

    sql = "INSERT INTO tblMonthlyForms (GAPID, DateDue) Values (" & lngGAPID & ", " & #DateDue# & ")"

    . . . but I got a Compile Error: Expected Expression message.

    Is there somewhere else I should put those to test it?

    Many thanks for your assistance.

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Try this

    sql = "INSERT INTO tblMonthlyForms (GAPID, DateDue) Values (" & lngGAPID & ", # " & format(DateDue,"mm/dd/yyyy") & "#)"

    Because you, like me, use Dates in dd/mm/yyyy format (I think) they need to be conveted to US format when used with SQL.
    Regards
    John



  13. #13
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks John, you're a star! That worked perfectly.

    Thank you so much for all your help, and thanks to everyone else for your replies--all greatly appreciated.

    Have a great day!

    Brian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •