Get dynamic data based on checkbox selected in access query

[hrdpgajjar]

Hi there,
I've a table named "clients" which contains following columns,

- Client Name
- Client City
- Client District

now I need to create a query which shows all data of specific "District" selected by checkbox.

Note that there is no checkbox option selected in table design. And all fields are of same property i.e. short text only

I've created a separate form which has five checkboxes with district name, and a button which generate query.

My question is, How can I design a query to generate data of only the districts selected by checkbox.




Thank You,

 

[arnelgp]

use Combobox or Listbox instead.
if you use checkbox on the form, everytime you add new "district" you will need to
modify the form and add more checkboxes.

 

[hrdpgajjar]

use Combobox or Listbox instead.
if you use checkbox on the form, everytime you add new "district" you will need to
modify the form and add more checkboxes.

Thank for the prompt reply.
But my district numbers are fixed and my idea is to generate a report based on multiple districts selected as per my requirement. So please guide me how can I do this.


Thanks

 

[arnelgp]

add Option Group to your form
then you only need to check the Value of this control, example
on AfterUpdate event of OptionGroup:

Private Sub Frame0_AfterUpdate()
Select Case Frame.Value
Case 1 'the first district on your option group
'what to do
Case 2 'the second dstrict on your option group
'what to do
...
...
End Select
End Sub

 

[hrdpgajjar]

add Option Group to your form
then you only need to check the Value of this control, example
on AfterUpdate event of OptionGroup:

Private Sub Frame0_AfterUpdate()
Select Case Frame.Value
Case 1 'the first district on your option group
'what to do
Case 2 'the second dstrict on your option group
'what to do
...
...
End Select
End Sub

I've tried it but it gives me option to select any one district only. It does not serve my purpose. What I need is to select multiple checkboxes (Districts) at the same time to get data from query.


cheers!!

 

[GPGeorge]

"I've created a separate form which has five checkboxes with district name, and a button which generate query."

This approach, unfortunately, is the most difficult to implement even though on the surface it does seem attractive.

As you've already been told, it is inflexible and unforgiving of any changes in the data to be used. Any new district will require overhauling the form, the queries involved and the VBA involved. Your maintenance work is guaranteed for the life of the relational database application.

Instead, there is a "once and done" approach that can involve checkboxes, but which is flexible and dynamic.

You will need to do this in a subform displayed in continuous or datasheet view, or as the original suggestion, in a multi-select list box. I would implement the latter because it is less work and just as effective, but maybe that's just me.

Create a query that includes both the table in question and a helper temp table with two fields: "District" and "Included". District needs to be the same values as the values in the original table. Included is a Yes/No datatype field, formatted as a checkbox.

Here's a screenshot of a similar approach from an existing sample accdb I have:

If you create a subform using this query as it's recordsource, you can then insert it into the form where you need to select one or more districts by checking the checkbox(es) in the subform.

Then, you can use code to create your required output query by filtering it against the records in this subform, i.e. records in the query where "Included" is yes FOR THAT PARTICULAR ITERATION. Of course, you'll also have to add code to update the helper table to set all of the checkboxes to False between each session, or after each time you generate the filtered query.

As noted, additional work is required, but you can get a result very close to the requested technique.

 

[hrdpgajjar]

"I've created a separate form which has five checkboxes with district name, and a button which generate query."

This approach, unfortunately, is the most difficult to implement even though on the surface it does seem attractive.

As you've already been told, it is inflexible and unforgiving of any changes in the data to be used. Any new district will require overhauling the form, the queries involved and the VBA involved. Your maintenance work is guaranteed for the life of the relational database application.

Instead, there is a "once and done" approach that can involve checkboxes, but which is flexible and dynamic.

You will need to do this in a subform displayed in continuous or datasheet view, or as the original suggestion, in a multi-select list box. I would implement the latter because it is less work and just as effective, but maybe that's just me.

Create a query that includes both the table in question and a helper temp table with two fields: "District" and "Included". District needs to be the same values as the values in the original table. Included is a Yes/No datatype field, formatted as a checkbox.

Here's a screenshot of a similar approach from an existing sample accdb I have:

View attachment 106565

If you create a subform using this query as it's recordsource, you can then insert it into the form where you need to select one or more districts by checking the checkbox(es) in the subform.

