Print single record from report

[Guest]

I have a form for which I was trying build a button that would print the
selected record. I dumped the information into a report to make reading
easier. The report narrows the information down, but the whole report prints
instead of the single record. I tried the GoTo Record command and got an
error message and am unsure of what other options I have. Any suggestions
would be appreciated.

 

[Steve Schapel]

MissaHaarstad,

In the query that the report is based on, enter a criteria in the
primary key field, to identify the form's current reecord. To do thjis,
enter the criteria like this...
[Forms]![NameOfForm]![YourIDfield]

 

[Guest]

I have a similar problem. I have the criteria in my query and the macro
prints wonderfully if I run it outside of my form. The problem I have is
that when I run the macro with the form open the PrintOut function prints all
"active" windows (this includes my form and all of the entries on it). I
need some way to inactivate the form while leaving it open to run the macro.
Does anyone have any advice?

MissaHaarstad,

In the query that the report is based on, enter a criteria in the
primary key field, to identify the form's current reecord. To do thjis,
enter the criteria like this...
[Forms]![NameOfForm]![YourIDfield]

--
Steve Schapel, Microsoft Access MVP

I have a form for which I was trying build a button that would print the
selected record. I dumped the information into a report to make reading
easier. The report narrows the information down, but the whole report prints
instead of the single record. I tried the GoTo Record command and got an
error message and am unsure of what other options I have. Any suggestions
would be appreciated.

 

[Steve Schapel]

Bnichols,

I do not understand why you are using PrintOut action. The usual way to
print a report is OpenReport action.

 

[Guest]

thanks. I got it to work. I think I only have one more problem. In my
database I have 2 forms. I need users at different computers to be able to
use the forms simultaneously to input data and generate the report. I don't
need true internet access. My question is this: if I place the database on
a network drive will multiple users be able to access it simultaneously? If
not, is there an easy way to transfer my 2 forms into data access pages in
order to provide this function? Also, I would prefer for these forms to come
up without opening the entire database allowing users to change the
structure. Thank you so much for your help.

Brandon

 

[Steve Schapel]

Brandon,

In a multi-user environment, the recommended approach is to split the
database, so that yoiu have a backend data file containing the core
tables, and this is placed on the network server, and a frontend
application file containing everything else (forms, reports, etc), and
each user has his own copy of this, on his local machine, linked to the
backend tables. Some more information at
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

Go to the Tools|Startup menu. You will see a place to nominate the form
to open when the database opens, and also an option to untick 'Display
Database Wiondow'.

 

[Guest]

Steve,

Thank you so much for your help. I'm working on splitting the database. I
do have a different question that I don't think should be that hard.

I have a form that is used to enter data into the database. This form has a
field that the user can type in up to 255 characters of text. There are a
few very common phrases that are used in this box, but I need the ability of
the user to enter text freely or edit the common pharase so I can't use a
lookup box. Is there a way to set up several command buttons on the side of
the form that "on click" will choose a given phrase and paste it into that
field on the form?

Thanks again for your help,
Brandon

 

[Steve Schapel]

Brandon,

If by "lookup box" you mean a combobox, then yes you could use a
combobox. Put your common expressions in a table, and make this table
the Row Source of the combobox. Then, set the 'Limit to List' property
of the combobox to No. This will alllow the selection of one of the
common expressions, or the typing in of something else, or the editing
of a common expression.

If, on the other hand, you would still prefer the command button
approach, you would use a SetValue action in a macro on the Click event
of the command button, with its arguments set like this...
Item: [NameOfTheTextbox]
Expression: "What you want your common phrase to say."

 

[Guest]

Steve you are so helpful and I appreciate your advice. If they pay you to do
this and there is a form I can fill out to sing your praises let me know
where it is.

My new problem I know is an easy fix, I just can't seem to do it. I have a
query that is based on a table. I need to be able to search within a field
for a given text.
Ex. Table info - field 1 "No acute findings", field 2 "blank"
In my query I want to be able to use a parameter (via a form) to type in
"acute" and it will pull up the above record because acute is in one of the 2
fields.

I hope that makes sense and is not a big deal to fix.

Thanks again

Brandon,

