Check Box List on a form which relate to a like query

[MarcA-D]

Hi everyone,



i was just trying to make a form which has a list of check boxes with age brackets on. I wanted to try to link these with a query so if for example the 18-25 , 26-30 and 31-35 check boxes are checked it would in the query make a like statement in the age_band column to then search for them age bands, iv been looking online but cant really find anything that i understand. I am guessing i would have to use VBA which i don't know the language of which is why i thought i would come to see if any of you guys would be able to give me a hand. if possible i would also like it to have to other columns that then had date of birth and age banding on. but that can wait until l have worked or got some help working the like query first.

Thanks in advance,

Marc.

P.S. I have attached photos of the form that i have got and the query layout that i sort of want to try to help you guys help me.

[ItsMe]

What does your age_band field look like in your table? What data type is it and what would one example of a record look like?

I suspect you wish to display some information on a form that is relative to the value of age_band. The solution may be to use VBA behind a form that will decide what to display on the form. A Select Case statement may be what you are after.

[MarcA-D]

here are some records out of the age_band column and the column is short text,

age_band
61-65
31-35
66-70
46-50
66-70
46-50
56-60
66-70
61-65
66-70
71-75
71-75
66-70
26-30
36-40
46-50
71-75
71-75
61-65
46-50
36-40
71-75
61-65
46-50
56-60
66-70
41-45
51-55
61-65
36-40

i was looking at VBA code but i don't really understand it as i'm new to this sort of stuff. thanks for your help

[MarcA-D]

title	first_name	middle_name	Surname	address_1	address_2	address_3	city	county	postcode	year_of_birth	date_of_birth	age	age_band	Landline
Mr	J		c	39 Jays	Marks		Colchester	Essex	CO1LR				76-100	1111111111111

i have attached what one records would look like it may have more fields on depending on what table i use but i want to get this one sorted first, thanks once again

[ItsMe]

If you are creating a search form, there are various techniques to do this. You would use the search form to collect user input. The search form would then concatenate the user input as a String Literal and apply the string as WHERE Criteria. The search form would, in turn, open another form to display the data that the query retrieved.

The first step would be to isolate one aspect of your objective. I would focus on a search form that only creates the where criteria for the age_band field. One example of this would be the image of the 12 Checkbox Controls you posted in Post #1. These controls will be Unbound. Use meaningful names for each of the controls, e.g. ageBand66_70.

Now that you have this, you can use code behind that will iterate all of the controls of the form. The looping code will evaluate each control and determine its value. If it is determined that the value is -1 (Yes), the code would use a Select Case statement to Concatenate literal text to a string that will be used for the WHERE clause.

I would start with getting this done first. You have a long road ahead of you. I suggest you create a new form and add the twelve controls to the new form, as I have described. Additional controls, features, code, etc. will only complicate and confuse the matter. With an unbound form and the twelve controls you should be able to use code like this to get the names of the controls that have a value of Yes.

On your new form with the Checkbox controls, place a Button Control and create a click event handler for the button. Within the Click Event subprocedure, place the following code. The result will print to the immediate window. You can view the immediate window via View > Immediate Window or Ctrl+G on your keyboard.

Dim ctl As Control
    For Each ctl In Me.Controls
        
        If ctl.ControlType = acCheckBox Then
        
        'Now that we have a checkbox
        'let's see what the value is
            If ctl.Value = -1 Then
                Debug.Print ctl.Name
            End If
            
        End If   'ctl.ControlType
        
    Next ctl

[MarcA-D]

Sorry but where exactly do i put this code? I'm not to sure with the coding side of access i know how to use access just not code in VBA or any other language for that matter sorry if i'm being dumb.
Thanks once again Marc

[ItsMe]

In your DB, create a new form (use Design View). Add the checkbox controls to your new form and give the controls good names. Now, add a new button. With the button highlighted/selected, go to the Property Sheet. Use the Ribbon and click "Property Sheet" if your Property Sheet is not visible. Within the Property Sheet is a tab called "Events". You want to create a new event handler.

If you double click the ellipses(...) next to the On Click event (towards the top of the list), a dialog will open asking what to do next. Click "Code Builder" and then "OK".

This will open the VBA Editor. The cursor will be within the first line of your Sub Procedure and the last line of your sub procedure. Your code will go where the cursor is, in between the first line and the termination line (End Sub).

You want to be able to see the Immediate Window. From within the VBA editor, go to View > Immediate Window or use Ctrl+G on your keyboard.

