Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL statements based on recordsets? (Office 2000 / SP2 and VBA)

    I have a situation where a subform on a form can be made to display a different set of records drawn from a table by clicking on a button on the main form. I have used the "RecordSource" property for the subform in VBA function to determine the records displayed in the subform. For example if I click on a button named "Bagel" then all records containing the string "Bagel" somewhere are displayed. I now want to further pare down the list displayed when I click on a second button (say "toasted") by somehow restricting the recordsource of the subform to the subset of the current recordset also containing the word "toasted". EITHER BEFORE OR AFTER 'BAGEL'.
    As the RecordSource property only accepts a Table or
    an SQL statement in its syntax this isn't easy. What seems to be a way out is to create a new temporary table from the bagel recordset and apply the SQL for 'toasted'statement to that, and so on.
    But this seems clumsy. Is there some easier way?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL statements based on recordsets? (Office 2000 / SP2 and VBA)

    Why is it any harder to create the recordsource a second time adding the "toasted" parameter to the Where clause? You'll only need to add an AND phrase to it:

    Where [Choice] Like "*Bagel*" AND [Choice] Like "*toasted*"
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL statements based on recordsets? (Office 2000 / SP2 and VBA)

    Voil

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL statements based on recordsets? (Office 2000 / SP2 and VBA)

    Although the suggestion works well I now have a more complicated need, viz to create a fairly complicated recordset depending on the choices of "bagel", "toasted" and an unspecified number of extra parameters like these. The SQL code would become increasingly convoluted and complicated with each additional choice, not just "and **something**".
    I thought I could somehow use the Recordset property for forms to specify the recordsouce of the various subforms but this doesn't work as a subform is really a control and you can only use the recordset property with OPEN forms. Is there some indirect way you can specify the recordsource of a subform control using the recordset property?
    I hope this doesn't sound all too woolly and weird!

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL statements based on recordsets? (Office 2000 / SP2 and VBA)

    You can use the Name property of a DAO recordset to return the SQL used to generate the recordset, but I'm not sure how that would help you. Why do you need an indirect method of setting the recordsource?
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL statements based on recordsets? (Office 2000 / SP2 and VBA)

    I'd love to set the recordsource directly (as you apparently can with an open form from the forms collection) but a subform is not a form, it is a control which displays a form's records. And the thing is I want to vary the recordsource of the forms displayed in several different subforms according to what information is entered on the main form by clicking on captioned buttons. The captions ARE the data that generates the recordset on which the subforms so created would be based. I think I am going to scream.
    The idea is this: if a customer in my cafe says " I had a bagel, a coffee and a chicken sandwich" then I click on the buttons labelled "bagel", "Coffee" and "chicken" and Access will seach through all unpaid orders and return a set of possible matching unpaid orders. These unpaid orders would be ranked according their probability of being the actual orders involving that customer. It gets complicated because the customer's consumed items may not have all been on the same order. They often casually add extra things during their visit and these would not necessarily be put on the original order. I AM going to scream.
    Any brilliant suggestions would be really appreciated.
    Thanks!

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL statements based on recordsets? (Office 2000 / SP2 and VBA)

    We may be confusing each other. I do this all the time with subforms without problems. The suform control doesn't have a recordsource but the subform.Form does. The simplest way is to dim an object variable as a Subform and set that variable equal to the subform control's Form property. Then you should have no problem setting the recordsource of the subform object.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL statements based on recordsets? (Office 2000 / SP2 and VBA)

    Yes Charlotte, that's it. Thanks. In fact I didn't bother dimming a variable I just used:
    Set Forms!PaymentScreen!CurrentOrdersubform01.Form.Rec ordset = rst5
    Of course you need the .Form part which was missing before.
    David

Posting Permissions

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