Adding a dynamic text box to a report

[klfduncan]

I manage a database which lists possible business leads. Each lead's priority is measured as either 'Daily', 'Weekly' or 'Monthly'. I then produce a report which displays the leads according to which priority is required, as defined by the control Me!PriorityFilter on the ReportSwitch Form.

My problem is that I want a text box to display what the priority is on the report. In the report, I have created a TextBox, PriorityTxt which is unbound. I then have the following code to open the :

DoCmd.OpenReport "ActiveLeadsCond", acViewPreview, , Me!PriorityFilter

Reports!ActiveLeadsCond!PriorityTxt = Forms!ReportSwitch!PriorityTxt

However, it doesn't work. I've checked with a watch and note that on the second line of the above code I check that the left hand side equals 'Null' whilst the right hand side equals 'Monthly', yet the PriorityTxt never takes that value on board.

Thank you for your time.

 

[Eupher]

Hi, klfduncan,

Hmm... I'm a little confused... What control on the form holds the value?

DoCmd.OpenReport "ActiveLeadsCond", acViewPreview, , Me!PriorityFilter

Or...

Reports!ActiveLeadsCond!PriorityTxt = Forms!ReportSwitch!PriorityTxt

In any event, I would instead set the control source of PriorityTxt on the report to =[Forms]![ReportSwitch]![TheField]
... where TheField is the name of the control on the form which holds the value you want.

Ken S.

 

[klfduncan]

Hi Ken,

sorry the second line should read:

Reports!ActiveLeadsCond!PriorityTxt = Forms!ReportSwitch!PriorityFilter

i.e. I'm trying to do what you had suggested by setting the control source of PriorityTxt on the Report to the field on the form. I still can't understand why it doesn't work.

 

[Eupher]

Is the form still open when the report loads?

Ken S.

 

[klfduncan]

Yeah it is.

I put a breakpoint on this line of code

Reports!ActiveLeadsCond!PriorityTxt = Forms!ReportSwitch!PriorityFilter

and it's definitely aware that Forms!etc contains the word 'Monthly' but for some reason, it refuses to populate the PriorityTxt field on the report.

 

[Eupher]

What kind of control is PriorityFilter?

Ken S.

 

[klfduncan]

PriorityFilter is a combo box with the following values:

Daily
Weekly
Daily and Weekly
Monthly
All

The where clause is dependent upon the input from this combo.

 

[Eupher]

And what happens when you set the control source for the report's textbox to =[Forms]![ReportSwitch]![PriorityFilter]?

Ken S.

 

[klfduncan]

Nothing at all. When I put a breakpoint in the program and check the values by hovering the mouse pointer over the code, the following values appear:

Reports!ActiveLeadsCond!PriorityTxt = Null
[Forms]![ReportSwitch]![PriorityFilter] = 'Monthly'

I put in a line of code and put a breakpoint on it after this line and it confirms:

Reports!ActiveLeadsCond!PriorityTxt = 'Monhtly'
[Forms]![ReportSwitch]![PriorityFilter] = 'Monthly'

OK. Now it gets weird. Just tried again and in preview mode there's nothing there, yet when I look at the report in design mode and then immediately switch to preview mode again, the text box displays correctly. Now I'm really confused...

 

[Eupher]

I'm missing something here... If you are setting the control source to =[Forms]![ReportSwitch]![PriorityFilter] in the control's property sheet on the report, what does code have to do with anything?

Ken S.

 

[klfduncan]

Sorry,

the line of code was just so that I could check the breakpoint and what values were held in the fields.

It's just confusing that when the report opens, the PriorityTxt is empty. Yet when I press the 'Open in Design Mode' button followed immediately by the 'Open in Preview Mode' PriorityTxt shows the correct values.

I really appreciate your time and help on this one by the way.

Keith

 

[Eupher]

Okay, something else is going here to foul things up. Can you post the entire form's module code?

Ken S.

 

[dhookom]

If you want to display the filter/where clause used to open the report, just use a text box on your report with a control source of:
=[Filter]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[klfduncan]

Hi,

here's the full code:

Dim stCaption As String

If IsNull(Me!PriorityFilter) = True Then
MsgBox ("Please choose what priority leads you would like to view from the combo box at the top of this window")
GoTo Exit_ActiveLeads_Click
End If

DoCmd.OpenReport "ActiveLeadsCond", acViewPreview, , Me!PriorityFilter

Reports!ActiveLeadsCond!PriorityTxt = Forms!ReportSwitch!PriorityFilter

Exit_ActiveLeads_Click:
Exit Sub

Err_ActiveLeads_Click:
MsgBox Err.Description
Resume Exit_ActiveLeads_Click

End Sub

The PriorityFilter combo has the values: Daily;Weekly;Monthly

 

[dhookom]

How would you expect a value like "Daily" all by itself to be used as a where clause? I would expect to see something like:

DoCmd.OpenReport "ActiveLeadsCond", acViewPreview, , "[TimePeriodField]=""" & Me!PriorityFilter & """"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[klfduncan]

OK. How about this:

Dim stCaption, stWhere As String

If IsNull(Me!PriorityFilter) = True Then
MsgBox ("Please choose what priority leads you would like to view from the combo box at the top of this window")
GoTo Exit_ActiveLeads_Click
End If

stWhere = "LeadInfo.LeadPriority = '" & Me!PriorityFilter & "'"

DoCmd.OpenReport "ActiveLeadsCond", acViewPreview, , stWhere

Reports!ActiveLeadsCond!PriorityTxt = Forms!ReportSwitch!PriorityFilter

Exit_ActiveLeads_Click:
Exit Sub

Err_ActiveLeads_Click:
MsgBox Err.Description
Resume Exit_ActiveLeads_Click

End Sub

It still doens't do what I'm asking it to on the Reports!Active... etc. line. The only way it actually populates that field is if, once the module has finished and the report is open, I open the report in Design Mode, then reopen it immediately (without changing or touching anything) in Preview mode. It just seems weird to do that.

 

[dhookom]

I would never, ever put code outside of a report to modify the report. Did you try just add a text box (no code) that has a control source of:
=[Filter]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[klfduncan]

Yes.

Do you think it would be better to write code in the report itself, something along the lines of:

ReportOpen()

Me!PriorityTxt = Forms!ReportSwitch!PriorityFilter

?

 

[klfduncan]

OK.

I've managed to get it to work by specifying the path Forms!ReportSwitch!Filter in the 'Control Source' property of the text box in design view of the report. It works, but I don't understand why I couldn't just code it in Visual Basic - any ideas?

Thanks for both of your help by the way - it's increased my understanding in other areas!

 

[Eupher]

Probably the line of code that attempted to set the value of the textbox was executed before the form had fully loaded.

Ken S.