If by "lookup box" you mean a combobox, then yes you could use a
combobox. Put your common expressions in a table, and make this table
the Row Source of the combobox. Then, set the 'Limit to List' property
of the combobox to No. This will alllow the selection of one of the
common expressions, or the typing in of something else, or the editing
of a common expression.

If, on the other hand, you would still prefer the command button
approach, you would use a SetValue action in a macro on the Click event
of the command button, with its arguments set like this...
Item: [NameOfTheTextbox]
Expression: "What you want your common phrase to say."

--
Steve Schapel, Microsoft Access MVP

Steve,

Thank you so much for your help. I'm working on splitting the database. I
do have a different question that I don't think should be that hard.

I have a form that is used to enter data into the database. This form has a
field that the user can type in up to 255 characters of text. There are a
few very common phrases that are used in this box, but I need the ability of
the user to enter text freely or edit the common pharase so I can't use a
lookup box. Is there a way to set up several command buttons on the side of
the form that "on click" will choose a given phrase and paste it into that
field on the form?

 

[Steve Schapel]

Brandon,

It is great to see you making progress with your use of Access. MVPs
are volunteers and there is no "praise singing" place, but it is good to
have your acknowledgement.

In the design view of the query, in the Criteria row of the grid, under
Field 1, put like this...
Like "*" & [Forms]![NameOfForm]![NameOfTextbox] & "*"

Then, enter exactly the same under Field 2, but in the next row down in
the grid.

 

[Guest]

Steve, I have one more big favor that I am not sure if it is possible. This
may take some explaining to ask if I can do this:

First, I have a form to enter data (prelimreport). Then I have a second
form that pulls up all records that have not been signed of on by a
supervisor. The second form is in tabular format so that it displays all
records without a sign off on the same page. The supervisor then signs off
every record with agree, disagree, or addendum. When he is at the end of
this list all records there are signed off so that the next time the sign off
form is run they will not come up. Now, what i would like to do is generate
a report run by a command button (macro) at the bottom of that page the sign
off form that would print out a report showing all records signed off in that
sitting. I thought of using time as a criteria, but these "sign-off
sessions" could take 5 minutes or 2 hours. I tried referencing the primary
key on that form in a query, but it only queries the last record. I would
appreciate any help/advice you have.

Thanks

Brandon,

It is great to see you making progress with your use of Access. MVPs
are volunteers and there is no "praise singing" place, but it is good to
have your acknowledgement.

In the design view of the query, in the Criteria row of the grid, under
Field 1, put like this...
Like "*" & [Forms]![NameOfForm]![NameOfTextbox] & "*"

Then, enter exactly the same under Field 2, but in the next row down in
the grid.

--
Steve Schapel, Microsoft Access MVP

Steve you are so helpful and I appreciate your advice. If they pay you to do
this and there is a form I can fill out to sing your praises let me know
where it is.

My new problem I know is an easy fix, I just can't seem to do it. I have a
query that is based on a table. I need to be able to search within a field
for a given text.
Ex. Table info - field 1 "No acute findings", field 2 "blank"
In my query I want to be able to use a parameter (via a form) to type in
"acute" and it will pull up the above record because acute is in one of the 2
fields.

 

[Steve Schapel]

Brandon,

There would be a number of approaches that could be taken here.

Your idea about using the time is a good one. You could put an unbound
textbox in the header of the continuous "sign-off" form, with its
Default Value set to Now(). So this will show when the form was opened.
Then, you could add a new field to the table, Date/Time data type,
Let's say you call it WhenUpdated. And on the After Update event of the
control on the form where the agree/disagree/addendum option is entered,
you could use a SetValue macro, with these arguments...
Item: [WhenUpdated]
Expression: Now()
Then, in the Criteria of the query that the report is based on, in the
WhenUpdated column you can use like this...

[Forms]![NameOfYourFrom]![NameOfUnboundTextbox]

Another idea would be to add a Yes/No data type field to the table, and
on the After Update event of the agree/disagree/addendum, your SetValue
macro with these arguments...
Item: [WasUpdated]
Expression: -1
Then, in the Criteria of the query that the report is based on, in the
WasUpdated column you can use like this...
-1
You would then need an Update Query to return all the WasUpdated back to
0 and you could add an OpenQuery action to the macro that prints the
report, after the OpenReport action.

Hope that points you in a useful direction.

 

[Guest]

Steve,

again thanks for your help. I'm playing with your advice and I think I am
going to be able to get it to work. however, I have a more pressing problem.
I was playing with the "Startup" options and checked off all of the boxes to
make the my main form open without shortcuts and all. The problem I have is
that now I can't get back in to the design atmosphere. How can I get back in?

Brandon,

There would be a number of approaches that could be taken here.

Your idea about using the time is a good one. You could put an unbound
textbox in the header of the continuous "sign-off" form, with its
Default Value set to Now(). So this will show when the form was opened.
Then, you could add a new field to the table, Date/Time data type,
Let's say you call it WhenUpdated. And on the After Update event of the
control on the form where the agree/disagree/addendum option is entered,
you could use a SetValue macro, with these arguments...
Item: [WhenUpdated]
Expression: Now()
Then, in the Criteria of the query that the report is based on, in the
WhenUpdated column you can use like this...

[Forms]![NameOfYourFrom]![NameOfUnboundTextbox]

Another idea would be to add a Yes/No data type field to the table, and
on the After Update event of the agree/disagree/addendum, your SetValue
macro with these arguments...
Item: [WasUpdated]
Expression: -1
Then, in the Criteria of the query that the report is based on, in the
WasUpdated column you can use like this...
-1
You would then need an Update Query to return all the WasUpdated back to
0 and you could add an OpenQuery action to the macro that prints the
report, after the OpenReport action.

Hope that points you in a useful direction.

--
Steve Schapel, Microsoft Access MVP

Steve, I have one more big favor that I am not sure if it is possible. This
may take some explaining to ask if I can do this:

First, I have a form to enter data (prelimreport). Then I have a second
form that pulls up all records that have not been signed of on by a
supervisor. The second form is in tabular format so that it displays all
records without a sign off on the same page. The supervisor then signs off
every record with agree, disagree, or addendum. When he is at the end of
this list all records there are signed off so that the next time the sign off
form is run they will not come up. Now, what i would like to do is generate
a report run by a command button (macro) at the bottom of that page the sign
off form that would print out a report showing all records signed off in that
sitting. I thought of using time as a criteria, but these "sign-off
sessions" could take 5 minutes or 2 hours. I tried referencing the primary
key on that form in a query, but it only queries the last record. I would
appreciate any help/advice you have.

 

[Steve Schapel]

Brandon,

Hold down the Shift key while you open the database.

 

[Guest]

Steve, thanks again for your help but I'm back. I thought everything was
working well, but on the test run we noticed a few problems (primarily
formatting stuff that I took care of). However, I did have one problem that
I need your help with. I set up a query as you suggested 3 posts prior to
this one based on the unbound textbox that = Now() and I made a macro that
will set value of [WhenUpdated] = Now(). Then I have a query that selects
all records where [WhenUpdated]> the unbound textbox. It seemed to work
great, but in running the tests we noticed that it always leaves off the most
recent record. For example, I have 5 entries and the [WhenUpdated] field for
all 5 of them is > my textbox. When I run the query, it will successfully
pull the first 4 records and generate my report beautifully but the 5th
record isn't there eventhough [WhenUpdated]>text box.

My only thought on this is that in the form where my macro is to set value
of [WhenUpdated] it always shows an extra record I assume for the next new
record. However, it does this on all forms I have seen that are in tabular
format so I don't really know what else to do. Any ideas? Thanks

 

[Steve Schapel]

Brandon,

The most likely cause of this is that the most recent record is still in
'edit mode', and hasn't yet been saved to the table, and as such will
not be included in the records accessed by the query. Try this... in
the macro where you use SetValue to make [WhenUpdated] = Now(), add
another action:
RunCommand/SaveRecord
See if that resolves it.

 

[Guest]

steve,

thanks. that fixed my problem. I think I only have one more glitch & I'll
be ready. I have a query that I need to pull all records whose [WhenUpdated]
date is in the last date. The parameter that I used in the query is >Date()
- 10. That worked to select the correct records however it pulled each of
the records 20 times so my results from teh query should be 4 records but
instead it was the same 4 records repeated 20 times so my report had 80
entries. Any ideas?

Brandon,

