Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append Query (97)

    I've got an append query that works just fine. However, I'd like the users to be able to look at the results of the query before the append, to make sure that they entered the variables properly. I could do this either one of two ways. 1) Change the append query so that results are shown on screen and then the user gets a choice to append the results to the table or not. 2) Change the query to a regular query, and have a button on the form to append query results to the table. However, I can't figure out how to do either one. I'm not strong on VB (but if it isn't too hard, I think I could do it). I keep thinking that there must be a way in macro to do this, but I have not found it. Any ideas?

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Append Query (97)

    If the queries are the same, why not create two queries. One is the append query and one is a select query. On your form, you could have two command buttons. The first runs the select query to review the data. If the data is ok, direct the users to press the other command button that runs the append query.

    This will save you from writing code which you could do on a command button as well. The code would first run the select query and then ask if the data is ok. If not, end the subroutine. It it is ok, then run the append query.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Lounger
    Join Date
    May 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (97)

    Thanks, it is an excellent idea. However, I already had it (I was so excited when I thought of it, by the way). The user would have to type in the variables (there are four) that define the query two times, and my supervisor thought that was too awkward, and had too much potential for error.

    Currently, I have the select query, and then another button to open up the table, and the user is asked to run the select query, then cut and paste to the table. It works, but is by no means elegant. I could go with that, but I wanted to get a look that felt cleaner.
    However, I have to admit that the part about the command button has me stumped. I know how to get the command button to run a query, but not how to get the question if the data is ok. That would help.... I'm trying to decide if it would be enough for me, but it would be close.

    Any thoughts?
    Thanks, though for your time.

  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Location
    Kent, Kent, United Kingdom
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (97)

    You can still use the two query idea - one as a select and one as an append

    The trick is to use a single form and get the user to enter the selection values onto that form. Both queries should then run off the same selection values.
    This is simple to do and means you can use the form to control options like "Is this data the correct data? If so click this button" etc
    (Incedentally you might want drop down boxes bound to the base table to prevent the user mis spelling a value - less chance of an error for your supervisor!)

    This approach needs only very basic VB

    If you want an example post and I'll copy something up. <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

  5. #5
    Lounger
    Join Date
    May 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (97)

    Ok, I'm getting there, it sounds like you have the answer. But I wanted to try the VB myself before I gave up. I just gave up.

    I don't want to think about the dropdowns yet, though it is a very valid point.
    I get the part about using a single form- agreed.
    I get the part about having the user enter the selection values onto that form.
    I understand the part about asking "Is this data the correct data....?"
    But I do not understand how to use the input variables captured when running the first (Select) query as the input into the second (Append) query. That is, I think the VB part. The input, by the way, is one number (currency), one date, and two text strings- one of which is 7 digits, and one of which is one digit long.

    So when I got out my VB book, I'm looking at recordsets, and now it looks like that is not correct, since I can't figure out 1) how to store the input to run the query 2) how to append the table. So I'm giving up on recordsets.


    Rather, it seems to be simply a matter of holding the four input variables on the side, and then applying them as input for the append query. Easy though it may be, I am stumped. I would appreciate some code.

    I do thank you for your time.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (97)

    I may be on a different path, but you do not need code to collect the input data on a form and execute two queries with that same input. Use a dialog form and keep it open. When either of the two queries run from buttons on that form, they will see the input values. Enter it once and you can use it twice.

    For example, you could create a dialog box form named Customer Info with a control for the CustomerID field. By entering a customer ID in the dialog box, you could have the query return the correct customer's company name, address, and city. In the Criteria cell, enter the expression that refers to the Customer ID control in the dialog box for the criteria values the query uses. In this case, you'd enter the expression: Forms![Customer Info]![CustomerID].

    You can run another query from that dialog form, using the same expression for the criteria in the second query's criteria. If the form remains open, Access won't ask for the input again.

    If you want to try this path, post back and I can post a small sample db.

  7. #7
    Lounger
    Join Date
    May 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (97)

    Please Post small database. I'd like to work through this myself, but it just isn't cost effective. My supervisor told me not to spend too much time on it, since I already have something that works (though is ugly). However, it would be good to learn about this. I looked through Access online help on dialog forms, and got lost as usual. Actually, I think I understand this, it is just that I'm not sure I do enough to start spinning wheels.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (97)

    Open the form frmItemDialog and select a Location. A report will display showing only the Items for that Location. If you look at the query design you will see the criteria which references the input on frmItemDialog.

    The OK button runs a report, you could have it run an query and put more buttons on to run more objects using the same input value. You could also collect more input values on the form for additional criteria.

    (I have had this sample for many years and do not remember where it came from - it may have been written by someone else on this forum.)

    HTH
    Attached Files Attached Files

  9. #9
    Lounger
    Join Date
    May 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (97)

    I can't open a zipped file. Could you unzip it?

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (97)

    Unzipped it is larger than 100K which is the attachment maximum ... you don't have WinZip?

    Any other ideas for getting this to you?

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

    Re: Append Query (97)

    You can download an evaluation version of WinZip at http://www.winzip.com.

    Even if the database is below 100 K, it can't be attached to a post. The Lounge software doesn't allow .mdb's.

  12. #12
    Lounger
    Join Date
    May 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (97)

    Got it. Now to see how it works.

  13. #13
    Lounger
    Join Date
    May 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append Query (97)

    Total Success! Many Thanks! This [Forms]![etc] stuff will really come in handy!
    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

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