Now, save your form and go to Form View. Click the button, once. Now, look at the results in the Immediate Window.

[MarcA-D]

Thanks a lot for this!!!! I appreciate it a lot.

I have now done this and it is coming up with check and then a number, the more boxes i click the more it says.I'm guessing this is what we want to happen, but now i'm wondering how do I link this with a query that will run against my table? Thanks once again Marc

[ItsMe]

Within your VBA module, there is a line at the very top. It reads, "Option Compare Database". Just below that line paste this.

Dim strWhere As String

You need this string variable to be declared at the top so it is available everywhere within the form's module.

Now, adjust the code in your Click Event sub- procedure to look like this. Be sure not to disturb the first line and the last line that Access created for you.

'Initialize and reset criteria
strWhere = ""
    For Each ctl In Me.Controls
        
        If ctl.ControlType = acCheckBox Then
        
        'Now that we have a checkbox
        'let's see what the value is
            If ctl.Value = -1 Then
'                Debug.Print ctl.Name
                buildAgeCriteria (ctl.Name)
            End If
            
        End If   'ctl.ControlType
        
    Next ctl
'Trim off the OR keyword from the begining of our clause.
strWhere = Mid(strWhere, 5)
Debug.Print strWhere

Now you need to paste some code at he very bottom of your module. This is your own Sub Procedure. So, this code has its own Start and Finish lines. Paste this AFTER the End Sub line of your Click Event handler.

Private Sub buildAgeCriteria(p_AgeBand As String)
 
'p_AgeBand will be the name of the control
'We will use the name to understand the literal text
'that needs to be used within the WHERE criteria
Select Case p_AgeBand
Case "ageBand66_70"
strWhere = strWhere & " OR 66-70"
Case "ageBand71_75"
strWhere = strWhere & " OR 71-75"
Case "ageBand76_100"
strWhere = strWhere & " OR 76-100"
End Select
 
End Sub

With all of this, you should get a different result in your Immediate Window. This will be the start to building your WHERE criteria. You need to match the names of your controls in the Case lines, e.g. Case "ageBand66_70"

I would like to add an aside.

Before moving forward, you should understand that you are at a crossroads. There is, likely, a better way to design your table structure. A better table design would allow for more flexibility over the life of your project/application. It may be easiest to get this exercise out of the way, as the table sit. Then, come back and look for ways to improve things.

[MarcA-D]

Before moving forward, you should understand that you are at a crossroads. There is, likely, a better way to design your table structure. A better table design would allow for more flexibility over the life of your project/application. It may be easiest to get this exercise out of the way, as the table sit. Then, come back and look for ways to improve things.[/QUOTE]

Hi thanks again for helping me out this as been really helpful. How do you mean that there is a better way to design my table ? I'm interested in what you think as you obviously know a lot about access, I have now done what you said above how would I now incorporate that in the where query? If you think I should do the structure of the table first I will do that if you know any ways of improving it to make it more flexible for the future. Thanks once again Marc

[ItsMe]

If we are going to continue with the idea of having multiple check boxes for the user to click, we will need to make further adjustments to how we are creating our string variable named strWhere. We will need to address the syntax. We will need to add the field name, "age_band". There are different ways this can be done. One way, that may be best to illustrate the mechanics of WHERE criteria, would be like this.

strWhere = strWhere & " OR age_band ='76-100'"

With this, we should be able to get the search form to function. For instance, we should be able to add our strWhere variable to another form's Filter property. We could also apply it to a Query Object's SQL; we would just need to add the keyword WHERE to our variable.

However, we should take a step back and ask ourselves why we are using '76-100' or '66-70', for instance. A major part to building applications is considering the Business Rules. This is an oversimplification of the matter. But, considering the business rules is a good, general, description of what needs to be done. So, why do we have this '76-100'?

Is this something the boss came up with? Did management say there will always be groups defined this way? Is there an industry standard that says these age groups will always be classified and treated a certain way? If this is the case, we may want to store these values in a table as a list. The values (age groups) should be treated as an Entity and stored in their own table where they can be referenced and maintained. They need to be referenced and maintained in a way that does not impact other Entities. Another example of this type of table would be a table dedicated to States within the United States. A table would contain records for California, Texas, New York, etc.

If there is no such business rule, then there is no reason to have these values stored in our database. They should not be in a table and they should not be hard coded in our forms. We should not build "Magic Strings" into our code (strWhere = strWhere & " OR age_band ='76-100'").