The most likely cause of this is that the most recent record is still in
'edit mode', and hasn't yet been saved to the table, and as such will
not be included in the records accessed by the query. Try this... in
the macro where you use SetValue to make [WhenUpdated] = Now(), add
another action:
RunCommand/SaveRecord
See if that resolves it.

--
Steve Schapel, Microsoft Access MVP

Steve, thanks again for your help but I'm back. I thought everything was
working well, but on the test run we noticed a few problems (primarily
formatting stuff that I took care of). However, I did have one problem that
I need your help with. I set up a query as you suggested 3 posts prior to
this one based on the unbound textbox that = Now() and I made a macro that
will set value of [WhenUpdated] = Now(). Then I have a query that selects
all records where [WhenUpdated]> the unbound textbox. It seemed to work
great, but in running the tests we noticed that it always leaves off the most
recent record. For example, I have 5 entries and the [WhenUpdated] field for
all 5 of them is > my textbox. When I run the query, it will successfully
pull the first 4 records and generate my report beautifully but the 5th
record isn't there eventhough [WhenUpdated]>text box.

My only thought on this is that in the form where my macro is to set value
of [WhenUpdated] it always shows an extra record I assume for the next new
record. However, it does this on all forms I have seen that are in tabular
format so I don't really know what else to do. Any ideas? Thanks

 

[Steve Schapel]

Brandon,

Can you post back with the SQL view of the query please? That will help
me to understand what you are doing.

Did you mean >Date()-1 instead of >Date()-10? When you say "in the last
date", does this mean "in the last 24 hours", or "since midnight
yesterday", or what? There's something not right here.

 

[Guest]

I'm not sure what you mean by the SQL view, but this is copied out of the
parameter field in my query: I have 3 parameters in the query:

1) In the field title [Date] (to pull records in the past 10 days):

Date()-10

2) I also have a parameter in a yes/no field (checkbox) to pull any records
that are yes (checked box):

<>0

3) Lastly, I have a parameter in a field to pull types of exams (1, 2, 3,
or 4 possible)
I only want 1 or 4:

1 Or 4

I want to select all records from the table that have a checked box, are of
exam type 1 or 4, and have been entered in the past 10 days. Where am I
going wrong? If there is a better way for me to post this message let me know
and I will do it.

Thanks

Brandon,

Can you post back with the SQL view of the query please? That will help
me to understand what you are doing.

Did you mean >Date()-1 instead of >Date()-10? When you say "in the last
date", does this mean "in the last 24 hours", or "since midnight
yesterday", or what? There's something not right here.

--
Steve Schapel, Microsoft Access MVP

steve,

thanks. that fixed my problem. I think I only have one more glitch & I'll
be ready. I have a query that I need to pull all records whose [WhenUpdated]
date is in the last date. The parameter that I used in the query is >Date()
- 10. That worked to select the correct records however it pulled each of
the records 20 times so my results from teh query should be 4 records but
instead it was the same 4 records repeated 20 times so my report had 80
entries. Any ideas?

 

[Steve Schapel]

Brandon,

"Parameter" has a different meaning. What you are referring to here are
Criteria.

In design view of the query, if you select SQL from the View menu, this
will show you the SQL view of the query - can you then copy/paste it
into your reply? Thanks. What you have done with the criteria seems
correct, but I can not see so far why you are getting replicated records
returned.

However, this may fix it... in the design view of the query, right-click
anywhere on the background of the upper panel of the query design
window, and then select Properties from the pop-up menu. Set the Unique
Values property to Yes.

By the way, 'date' is a "Reserved Word" (i.e. has a special meaning) in
Access, and as such should not be used as the name of a field.

--
Steve Schapel, Microsoft Access MVP

I'm not sure what you mean by the SQL view, but this is copied out of the
parameter field in my query: I have 3 parameters in the query:

1) In the field title [Date] (to pull records in the past 10 days):

Date()-10

2) I also have a parameter in a yes/no field (checkbox) to pull any records
that are yes (checked box):

<>0

3) Lastly, I have a parameter in a field to pull types of exams (1, 2, 3,
or 4 possible)
I only want 1 or 4:

1 Or 4

I want to select all records from the table that have a checked box, are of
exam type 1 or 4, and have been entered in the past 10 days. Where am I
going wrong? If there is a better way for me to post this message let me know
and I will do it.

\