• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

VBA radio button coding

Hi Experts,

I currently have a form which has 2 option groups both containing sets of radio buttons. The form also contains a button which loads a report from its onlclick event. The report has a query behind it which links various link tables and performs basic filtering.

Option Group 1 contains 2 radio buttons, All Students & Second years only. Obv if the user selects all students then no filtering is necessary but if they select second years only then **/2/* needs to be applied to the 'course code' value in the query.

Options Group 2 contains 4 options, National Award, National Certificate, National Diplioma & AS/A2. The query contains a field that uses the midpoint function to pull (for example) ND for a national awards code NC for national certificate etc

Is it easier to filter using VBA code behind the oncllick event of the button that launches the report or actually in the where clause of the query itself. Bearing in mind that the query already has the following where clause already,,,,, Like "9ND*/1/*" Or Like "8ND*/2/*" Or Like "9NC*" Or Like "9NA*"

Thanks,
Dean.
0
deanlee17
Asked:
deanlee17
  • 11
  • 5
1 Solution
 
deanlee17Author Commented:
Ok ive made some progress, im using a case select statement like so:

Private Sub Command21_Click()
Select Case Me.Year_select_options

    Case 1
          DoCmd.OpenReport "Predicted_VA", acViewPreview
    Case 2
          DoCmd.OpenReport "Predicted_VA", acViewPreview ,,where [E_REFERENCE] like **/2/*

 End Select
End Sub

Case 2 is throwing up a problem though. E_REFERENCE is a field in the query how would i code the Like **/2/*?
0
 
deanlee17Author Commented:
Ok i now have 2 case select statements behind the 1 button, however it only reads the first case select, is there a way around this?
Private Sub Command21_Click()
Select Case Me.Year_select_options

    Case 1
          DoCmd.OpenReport "Predicted_VA", acViewPreview
    Case 2
          DoCmd.OpenReport "Predicted_VA", acViewPreview, , "E_REFERENCE like '*/2/*'", acWindowNormal

 End Select
 
 Select Case Me.Award_option_group

    Case 1
          DoCmd.OpenReport "Predicted_VA", acViewPreview
    Case 2
          DoCmd.OpenReport "Predicted_VA", acViewPreview, , "test like 'ND'", acWindowNormal
    Case 3
          DoCmd.OpenReport "Predicted_VA", acViewPreview, , "test like 'NC'", acWindowNormal
    Case 4
          DoCmd.OpenReport "Predicted_VA", acViewPreview, , "test like 'NA'", acWindowNormal
    Case 5
          DoCmd.OpenReport "Predicted_VA", acViewPreview, , "test like 'AS' OR 'A2'", acWindowNormal

 End Select
 
End Sub

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd need to build up your Where clause for the OpenReport method.

Note in the code below I've used the OR keyword when adding filtering criteria. Depending on your needs, you may need to change that to AND.


Private Sub Command21_Click()

Dim sWhere As String


Select Case Me.Year_select_options
    Case 1
          '/DoCmd.OpenReport "Predicted_VA", acViewPreview
    Case 2
          sWhere = "E_REFERENCE like '*/2/*'"

 End Select
 
 Select Case Me.Award_option_group

    Case 1
          '/DoCmd.OpenReport "Predicted_VA", acViewPreview
    Case 2
	If Len(sWhere) > 0 Then sWhere = sWhere & " OR "
	sWhere = sWhere & " test like 'ND'"
    Case 3
	If Len(sWhere) > 0 Then sWhere = sWhere & " OR "
	sWhere = sWhere & "test like 'NC'"
    Case 4
	If Len(sWhere) > 0 Then sWhere = sWhere & " OR "
	sWhere = sWhere & "test like 'NA'"
    Case 5
	If Len(sWhere) > 0 Then sWhere = sWhere & " OR "
	sWhere = sWhere & "test like 'AS' OR 'A2'"

 End Select

 DoCmd.OpenReport "Predicted_VA", acViewPreview, , sWhere, acWindowNormal
 
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
deanlee17Author Commented:
Ah ok, i see how this is building the Where clause. Did you mean to comment out Case 1 for the first select statement? What if they with to choose all students (case 1) then a selection from the second case select?

You were correct that i need to change the OR for AND to build the correct Where clause:

sWhere = "E_REFERENCE like '*/2/*'" " AND " sWhere = sWhere & " test like 'ND'"
 
0
 
deanlee17Author Commented:
Oh, with case 1 the way it currently stands does it just carry nothing down to the second select statement? so you just end up with all students and the course selected from the second case select?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<Did you mean to comment out Case 1 for the first select statement? >

There is no need to provide anything for a "non-filter"; I just left the Case 1 stuff in there for reference.

<so you just end up with all students and the course selected from the second case select?>

Yes.
0
 
deanlee17Author Commented:
If i select all students and all awards it runs fine. If i select all students and ND for the award then it asks for a Parameter value for test. However if I run the query alone it returns values for test, such as ND, NC etc?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There's an extra space in my code:

   Case 2
        If Len(sWhere) > 0 Then sWhere = sWhere & " OR " 
        sWhere = sWhere & "test like 'ND'"  <<<<< note this line

Also, it's a good idea to enclose your Field values in square brackets:

   Case 2
        If Len(sWhere) > 0 Then sWhere = sWhere & " OR " 
        sWhere = sWhere & "[test] like 'ND'"

 
0
 
deanlee17Author Commented:
Where abouts is the extra space? I tried altering it but get the same problem.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The extra space was in front of the word "test"

If your OpenReport method is prompting you for the value of "test", then this means it cannot find that value (i.e. Field) in the query used for your Report. Can you post the SQL of that query here?

IN case you're not familiar with that, open the Query in Design view, then click View - SQL. Copy/paste that back here.
0
 
deanlee17Author Commented:
Here it is mate....

SELECT UNITE_CAPD_MODULEENROLMENT_CRS.E_PROJGRADE AS [Projected Grade], UNITE_CAPD_PERSON.P_SURNAME AS Surname, UNITE_CAPD_PERSON.P_FORENAMES AS Forenames, UNITE_CAPD_STUDENT.S_STUDENTREFERENCE AS PSN, Mid(UNITE_CAPD_MODULEENROLMENT.E_REFERENCE,2,4) AS [Course Factor], UNITE_CAPD_ENROLMENTISR.EI_Q21 AS [Actual Grade], [QCA Score table].[Avg QCA Score], Round(([Avg QCA Score]-10)/6,1) AS [AVG GCSE], UNITE_CAPD_MODULEENROLMENT.E_STATUS, IIf(UNITE_CAPD_MODULEENROLMENT.E_STATUS="1","L",IIf(UNITE_CAPD_MODULEENROLMENT.E_STATUS="2","C",IIf(UNITE_CAPD_MODULEENROLMENT.E_STATUS="3","W",IIf(UNITE_CAPD_MODULEENROLMENT.E_STATUS="4","T",IIf(UNITE_CAPD_MODULEENROLMENT.E_STATUS="9","X",UNITE_CAPD_MODULEENROLMENT.E_STATUS))))) AS Status, Round(((Round(([Avg QCA Score]-10)/6,1))*[Gradient])+[Intercept],0) AS [ALIS Predict UCAS Points], LOOKUP_Target.Grade AS [Target Grade], LOOKUP_Predict.QCA_Ped_points AS [QCA points value], LOOKUP_Predict.UCAS AS [Staff Predict UCAS], LOOKUP_Predict_Actual.UCAS AS [Actual Residual], LOOKUP_Predict_Actual.QCA_Ped_points AS [Actual QCA points value], Round(([Actual Residual]-[ALIS Predict UCAS Points])/[SD],1) AS [Actual VA], Round(([Staff Predict UCAS]-[ALIS Predict UCAS Points])/[SD],1) AS [Predicted VA], UNITE_CAPD_MODULEENROLMENT.E_REFERENCE, UNITE_CAPD_SECTION.S_NAME, UNITE_CAPD_DEPARTMENT.D_NAME, Mid(UNITE_CAPD_MODULEENROLMENT.E_REFERENCE,2,2) AS test, [Course VA Target].VA_Target_VA, UNITE_CAPD_MODULEENROLMENT.E_NAME, [Team VA Target].Team_VA_Target, [Course VA Target].VA_Target_VA AS [Course VA target], UNITE_CAPD_ENROLMENTISR_1.EI_Q17 AS [Expected end]
FROM EQUATIONS_ALIS, LOOKUP_Target, LOOKUP_Predict, LOOKUP_Predict AS LOOKUP_Predict_Actual, [Course VA Target], UNITE_CAPD_ENROLMENTISR AS UNITE_CAPD_ENROLMENTISR_1 INNER JOIN ((((UNITE_CAPD_MODULE INNER JOIN ((((((UNITE_CAPD_MODULEENROLMENT INNER JOIN UNITE_CAPD_STUDENT ON UNITE_CAPD_MODULEENROLMENT.E_STUDENT = UNITE_CAPD_STUDENT.S_ID) INNER JOIN UNITE_CAPD_PERSON ON UNITE_CAPD_STUDENT.S_ID = UNITE_CAPD_PERSON.P_ID) INNER JOIN [QCA Score table] ON UNITE_CAPD_STUDENT.S_STUDENTREFERENCE = [QCA Score table].studentreference) INNER JOIN UNITE_CAPD_MODULEENROLMENT AS UNITE_CAPD_MODULEENROLMENT_CYR ON UNITE_CAPD_MODULEENROLMENT.E_PARENT = UNITE_CAPD_MODULEENROLMENT_CYR.E_ID) INNER JOIN UNITE_CAPD_MODULEENROLMENT AS UNITE_CAPD_MODULEENROLMENT_CRS ON UNITE_CAPD_MODULEENROLMENT_CYR.E_PARENT = UNITE_CAPD_MODULEENROLMENT_CRS.E_ID) INNER JOIN UNITE_CAPD_ENROLMENTISR ON UNITE_CAPD_MODULEENROLMENT_CRS.E_ID = UNITE_CAPD_ENROLMENTISR.EI_ID) ON UNITE_CAPD_MODULE.M_ID = UNITE_CAPD_MODULEENROLMENT.E_MODULE) INNER JOIN UNITE_CAPD_SECTION ON UNITE_CAPD_MODULE.M_MODULESECTION = UNITE_CAPD_SECTION.S_ID) INNER JOIN UNITE_CAPD_DEPARTMENT ON UNITE_CAPD_MODULE.M_MODULEDEPT = UNITE_CAPD_DEPARTMENT.D_ID) INNER JOIN [Team VA Target] ON UNITE_CAPD_SECTION.S_REFERENCE = [Team VA Target].Code) ON UNITE_CAPD_ENROLMENTISR_1.EI_ID = UNITE_CAPD_MODULEENROLMENT.E_ID
WHERE (((UNITE_CAPD_MODULEENROLMENT.E_STATUS) In ("1","2")) AND ((UNITE_CAPD_MODULEENROLMENT.E_REFERENCE) Like "9ND*/1/*" Or (UNITE_CAPD_MODULEENROLMENT.E_REFERENCE) Like "8ND*/2/*" Or (UNITE_CAPD_MODULEENROLMENT.E_REFERENCE) Like "9NC*" Or (UNITE_CAPD_MODULEENROLMENT.E_REFERENCE) Like "9NA*") AND ((Mid([UNITE_CAPD_MODULEENROLMENT].[E_REFERENCE],2,4))=[Course Code] And (Mid([UNITE_CAPD_MODULEENROLMENT].[E_REFERENCE],2,4))=[Course VA Target].[VA_Course Code] And (Mid([UNITE_CAPD_MODULEENROLMENT].[E_REFERENCE],2,4))=[Course Code]) AND ((IIf([Avg QCA Score] Is Null,-9,Round(((Round(([Avg QCA Score]-10)/6,1))*[Gradient])+[Intercept],0)+40)) Between [UCAS_Min] And [UCAS_Max] Or (IIf([Avg QCA Score] Is Null,-9,Round(((Round(([Avg QCA Score]-10)/6,1))*[Gradient])+[Intercept],0)+40)) Is Null) AND ((IIf([UNITE_CAPD_MODULEENROLMENT_CRS].[E_PROJGRADE] Is Null,"N/A",[UNITE_CAPD_MODULEENROLMENT_CRS].[E_PROJGRADE]))=[LOOKUP_Predict].[Grade] Or (IIf([UNITE_CAPD_MODULEENROLMENT_CRS].[E_PROJGRADE] Is Null,"N/A",[UNITE_CAPD_MODULEENROLMENT_CRS].[E_PROJGRADE])) Is Null) AND ((IIf([UNITE_CAPD_ENROLMENTISR].[EI_Q21] Is Null,"N/A",[UNITE_CAPD_ENROLMENTISR].[EI_Q21]))=[LOOKUP_Predict_Actual].[Grade] Or (IIf([UNITE_CAPD_ENROLMENTISR].[EI_Q21] Is Null,"N/A",[UNITE_CAPD_ENROLMENTISR].[EI_Q21])) Is Null) AND ((Mid([UNITE_CAPD_MODULEENROLMENT].[E_REFERENCE],2,2))=[LOOKUP_Target].[Code] Or (Mid([UNITE_CAPD_MODULEENROLMENT].[E_REFERENCE],2,2)) Is Null))
ORDER BY UNITE_CAPD_PERSON.P_SURNAME;
0
 
deanlee17Author Commented:
Oh, ignore that, this is the code behind that query....


SELECT [info for make table query for reporting].S_NAME, [info for make table query for reporting].E_REFERENCE, [info for make table query for reporting].E_NAME, Left([E_REFERENCE],10) AS [Course code], [info for make table query for reporting].E_REFERENCE AS [Full code], Count([info for make table query for reporting].PSN) AS CountOfPSN, Avg([info for make table query for reporting].[Actual VA]) AS [AvgOfActual VA], [info for make table query for reporting].Team_VA_Target, [info for make table query for reporting].D_NAME, Avg([info for make table query for reporting].[QCA points value]) AS [AvgOfQCA points value], StrConv([S_NAME],3) AS [Propercase Team], [info for make table query for reporting].Team_VA_Target, Round([Course VA target],1) AS [crs va], StrConv([D_NAME],3) AS [Propercase Academy], [info for make table query for reporting].[Course VA target], Avg([info for make table query for reporting].[Predicted VA]) AS [AvgOfPredicted VA], [QCA Sum]/[QCA Count] AS [QCA for Academy totals], Sum([info for make table query for reporting].[QCA points value]) AS [QCA Sum], Sum(IIf([QCA points value] Is Null,0,1)) AS [QCA Count], Sum([info for make table query for reporting].[Predicted VA]) AS [Predicted VA sum], Sum(IIf([Predicted VA] Is Null,0,1)) AS [Predicted VA Count], [Predicted VA sum]/[Predicted VA Count] AS [Predicted VA for Academy totals], Sum([info for make table query for reporting].[Actual VA]) AS [Actual VA sum], Sum(IIf([Actual VA] Is Null,0,1)) AS [Actual VA Count], [Actual VA sum]/[Actual VA Count] AS [Actual VA for Academy totals]
FROM [info for make table query for reporting]
GROUP BY [info for make table query for reporting].S_NAME, [info for make table query for reporting].E_REFERENCE, [info for make table query for reporting].E_NAME, Left([E_REFERENCE],10), [info for make table query for reporting].E_REFERENCE, [info for make table query for reporting].Team_VA_Target, [info for make table query for reporting].D_NAME, StrConv([S_NAME],3), [info for make table query for reporting].Team_VA_Target, Round([Course VA target],1), StrConv([D_NAME],3), [info for make table query for reporting].[Course VA target];
0
 
deanlee17Author Commented:
Which i then realised didnt have the test field added and not all works fine :)

Thanks very much, you have been very helpful.

0
 
deanlee17Author Commented:
Noticed one problem, when i select AS OR A2 (    sWhere = sWhere & "[test] like 'AS' OR 'A2'"   ) is displays all courses, when (at this stage of the database) it should display none
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You must use separate LIKE statements for this:

WHERE "[test] LIKE 'AS' OR [test] LIKE 'A2'"

This would show records where the [test] field had EITHER of those values.
0
 
deanlee17Author Commented:
Excellent support.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 11
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now