asked on

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.

deanlee17

ASKER

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/*?

deanlee17

ASKER

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

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deanlee17

ASKER

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'"

deanlee17

ASKER

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?

Scott McDaniel (EE MVE )

<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.

deanlee17

ASKER

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?

Scott McDaniel (EE MVE )

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'"

deanlee17

ASKER

Where abouts is the extra space? I tried altering it but get the same problem.

Scott McDaniel (EE MVE )

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.

deanlee17

ASKER

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;

deanlee17

ASKER

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];

deanlee17

ASKER

Which i then realised didnt have the test field added and not all works fine :)

Thanks very much, you have been very helpful.

deanlee17

ASKER

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

Scott McDaniel (EE MVE )

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.

deanlee17

ASKER

Excellent support.