Generation of Report using cascading unbound combobox

[makali123]

Sir
I am new to this forum & I am not so sound in ms access programming. However I have created a form using three cascading unbound combo boxes viz. CLASS(cbo1class), SEC(cbo1sec), MEDIUM(cbo1med) , a subform CLASSISESUB, two command buttons named SHOW ALL and CMDPRE (for generation of report preview on selection of combo box drop don menu). My form work fine with all and showing the required choice from selecting three combo box and SHOW ALL button works fine. But CMDPRE is not working, better to say I cannot reach to that programming extent. I also need to generate PRINT and SEND TO EXCELL button. Sir is it possible to generate the report on the basis of two choices from combo boxes out of three combo boxes i.e. say I am giving choice only in CLASS and SEC and the report will be generated only for CLASS and SEC in the same sheet. Please help me. The code is as follows ---


Option Compare Database
Option Explicit


Private Sub cbo1sec_AfterUpdate()


Dim strSQL As String
Dim strSQLSF As String

cbo1med = Null


strSQL = " SELECT DISTINCT STUDENT.MEDIUM FROM STUDENT "
strSQL = strSQL & " WHERE STUDENT.[PRESENT CLASS] = '" & cbo1class & "' And "
strSQL = strSQL & " STUDENT.SECTION = '" & cbo1sec & "'"
strSQL = strSQL & " ORDER BY STUDENT.MEDIUM;"

cbo1med.RowSource = strSQL

strSQLSF = " SELECT * FROM STUDENT "
strSQLSF = strSQLSF & " WHERE STUDENT.[PRESENT CLASS] = '" & cbo1class & "' And "
strSQLSF = strSQLSF & " STUDENT.SECTION = '" & cbo1sec & "'"



Me!classwisesub.LinkChildFields = ""
Me!classwisesub.LinkMasterFields = ""

Me!classwisesub.LinkChildFields = "[PRESENT CLASS];SECTION"
Me!classwisesub.LinkMasterFields = "[PRESENT CLASS];SECTION"

Me.RecordSource = strSQLSF
'Me.classwisesub.Requery
Me.Requery


End Sub


Private Sub cbo1med_AfterUpdate()


Dim strSQLSF As String

strSQLSF = " SELECT * FROM STUDENT "
strSQLSF = strSQLSF & " WHERE STUDENT.[PRESENT CLASS] = '" & cbo1class & "' And "
strSQLSF = strSQLSF & " STUDENT.SECTION = '" & cbo1sec & "' And "
strSQLSF = strSQLSF & " STUDENT.MEDIUM = '" & cbo1med & "'"

Me!classwisesub.LinkChildFields = ""
Me!classwisesub.LinkMasterFields = ""

Me!classwisesub.LinkChildFields = "[PRESENT CLASS];SECTION;MEDIUM"
Me!classwisesub.LinkMasterFields = "[PRESENT CLASS];SECTION;MEDIUM"

Me.RecordSource = strSQLSF
Me.Requery



End Sub
Private Sub cbo1class_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

cbo1sec = Null
cbo1med = Null

strSQL = "SELECT DISTINCT STUDENT.SECTION FROM STUDENT "
strSQL = strSQL & " WHERE STUDENT.[PRESENT CLASS] = '" & cbo1class & "'"
strSQL = strSQL & " ORDER BY STUDENT.SECTION;"

cbo1sec.RowSource = strSQL

strSQLSF = "SELECT * FROM STUDENT "
strSQLSF = strSQLSF & " WHERE STUDENT.[PRESENT CLASS] = '" & cbo1class & "'"

Me!classwisesub.LinkChildFields = "[PRESENT CLASS]"
Me!classwisesub.LinkMasterFields = "[PRESENT CLASS]"
Me.RecordSource = strSQLSF
'Me.classwisesub.Requery
Me.Requery


End Sub
Private Sub btnshow_Click()


On Error GoTo err_btnshow_Click


cbo1class = Null
cbo1sec = Null
cbo1med = Null
'Me.classwisesub.Requery
Me!classwisesub.LinkChildFields = ""
Me!classwisesub.LinkMasterFields = ""

Me.RecordSource = "STUDENT"
'Me.classwisesub.Requery
Me.Requery

exit_btnshow_Click:
Exit Sub

err_btnshow_Click:
MsgBox Err.Description
Resume exit_btnshow_Click


End Sub




Private Sub cmdPre_Click()
Dim strSQL As String
Dim strSQLSF As String


Const strReportName As String = "ALLSTUDENTS"






strSQLSF = " SELECT * FROM STUDENT "
strSQLSF = strSQLSF & " WHERE STUDENT.[PRESENT CLASS] = '" & cbo1class & "' And "
strSQLSF = strSQLSF & " STUDENT.SECTION = '" & cbo1sec & "' And "
strSQLSF = strSQLSF & " STUDENT.MEDIUM = '" & cbo1med & "'"








On Error Resume Next


strSQL = strSQLSF
If strSQL = "" Then
MsgBox "No filters have been applied."
Else
DoCmd.OpenReport strReportName, acViewPreview, , strSQL
End If


Exit Sub
End Sub




Private Sub Form_Open(Cancel As Integer)


Dim strSQL As String

strSQL = "SELECT DISTINCT STUDENT.[PRESENT CLASS] FROM STUDENT ORDER BY STUDENT.[PRESENT CLASS];"
cbo1class.RowSource = strSQL

End Sub


Thanking u. Ur help will oblige me.

[pbaldy]

You're providing a full SQL statement; you want only the "WHERE" clause, without the word "WHERE":

BaldyWeb wherecondition

[makali123]

You're providing a full SQL statement; you want only the "WHERE" clause, without the word "WHERE":

BaldyWeb wherecondition

I could not follow u, please elaborate ur esteemed suggestion.

[pbaldy]

For this line:


DoCmd.OpenReport strReportName, acViewPreview, , strSQL

the variable strSQL should not contain any of the parts up to and including the word WHERE. It should only contain what comes after.