Then, you can use code to create your required output query by filtering it against the records in this subform, i.e. records in the query where "Included" is yes FOR THAT PARTICULAR ITERATION. Of course, you'll also have to add code to update the helper table to set all of the checkboxes to False between each session, or after each time you generate the filtered query.

As noted, additional work is required, but you can get a result very close to the requested technique.

thanks, I will try and give you update

Cheers!!

 

[Josef P.]

now I need to create a query which shows all data of specific "District" selected by checkbox.

add #2: How about a checkbox in a listbox?

access-examples/data/MultiValueFilterControl.zip at main · josef-poetzl/access-examples

Contribute to josef-poetzl/access-examples development by creating an account on GitHub.

github.com

Could look like this, for example:

 

[hrdpgajjar]

add #2: How about a checkbox in a listbox?

access-examples/data/MultiValueFilterControl.zip at main · josef-poetzl/access-examples

Contribute to josef-poetzl/access-examples development by creating an account on GitHub.

github.com

Could look like this, for example:
View attachment 106581

This is what I want. But I have to learn a lot about this format and then apply in my format. But Thanks Though....

 

[GPGeorge]

add #2: How about a checkbox in a listbox?

access-examples/data/MultiValueFilterControl.zip at main · josef-poetzl/access-examples

Contribute to josef-poetzl/access-examples development by creating an account on GitHub.

github.com

Could look like this, for example:
View attachment 106581

That does not look like a "checkbox in a listbox" to me. That looks like a set of checkboxes on a form header in a continuous view form. And it also looks like it is used to FILTER items in the form's recordsource, not to SELECT individual records from the form's recordsource to allow them to be processed for another purpose. The latter is the requirement in this case.

Can you elaborate a bit in what this actually does? It looks interesting, indeed, but I'm not sure I see the correlation with the current requirement.

 

[Josef P.]

I just set the listbox background to the form background so it looks like checkboxes in the form. Since I thought that's what the OP would like to see.
Listbox is bound on a MVF => checkboxes in Listbox.

Yes, filtering is performed in the form. So that you can see the result right away.
In my opinion, it doesn't really matter how you use the filter values. Whether you use them to filter a form or to filter records of an update statement... where is the big difference?

Depending on how you want to process the filter values:
* Update ... where XYZ IN (<Values from MultiValueControl>)
... same processing as for filtering.
* Use the table with the MultiValueField as source. (See qryTest1, qryTest2 in sample file)

Example:

SELECT
     ...
FROM
     tabMultiValueFilter AS MVF
     INNER JOIN
     tabTest AS T ON T.fkCategory = MVF.CategoryFilters.Value
WHERE
     MVF.idFilterRec = ...

Note: MVF.CategoryFilters.Value


[OT]
I would never use the MVF in a data structure. As a tool for filtering records with the option to make a multiple selection, I find it handy.
Unfortunately, the controls have to be bound to a data field. It would be nicer if you could set the n:m source in the control itself.

 

[KitaYama]

add #2: How about a checkbox in a listbox?

access-examples/data/MultiValueFilterControl.zip at main · josef-poetzl/access-examples

Contribute to josef-poetzl/access-examples development by creating an account on GitHub.

github.com

Could look like this, for example:
View attachment 106581

@Josef P. Going through your sample database, I couldn't understand how the listbox shows checkboxes.
Can you please explain how it's done? (in simple words if possible)
Thank you.

 

[Josef P.]

I couldn't understand how the listbox shows checkboxes.

The listbox is bound to a MultiValue field. The SharePoint stuff that was built in once.
For this I use a local table in my applications, which is only used in the client - is not part of the data model.

Form: Record source = tabMultiValueFilter
Listbox: Control source = CategoryFilters (multi value data field)
Auxiliary table:

 

[KitaYama]

The listbox is bound to a MultiValue field. The SharePoint stuff that was built in once.
For this I use a local table in my applications, which is only used in the client - is not part of the data model.

Form: Record source = tabMultiValueFilter
Listbox: Control source = CategoryFilters (multi value data field)
Auxiliary table:
View attachment 106593

I've never used multi value fields.
Now I know something that I didn't know yesterday.
Thanks for for the detailed reply and the lesson.

 

[Pat Hartman]

Look at samples 2,3,4. They show how to work with an unbound multi-select list box to filter a different form.

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk