Results 1 to 4 of 4

Thread: updating record

  1. #1
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224

    Unanswered: updating record

    I have 2 tables, Books and Checkouts
    Books has name, id and status
    Checkouts has id, startdate and enddate
    When i click on my return book button I want to update the returndate of the book to date()
    dbCW.Execute ("UPDATE checkouts set [enddate] = #" & Date & "# where [enddate]=null and [bookid]='" & Me.BookID & "'")

    Can you tell me whats rong with the code bcos it doesnt to work

    thank you

  2. #2
    Join Date
    Sep 2003
    Posts
    228
    what is the data type of bookid?

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by fullymooned
    dbCW.Execute ("UPDATE checkouts set [enddate] = #" & Date & "# where [enddate]=null and [bookid]='" & Me.BookID & "'")

    Can you tell me whats rong with the code bcos it doesnt to work

    thank you
    The easiest way to hack out the query string for this is to use the query design grid to design and test the query, then go to the SQL view and copy the SQL. Use this in your 'Execute' method. Add variables as needed for your criteria.

    Code:
     
    check these things:
    1. contain 'Date' Function in the query string
    2. Pound (#) signs not needed
    3. Use 'is Null' vs = Null
    4. quote (') marks not used on numeric fields ie. AutoNumber index
    
    CurrentDB.Execute("UPDATE checkouts SET checkouts.[enddate] = Date() " & _
    " WHERE (((checkouts.enddate) Is Null) AND ((checkouts.bookid)=" & Me.BookID & "))")
    ~

    Bill

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi fullymooned,

    I have a database that does something similar to what you are doing. In my form I have CheckBoxes for Completed and Fulfilled, then a field for ReceivedDate. What happens is once the product comes in I click on Complete CheckBox, and coded it so that it automatically inputs the CurrentDate in the ReceivedDate field,as well as checks the Fulfilled CheckBox for me too.
    Here is what I have:
    Code:
    Private Sub Completed_AfterUpdate()
    'Created by Carlton Edwards 3-9-2004.
    'This will input the date Received and check FullFill also. Fewer keystrokes.
          
        If Completed = -1 Then
               ReceivedDate = VBA.Date
               FullFill = -1
               [Print] = 0
            ElseIf Completed = 0 Then
               ReceivedDate = ""
               FullFill = 0
         End If
    
        
    End Sub
    Hope this helps you out.
    have a nice day,
    Bud

Posting Permissions

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