Problem using Select Case

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott A - 22 Feb 2005 12:05 GMT

I am using the following code to select which subform appears based on the
type of record displayed (there are three classes of objects in the database,
each with the same core data set and three separate sub-tables of data).

Private Sub Form_Load()

Select Case Me!Class
Case Is = 1
Me!fsubA.Visible = True
Case Is = 2
Me!fsubB.Visible = True
Case Is = 3
Me!fsubC.Visible = True
Case Else
Me!fsubA.Visible = False
Me!fsubB.Visible = False
Me!fsubC.Visible = False
End Select
End Sub

It works very well on a form, but when I try to use the same code on a
report (to show/hide subreports), it doesn't work - telling me that the
database cannot find the field 'Class' referred to in my expression.

The field is definitely included in the record source for the report, and I
even put a text box (txtClass) on the report with Class as the record source,
without success.

What do I need to do?

Thanks,

Scott A

Reply to this Message

Wayne Morgan - 22 Feb 2005 12:45 GMT

Where are you trying to do this in the report? It should be in the Format
event of the Detail section. Also, you may need to set the other two subs to
Visible = False in cases 1, 2, and 3. Even in the form, I wouldn't expect
this to work properly unless you are only displaying one type of record.
Otherwise, this would need to be in the form's Current event instead of the
Load event to swap out the subforms as you move from record to record.

Signature

Wayne Morgan
MS Access MVP

>I am using the following code to select which subform appears based on the
> type of record displayed (there are three classes of objects in the
[quoted text clipped - 26 lines]
> source,
> without success.

Reply to this Message

Scott A - 22 Feb 2005 13:17 GMT

I'm trying to do this in a group header.

The Visible property of the subreports is already False. I want to use the
select case statement to turn them on.

The code on the form works fine - I'm only using the form to display single
records when the user wants to view record details (navigation buttons
disabled, etc.). Your suggestion would certainly apply if the user were able
to navigate between records on the form.

As for the report, I have tried a few different events (Report Open, Group
Header On Format), but it has yet to work in the same way that the same code
does on the form.

Here's what I have right now for the report in VBA:

Private Sub grhSample_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me!Class
Case Is = 1
Me!rsubA.Visible = True
Case Is = 2
Me!rsubB.Visible = True
Case Is = 3
Me!rsubC.Visible = True
Case Else
Me!rsubA.Visible = False
Me!rsubB.Visible = False
Me!rsubC.Visible = False
End Select
End Sub

It tells me it can't find the field 'Class' referred to in the expression -
even though it is included in the query and is referenced by a text box on
the report.

???

> Where are you trying to do this in the report? It should be in the Format
> event of the Detail section. Also, you may need to set the other two subs to
[quoted text clipped - 33 lines]
> > source,
> > without success.

Reply to this Message

Wayne Morgan - 22 Feb 2005 14:07 GMT

Where have you placed the textbox? Since the code is in the group header,
the textbox will probably need to be also. Another possibility is that if
the field and textbox are both called Class, there may be a conflict. Try
renaming the textbox to txtClass and use that instead.

Signature

Wayne Morgan
MS Access MVP

> I'm trying to do this in a group header.
>
[quoted text clipped - 35 lines]
> even though it is included in the query and is referenced by a text box on
> the report.

Reply to this Message

Scott A - 22 Feb 2005 14:21 GMT

I placed the textbox in the same group where I want the subreports to display
and named it txtClass. I do not have any other objects on the report named
"Class".

> Where have you placed the textbox? Since the code is in the group header,
> the textbox will probably need to be also. Another possibility is that if
[quoted text clipped - 40 lines]
> > even though it is included in the query and is referenced by a text box on
> > the report.

Reply to this Message

Wayne Morgan - 22 Feb 2005 16:07 GMT

Sorry, I should have caught the syntax error last time.

Select Case Me!Class
Case Is = 1

Should be

Select Case Me!Class
Case 1

Signature

Wayne Morgan
MS Access MVP

>I placed the textbox in the same group where I want the subreports to
>display
> and named it txtClass. I do not have any other objects on the report
> named
> "Class".

Reply to this Message

Scott A - 22 Feb 2005 16:41 GMT

The syntax works either way. Even so, I have found a solution...

I have been able to get it to work by moving the textbox to the detail
section of the report (with the subreports and the trigger event still in the
group header)! The database is now able to find the field it couldn't seem
to locate before.

It doesn't seem logical to me - at least I never thought it would work that
way, but I've been wrong before.

Anyhow, thanks for helping me work through the problem.

Cheers,

Scott A

> Sorry, I should have caught the syntax error last time.
>
[quoted text clipped - 11 lines]
> > named
> > "Class".

Reply to this Message