Opening Specific Report Based one Field Value

[nate]

I have an enrollment database for a theatre company, that is used to enroll the students into classes. How ever they have 9 different types of classes (eg, 1 day classes, 1 week classes, etc..) and each of these needs a differently laid out Class List.

Its currently set up that they can click on the class list, and the standard one opens up. I've set it up so when you create the new class, you choose one of the 9 class options, and I've created the 9 report templates to be used.

All I need now is to figure out how choosing the class will open the correct version of the report. I hope I'm being clear enough here, and I'd really appreciate any ideas.

 

[vbaInet]

Erm, not got it completely but can you not check against the field? Then use an IF ...ELSEIF block or SELECT CASE statement.

 

[nate]

Erm, not got it completely but can you not check against the field? Then use an IF ...ELSEIF block or SELECT CASE statement.

That worked perfectly, thanks.

 

[vbaInet]

Glad to know you got it working.

 

[nate]

Now I have another question, I have it so I click OPEN and it opens, but I need it to go to the report for the ClassID.

Here is my code;

Private Sub Text32_Click()
Select Case Me.Text32
Case Is = "1 Day Camp/Class"
DoCmd.OpenReport "Class Enrollment 1 Day", acViewPreview
Case Is = "2 Day Camp/Class"
DoCmd.OpenReport "Class Enrollment 2 Day", acViewPreview
Case Is = "4 Day Camp/Class"
DoCmd.OpenReport "Class Enrollment 4 Day", acViewPreview
Case Is = "1 Week Camp"
DoCmd.OpenReport "Class Enrollment 1 Week", acViewPreview
Case Is = "1 Week Summer Camp"
DoCmd.OpenReport "Class Enrollment 1 Week Summer", acViewPreview
Case Is = "3 Week Camp"
DoCmd.OpenReport "Class Enrollment Production", acViewPreview
Case Is = "4 Lesson Camp]"
DoCmd.OpenReport "Class Enrollment 4 Lesson", acViewPreview
Case Is = "8 Lesson Camp]"
DoCmd.OpenReport "Class Enrollment 8 Lesson", acViewPreview
Case Is = "13 Lesson Camp]"
DoCmd.OpenReport "Class Enrollment 13 Lesson", acViewPreview
Case Else
DoCmd.OpenReport "Class Enrollment 13 Lesson", acViewPreview

End Select
End Sub

I'm not sure what code, or where to put it in... :/

 

[nate]

Figured that out too. Thanks.

 

[vbaInet]

Just thought I should highlight a few things:

Instead of having lots of Docmd.OpenReport lines, I would do it like this:

Dim rptName as String

Select Case Nz(Me.Text32, vbNullString)
    Case "1 Day Camp/Class"
        rptName = "Class Enrollment 1 Day"

    Case "2 Day Camp/Class"
        rptName = ""Class Enrollment 2 Day""
    .
    .
    .
    Case Else
        rptName = vbNullString
End Select

if Len(rptName) <> 0 then
    DoCmd.OpenReport rptName, acViewPreview
end if

(Plus, you don't need Is =)

By the way, would you mine telling me how you solved your second problem?

 

[nate]

Just thought I should highlight a few things:

Instead of having lots of Docmd.OpenReport lines, I would do it like this:

Dim rptName as String
 
Select Case Nz(Me.Text32, vbNullString)
    Case "1 Day Camp/Class"
        rptName = "Class Enrollment 1 Day"
 
    Case "2 Day Camp/Class"
        rptName = ""Class Enrollment 2 Day""
    .
    .
    .
    Case Else
        rptName = vbNullString
End Select
 
if Len(rptName) <> 0 then
    DoCmd.OpenReport rptName, acViewPreview
end if

(Plus, you don't need Is =)

By the way, would you mine telling me how you solved your second problem?

Hey, thanks for the advice, I'm not very good at vb so I have a tendancy to over code. Here was what I used to solve my problem;

Private Sub Text32_Click()
Select Case Me.Combo34
Case "1 Day Camp/Class"
DoCmd.OpenReport "Class Enrollment 1 Day", acViewPreview, , "[ProductID Field]=" & Nz([ProductID], 0)
Case "2 Day Camp/Class"
DoCmd.OpenReport "Class Enrollment 2 Day", acViewPreview, , "[ProductID Field]=" & Nz([ProductID], 0)
Case "4 Day Camp/Class"
DoCmd.OpenReport "Class Enrollment 4 Day", acViewPreview, , "[ProductID Field]=" & Nz([ProductID], 0)
Case "1 Week Camp"
DoCmd.OpenReport "Class Enrollment 1 Week", acViewPreview, , "[ProductID Field]=" & Nz([ProductID], 0)
Case "1 Week Summer Camp"
DoCmd.OpenReport "Class Enrollment 1 Week Summer", acViewPreview, , "[ProductID Field]=" & Nz([ProductID], 0)
Case "3 Week Camp"
DoCmd.OpenReport "Class Enrollment Production", acViewPreview, , "[ProductID Field]=" & Nz([ProductID], 0)
Case "4 Lesson Class"
DoCmd.OpenReport "Class Enrollment 4 Lesson", acViewPreview, , "[ProductID Field]=" & Nz([ProductID], 0)
Case "8 Lesson Class"
DoCmd.OpenReport "Class Enrollment 8 Lesson", acViewPreview, , "[ProductID Field]=" & Nz([ProductID], 0)
Case "13 Lesson Class"
DoCmd.OpenReport "Class Enrollment 13 Lesson", acViewPreview, , "[ProductID Field]=" & Nz([ProductID], 0)
Case Else
DoCmd.OpenReport "Class Enrollment 13 Lesson", acViewPreview, , "[ProductID Field]=" & Nz([ProductID], 0)

End Select


Combo 34 is the combo box with the Class Types listed in to. But I will go back and clean it up a little.

 

[vbaInet]

If you look closely at my example you will be to see what needs to be changed. Try and follow it. You need the Nz() in Select Case Me.Combo34

 

[nate]

And thats changed. Thanks again for the help.

 

[vbaInet]

You're welcome!