Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2004
    Location
    Tunbridge Wells, Kent. UK
    Posts
    24

    Exclamation Unanswered: parse value from a form to a query via macro

    Help!

    I have a form frmEnterDate. I want to run a process that opens this form, I enter a date in the dialog box and this date value is then passed to two further queries. I have tried to use one macro to open the form, and then it calls another macro which runs the parameter query (which take a value from the 'loaded form'). Of course it does not work, becuase I have to close the form frmEnterDate in order for the first macro to continue running! I want to use this EnterDate form from lots of places, so I do not want to add an event to it that specifically runs only the second macro, I need to just continue running the original macro, once the value has been entered. (This assuems I am doing it the right way - which I'm probably not!

    Any help gladly accepted

  2. #2
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Hi,

    I think you may be into writing this in VBA and abandoning Access Macros. I invite other posters to comment on this. While macros pause after opening a form (I think this is as you say but it's a long time since I wrote a macro), VBA procedures plough on to the bitter end of the code except when the form is opened as a pop-up dialog.

    Unfortunately passing parameters to parameter queries in VBA, especially using the ADO model, is not the easiest thing. I often liken it to an alchemist's black art. Luckily in most situations parameter queries can be avoided by using the RecordSource and Filter properties of many objects or building the SQL dynamically within the code.

    If you want to go the VBA route and no one else offers you a Macro solution then I'm willing to help if I can.
    Rod

    fe_rod@hotmail.com

  3. #3
    Join Date
    Oct 2004
    Location
    Tunbridge Wells, Kent. UK
    Posts
    24

    parse value from a form to a query via macro

    Thanks Rod. Unfortunately I am a bit of a beginner at all this, and every time the VB code window opens up, my pants get wet, my pulse races, more beer gets spilt as I reach for the mouse to close the window as quickly as possible! Surely though I can set up some temp variable, assign a value to it, and use it later in the process. If we have to go the hard way we will, but lets just wait an hour or so to see if any bright spark can make my life simpler!! Cheers.

  4. #4
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    How many more beers will you get through in that hour? You make me envious, alcohol's banned here.

    Seriously, does it all boil down to storing a value in a session-wide variable?
    Rod

    fe_rod@hotmail.com

  5. #5
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Create a table unrelated to anything else...

    t_frm_enter_date
    id, the_date


    Store that enter date value in that table and have queries, form, and reports call for that value...

    i.e. you have a query q_a_query structured like so:
    "SELECT * FROM your_tables, t_frm_enter_date WHERE your_table.a_date = t_frm_enter_date.the_date"

    Does that help?

    Rod.... no alchohol? I bet in most ways that's a good thing...

    -Warren

  6. #6
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Oh...

    If that doesn't work, either me or Rod will give you some help with writing the VBA code so long as you agree to keep your pants dry...

    -Warren

  7. #7
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    The thought of damp pants all over Tunbridge Wells somewhat puts me off my breakfast so we have to do something about this quickly. However I rather suspect it's the beer and not the VBA code window that causes all this moisture.

    If you specify that a form opens 'hidden' then even a macro does not pause but races on through subsequent statements. Hence if the requirement is simply to store and retrieve values during an Access session and you don't want to step through the looking-glass into the wonderland of VBA may I suggest the following.

    Design a form with an unbound text box for every value that you wish to store and retrieve. At start-up, perhaps in an AutoExec macro (does it still work this way?), open this form as hidden (bottom line in the parameter box). You may not be able to see it but the form is there, it is added to the collection of loaded forms and it, and all its controls, are addressable. This should solve your problem in a macro halting for a visible open form and gives you a place to store and retrieve values.
    Rod

    fe_rod@hotmail.com

  8. #8
    Join Date
    Oct 2004
    Location
    Tunbridge Wells, Kent. UK
    Posts
    24
    dont think this will work. I need to change the value in the date entry form during the session, depending on what I want to do. Perhaps list all records this week, or may be today or maybe next week, etc. I would like lots of operations to utilise this date entry form.

  9. #9
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Pity. I seem to have misunderstood the situation.

    "I want to run a process that opens this form, I enter a date in the dialog box"

    Is the form the same thing as the dialog box?

    "this date value is then passed to two further queries"

    Presumably you want to run these queries as well. What do they do? No I don't mean you to explain how the queries work but all queries return a result set; what do you do with that result set?

    "I want to use this EnterDate form from lots of places"

    This sounds to me much like an Input Box.

    Anything more complicated is best solved - are you sitting comfortably - with exploiting the form as an instance of the form object class. If that sounds like gobbledegook it is because I can't express it any differently. Passing values to a form is straightforward via the OpenArgs but getting the form to pass back a value is more awkward. Solutions include storing values in global variables, the Registry, and the system properties. However if you open the form as an object and code some property let/get pairs then you can have a dialog with your form, passing data back and forth. You can even define events such that your 'calling' object can be informed when significant things happen (such as the user completes entering a valid date). OK, this is all a bit advanced and sounds like overkill for your requirement.

    If you can answer my questions perhaps we can find a simple solution.
    Rod

    fe_rod@hotmail.com

  10. #10
    Join Date
    Oct 2004
    Location
    Tunbridge Wells, Kent. UK
    Posts
    24
    Hi Rod - v.brief background. The DB is used to store all the fixtures of the cricket & hockey club for a season. What date the games are, against which club, time & venue for the match are all held in none table, Opposition club names, contact details. email addys etc are all held in a separate, related table. We have seven teams so as you would expect we could have many fixtures against 1 club, throughout the course of a season.
    =========================

    "I want to run a process that opens this form, I enter a date in the dialog box"

    Is the form the same thing as the dialog box? - YES

    "this date value is then passed to two further queries"

    Presumably you want to run these queries as well. What do they do? No I don't mean you to explain how the queries work but all queries return a result set; what do you do with that result set?
    1st query find all the fixtures we have on the 'date entered' and goes and finds all the email addresses for those clubs we are playing, and then dumps them into excel (not very elegant, but my next challenge is to put the multiple selected email addys directly in to the 'To:' field of an Outlook email)

    2nd Query runs a report that outputs all the fixture details for that specific date. It is invoked by a macro, so I can make use of the 'SendObject' function, so it outputs is an .rtf file sa an attachment to an email. (Of course I have to enter the fixture date AGAIN).

    Basically, this all started, because it is very inelegant to have enter the date twice, as each query runs. So I wanted to enter the date once, and have that value made available to both queries when they run.

    "I want to use this EnterDate form from lots of places"

    This sounds to me much like an Input Box. - I'll take your word for it, but it sounds right.
    Anything more complicated is best solved - are you sitting comfortably - with exploiting the form as an instance of the form object class. (Beer spilt!)

    If that sounds like gobbledegook it is because I can't express it any differently. Passing values to a form is straightforward via the OpenArgs but getting the form to pass back a value is more awkward. Solutions include storing values in global variables, the Registry, and the system properties. However if you open the form as an object and code some property let/get pairs then you can have a dialog with your form, passing data back and forth. You can even define events such that your 'calling' object can be informed when significant things happen (such as the user completes entering a valid date). OK, this is all a bit advanced and sounds like overkill for your requirement.

    If you can answer my questions perhaps we can find a simple solution.

  11. #11
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Thanks. Have another beer while I think about it. Well you may have to have two or three as my wife returns tonight!
    Rod

    fe_rod@hotmail.com

  12. #12
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    While creating additional properties for your form object will work, it will take a good amount of code to accomplish this...

    Was my suggestion to store this date in a table/entity overlooked or ignored? It seems like the easiest way to implement this problem?

    -Warren
    -Warren
    Hack the Planet.

  13. #13
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Warren don't take it personally. Having now asked the questions I should have in the first instance it seems that my conclusion that it was a problem of storing a date value is wrong, a total red herring in fact. The problem seems to be: how to pass a known date value as a parameter to one or more queries without recourse to VBA.

    Well, I was on the point of giving up and saying this is impossible when one more pass through that adventure game known as MS Help suggested something that either I have never known or have forgotten. I can envisage many posters reading this and casting their eyes heavenwards muttering, "He didn't know that! This is basic stuff."

    Anyway enough of the rubric; yes it is possible and here is how you can do it. MC what you need to do is separate your two queries into two macros with the 'crunch' statement in each macro being the OpenQuery statement. I think you have already done this. Then you need a form with at least three controls: one to hold the date value and two command buttons, each associated with one of the macros. In design mode for the form, label each command button with something appropriate and then in the event tab of the properties dialog select the OnClick event. Reference your macro here.

    Now you need to go to the design grid for each of your queries and on the Criteria line instead of having something like [Enter date] substitute the following.

    [Forms]![MyFormName]![MyControlName]

    Where MyFormName is the name of the form I refer to above and MyControlName is the name of the control on that form containing the date parameter.

    Now when you click the command button the macro will execute and the query will automatically take as its parameter the date in the control on the form provided that the form is loaded. (If you can see it, it's loaded!) My tests indicate that Access is a little bit fussy about the formats of the control holding the parameter value and the format of the column in the query; these should both be the same. If the formats conflict or the form is not loaded the query returns an empty result set - no error message!

    I have tried different forms or syntax for the query parameter and only the bracket/exclamation mark syntax shown above seems to work. Forms("MyFormName") causes an error.

    Now I have a suggestion. Why enter the date? Why not make the control on your form a combo or list box that is populted by a query that extracts only the dates on file later or equal to today? This also helps to keep the formats the same.

    Now for the bad news. I can discover no way other than VBA of automatically populating the 'To:' and other values in the SendObject method. But don't despair, the code will not be too complicated and we can talk you through inserting it into an appropriate event listener.
    Rod

    fe_rod@hotmail.com

  14. #14
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi MCawsey, Rod, Warren,

    Back at the top, YES Rod you are very very correct about abandoning the Macros for VBA coding. MCawsey, Learn VBA as I am. Really it seems more intimidating than it actually is. I'm a newbie and have done well in learning VBA in place of Macros. How did I do it? Well, Uncle Bill created something of real value. To learn how to write VBA code to replace a Macro, there is this thing called the Convert Macro-To-Code. What it does? Simply create a Macro then click SaveAs for the Macro, click the drop-down arrow on the As part and select Module. It then asks if you want ErrorHandling and Include Macro comments...just click CONVERT. DONE. NOW, close that window and go to Modules. Look for the Module you just created and click the DesignView to look at it. Voila, you're no longer a virgin, ya just lost it....haha. THAT was how I got started, now I am kinda almost intermediate level or advanced basic user.
    Word is Uncle Bill is going to soon drop Macros in favor of VBA or whatever comes down the line next. It would be a good idea to start learning it now. AND it is much more powerful and flexible than Macros.
    ROD/WARREN, if this is a process he is going to do many times over would this be a good case for a Function to keep all that in and do a Call for it when needed wherever? Kinda like a GlobalVariable? Just asking as I'm still learning myself.
    Also, once the Queries are set, he can run them from VBA as I have done in my databases.

    Have a nice day ya'll
    Bud

  15. #15
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Bud,

    Just to add to what you are saying, many Office applications have a Record Macro facility - Word in particular. Use it to record a few actions and then review the code it produces. Such code introduces you to the COM object properties and methods and how to use them.

    Yes, it's always a good idea to isolate any common re-used process in one procedure or set of procedures.
    Rod

    fe_rod@hotmail.com

Posting Permissions

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