Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Filtering Subform With Combo Box

  1. #1
    Join Date
    1st May 2007
    Posts
    21

    Filtering Subform With Combo Box

    Hi,

    I'm trying to create a form that will allow users to filter a subform using a combo box. The snag is that the subform must contain an updateable recordset, so I need to base it directly on the source table, rather than on a query.
    The main form that the subform is embedded in is unbound (I'll eventually need to use two or three of these linked subforms on the same form), so I don't think I can use a master-child link (or can I?); I haven't been able to get ApplyFilter to work because the underlying table/subform isn't actually open, but embedded. Is there something I can do using SetValue on the datasource of my subform? Or anything else, for that matter?

    Thanks in advance for any suggestions!

    Clair

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    12th May 2005
    Location
    Land of long white clouds and flightless birds.
    Posts
    140

    Re: Filtering Subform With Combo Box

    Hi Clair

    Nice to be able to address someone by name.

    For me, I prefer to work with unbound forms even though they require a little more thought and work.

    You have almost answered your own question I think. The way I would do this would be to put an AfterUpdate event on the combo. This event uses the bound value of the control concatenated into a SQL string. You then set the recordsource of the subform as this string and then requery the subform - bling! your data will appear. As it is an unbound form, you will then need a save process (activate it when a new record is selected or when the subform is exited) which updates the existing record. If you are wanting to create new records from this form you would need a similar process which does an insert rather than an update.

    Just as a matter or technique, I would create the subform as a bound form using the table as the recordsource first just to make it easier to create the controls you need. Actually, having said that, once you have created the form this way leave it bound, then modify the recordsource as a select query with all the columns you need to match the controls and then hardwire a criteria into the query so that the query will always return an empty rowset. That way, when you open the form the subform will always be blank; it will only display data after you use the combo on your main form.

    If any of this doesn't make sense, or you want some further help, just post.

    Regards

    Rowan

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    1st May 2007
    Posts
    21

    Re: Filtering Subform With Combo Box

    Hi Rowan,

    Thanks for your hints! I'm trying to do exactly what you said, but for some reason it doesn't work. I've got an 'apply filter' button that should setvalue the recordsource of the subform to a concatenated string held in an unbound text box, which is itself made up of the SQL defining the fields that I want, and the variable parameters taken from the combo boxes. It will then requery the subform.

    Unfortunately, every time I press the button, it comes up with an error: "The object you referenced in the Visual Basic procedure as an OLE object isn't an OLE object". I'm guessing that this is a problem with how I'm referring to the subform, but I'm using the expression builder in Access 2007, so I'm not quite sure how I can be doing it wrong!

    Any further suggestions would be much appreciated!

    Thanks again,

    Clair

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th May 2005
    Location
    Land of long white clouds and flightless birds.
    Posts
    140

    Re: Filtering Subform With Combo Box

    Hi Clair

    Post the vba code behind the button along with the SQL string and then we can see what is going on. Why the error is referring to an OLE object is the puzzling thing. Have you placed any OLE controls on the form? I don't have 2007 to be able to build a test form with.

    If you know how to use the debugging functions, set a breakpoint on the event procedure behind the button then step through the code. That will let yousee exactly which piece of code is producing the error. You may find it is the way you are concatenating the string - might be a syntax error in it.

    Post the code anyway.

    Cheers

    Rowan

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    1st May 2007
    Posts
    21

    Re: Filtering Subform With Combo Box

    Hi Rowan,

    Unfortunately at this point things have become slightly more complicated.

    Because I'm working in 2007 I have to convert the database to 2002-2003 format in order for anyone else to be able to understand what I'm doing, code-wise. In order to do that I have to convert the macros that 2007 has handily built for me into VBA code; so far, so good. However, now that I'm running it in 2002-3, I'm getting a new error message, telling me that Access can't find the sub-form that I'm referring to.

    I'm sure there's some really basic aspect of how to refer to embedded subforms that I'm missing, but I just can't find what it is. I've attached a mini version of the converted database to show what I'm trying to achieve - anything that you can suggest would be a great help!

    Thanks,

    Clair
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th May 2005
    Location
    Land of long white clouds and flightless birds.
    Posts
    140

    Re: Filtering Subform With Combo Box

    Hi Clair

    Just simple stuff. The syntax of your sql statement is wrong. The value coming from the combo control is alpha, so when you concatenate it into the string you have to include the opening and closing quotes so that Access knows to treat it as alpha and not attempt to treat it as a number. Thus, it should be:
    VB:
    ="SELECT execs.CUSTOMER, execs.[salesexec] 
    FROM execs 
    WHERE ((execs.CUSTOMER)= '" & [Forms]![Filter]![Combo] & "');"
    
    
    The form referencing was incorrect as you suspected:
    VB:
     'Forms!sfExecs.RecordSource = Forms!Filter!SourceCode
    Me.sfExecs.Form.RecordSource = Forms!Filter!SourceCode 
    
    
    Finally, I don't know if others will agree with me but unfortunately, Microsoft have taught us bad habits by allowing spaces in file names. My early computing was done in Unix and MSDOS, both treating spaces in file names as signifying the end of one name and the start of another. Consequently, the habit of spaces in filenames follows through into code and databases as I see it all the time with younger programmers that I teach; I never use spaces in object names or references. If I feel the urge to use a space, I use the underscore. I personally don't believe that it is good practice and I'm sure most seasoned programmers (I don't include myself in that category by the way - I'm still learning as far as I'm concerned) will agree with me. So before you use the code above, note that I have changed the object names to remove them for my testing.

    All in all, you were pretty close - its always the little things that cause the breakages! And the frustration!

    At the risk of incurring the wrath of the moderators on this forum, I am attaching a doc file which you might find useful. It contains examples of the syntax for referencing forms, subforms and the controls/properties for them. I've found it useful as a reminder as I am not writing code every day so it is not second nature to me. I hope it will be of use to me. I can't take credit for it - I found it on a website some years ago I think written by a guy called Keri Hardwick. Anyway, I have always been grateful for his contibution to my efforts.

    Cheers

    Rowan
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    1st May 2007
    Posts
    21

    Re: Filtering Subform With Combo Box

    Thankyou so much Rowan, that's fantastic! I knew it would be something simple. I've been using the 'embedded macro' option in Access 2007, which basically builds the code for you using a series of set options... unfortunately it doesn't allow nearly as much flexibility as writing your own code, and this seems to be one of the functions that it just can't cope with. I copied my SQL from the query that Access built for me automatically, which obviously isn't such a good idea either in some circumstances... so anyone else that's using Access 2007 would be well advised to double-check the code that is automatically generated if they're having problems!

    Thanks again,

    Clair

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    12th May 2005
    Location
    Land of long white clouds and flightless birds.
    Posts
    140

    Re: Filtering Subform With Combo Box

    Hi Clair

    Copying the SQL from a query is the best way to go - I do that all the time. Typing it in when you've got a tool that generates it doesn't make a lot of productive sense. You get a query working as a query, verify the data, etc. Then you know that the one in your code is going to do the right job as well.

    The trick is being aware of all the traps such as the one you fell into. But being able to use the Immediate window function of the debugger is your best tool in this regard, combined with setting and using breakpoints. If you are not sure about how to use these have a look around the net to see if you can find some instructions. Also the Access help. If you have any questions that you can't find the answers for, you know where to come for assistance.
    ;-)

    All the best.

    Regards

    Rowan

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Deleting Records Through VB in a Subform
    By Hazy in forum Excel and/or Access Help
    Replies: 1
    Last Post: June 29th, 2006, 17:52
  2. Combo box requery method causing onCurrent event in subform!
    By A9192Shark in forum Excel and/or Access Help
    Replies: 3
    Last Post: June 7th, 2005, 18:57
  3. Filtering using a Combo Box
    By klxracer in forum EXCEL HELP
    Replies: 1
    Last Post: June 6th, 2005, 19:01
  4. List Box/Combo Box in a subform
    By willbrewin in forum Excel and/or Access Help
    Replies: 1
    Last Post: January 10th, 2005, 17:43

Bookmarks

Posting Permissions

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