Text Box on Report won't hold user data entry after loosing focus...

[Andrew Meador - ASCPA, MCSE, MCP+I, Network+, A+]

I have created a report in Access 2007. This report needs to
display records between two dates entered by the user. I put two text
boxes on the report so I can enter the start and end dates - I set
them to use an input mask of 'short date' format. The problem is that
when I enter anything in these text boxes, as soon as the field looses
focus, the text is cleared out - thus the text box is left blank. I
have a button on the report that calls the requery method of the
report and also, as a test, displays the values of these text boxes in
a msgbox, but it also shows they are blank. What is wrong? I can set
the values by code for the text boxes in the button's click event
before calling the requery method, and the report runs with the proper
data and also shows the text in those text boxes - I just can't seem
to type values into these text boxes.

If this helps, this report is based on a saved SQL Query that I
edited to pull the date range from these text boxes in this report.

Please help!

Thanks!

Andrew

 

[John W. Vinson]

I have created a report in Access 2007. This report needs to
display records between two dates entered by the user. I put two text
boxes on the report so I can enter the start and end dates - I set
them to use an input mask of 'short date' format. The problem is that
when I enter anything in these text boxes, as soon as the field looses
focus, the text is cleared out - thus the text box is left blank. I
have a button on the report that calls the requery method of the
report and also, as a test, displays the values of these text boxes in
a msgbox, but it also shows they are blank. What is wrong? I can set
the values by code for the text boxes in the button's click event
before calling the requery method, and the report runs with the proper
data and also shows the text in those text boxes - I just can't seem
to type values into these text boxes.

If this helps, this report is based on a saved SQL Query that I
edited to pull the date range from these text boxes in this report.

Please help!

Thanks!

Andrew

Reports aren't editable. You can't type a date into a textbox on a Report and
have it stay there!

Rather than *editing* your query every time you change date ranges, make it a
parameter query. For instance, you could use a small unbound Form named
frmCrit with textboxes txtFrom and txtTo; in the query use

BETWEEN [Forms]![frmCrit]![txtFrom] AND [Forms]![frmCrit]![txtTo]

as a criterion on the date field. On the Report you can also set the control
source of a textbox to

=[Forms]![frmCrit]![txtFrom]

to display the data entered by the user.

John W. Vinson [MVP]

 

[Andrew Meador - ASCPA, MCSE, MCP+I, Network+, A+]

I have created a report in Access 2007. This report needs to
display records between two dates entered by the user. I put two text
boxes on the report so I can enter the start and end dates - I set
them to use an input mask of 'short date' format. The problem is that
when I enter anything in these text boxes, as soon as the field looses
focus, the text is cleared out - thus the text box is left blank. I
have a button on the report that calls the requery method of the
report and also, as a test, displays the values of these text boxes in
a msgbox, but it also shows they are blank. What is wrong? I can set
the values by code for the text boxes in the button's click event
before calling the requery method, and the report runs with the proper
data and also shows the text in those text boxes - I just can't seem
to type values into these text boxes.

If this helps, this report is based on a saved SQL Query that I
edited to pull the date range from these text boxes in this report.

Please help!

Andrew

Reports aren't editable. You can't type a date into a textbox on a Report and
have it stay there!

Rather than *editing* your query every time you change date ranges, make it a
parameter query. For instance, you could use a small unbound Form named
frmCrit with textboxes txtFrom and txtTo; in the query use

BETWEEN [Forms]![frmCrit]![txtFrom] AND [Forms]![frmCrit]![txtTo]

as a criterion on the date field. On the Report you can also set the control
source of a textbox to

=[Forms]![frmCrit]![txtFrom]

to display the data entered by the user.

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

This seems clumsy - open a form, fill in the date fields, click
button, open report... then if you want to run with new dates you have
to close the report, re-enter dates in the form, click the button, re-
open report... Is this the only way to get a report that can take
custom parameters? I'm not doubting your input, but this seems complex
compared to changing the text fields and clicking a button to refresh
the report. It seems counter inuitive to me (I'm not a strong access
user yet), to have to open a form to get a report. Why does MS allow
text boxes to be put on reports? If you can't edit them, they should
only allow lables. How odd. I will give this a try - if it works, it
works! MS should rethink this though... if my attempted method is
truely not doable.

Anyway, thanks for the help. I appreciate it. Thanks!

 

