What happened to my title?

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have control in the header of my report that shows one of the parameters
of the query on which it is based. The report is run from a form that has a
command button that runs the report. The control has this in it's source
=[Forms]![frmFindDocumentName]![finddocnametxt]
When viewed on screen it's there but when printed I get #Name? and when
exported to Word there is a blank. What's happening?
TIA
Tony Williams
 
Tony said:
I have control in the header of my report that shows one of the parameters
of the query on which it is based. The report is run from a form that has a
command button that runs the report. The control has this in it's source
=[Forms]![frmFindDocumentName]![finddocnametxt]
When viewed on screen it's there but when printed I get #Name? and when
exported to Word there is a blank. What's happening?
Click to expand...


It sounds like the form has been closed.
 
Yes Marshall I close the form before the report is run. Can I populate the
control on the report before the report is run or do I have to leave the
form open. If I leave it open it lies on top of the report. Any suggestions?
Tony
Marshall Barton said:
Tony said:
I have control in the header of my report that shows one of the parameters
of the query on which it is based. The report is run from a form that has a
command button that runs the report. The control has this in it's source
=[Forms]![frmFindDocumentName]![finddocnametxt]
When viewed on screen it's there but when printed I get #Name? and when
exported to Word there is a blank. What's happening?
Click to expand...


It sounds like the form has been closed.
Click to expand...
 
Tony said:
Yes Marshall I close the form before the report is run. Can I populate the
control on the report before the report is run or do I have to leave the
form open. If I leave it open it lies on top of the report. Any suggestions?
Click to expand...

No, you have to open the report before closing the form.
This is pretty easy to do by leaving the form open and using
a little VBA code in the report that copies the value to the
header and then closes the form. Place something like this
in the report header section's Format event:

Me.headertextbox=[Forms]![frmFindDocumentName]![finddocnametxt]
DoCmd,Close acForm, "frmFindDocumentName"
--
Marsh
MVP [MS Access]


Tony said:
I have control in the header of my report that shows one of the parameters
of the query on which it is based. The report is run from a form that has a
command button that runs the report. The control has this in it's source
=[Forms]![frmFindDocumentName]![finddocnametxt]
When viewed on screen it's there but when printed I get #Name? and when
exported to Word there is a blank. What's happening?
Click to expand...
Click to expand...
Marshall Barton said:
It sounds like the form has been closed.
Click to expand...
Click to expand...
 
Thanks Marshall
Tony
Marshall Barton said:
Tony said:
Yes Marshall I close the form before the report is run. Can I populate the
control on the report before the report is run or do I have to leave the
form open. If I leave it open it lies on top of the report. Any
Click to expand...
suggestions?

No, you have to open the report before closing the form.
This is pretty easy to do by leaving the form open and using
a little VBA code in the report that copies the value to the
header and then closes the form. Place something like this
in the report header section's Format event:

Me.headertextbox=[Forms]![frmFindDocumentName]![finddocnametxt]
DoCmd,Close acForm, "frmFindDocumentName"
--
Marsh
MVP [MS Access]


Tony Williams wrote:
I have control in the header of my report that shows one of the parameters
of the query on which it is based. The report is run from a form that
Click to expand...
has
a
command button that runs the report. The control has this in it's source
=[Forms]![frmFindDocumentName]![finddocnametxt]
When viewed on screen it's there but when printed I get #Name? and when
exported to Word there is a blank. What's happening?
Click to expand...
Marshall Barton said:
It sounds like the form has been closed.
Click to expand...
Click to expand...
Click to expand...
 
Marshall here is my code behind the command button on the prompt form
Private Sub cmdRunReport_Click()
Dim stDocName As String
stDocName = "rptDocumentbyKeyword"
If IsNull(Me.findkeywordtxt) Then MsgBox "Please enter Keyword"
Me.findkeywordtxt.SetFocus
Cancel = True
If IsNull(Me.StartDatetxt) Then MsgBox "Please enter Start Date"
Me.StartDatetxt.SetFocus
Cancel = True
If IsNull(Me.EndDatetxt) Then MsgBox "Please enter End Date"
Me.EndDatetxt.SetFocus
Cancel = True
If Not IsNull(StartDatetxt) And Not IsNull(EndDatetxt) And Not
IsNull(findkeywordtxt) Then
DoCmd.OpenReport stDocName, acPreview

