Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224

    Unanswered: overdue function

    I have the following fields
    end date
    status

    I need to change the status to overdue if the present date becomes more than the end date .. how do i do this

    thank you

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    do you need a update query for whole table? or it is in form? and what is the name of the table?
    ghozy.

  3. #3
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    I have a table called books
    it has bookid, bookname, status
    I have another table called check out
    it has bookid, empname, enddate

    In a form which is bound to table checkout bfor i click close i need to udate the status of the book in table books as out of library

    and if date is greater than enddate it should change the status to overdue

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    in your form's before update event type:
    Edit: sorry I thought you had a bound form. please follow my other post.
    Last edited by ghozy; 08-13-04 at 12:58.
    ghozy.

  5. #5
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    Here is some of the code m using

    Private Sub saverecord_Click()
    Dim save As DAO.Recordset
    Dim book As DAO.Recordset
    Set dbCW = CurrentDb()
    Set save = dbCW.OpenRecordset("Checkout")
    With save
    .AddNew
    ![Bookid] = me.bookid
    ![empname] = Me.empname
    ![Startdate] = Me.startdate
    ![Enddate] = Me.enddate
    .Update
    .close
    End With
    Set book = dbCW.OpenRecordset("books")
    Do While Not book.EOF

    If book!bookid = Me.bookid Then
    Status = "OUT OF Library"
    End If
    book.MoveNext
    Loop
    book.close
    End Sub

  6. #6
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    here is the code for you. I didn't test it. so please have a backup before trying.
    Code:
    Private Sub saverecord_Click()
    Dim save As DAO.Recordset
    Dim book As DAO.Recordset
    Set dbCW = CurrentDb()
    Set save = dbCW.OpenRecordset("Checkout")
    With save
    .AddNew
    ![Bookid] = me.bookid
    ![empname] = Me.empname
    ![Startdate] = Me.startdate
    ![Enddate] = Me.enddate
    if date()>me.enddate then '
    docmd.setwarnings false
    docmd.runsql "UPDATE books set status='overdue' where [bookid]=" & me.bookid
    docmd.setwarnings true
    end if
    .Update
    .close
    End With
    Set book = dbCW.OpenRecordset("books")
    Do While Not book.EOF
     
    If book!bookid = Me.bookid Then
    Status = "OUT OF Library"
    End If
    book.MoveNext
    Loop
    book.close
    End Sub
    Last edited by ghozy; 08-13-04 at 12:59. Reason: typo in the code
    ghozy.

  7. #7
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    Here is the file
    Lemme know if u need more info

    thank you
    Attached Files Attached Files

  8. #8
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    if the code I gave doesn't work tell me, so I will check your file.
    ghozy.

  9. #9
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    it doesnt work
    it says satus is not a defined variable
    which is the same problem my code was giving

  10. #10
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I fixed it and added the file back. and I also fixed some table design.
    *made bookid primarykey and made a releationship between books.bookid and checkout.bookid.
    * added afterupdate event to checkout form and in this event I update status of book table depending on bookid.
    *changed date fields in checkout table from text to date/time type. which was necessary to compare dates.
    Attached Files Attached Files
    ghozy.

Posting Permissions

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