[John W. Vinson]

This seems clumsy - open a form, fill in the date fields, click
button, open report... then if you want to run with new dates you have
to close the report, re-enter dates in the form, click the button, re-
open report... Is this the only way to get a report that can take
custom parameters? I'm not doubting your input, but this seems complex
compared to changing the text fields and clicking a button to refresh
the report. It seems counter inuitive to me (I'm not a strong access
user yet), to have to open a form to get a report. Why does MS allow
text boxes to be put on reports? If you can't edit them, they should
only allow lables. How odd. I will give this a try - if it works, it
works! MS should rethink this though... if my attempted method is
truely not doable.

The purpose of a Report is to print data onto paper. If you want to just
display data on screen, use a Form instead.

And you would certainly never (in a production system) see either the Forms or
the Reports window, nor need you close and open things! There would be a
Switchboard form (probably something you build yourself rather than using the
switchboard wizard); it might have the criteria textboxes built into it, or it
could pop up your criteria form. There'd be either a command button on the
criteria form to open (or print) the report, and the Report's Close event
could either close the criteria form, or set focus to it to let the user enter
new criteria; or you could just use the AfterUpdate events of the textboxes to
launch the report.


John W. Vinson [MVP]

 

[George Nicholson]

And you would certainly never (in a production system) see either the

Forms or
the Reports window...

....and in Runtime mode, design view isn't even allowed, so a user would
never be able to change the contents of a text box on a report.

 

[Andrew Meador - ASCPA, MCSE, MCP+I, Network+, A+]

The purpose of a Report is to print data onto paper. If you want to just
display data on screen, use a Form instead.

And you would certainly never (in a production system) see either the Forms or
the Reports window, nor need you close and open things! There would be a
Switchboard form (probably something you build yourself rather than using the
switchboard wizard); it might have the criteria textboxes built into it, or it
could pop up your criteria form. There'd be either a command button on the
criteria form to open (or print) the report, and the Report's Close event
could either close the criteria form, or set focus to it to let the user enter
new criteria; or you could just use the AfterUpdate events of the textboxes to
launch the report.

John W. Vinson [MVP]

I obviously have a lot to learn about Access. I was not trying to
have the user do data entry in the report, only to have the fields on
the report so they could change the content of the report before
printing it. I see and understand your explanation about this though.
I think I need to get a better understanding of the overall structure
of how Access is to be used in a production environment, then I might
be able to see these kinds of logic flows better. Looking at the
forms, reports, and controls, I figured entering data into controls on
a report was planned and expected. One, because they can be put on the
reports, and two because of the property of many of the editable
controls that allows them to be viewed on-screen/printed/or both. I
set the textboxes up to show on both, but the button to only show on
screen. This, 'in my theory', allowed the user to set the date range
on the text boxes, that would then print (showing the date range of
the report), and would keep the button from printing (so it wouldn't
look odd on the printed report). I think you cound see how this could
be confusing to a new Access user.

My background in data usage is in programming and creating the
front end from code, not through Access. I think my flow logic is a
little different due to that. I am not really trying to use Access for
the purpose of an end user front end even now. I am using it as a way
to work with my databases and their data before writing applications
to be the front ends. But, I couldn't wait any longer to use the data,
I needed to use it now and Access gave me the quicker/faster way of
getting to it and getting some reports out of it. So, I'm not real
worried about how to set it up for runtime mode, using switchboards,
etc... But that could change in the future. Thanks for your insight
and help.

Andrew

 

[John W. Vinson]

I think I need to get a better understanding of the overall structure
of how Access is to be used in a production environment, then I might
be able to see these kinds of logic flows better.

In a nutshell, data is stored in Tables; combined, selected, filtered and
sorted in Queries; edited and displayed in Forms; and printed in Reports. From
the user's point of view the tables are "under the hood" out of sight; they
would mainly be interacting with Forms, using a switchboard form to select
which features of the application to run, entering and editing data on a Form
(usually with subforms). When a Report is to be printed the user might enter
criteria into textboxes on a form, and then click a button to print that
report.

If user-entered data needs to be printed on a Report but not stored in any
table (say it's a one-shot comment about the purpose of printing this report)
you can set the Control Source of a report textbox to

=Forms!SomeFormName!SomeTextboxName

to "echo" the data which the user has typed into SomeTextboxName.

John W. Vinson [MVP]