End If

Exit_cmbViewReport_Click:
Exit Sub

Err_cmbViewReport_Click:
MsgBox Err.Description
Resume Exit_cmbViewReport_Click
End Sub


This is the code I have put in the header sections Format event

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Me.keywordparatxt = [Forms]![frmFindkeyword]![findkeywordtxt]
DoCmd.Close acForm, "frmFindkeyword"
End Sub

However I get error 2450 Access can't find "frmFindkeyword"
Why is this because there is nothing in the command code that closes the
form and the from name is correct? Any ideas?

TIA
Tony
Marshall Barton said:
Tony said:
Yes Marshall I close the form before the report is run. Can I populate the
control on the report before the report is run or do I have to leave the
form open. If I leave it open it lies on top of the report. Any
Click to expand...
suggestions?

No, you have to open the report before closing the form.
This is pretty easy to do by leaving the form open and using
a little VBA code in the report that copies the value to the
header and then closes the form. Place something like this
in the report header section's Format event:

Me.headertextbox=[Forms]![frmFindDocumentName]![finddocnametxt]
DoCmd,Close acForm, "frmFindDocumentName"
--
Marsh
MVP [MS Access]


Tony Williams wrote:
I have control in the header of my report that shows one of the parameters
of the query on which it is based. The report is run from a form that
Click to expand...
has
a
command button that runs the report. The control has this in it's source
=[Forms]![frmFindDocumentName]![finddocnametxt]
When viewed on screen it's there but when printed I get #Name? and when
exported to Word there is a blank. What's happening?
Click to expand...
Marshall Barton said:
It sounds like the form has been closed.
Click to expand...
Click to expand...
Click to expand...
 
Tony said:
Marshall here is my code behind the command button on the prompt form
Private Sub cmdRunReport_Click()
Dim stDocName As String
stDocName = "rptDocumentbyKeyword"
If IsNull(Me.findkeywordtxt) Then MsgBox "Please enter Keyword"
Me.findkeywordtxt.SetFocus
Cancel = True
If IsNull(Me.StartDatetxt) Then MsgBox "Please enter Start Date"
Me.StartDatetxt.SetFocus
Cancel = True
If IsNull(Me.EndDatetxt) Then MsgBox "Please enter End Date"
Me.EndDatetxt.SetFocus
Cancel = True
If Not IsNull(StartDatetxt) And Not IsNull(EndDatetxt) And Not
IsNull(findkeywordtxt) Then
DoCmd.OpenReport stDocName, acPreview

End If

Exit_cmbViewReport_Click:
Exit Sub

Err_cmbViewReport_Click:
MsgBox Err.Description
Resume Exit_cmbViewReport_Click
End Sub


This is the code I have put in the header sections Format event

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Me.keywordparatxt = [Forms]![frmFindkeyword]![findkeywordtxt]
DoCmd.Close acForm, "frmFindkeyword"
End Sub

However I get error 2450 Access can't find "frmFindkeyword"
Why is this because there is nothing in the command code that closes the
form and the from name is correct? Any ideas?
Click to expand...


Your If logic has a fatal flaw. It should look more like:

If IsNull(Me.findkeywordtxt) Then
MsgBox "Please enter Keyword"
Me.findkeywordtxt.SetFocus
Cancel = True
ElseIf IsNull(Me.StartDatetxt) Then
MsgBox "Please enter Start Date"
Me.StartDatetxt.SetFocus
Cancel = True
ElseIf IsNull(Me.EndDatetxt) Then
MsgBox "Please enter End Date"
Me.EndDatetxt.SetFocus
Cancel = True
Else
DoCmd.OpenReport stDocName, acPreview
End If

However, I don't think that's the source of the error
message. How do the findkeywordtxt, StartDatetxt and
EndDatetxt text boxes interact with the report? Is it
possible that the report has a reference to one of those
after you've closed the form?
 
