Results 1 to 8 of 8
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating Table (Access 2002/Windows XP)

    1) What is the structure of tblHistory?
    2) What is the SQL of the update query you were trying to use?

  2. #2
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating Table (Access 2002/Windows XP)

    The database has a tblhistory that is updated each month when rents are paid. An append query is performed to do this. The user at times may need to make comments to a particular rent, so there is a comments section on a form to add any needed comments to the table. I was using an update query to add these comments, but then found out that I cannot use an update query based on rent number and month because it updates the entire table each time and erases anything previously input. How can the users comments get into the tblhistory by month and rent number? These comments would either be viewed or printed that month or in the future. Any suggestions would be appreciated. Thank you in advance.

  3. #3
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating Table (Access 2002/Windows XP)

    1. The History table consists of mostly text data types. It has a few number data types and also a date data type. The structure is standard.
    2. The SQL for the update query is: UPDATE tc_RentHist SET tc_RentHist.Comments = [forms]![frmhistory]![txtcomments]
    WHERE (((tc_RentHist.INV_NUM)=[forms]![frmhistory]![inv_num])) OR (((tc_RentHist.Comments) Is Null));

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating Table (Access 2002/Windows XP)

    In the first place, shouldn't it be AND instead of OR?
    In the second place, shouldn't there be a condition on the date field? If so, does the form contain a text box or other control that can be used for this?

  5. #5
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating Table (Access 2002/Windows XP)

    The AND sounds more appropriate but perhaps the null isn't needed at all because the user may want to edit or over write an existing comment.
    When the user wants to look at a particular Inv_No for a rent they type in the Inv_No in a message box which then brings up the frmhistory. The frmhistory then shows in the detailed section a running total of previously paid rents by date. i.e:
    Comdate SchNo Amt Comment
    10/16/2006 C123456 1000.00
    11/17/2006 C654987 1010.00 Rent increased by $10 per contract
    I would think the SQL would need a condition for date because when the comments text box is typed in by the user. Every comment text box available gets filled in with the same information.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating Table (Access 2002/Windows XP)

    I first thought your form was unbound, but now I get the impression it is bound to tblHistory. If that is correct, why would you need an update query? Wouldn't entering or editing a comment automatically update the record?

  7. #7
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating Table (Access 2002/Windows XP)

    The frmhistory is bound to a query so when the form comes up it brings up all needed informaiton for the user to view. The comments text box, however, is unbound because if it was bound the user would not be able to input information into the field. I was thinking I needed to run another query to update any new comments input by the user, but an update query rewrites over all information.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating Table (Access 2002/Windows XP)

    Is the query not updatable? If so, do you know why not?

Posting Permissions

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