An alternative may be to have the user type in a lower bound and an upper bound. For instance, an unbound textbox control could accept user input for the lower age and another textbox control could accept the upper age limit. Then, VBA code would consider these variables to query the birthdates. This approach would be a more conventional approach. Another approach would likely create issues for maintainability. For instance, hard coded magic strings will create maintenance issues for the User Interface.

You have to consider the Business Rules. Apply business rules to the Business Domain (Business Layer). Then, consider the User and how they interact with the application. Address these interactions in the User Interface (Presentation Layer).

[MarcA-D]

Hi thanks for this AGAIN , the reason they are in age bands is that is how we get some data, they come in the age banding so we will receive 100,000 records that have all different age banding in the areas, so the records where the age band column is will have for example 25-30. They don't just have one number in so would I be able to still do the minim age band and the maxim age banding as that would be a lot easier for the user to do, if not then I am happy with how this is looking so far just need it to link with the query. Thanks again Marc

[ItsMe]

OK, give this a shot; create another form (use Design View) and bind it to your table that has the age_band field. Add the field, age_band, to your new form by dragging the name of the field onto the design surface. Save your form and give it a name that indicates this form is temporary. I use a convention like A_A_age_groupQueryTest.

Fix the Select Case statement with the following code. Pay attention to the single quotes and double quotes and spaces, etc. You will need to adjust code for your names, add Case statements, etc.

'p_AgeBand will be the name of the control
'We will use the name to understand the literal text
'that needs to be used within the WHERE criteria
Select Case p_AgeBand
Case "ageBand66_70"
strWhere = strWhere & " OR age_band ='66-70'"
Case "ageBand71_75"
strWhere = strWhere & " OR age_band ='71-75'"
Case "ageBand76_100"
strWhere = strWhere & " OR age_band ='76-100'"
End Select

You will also need to add some code to your click event. Replace "Debug.Print strWhere" with ... where A_A_age_groupQueryTest is the name of your new temp form.

'Debug.Print strWhere
DoCmd.OpenForm "A_A_age_groupQueryTest", acNormal, , strWhere

This should open the temp form with Filtered results. I have to take care of a few things and will be away from my desk for a little while. If you have trouble, go ahead and upload your DB here. I will take a look at it. Just drag your temp form and the new form we have been working in to a new blank DB to upload. If you can, include the table. Just be sure to remove sensitive data. Compact and repair your new DB for uploading. Zip/Compress the file/DB before uploading.

[MarcA-D]

OK, give this a shot; create another form (use Design View) and bind it to your table that has the age_band field. Add the field, age_band, to your new form by dragging the name of the field onto the design surface. Save your form and give it a name that indicates this form is temporary. I use a convention like A_A_age_groupQueryTest.

Fix the Select Case statement with the following code. Pay attention to the single quotes and double quotes and spaces, etc. You will need to adjust code for your names, add Case statements, etc.

'p_AgeBand will be the name of the control
'We will use the name to understand the literal text
'that needs to be used within the WHERE criteria
Select Case p_AgeBand
Case "ageBand66_70"
strWhere = strWhere & " OR age_band ='66-70'"
Case "ageBand71_75"
strWhere = strWhere & " OR age_band ='71-75'"
Case "ageBand76_100"
strWhere = strWhere & " OR age_band ='76-100'"
End Select

You will also need to add some code to your click event. Replace "Debug.Print strWhere" with ... where A_A_age_groupQueryTest is the name of your new temp form.

'Debug.Print strWhere
DoCmd.OpenForm "A_A_age_groupQueryTest", acNormal, , strWhere

This should open the temp form with Filtered results. I have to take care of a few things and will be away from my desk for a little while. If you have trouble, go ahead and upload your DB here. I will take a look at it. Just drag your temp form and the new form we have been working in to a new blank DB to upload. If you can, include the table. Just be sure to remove sensitive data. Compact and repair your new DB for uploading. Zip/Compress the file/DB before uploading.

I have attached the test data that has been generated and the test form that we first down. I have tried making this one that you are talking about but I cant see it doing anything, when I tried to move it into the test data database it then wouldn't work as the table that i used was not in that DB. thanks a lot for doing this it is very helpful.

please see below for the test DB

Test data.zip

[ItsMe]

I took the DB you uploaded here and then followed the steps outlined in post #13. I have uploaded the result here. I am not sure what parts of post #13 you attempted to apply to your DB. This example, here, will open a second form where the form's Filter property includes WHERE criteria generated from the previous form.

It is important to understand the steps in post #13 so you can create your "Search Form".