Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modifying an existing query using VBA (Access 2000 SR1)

    Hello,

    I currently have a form with 5 different comboboxes for the user to select filter criteria. As a value is selected for each combobox, I would like to adjust the query to match the parameters outlined by the comboboxes. There will always be one combobox that has a value, but aside from that all or none of the other comboboxes may or may not be used.

    I would like to use the on change event of each combobox to redefine the query, but I do not wan the changes to be permanent.

    Any help would be appreciated.


    Best REgards,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modifying an existing query using VBA (Access 2000 SR1)

    You don't provide much detail, but you could set up the query as follows:
    - In an empty column, enter [Forms]![frmMyForm]![cbxMyCombo1] in the Field row, substituting the correct form and combo box name.
    - Clear the Show check box in this column.
    - Enter [NameOfField] Or Is Null in the Criteria row for this column, substituting the correct field name.
    Repeat for the other combo boxes.
    Save the query.

    You won't have to modify the query, and you don't need an event procedure for the combo boxes.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modifying an existing query using VBA (Access 2000 SR1)

    Thanks for your suggestion, but I cannot seem to get it to work. I am attaching a sample of the DB. If you have time, would you mind taking a look at it and telling me what I am doing wrong.

    Regards,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modifying an existing query using VBA (Access 2000 SR1)

    Mark,

    The attachment doesn't seem to have made it. Could you try again?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modifying an existing query using VBA (Access 2000 SR1)

    Sorry about that.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Modifying an existing query using VBA (Access 2000 SR1)

    Mark,

    There are three problems with your query and form/subform:
    <UL><LI>You put the criteria in the query on two lines instead of one (this is essential.)
    <LI>The combo boxes are bound to the primary key fields of the lookup tables, so the criteria must refer to these fields too, instead of to the descriptive text fields.
    <LI>You must requery the subform in the After Update event of each of the combo boxes.[/list]By the way, I find your jurisdiction combo box with repeating names in the displayed column confusing.

    I have attached the modified version.
    Attached Files Attached Files

Posting Permissions

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