Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query updating a form (97/SR2)

    Not sure how to explain the problem, but I'll give it a go:
    I have a form with a button that runs 2 queries, one to append some names (+unique key) to another table and one to update the date of transaction field in the 1st table. My problem happens when an error occurs in the first query. The second query still runs (needs to be relatively automatic as users aren't very computer literate), and updates the date even though no data was appended.

    The queries are Docmd.runsql statements and if a NO is pressed as an answer in the 1st query it jumps to the error section and therefore doesn't run the second query. However, the problem occurs when a unique id is already in the 2nd table - the data won't be appended - it displays, "can't append due to key violations". If the user clicks "yes", the 2nd query still updates the date of transaction field for the record not copied.

    Is there a way of capturing what button the user presses in answer to the "can't append" question Access asks?

    I have used the command below before, but I couldn't find anything to get the response from a query.
    impResponse = MsgBox("msg", vbYesNo + vbDefaultButton1 + vbInformation)

    Any help would be much appreciated.

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query updating a form (97/SR2)

    I would suggest that you use DLookup before you run your append query to see if the ID exisits. If it does then pop up a message telling the user and then exit the subroutine without running the query.

    hth,
    Jack

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query updating a form (97/SR2)

    Thanks Jack, i'll give it a go.

Posting Permissions

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