Marshall, the three controls you mention are on the form frmfindkeyord and
are used by the query on which the form is based as parameters for the
records that appear on the report. They do not, apart from the control
findkeywordtxt, appear in the report.
Thanks for the amended code; as a matter of interest what was the "fatal
flaw" just so I can learn for the future.
Thanks
Tony
Marshall Barton said:
Tony said:
Marshall here is my code behind the command button on the prompt form
Private Sub cmdRunReport_Click()
Dim stDocName As String
stDocName = "rptDocumentbyKeyword"
If IsNull(Me.findkeywordtxt) Then MsgBox "Please enter Keyword"
Me.findkeywordtxt.SetFocus
Cancel = True
If IsNull(Me.StartDatetxt) Then MsgBox "Please enter Start Date"
Me.StartDatetxt.SetFocus
Cancel = True
If IsNull(Me.EndDatetxt) Then MsgBox "Please enter End Date"
Me.EndDatetxt.SetFocus
Cancel = True
If Not IsNull(StartDatetxt) And Not IsNull(EndDatetxt) And Not
IsNull(findkeywordtxt) Then
DoCmd.OpenReport stDocName, acPreview

End If

Exit_cmbViewReport_Click:
Exit Sub

Err_cmbViewReport_Click:
MsgBox Err.Description
Resume Exit_cmbViewReport_Click
End Sub


This is the code I have put in the header sections Format event

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Me.keywordparatxt = [Forms]![frmFindkeyword]![findkeywordtxt]
DoCmd.Close acForm, "frmFindkeyword"
End Sub

However I get error 2450 Access can't find "frmFindkeyword"
Why is this because there is nothing in the command code that closes the
form and the from name is correct? Any ideas?
Click to expand...


Your If logic has a fatal flaw. It should look more like:

If IsNull(Me.findkeywordtxt) Then
MsgBox "Please enter Keyword"
Me.findkeywordtxt.SetFocus
Cancel = True
ElseIf IsNull(Me.StartDatetxt) Then
MsgBox "Please enter Start Date"
Me.StartDatetxt.SetFocus
Cancel = True
ElseIf IsNull(Me.EndDatetxt) Then
MsgBox "Please enter End Date"
Me.EndDatetxt.SetFocus
Cancel = True
Else
DoCmd.OpenReport stDocName, acPreview
End If

However, I don't think that's the source of the error
message. How do the findkeywordtxt, StartDatetxt and
EndDatetxt text boxes interact with the report? Is it
possible that the report has a reference to one of those
after you've closed the form?
Click to expand...
 
The code you gave me works without the last line As soon as I include the
Docmd line I get the message. As far as I can see nothing else is closing
the form
Weird???
Tony
Marshall Barton said:
Tony said:
Marshall here is my code behind the command button on the prompt form
Private Sub cmdRunReport_Click()
Dim stDocName As String
stDocName = "rptDocumentbyKeyword"
If IsNull(Me.findkeywordtxt) Then MsgBox "Please enter Keyword"
Me.findkeywordtxt.SetFocus
Cancel = True
If IsNull(Me.StartDatetxt) Then MsgBox "Please enter Start Date"
Me.StartDatetxt.SetFocus
Cancel = True
If IsNull(Me.EndDatetxt) Then MsgBox "Please enter End Date"
Me.EndDatetxt.SetFocus
Cancel = True
If Not IsNull(StartDatetxt) And Not IsNull(EndDatetxt) And Not
IsNull(findkeywordtxt) Then
DoCmd.OpenReport stDocName, acPreview

End If

Exit_cmbViewReport_Click:
Exit Sub

Err_cmbViewReport_Click:
MsgBox Err.Description
Resume Exit_cmbViewReport_Click
End Sub


This is the code I have put in the header sections Format event

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Me.keywordparatxt = [Forms]![frmFindkeyword]![findkeywordtxt]
DoCmd.Close acForm, "frmFindkeyword"
End Sub

However I get error 2450 Access can't find "frmFindkeyword"
Why is this because there is nothing in the command code that closes the
form and the from name is correct? Any ideas?
Click to expand...


Your If logic has a fatal flaw. It should look more like:

If IsNull(Me.findkeywordtxt) Then
MsgBox "Please enter Keyword"
Me.findkeywordtxt.SetFocus
Cancel = True
ElseIf IsNull(Me.StartDatetxt) Then
MsgBox "Please enter Start Date"
Me.StartDatetxt.SetFocus
Cancel = True
ElseIf IsNull(Me.EndDatetxt) Then
MsgBox "Please enter End Date"
Me.EndDatetxt.SetFocus
Cancel = True
Else
DoCmd.OpenReport stDocName, acPreview
End If

