Updating captions values based on radios selected (1 Viewer)

bouncingtigers

Registered User.
Local time
Today, 12:47
Joined
Aug 9, 2016
Messages
18
Morning,

Im looking to start a discussion on the best method to approach based on the image below:

What im reviewing is a form with various radio buttons, when all options are selected for each group the user clicks the requery button. The part im getting stuck with, and confused is the test in red.

The text in red I want the values to update when the user clicks requesry
- User clicks yes for postcode;
- valAccounts figures will update to values only where postcode is 'yes', or TRUE
- same got gender, values update where postcode is 'yes, or TRUE

It sounded simple to start with, until I inclue more than one option
- Postcode = TRUE & val account = TRUE, how do i approach in updating al the values?

Ive started by trying to build up a WHERE clause but keep losing track - Im not sure if I am over complicating this, or if it is actually a difficult thing to do




photo.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:47
Joined
Sep 21, 2011
Messages
7,126
I would have 3 separate strings for DCounts.
Build each string, then Debug.Print it to ensure it is correct then use it.
In your example is every record meant to be included in the totals?, as your numbers do not add up. You have 518 Postcode records, yet 531 ValAccount records and only 500 gender records?
 

bouncingtigers

Registered User.
Local time
Today, 12:47
Joined
Aug 9, 2016
Messages
18
Thanks for the reply Gasman

I holds my hands up, the values in the picture ive made up (although the real data its possible for the figures not to match given the way the data is in the table)

Im not sure i understand how to implement the dcount. In my picture theres 6 labels that will need to update when the requery button it clicked. Somehow each label will need to check all radio buttons to see which is selected (if any), then build the criteria for every dcount assigned to each label.

Hope that makes sense?
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:47
Joined
Sep 21, 2011
Messages
7,126
So just to clarify, you would pick Postcode = Yes, Account=No, Gender = Male and expect to see cascading values
IE Number of Postcodes = Yes
Within the above selection Account=No
Within the above selections Gender = Male

So what about the captions that are not in the selection?

Radio buttons are normally on or off.?
 

bouncingtigers

Registered User.
Local time
Today, 12:47
Joined
Aug 9, 2016
Messages
18
No radio buttons are selected when the form initally opens.

Let say the user clicks 'yes' to postcode only, then clicks requery:

The values in the 'Yes' label for the 'Account' would update based on the number of records where postcode is yes and account is yes
The values in the 'No' label for the 'Account' would update based on the number of records where postcode is yes and account is no

This could grow if the user then also selects gender:

It would then need to add the criteria for genders Yes, and no, to the count.

Im struggling to explain with very well, so appreciate your patience
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:47
Joined
Jul 9, 2003
Messages
12,096
There's a very old technique I use for building SQL Statements which might apply in this case. However, I think you need to supply a lot more information before a solution can be arrived at. I have tackled a couple of questions lately spending several hours arriving at the solution, only to find that the OP hadn't really posed the correct question and my work was basically a waste of time. I'm unwilling to to spend any time on this because you have a lot more work to do to define the question.

Start writing some VBA code of your own and posting that. This process will give a good idea of what you are thinking, and will provide a framework for others to build on. That's not quite a rant, but what should it be called?

The technique:-
You write several functions that Return parts of an SQL Statement. For instance in the case of the option group for selecting postcode, if nothing was selected then the function would return an empty string thereby effectively excluding itself from the SQL Statement.

You can see the technique used in my blog here:-


See Video 6a, you can choose where to start, there are several sets which cater from beginner to advanced programmers.


This page gives you a overall description of the process of building a search form and the techniques used..

 

Gasman

Enthusiastic Amateur
Local time
Today, 20:47
Joined
Sep 21, 2011
Messages
7,126
Ok, and what are your values for the radio buttons?
This should be a simple case of testing if a radio button has a value and then concatenating the correct string criteria.?

Let's start simple.
We will have 6 string variables that will hold the criteria for each and build them depending on the radio buttons.
We will then have 2 more string variables for Account and Gender

So let's say for Postcode, we just need
strPostCode1 = "[Postcode] = " & IIF(Me.Postcode=1, "True", "False")
strPostCode2 = "[Postcode] = " & IIF(Me.Postcode=1, "False", "True")

For Account
strAcount1 = [Account] = " & IIF(Me.Account=1, "True", "False")
strAcount2 = [Account] = " & IIF(Me.Account=1, "False", "True")

and the same for Gender.

You could actually hard code these as well. :)

Then we test each radio option and build the DCount criteria.

As I write this, I cannot help feeling there should be a better way :(

However for now a Select statement should do the job
This is the structure I was thinking of.

Code:
Select Case iPC And iAcc And iGender
Case 0 And 0 And 0
    Debug.Print iPC & iAcc & iGender
Case 1 And 0 And 0
    Debug.Print iPC & iAcc & iGender
Case 1 And 1 And 0
    Debug.Print iPC & iAcc & iGender
Case 1 And 1 And 1
    Debug.Print iPC & iAcc & iGender
Case Else
    MsgBox "Not catered for"
End Select
So in place of the debug.print you build the final criteria from each of the individual strings? Then just execute them
So for 1 and 0 and 0
strPostcodeYes = strPostcode1
strPostcodeNo = strPostcode2
strAccountYes = strPostcode1 & " AND " & strAccount1
strAccountNo = strpostcode1 & " AND " & strAccount2
[/code]

The use the final Yes No strings as criteria for the DCount

However if we then need another set, the maintenance is going to be a nightmare? :(

I'm more inclined now to have a table with the options and criteria and DLookUp that and execute that.?

So as Tony has mentioned, is this just a theory exercise? as increasing the options means the table option would be more flexible.?
 

Users who are viewing this thread

Top Bottom