Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    1

    Unanswered: Combo box requery values

    Iím trying to build a form and a subform, with two combo boxes, wich the first is a ComboBox to control the subform data, and in the Combobox on the subform I want to have requeried itís values accordingly with the main-form combobox.

    Basically the main-form is just to choose an option, to filter/requery data in the subform that will be in datasheet, and the combobox of the subform is to choose a value related to the option.

    Iíve tried to make the form and the result works on already made records and shows them respectively related to the chosen option, but the combobox of the subform returns all records existing when I click to choose another one to register, and the purpose is to return a list of values related to the option only.

    Iíve made two queries, one to do the list of the combobox, and the another one with that field and more fields form the tables to make the sub form datasheet. And still Iím lost.

    Can You help me out?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Difficult to answer without a bit more info on what your data source is for the subform. What I usually do is this:

    1. On my mainform, have a combobox (called for example, FName). I make sure this combobox is returning the ID or whatever field which matches the data I want returned in the subform.
    2. In the AfterUpdate event of the combobox (FName) on the mainform, I put in this code:
    me.MySubformName.requery
    3. In the recordsource for the subform, my sql syntax is something like this:
    "Select * from MyRelationalTableName where IDField = " & Forms!MyMainFormName!IDField & ""

    In regards to further filtering your subform's data, the sql syntax for 3 above might instead look like this:
    3. In the recordsource for the subform, my sql syntax is something like this:
    "Select * from MyRelationalTableName where IDField = " & Forms!MyMainFormName!IDField & " And FieldNameWhichMatchesComboboxSubformValue = '" & Forms!MyMainFormName!MyMainFormSubformName!combobo xSubformName & "'" (note that I used "" for the IDField match (being an integer type match) and I used "'" for comboboxSubformName assuming that it would be a text match.)

    Now you have to keep in mind that the above 3 isn't what I "think" you to do (this part was unclear in your question.) I think you want to requery the subform to data so it EITHER shows the matching combobox name on the mainform or combobox name on the subform. If for example, for when a value is selected in the combobox on the subform and you want to "override" the records returned from the FName sql 3 example above, then the recordsource might look like this:

    3. In the recordsource for the subform, my sql syntax is something like this:
    "Select * from MyRelationalTableName where IDField = '" & FieldNameWhichMatchesComboboxSubformValue = '" & Forms!MyMainFormName!MyMainFormSubformName!combobo xSubformName & "'" (note that I used "" for the IDField match (being an integer type match) and I used "'" for comboboxSubformName assuming that it would be a text match.)

    BUT then you also have to keep in mind that you don't want to simply issue a .requery command as stated in 2 above and you would need to also change the subform's recordsource before requerying (so the code in FName AfterUpdate event might look like this):

    (ie. AfterUpdate for combobox on mainform combobox name)
    me.MySubformName.recordsource = "Select * from MyRelationalTableName where IDField = " & Forms!MyMainFormName!IDField & ""
    me.MySubformName.requery

    Since you've already designed the queries, you can make your job even easier in the code above by simply setting the recordsource of the subform to the query name itself versus a sql statement:

    (ie. AfterUpdate for combobox on mainform combobox name)
    me.MySubformName.recordsource = "myQueryName"
    me.MySubformName.requery

    The "myQueryName" would be the one which shows a matching combobox on your mainform (ie. FName) or in the Afterupdate event of combobox on your subform (ie. options), it would look like this:

    (ie. AfterUpdate for combobox on subform)
    me.MySubformName.recordsource = "myQueryNamereturningrecordsmatchingcomboboxSubfor mName"
    me.MySubformName.requery

    (note: some may argue that you don't need the .requery command but I usually do it to make sure the data is requeried.)

    You may also want to consider having the combobox on the Subform moved to the mainform since it's basically a 1 recordset or another for criteria on the subform's recordsource and the query criteria is always then based upon a field in the mainform.) Again, it depends on how you want to do it.

    Long story short, all your basically doing is setting the recordsource for your subform which matches the correct criteria you want it based off of. I like simply setting it to a query name but the drawback of this can be that you then can end up with dozens of dozens of queries. On the other hand, I also hate spending any time trying to troubleshoot a sql statemnet in the coding and prefer the query designer view of a query to do my troubleshooting.

    Lastly, sometimes I'll do a refresh command first in the Afterupdate event:
    (ie. AfterUpdate for combobox on mainform combobox name)
    refresh
    me.MySubformName.recordsource = "myQueryName"
    me.MySubformName.requery

    The reasoning for the refresh command above is that if there are other buttons/fields on the mainform, I want to "hardcode so to speak" that the mainform be refreshed before changing the recordsource to make sure it has the selected value should someone only use the mouse to select the value (same for subform AfterUpdate) in the combobox.
    Last edited by pkstormy; 09-17-09 at 19:42.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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