Modifying an existing query using VBA (Access 2000 SR1)

Topic

New Reply

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,

Reply | Quote

Replies

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.

Reply | Quote

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,

Reply | Quote

Mark,

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

Reply | Quote

Sorry about that.

Reply | Quote

Mark,

There are three problems with your query and form/subform:

• You put the criteria in the query on two lines instead of one (this is essential.)
• 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.
• 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.

Reply | Quote

Mark,

There are three problems with your query and form/subform:

• You put the criteria in the query on two lines instead of one (this is essential.)
• 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.
• 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.

Reply | Quote

Sorry about that.

Reply | Quote

Mark,

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

Reply | Quote

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,

Reply | Quote

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.

Reply | Quote