However, I don't think that's the source of the error
message. How do the findkeywordtxt, StartDatetxt and
EndDatetxt text boxes interact with the report? Is it
possible that the report has a reference to one of those
after you've closed the form?
Click to expand...
 
Tony said:
Marshall, the three controls you mention are on the form frmfindkeyord and
are used by the query on which the form is based as parameters for the
records that appear on the report. They do not, apart from the control
findkeywordtxt, appear in the report.
Thanks for the amended code; as a matter of interest what was the "fatal
flaw" just so I can learn for the future.
Click to expand...

The flaw is that you were using the single line type of If
statement:

If IsNull(Me.findkeywordtxt) Then MsgBox . . .

and expecting the following lines to be skipped when the if
condition was true. When you want the If to conditionally
execute more than a single statement, you must use the
multi-line type of If:

If IsNull(Me.findkeywordtxt) Then
MsgBox . . .
Me.findkeywordtxt.SetFocus
Cancel = True
End If

or a squence of If, ElseIf, Else, End If as I used in my
suggested code.
--
Marsh
MVP [MS Access]


Tony said:
Marshall here is my code behind the command button on the prompt form
Private Sub cmdRunReport_Click()
Dim stDocName As String
stDocName = "rptDocumentbyKeyword"
If IsNull(Me.findkeywordtxt) Then MsgBox "Please enter Keyword"
Me.findkeywordtxt.SetFocus
Cancel = True
If IsNull(Me.StartDatetxt) Then MsgBox "Please enter Start Date"
Me.StartDatetxt.SetFocus
Cancel = True
If IsNull(Me.EndDatetxt) Then MsgBox "Please enter End Date"
Me.EndDatetxt.SetFocus
Cancel = True
If Not IsNull(StartDatetxt) And Not IsNull(EndDatetxt) And Not
IsNull(findkeywordtxt) Then
DoCmd.OpenReport stDocName, acPreview

End If

Exit_cmbViewReport_Click:
Exit Sub

Err_cmbViewReport_Click:
MsgBox Err.Description
Resume Exit_cmbViewReport_Click
End Sub


This is the code I have put in the header sections Format event

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Me.keywordparatxt = [Forms]![frmFindkeyword]![findkeywordtxt]
DoCmd.Close acForm, "frmFindkeyword"
End Sub

However I get error 2450 Access can't find "frmFindkeyword"
Why is this because there is nothing in the command code that closes the
form and the from name is correct? Any ideas?
Click to expand...

"Marshall Barton" wrote
Your If logic has a fatal flaw. It should look more like:

If IsNull(Me.findkeywordtxt) Then
MsgBox "Please enter Keyword"
Me.findkeywordtxt.SetFocus
Cancel = True
ElseIf IsNull(Me.StartDatetxt) Then
MsgBox "Please enter Start Date"
Me.StartDatetxt.SetFocus
Cancel = True
ElseIf IsNull(Me.EndDatetxt) Then
MsgBox "Please enter End Date"
Me.EndDatetxt.SetFocus
Cancel = True
Else
DoCmd.OpenReport stDocName, acPreview
End If

However, I don't think that's the source of the error
message. How do the findkeywordtxt, StartDatetxt and
EndDatetxt text boxes interact with the report? Is it
possible that the report has a reference to one of those
after you've closed the form?
Click to expand...
Click to expand...
 
Tony said:
The code you gave me works without the last line As soon as I include the
Docmd line I get the message. As far as I can see nothing else is closing
the form
Weird???
Click to expand...

It sure sounds like the form is being closed
somewhere,somehow. The only other thing I can think of is
that the referencess in the query are misspelled.

Try opening the form and using the query design window to
run the query. Maybe you can get another clue this way?
 
Thanks for the info on If statements Marshall. I'll have a look at the query
and all my code and get back to you if I may? I'm in the Uk so I don't know
if we're in the same time zone. Its 18.49 here now and I think I've worked
long enough I'll have another go tomorrow and get back around this time
Thanks again
Tony
 
Back
Top