Report Prompts for info again when you print

[CanBOnly]

Let me start by saying I am new to access and sql so here is what I
have done so far:

Using Access 2003 I have created a 4 column report that pulls data for
3 of the columns from a sql database. When you run the report it
prompts for the start and end dates. There are 4 columns (License #,
Name - which is made up of 4 sql fields (First, Middle, Last, suffix),
Date and Document #).

Here is my problem the Document # is not stored in a table and I don't
need to store the information in a table but I need it to prompt the
user for the information for each record and record it record and
display it on the report. I have created a report and it prompts the
user for the input and it displays correctly on the report. However
when I print the report (using the print button in access) the report
prompts you for the information again. I just used a inputbox command
in the report here is the expression I am using:

=InputBox("Enter Document Number for license # " &
[CredentialHolderPublicNumber])

Again the report works correctly but when you print it prompts for the
input again. Is there a way to suppress the second set of prompts when
I print.

I know this is probably basic but like I say I am new to access and
have not ever designed any reports. Thanks in advance for any help you
can provide.

 

[CanBOnly]

Thanks for your help but now I am getting an Error: 438: Object Doesn't
support this property or method. So let me recap what I have done:

I created a form that has two buttons and two text boxes. One of the
buttons is called cmdCancel and input the code you provided, the other
button is called cmdOK and again I entered the code you provided, the
first text box is called txtContinue and is hidden and the other text
box is just a unbound text box with the name of txtDocumentNo.

Then I went into my report and from the properties of the report I
added the open code you provided changing frmReportsSelection to
InputDocNum (which is the name of my form) and I created a unbound text
box with the name of txtDocumentNo. When I start the report the
InputDocNum form does open but I get the error mentioned above.

I don't think I mentioned but when the report open it prompts for a
starting and ending date so I don't know if that is what is causing my
problem or not.
Thanks again for your help and sorry for my lack of knowledge is this
matter.

Carl

Hello CanB

You can use a form instead of an InputBox. The user types in the desired
value, or picks it from a combo box. When you click on an OK button, you hide
the form (but don't close it). The report reads it's parameters from the open
form.

You can either open the form first, and then have the command button open
the report and hide the form, or you can use the Report's Open event
procedure to open a form in dialog mode. Something like this, where the
report and the form each include a text box named "txtDocumentNo":

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ProcError

DoCmd.OpenForm "frmReportsSelection", WindowMode:=acDialog

'Cancel the report if "cancel" was selected on the dialog form.
If Forms.frmReportsSelection.txtContinue = "no" Then
Cancel = True
Else
Me.txtDocumentNo = Forms![frmReportsSelection]![txtDocumentNo]
End If


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub


Code in the form would include, for example, the following for two command
buttons named "cmdCancel" and "cmdOK", and a text box named "txtContinue":

Private Sub cmdCancel_Click()
On Error GoTo ProcError

Me.txtContinue = "no"
Me.Visible = False

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

Private Sub cmdOK_Click()
On Error GoTo ProcError

Me.txtContinue = "yes"
Me.Visible = False

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Let me start by saying I am new to access and sql so here is what I
have done so far:

Using Access 2003 I have created a 4 column report that pulls data for
3 of the columns from a sql database. When you run the report it
prompts for the start and end dates. There are 4 columns (License #,
Name - which is made up of 4 sql fields (First, Middle, Last, suffix),
Date and Document #).

Here is my problem the Document # is not stored in a table and I don't
need to store the information in a table but I need it to prompt the
user for the information for each record and record it record and
display it on the report. I have created a report and it prompts the
user for the input and it displays correctly on the report. However
when I print the report (using the print button in access) the report
prompts you for the information again. I just used a inputbox command
in the report here is the expression I am using:

=InputBox("Enter Document Number for license # " &
[CredentialHolderPublicNumber])

Again the report works correctly but when you print it prompts for the
input again. Is there a way to suppress the second set of prompts when
I print.

I know this is probably basic but like I say I am new to access and
have not ever designed any reports. Thanks in advance for any help you
can provide.