Whats wrong with this code?

  • Thread starter Thread starter Angelsnecropolis
  • Start date Start date
A

Angelsnecropolis

"Object Doesn't Support This Property Or Code"

I either need help fixing this code or if someone knows a more simple way to
create the same result it would be much appreciated.

What should happen is:
1. If Replist is null & Mgrlist is null then Questionbox = "Questions - All
Reps" and is the only txtbox visible.
2. If Replist is not null then Questionbox = "Questions - Per Rep".
Questionbox & Repname are the only txtboxes visible.
3. If Mgrlist is not null then Managername = "Questions - Per Mgr".
Managername & Managername1 are the only txtboxes visible.

I know the problem lies in the Takeovers & Escalation (TR & ER) part of the
"If Not IsNull(Me!mgrlist) Then" statement and it causes the textbox
"Questionbox" to not function in the Takeover & Escalations reports. It seems
to only make the first line of code work but the 2nd and 3rd lines of code
don't work. When I take two of the 3 lines out no error. If I take out the
Question & Escalation parts and just leave Takeover then the Takeover reports
works. Same thing for the Escalation report. There is nothing wrong with the
reports or quieries because I literally copied/pasted the QR & Query then
changed all the associations to work with TR & ER. I left the name
"Questionbox" the same on all three reports.
Here are the control source codes for the textboxes on the reports:

Questionbox =IIf(Forms.[Helpgate Menu].replist Is Null,"Questions - All
Reps",null)
Questionbox2 =IIf(Forms.[Helpgate Menu].replist Is Null,null,"Questions -
Per Rep")
Repname =IIf(Forms.[Helpgate Menu].replist Is Null,null,[C/S Rep ID])
Managername1 =IIf([Forms].[Helpgate Menu].[Mgrlist] Is Null,Null,[Mgr])
Managername =IIf(Forms.[Helpgate Menu].Mgrlist Is Null,Null,"Questions - Per
MGR")


Private Sub GenerateReportcs_Click()
On Error GoTo Err_GenerateReportcs_Click

If IsNull(Me!Reportlistcs) Then
MsgBox "You must select a C/S Report first."
Reportlistcs.SetFocus
Else
DoCmd.OpenReport (Me!Reportlistcs), acPreview
End If

If Not IsNull(Me!mgrlist) Then
Reports.Questions.Questionbox.Visible = False
Reports.Takeovers.Questionbox.Visible = False
Reports.Escalations.Questionbox.Visible = False
End If

Exit_GenerateReportcs_Click:

Exit Sub
Err_GenerateReportcs_Click:
MsgBox Err.Description
Resume Exit_GenerateReportcs_Click

End Sub
 
After doing research it would appear that a multiple IIF statement would
solve the problem though I'm having difficulty with this statement.

=IIf(Forms.[Helpgate Menu].replist is null And Forms.[Helpgate Menu].Mgrlist
Is Null,"Questions - All Reps"),iff(Forms.[Helpgate Menu].Mgrlist Is not Null
and Forms.[Helpgate Menu].Replist is null,"Questions - Per
MGR"),iif(Forms.[Helpgate Menu].replist Is not Null and Forms.[Helpgate
Menu].Mgrlist is null,"Questions - Per Rep")

Can someone troubleshoot this for me please?

Thanks!

Angelsnecropolis said:
"Object Doesn't Support This Property Or Code"

I either need help fixing this code or if someone knows a more simple way to
create the same result it would be much appreciated.

What should happen is:
1. If Replist is null & Mgrlist is null then Questionbox = "Questions - All
Reps" and is the only txtbox visible.
2. If Replist is not null then Questionbox = "Questions - Per Rep".
Questionbox & Repname are the only txtboxes visible.
3. If Mgrlist is not null then Managername = "Questions - Per Mgr".
Managername & Managername1 are the only txtboxes visible.

I know the problem lies in the Takeovers & Escalation (TR & ER) part of the
"If Not IsNull(Me!mgrlist) Then" statement and it causes the textbox
"Questionbox" to not function in the Takeover & Escalations reports. It seems
to only make the first line of code work but the 2nd and 3rd lines of code
don't work. When I take two of the 3 lines out no error. If I take out the
Question & Escalation parts and just leave Takeover then the Takeover reports
works. Same thing for the Escalation report. There is nothing wrong with the
reports or quieries because I literally copied/pasted the QR & Query then
changed all the associations to work with TR & ER. I left the name
"Questionbox" the same on all three reports.
Here are the control source codes for the textboxes on the reports:

Questionbox =IIf(Forms.[Helpgate Menu].replist Is Null,"Questions - All
Reps",null)
Questionbox2 =IIf(Forms.[Helpgate Menu].replist Is Null,null,"Questions -
Per Rep")
Repname =IIf(Forms.[Helpgate Menu].replist Is Null,null,[C/S Rep ID])
Managername1 =IIf([Forms].[Helpgate Menu].[Mgrlist] Is Null,Null,[Mgr])
Managername =IIf(Forms.[Helpgate Menu].Mgrlist Is Null,Null,"Questions - Per
MGR")


Private Sub GenerateReportcs_Click()
On Error GoTo Err_GenerateReportcs_Click

If IsNull(Me!Reportlistcs) Then
MsgBox "You must select a C/S Report first."
Reportlistcs.SetFocus
Else
DoCmd.OpenReport (Me!Reportlistcs), acPreview
End If

If Not IsNull(Me!mgrlist) Then
Reports.Questions.Questionbox.Visible = False
Reports.Takeovers.Questionbox.Visible = False
Reports.Escalations.Questionbox.Visible = False
End If

Exit_GenerateReportcs_Click:

Exit Sub
Err_GenerateReportcs_Click:
MsgBox Err.Description
Resume Exit_GenerateReportcs_Click

End Sub
Click to expand...
 
it's not at all clear what you're doing or where the actual problem is. your
first post indicated that you're having trouble with the If statement that
controls the Visible property of the control Questionbox - in the report, if
i'm understanding correctly. that post seems to also indicate that the three
Visible instructions are referring to three separate reports, yet the
OpenReport code is only opening one of the three reports at any given time.
if the above is correct, then change that code from five lines to one, as

Reports(Me!Reportlistcs)!Questionbox.Visible = Not IsNull(Me!Mgrlist)

but your second post is focused on a complex expression using nested IIf()
functions, and you don't say where you're trying to use it. please clarify
the setup, what is happening, and what you want to happen.

hth


Angelsnecropolis said:
After doing research it would appear that a multiple IIF statement would
solve the problem though I'm having difficulty with this statement.

=IIf(Forms.[Helpgate Menu].replist is null And Forms.[Helpgate Menu].Mgrlist
Is Null,"Questions - All Reps"),iff(Forms.[Helpgate Menu].Mgrlist Is not Null
and Forms.[Helpgate Menu].Replist is null,"Questions - Per
MGR"),iif(Forms.[Helpgate Menu].replist Is not Null and Forms.[Helpgate
Menu].Mgrlist is null,"Questions - Per Rep")

Can someone troubleshoot this for me please?

Thanks!

Angelsnecropolis said:
"Object Doesn't Support This Property Or Code"

I either need help fixing this code or if someone knows a more simple way to
create the same result it would be much appreciated.

What should happen is:
1. If Replist is null & Mgrlist is null then Questionbox = "Questions - All
Reps" and is the only txtbox visible.
2. If Replist is not null then Questionbox = "Questions - Per Rep".
Questionbox & Repname are the only txtboxes visible.
3. If Mgrlist is not null then Managername = "Questions - Per Mgr".
Managername & Managername1 are the only txtboxes visible.

I know the problem lies in the Takeovers & Escalation (TR & ER) part of the
"If Not IsNull(Me!mgrlist) Then" statement and it causes the textbox
"Questionbox" to not function in the Takeover & Escalations reports. It seems
to only make the first line of code work but the 2nd and 3rd lines of code
don't work. When I take two of the 3 lines out no error. If I take out the
Question & Escalation parts and just leave Takeover then the Takeover reports
works. Same thing for the Escalation report. There is nothing wrong with the
reports or quieries because I literally copied/pasted the QR & Query then
changed all the associations to work with TR & ER. I left the name
"Questionbox" the same on all three reports.
Here are the control source codes for the textboxes on the reports:

Questionbox =IIf(Forms.[Helpgate Menu].replist Is Null,"Questions - All
Reps",null)
Questionbox2 =IIf(Forms.[Helpgate Menu].replist Is Null,null,"Questions -
Per Rep")
Repname =IIf(Forms.[Helpgate Menu].replist Is Null,null,[C/S Rep ID])
Managername1 =IIf([Forms].[Helpgate Menu].[Mgrlist] Is Null,Null,[Mgr])
Managername =IIf(Forms.[Helpgate Menu].Mgrlist Is Null,Null,"Questions - Per
MGR")


Private Sub GenerateReportcs_Click()
On Error GoTo Err_GenerateReportcs_Click

If IsNull(Me!Reportlistcs) Then
MsgBox "You must select a C/S Report first."
Reportlistcs.SetFocus
Else
DoCmd.OpenReport (Me!Reportlistcs), acPreview
End If

If Not IsNull(Me!mgrlist) Then
Reports.Questions.Questionbox.Visible = False
Reports.Takeovers.Questionbox.Visible = False
Reports.Escalations.Questionbox.Visible = False
End If

Exit_GenerateReportcs_Click:

Exit Sub
Err_GenerateReportcs_Click:
MsgBox Err.Description
Resume Exit_GenerateReportcs_Click

End Sub
Click to expand...
Click to expand...
 
After doing research it would appear that a multiple IIF statement would
solve the problem though I'm having difficulty with this statement.

=IIf(Forms.[Helpgate Menu].replist is null And Forms.[Helpgate Menu].Mgrlist
Is Null,"Questions - All Reps"),iff(Forms.[Helpgate Menu].Mgrlist Is not Null
and Forms.[Helpgate Menu].Replist is null,"Questions - Per
MGR"),iif(Forms.[Helpgate Menu].replist Is not Null and Forms.[Helpgate
Menu].Mgrlist is null,"Questions - Per Rep")

Can someone troubleshoot this for me please?
Click to expand...

First off, you have an IFF in there - it's IIF.

Secondly, IIF takes three arguments, you have only two!

Thirdly, I'd suggest doing this in VBA code in the Report's Open event rather
than in the control source of textboxes, especially if you're changing the
Visible property of controls. You say:

What should happen is:
1. If Replist is null & Mgrlist is null then Questionbox = "Questions - All
Reps" and is the only txtbox visible.
2. If Replist is not null then Questionbox = "Questions - Per Rep".
Questionbox & Repname are the only txtboxes visible.
3. If Mgrlist is not null then Managername = "Questions - Per Mgr".
Managername & Managername1 are the only txtboxes visible.


What if Replist and Mgrlist are both non - NULL? 2 and 3 are contradictory in
that case. I'm not sure I understand just WHAT you want!

I'd venture a guess:

Private Sub Report_Open(Cancel as Integer)
If IsNull(Forms![Helpgate Menu]![Replist]) Then
If IsNull(Forms![Helpgate Menu]![Mgrlist]) Then
Me![Questionbox] = "Questions - All Reps"
Me![Questionbox].Visible = True
Me!<other textbox>.Visible = False
Else
Me!Questionbox = "Questions - Per rep"
Me!Questionbox.Visible = True
Me!Repname.Visible = True
<etc etc>
 
Sorry for the dbl post Tina. I'm a newbie when it comes to Access and posting
here.

At first I was dealing with Code and 3 seperate textboxes on 3 seperate
reports. ReportsListCS is actually a combobox used to select the different
report names. After researching I figured I could do what I needed with just
1 textbox and use multiple IIF statements for it. I'm trying to go for the
simplesest method (KISS rules). The code you posted, I'm sure, will work as
well and I may end up using it. But if I can get this IIF statement to work
then I may use it instead. The code you posted will undoubtably assist in the
future though and also enhances my understanding of how to referrence reports
from forms.

Thank you very much for your assistance ^_^

tina said:
it's not at all clear what you're doing or where the actual problem is. your
first post indicated that you're having trouble with the If statement that
controls the Visible property of the control Questionbox - in the report, if
i'm understanding correctly. that post seems to also indicate that the three
Visible instructions are referring to three separate reports, yet the
OpenReport code is only opening one of the three reports at any given time.
if the above is correct, then change that code from five lines to one, as

Reports(Me!Reportlistcs)!Questionbox.Visible = Not IsNull(Me!Mgrlist)

but your second post is focused on a complex expression using nested IIf()
functions, and you don't say where you're trying to use it. please clarify
the setup, what is happening, and what you want to happen.

hth


Angelsnecropolis said:
After doing research it would appear that a multiple IIF statement would
solve the problem though I'm having difficulty with this statement.

=IIf(Forms.[Helpgate Menu].replist is null And Forms.[Helpgate Menu].Mgrlist
Is Null,"Questions - All Reps"),iff(Forms.[Helpgate Menu].Mgrlist Is not Null
and Forms.[Helpgate Menu].Replist is null,"Questions - Per
MGR"),iif(Forms.[Helpgate Menu].replist Is not Null and Forms.[Helpgate
Menu].Mgrlist is null,"Questions - Per Rep")

Can someone troubleshoot this for me please?

Thanks!

Angelsnecropolis said:
"Object Doesn't Support This Property Or Code"

I either need help fixing this code or if someone knows a more simple way to
create the same result it would be much appreciated.

What should happen is:
1. If Replist is null & Mgrlist is null then Questionbox = "Questions - All
Reps" and is the only txtbox visible.
2. If Replist is not null then Questionbox = "Questions - Per Rep".
Questionbox & Repname are the only txtboxes visible.
3. If Mgrlist is not null then Managername = "Questions - Per Mgr".
Managername & Managername1 are the only txtboxes visible.

I know the problem lies in the Takeovers & Escalation (TR & ER) part of the
"If Not IsNull(Me!mgrlist) Then" statement and it causes the textbox
"Questionbox" to not function in the Takeover & Escalations reports. It seems
to only make the first line of code work but the 2nd and 3rd lines of code
don't work. When I take two of the 3 lines out no error. If I take out the
Question & Escalation parts and just leave Takeover then the Takeover reports
works. Same thing for the Escalation report. There is nothing wrong with the
reports or quieries because I literally copied/pasted the QR & Query then
changed all the associations to work with TR & ER. I left the name
"Questionbox" the same on all three reports.
Here are the control source codes for the textboxes on the reports:

Questionbox =IIf(Forms.[Helpgate Menu].replist Is Null,"Questions - All
Reps",null)
Questionbox2 =IIf(Forms.[Helpgate Menu].replist Is Null,null,"Questions -
Per Rep")
Repname =IIf(Forms.[Helpgate Menu].replist Is Null,null,[C/S Rep ID])
Managername1 =IIf([Forms].[Helpgate Menu].[Mgrlist] Is Null,Null,[Mgr])
Managername =IIf(Forms.[Helpgate Menu].Mgrlist Is Null,Null,"Questions - Per
MGR")


Private Sub GenerateReportcs_Click()
On Error GoTo Err_GenerateReportcs_Click

If IsNull(Me!Reportlistcs) Then
MsgBox "You must select a C/S Report first."
Reportlistcs.SetFocus
Else
DoCmd.OpenReport (Me!Reportlistcs), acPreview
End If

If Not IsNull(Me!mgrlist) Then
Reports.Questions.Questionbox.Visible = False
Reports.Takeovers.Questionbox.Visible = False
Reports.Escalations.Questionbox.Visible = False
End If

Exit_GenerateReportcs_Click:

Exit Sub
Err_GenerateReportcs_Click:
MsgBox Err.Description
Resume Exit_GenerateReportcs_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Well if Replist & Mgrlist were both non-null I was going to implement a
Msgbox to indicate to choose one or the other. But that was going to be a
problem to deal with in the future. But since you bring it up...

Do you know how to run multiple On Error functions in code? I've played with
all sorts of options in the code but couldn't get it to work for me. I
actually need 3: "choose a report", "select a date range", and "choose one or
the other."

This would ensure that all fields are completed properly before the report
runs.
If you can help it would be much appreciated ^_^

Private Sub GenerateReportcs_Click()
On Error GoTo Err_GenerateReportcs_Click

If IsNull(Me!Reportlistcs) Then
MsgBox "You must select a C/S Report first."
Reportlistcs.SetFocus
Else
DoCmd.OpenReport (Me!Reportlistcs), acPreview
End If

Exit_GenerateReportcs_Click:

Exit Sub
Err_GenerateReportcs_Click:
MsgBox Err.Description
Resume Exit_GenerateReportcs_Click

End Sub

John W. Vinson said:
After doing research it would appear that a multiple IIF statement would
solve the problem though I'm having difficulty with this statement.

=IIf(Forms.[Helpgate Menu].replist is null And Forms.[Helpgate Menu].Mgrlist
Is Null,"Questions - All Reps"),iff(Forms.[Helpgate Menu].Mgrlist Is not Null
and Forms.[Helpgate Menu].Replist is null,"Questions - Per
MGR"),iif(Forms.[Helpgate Menu].replist Is not Null and Forms.[Helpgate
Menu].Mgrlist is null,"Questions - Per Rep")

Can someone troubleshoot this for me please?
Click to expand...

First off, you have an IFF in there - it's IIF.

Secondly, IIF takes three arguments, you have only two!

Thirdly, I'd suggest doing this in VBA code in the Report's Open event rather
than in the control source of textboxes, especially if you're changing the
Visible property of controls. You say:

What should happen is:
1. If Replist is null & Mgrlist is null then Questionbox = "Questions - All
Reps" and is the only txtbox visible.
2. If Replist is not null then Questionbox = "Questions - Per Rep".
Questionbox & Repname are the only txtboxes visible.
3. If Mgrlist is not null then Managername = "Questions - Per Mgr".
Managername & Managername1 are the only txtboxes visible.


What if Replist and Mgrlist are both non - NULL? 2 and 3 are contradictory in
that case. I'm not sure I understand just WHAT you want!

I'd venture a guess:

Private Sub Report_Open(Cancel as Integer)
If IsNull(Forms![Helpgate Menu]![Replist]) Then
If IsNull(Forms![Helpgate Menu]![Mgrlist]) Then
Me![Questionbox] = "Questions - All Reps"
Me![Questionbox].Visible = True
Me!<other textbox>.Visible = False
Else
Me!Questionbox = "Questions - Per rep"
Me!Questionbox.Visible = True
Me!Repname.Visible = True
<etc etc>
Click to expand...
 
Thank you John. I was able to tweak what you provided. I had to change the
textboxes to labels and I just needed to edit the captions.

Private Sub Report_Open(Cancel As Integer)
If IsNull(Forms![Helpgate Menu]![Replist]) Then
If IsNull(Forms![Helpgate Menu]![mgrlist]) Then
Me!Questionbox.Caption = "Questions - All Reps"
End If
End If

If IsNull(Forms![Helpgate Menu]![Replist]) Then
If Not IsNull(Forms![Helpgate Menu]![mgrlist]) Then
Me!Questionbox.Caption = "Questions - Per MGR"
Me!Repname.Caption = (Forms![Helpgate Menu]![mgrlist])
End If
End If
If Not IsNull(Forms![Helpgate Menu]![Replist]) Then
Me!Questionbox.Caption = "Questions - Per Rep"
Me!Repname.Caption = (Forms![Helpgate Menu]![Replist])
End If
End Sub




John W. Vinson said:
After doing research it would appear that a multiple IIF statement would
solve the problem though I'm having difficulty with this statement.

=IIf(Forms.[Helpgate Menu].replist is null And Forms.[Helpgate Menu].Mgrlist
Is Null,"Questions - All Reps"),iff(Forms.[Helpgate Menu].Mgrlist Is not Null
and Forms.[Helpgate Menu].Replist is null,"Questions - Per
MGR"),iif(Forms.[Helpgate Menu].replist Is not Null and Forms.[Helpgate
Menu].Mgrlist is null,"Questions - Per Rep")

Can someone troubleshoot this for me please?
Click to expand...

First off, you have an IFF in there - it's IIF.

Secondly, IIF takes three arguments, you have only two!

Thirdly, I'd suggest doing this in VBA code in the Report's Open event rather
than in the control source of textboxes, especially if you're changing the
Visible property of controls. You say:

What should happen is:
1. If Replist is null & Mgrlist is null then Questionbox = "Questions - All
Reps" and is the only txtbox visible.
2. If Replist is not null then Questionbox = "Questions - Per Rep".
Questionbox & Repname are the only txtboxes visible.
3. If Mgrlist is not null then Managername = "Questions - Per Mgr".
Managername & Managername1 are the only txtboxes visible.


What if Replist and Mgrlist are both non - NULL? 2 and 3 are contradictory in
that case. I'm not sure I understand just WHAT you want!

I'd venture a guess:

Private Sub Report_Open(Cancel as Integer)
If IsNull(Forms![Helpgate Menu]![Replist]) Then
If IsNull(Forms![Helpgate Menu]![Mgrlist]) Then
Me![Questionbox] = "Questions - All Reps"
Me![Questionbox].Visible = True
Me!<other textbox>.Visible = False
Else
Me!Questionbox = "Questions - Per rep"
Me!Questionbox.Visible = True
Me!Repname.Visible = True
<etc etc>
Click to expand...
 
Thank you John. I was able to tweak what you provided. I had to change the
textboxes to labels and I just needed to edit the captions.
Click to expand...

Ah. As I suspected, I was misinterpreting the question! Glad you got it
working.
 
Back
Top