Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    5

    Exclamation Unanswered: Capture user entered txt as parameter for other queries??

    Long story short......

    I'm working in Access 97
    I have 2 update queries and a report all of which need to have the same value entered to the [date] field. I built a macro to run them back to back as follows.

    SetWarnings = No

    this eliminates warnings generated by running update queries

    RunQuery1
    RunReport
    RunQuery2

    The user see's "which date", "which date", "which date".

    as Query1, Report, and Query2 all prompt the user for a date.

    Is there a way to capture the user entered text from the first prompt and pass that value to the Report and Query2?

    In case you are wondering the report gives daily, weekly, and monthly production numbers. In a table named [Date Table] I have assigned a number for [Week], and [Month] for each [Date] based on our accounting month. Many sub reports use the date table to determine which records need to be included in the calculations. I use the update query to increase [Week] and [Month] by 1 for every record with [Date] > [User Entered Date].
    I do this because the database is live 24/7, and I print the yesterday's report today (ie I print the report for the 3rd on the 4th). By increasing all records where [Date]>[User Entered Date], my calculations will not include partially completed records that have been entered on the 4th when the weekly and monthly totals are calculated for the 3rd.
    When the report is closed the Second update query decreases [Month] and [Week] by 1 for every [Date] > [User Entered Date], putting everything back to normal.
    Last edited by SonoDBCRE8R; 12-03-04 at 17:52.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    use a form -- point your macro/query/whatever at the form field.

    "parameter queries" are blunt instruments: you the coder get no chance to make a sensible prompt, to validate the entry, or to reuse the user-input.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Where are the parameters for your query? If they're in the where clause you have another option. You could use an input box to capture the user defined parameter, then pass the entire where clause with your parameters in the openreport action.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Dec 2004
    Posts
    5
    I'm not sure I understand your reply Teddy. I'm not the best coder, really I just muck around with things until I get them to work. For the queries I entered ">[which date(mm/dd/yy)?]" in the criteria section of the [date] field. Also I'm not sure how to build an input box? I'm in over my head here... .

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You would need to get into a tiny bit of vba to get this done. It's really not that scary.

    Here's the basic idea:
    Code:
    Dim d As Date 'Creates a variable called "d" that will hold a date
    
    d = InputBox("Enter Start Date") 'Asks user for a date, dumps it into "d"
    
    DoCmd.SetWarnings = False
    DoCmd.RunQuery("first_query")
    DoCmd.RunReport "YourReport", , , "yourField > " & d
    DoCmd.RunQuery("that_other_query")
    DoCmd.SetWarnings = True 'NEVER FORGET TO RESET WARNINGS!!1!1!!!1!
    Anyways, that's the basic idea. Don't be scared to get in there and get your hands dirty
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Dec 2004
    Posts
    5
    Thanks for the quick reply....

    I put the following into a module..

    Option Compare Database
    Option Explicit


    Dim UserEDate As Date 'Creates a variable called "UserEDate" that will hold a date

    d = InputBox("Enter Date(mm/dd/yy)") 'Asks user for a date, dumps it into "UserEDate"

    DoCmd.SetWarnings = False
    DoCmd.RunQuery ("Test Change Week Month Up"
    )
    DoCmd.RunReport "24hr Production Report", , , "date(M/D/Y)" & UserEDate
    DoCmd.RunQuery ("Test Change Week Month Down"
    )

    DoCmd.SetWarnings = True 'NEVER FORGET TO RESET WARNINGS!!1!1!!!1!


    It wouldn't compile, I believe the name of my queries caused a problem..it thought "Month" was the function Month()... anyhow, if and when I get this to work, how do I set it up so my user can access it.. use OnClick for a button?
    Sorry in advance for the silly question, I've never used modules, or coding, I've been able to get by without them so far.

Posting Permissions

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