Open Report using current record

[Mike]

Hi,

Can anyone offer any tips/advice. I would like to use a command button to
open up a report for the current open record. So for example i have opened a
record up in the name of "Superted" on my form and I want to e-mail Superted
with his "Spotty Report". What I am doing at the moment is clicking on my
command button to send an email of the report, for which i created a macro,
but before it will attach the report, I am having to type in the data such as
name and the date range i want to show on the report. My concern is that
someone may enter the wrong information and send the wrong report or details.
All the information required for report is already stored on each of the
records. Is there anyway i can use the record information to generate the
report without entering the values and just use the values recorded on the
record?

 

[Cheese_whiz]

Hi Mike,

Is your report based on a query? If so, if you open the query in design
view, do you see things like the following in the criteria row of the query
grid:

[Enter Employee Name]
[Input Begin Date]
[Enter End Date]

??

CW

 

[Mike]

Hi,

Yes the report is run through a query which is filtered like you have
described.

Mike

Hi Mike,

Is your report based on a query? If so, if you open the query in design
view, do you see things like the following in the criteria row of the query
grid:

[Enter Employee Name]
[Input Begin Date]
[Enter End Date]

??

CW

Hi,

Can anyone offer any tips/advice. I would like to use a command button to
open up a report for the current open record. So for example i have opened a
record up in the name of "Superted" on my form and I want to e-mail Superted
with his "Spotty Report". What I am doing at the moment is clicking on my
command button to send an email of the report, for which i created a macro,
but before it will attach the report, I am having to type in the data such as
name and the date range i want to show on the report. My concern is that
someone may enter the wrong information and send the wrong report or details.
All the information required for report is already stored on each of the
records. Is there anyway i can use the record information to generate the
report without entering the values and just use the values recorded on the
record?

 

[Cheese_whiz]

Hi Mike,

Always make sure you have a backup copy or two before making changes.
Backing up the entire app is best, and I do that regularly, but sometimes
between backups I'll just make a copy of an object I'm working on so if I
'mess up' I can delete the one I was working on and use the copy (after
copying it again!).

What you have is a parameter query. What you can do is replace the entries
like this:

[Enter Employee Name]

in the query criteria row with references to your form's control values like
this:

=Forms!YourFormName!YourControlName

where 'YourFormName is the name of your form, and 'YourControlName' is the
name of the control with the employee name.

There's a couple of 'gotchas' to be aware of. First, if you use that query
elsewhere in your application, and it's working in that other place now, and
you change it, well, it's not going to work in that other place anymore. I
think that's pretty unlikely with a parameter query, but not inconceivable.
You could, if necessary, just make a copy of it for each use, but you would
also have to make sure whatever needs to use the copy has the appropriate
changes made to point to it instead of the original.

Second, combo boxes can be a little tricky, because they usually have more
than one column in their rows (choices), even if only one column is visible.
You may need to check the rowsource of the combo box if one of your
references in your query points to a combo box. You can ask about that in
another question if you have trouble with it, or I'll try to watch this but
I'm in and out this weekend.

The broader point is to remember that whatever values your query column
holds (strings, numbers, dates, etc), you have to point to a control (or
column in a control box or list box if they have multiple columns) that has
that kind of value.

There is another way to solve your issue, and in the long term it's a better
way imo, but probably doesn't really matter here.
You could just remove all the things in the criteria row of the query and
use the openreport method on the click event of your button, but that can get
a little tricky with multiple criteria and the quotes if you aren't use to
doing it. It's 'cleaner', imo, because then your query doesn't have hard
coded criteria, but frequently it doesn't really matter if the query does
have hard coded criteria.

HTH,
CW

Hi,

Yes the report is run through a query which is filtered like you have
described.

Mike

Hi Mike,

Is your report based on a query? If so, if you open the query in design
view, do you see things like the following in the criteria row of the query
grid:

[Enter Employee Name]
[Input Begin Date]
[Enter End Date]

??

CW

Hi,

Can anyone offer any tips/advice. I would like to use a command button to
open up a report for the current open record. So for example i have opened a
record up in the name of "Superted" on my form and I want to e-mail Superted
with his "Spotty Report". What I am doing at the moment is clicking on my
command button to send an email of the report, for which i created a macro,
but before it will attach the report, I am having to type in the data such as
name and the date range i want to show on the report. My concern is that
someone may enter the wrong information and send the wrong report or details.
All the information required for report is already stored on each of the
records. Is there anyway i can use the record information to generate the
report without entering the values and just use the values recorded on the
record?

 

[Cheese_whiz]

Mike,

I went back and re-read your question, and there's some question in my mind
as to your situation.

If your form is based on the same query as your report, or even a similar
one, you MAY be able to use the openreport method with a single criteria
which might be the easiest way to go.

Let me know if you think that's the case.

CW

Hi Mike,

Always make sure you have a backup copy or two before making changes.
Backing up the entire app is best, and I do that regularly, but sometimes
between backups I'll just make a copy of an object I'm working on so if I
'mess up' I can delete the one I was working on and use the copy (after
copying it again!).

What you have is a parameter query. What you can do is replace the entries
like this:

[Enter Employee Name]

in the query criteria row with references to your form's control values like
this:

=Forms!YourFormName!YourControlName

where 'YourFormName is the name of your form, and 'YourControlName' is the
name of the control with the employee name.

There's a couple of 'gotchas' to be aware of. First, if you use that query
elsewhere in your application, and it's working in that other place now, and
you change it, well, it's not going to work in that other place anymore. I
think that's pretty unlikely with a parameter query, but not inconceivable.
You could, if necessary, just make a copy of it for each use, but you would
also have to make sure whatever needs to use the copy has the appropriate
changes made to point to it instead of the original.

Second, combo boxes can be a little tricky, because they usually have more
than one column in their rows (choices), even if only one column is visible.
You may need to check the rowsource of the combo box if one of your
references in your query points to a combo box. You can ask about that in
another question if you have trouble with it, or I'll try to watch this but
I'm in and out this weekend.

The broader point is to remember that whatever values your query column
holds (strings, numbers, dates, etc), you have to point to a control (or
column in a control box or list box if they have multiple columns) that has
that kind of value.

There is another way to solve your issue, and in the long term it's a better
way imo, but probably doesn't really matter here.
You could just remove all the things in the criteria row of the query and
use the openreport method on the click event of your button, but that can get
a little tricky with multiple criteria and the quotes if you aren't use to
doing it. It's 'cleaner', imo, because then your query doesn't have hard
coded criteria, but frequently it doesn't really matter if the query does
have hard coded criteria.

HTH,
CW

Hi,

Yes the report is run through a query which is filtered like you have
described.

Mike

Hi Mike,

Is your report based on a query? If so, if you open the query in design
view, do you see things like the following in the criteria row of the query
grid:

[Enter Employee Name]
[Input Begin Date]
[Enter End Date]

??

CW

:

Hi,

Can anyone offer any tips/advice. I would like to use a command button to
open up a report for the current open record. So for example i have opened a
record up in the name of "Superted" on my form and I want to e-mail Superted
with his "Spotty Report". What I am doing at the moment is clicking on my
command button to send an email of the report, for which i created a macro,
but before it will attach the report, I am having to type in the data such as
name and the date range i want to show on the report. My concern is that
someone may enter the wrong information and send the wrong report or details.
All the information required for report is already stored on each of the
records. Is there anyway i can use the record information to generate the
report without entering the values and just use the values recorded on the
record?

 

[Mike]

Hi CW,

Thanks for all you advise on this, it's much appreciated. Unfortunately the
query is independant from the actual form. I will try and explain just a
little about what the form is and function, so you have a clearer picture.

I have my main form which basically provides me with contact details with 3
subforms that contain various types of absence, such as annual, sick, study
leave.

Retrieval of a particular record is aquired by dropdown box, (I just found
this to be easier for me) and contains surname and firstname in that order.

I have a command button with the openreport macro, which runs the query,
which in turn asks for the values, in this case surname, start date and end
date, which then produces the report and attaches to an e-mail.

Is there a way i can just click on the command button and let it prepare the
report based on the name, start date and end date recorded on the main form.

Thanks again
Mike

Mike,

I went back and re-read your question, and there's some question in my mind
as to your situation.

If your form is based on the same query as your report, or even a similar
one, you MAY be able to use the openreport method with a single criteria
which might be the easiest way to go.

Let me know if you think that's the case.

CW

Hi Mike,

Always make sure you have a backup copy or two before making changes.
Backing up the entire app is best, and I do that regularly, but sometimes
between backups I'll just make a copy of an object I'm working on so if I
'mess up' I can delete the one I was working on and use the copy (after
copying it again!).

What you have is a parameter query. What you can do is replace the entries
like this:

[Enter Employee Name]

in the query criteria row with references to your form's control values like
this:

=Forms!YourFormName!YourControlName

where 'YourFormName is the name of your form, and 'YourControlName' is the
name of the control with the employee name.

There's a couple of 'gotchas' to be aware of. First, if you use that query
elsewhere in your application, and it's working in that other place now, and
you change it, well, it's not going to work in that other place anymore. I
think that's pretty unlikely with a parameter query, but not inconceivable.
You could, if necessary, just make a copy of it for each use, but you would
also have to make sure whatever needs to use the copy has the appropriate
changes made to point to it instead of the original.

Second, combo boxes can be a little tricky, because they usually have more
than one column in their rows (choices), even if only one column is visible.
You may need to check the rowsource of the combo box if one of your
references in your query points to a combo box. You can ask about that in
another question if you have trouble with it, or I'll try to watch this but
I'm in and out this weekend.

The broader point is to remember that whatever values your query column
holds (strings, numbers, dates, etc), you have to point to a control (or
column in a control box or list box if they have multiple columns) that has
that kind of value.

There is another way to solve your issue, and in the long term it's a better
way imo, but probably doesn't really matter here.
You could just remove all the things in the criteria row of the query and
use the openreport method on the click event of your button, but that can get
a little tricky with multiple criteria and the quotes if you aren't use to
doing it. It's 'cleaner', imo, because then your query doesn't have hard
coded criteria, but frequently it doesn't really matter if the query does
have hard coded criteria.

HTH,
CW

Hi,

Yes the report is run through a query which is filtered like you have
described.

Mike

:

Hi Mike,

Is your report based on a query? If so, if you open the query in design
view, do you see things like the following in the criteria row of the query
grid:

[Enter Employee Name]
[Input Begin Date]
[Enter End Date]

??

CW

:

Hi,

Can anyone offer any tips/advice. I would like to use a command button to
open up a report for the current open record. So for example i have opened a
record up in the name of "Superted" on my form and I want to e-mail Superted
with his "Spotty Report". What I am doing at the moment is clicking on my
command button to send an email of the report, for which i created a macro,
but before it will attach the report, I am having to type in the data such as
name and the date range i want to show on the report. My concern is that
someone may enter the wrong information and send the wrong report or details.
All the information required for report is already stored on each of the
records. Is there anyway i can use the record information to generate the
report without entering the values and just use the values recorded on the
record?

 

[Keith Wilby]

Hi,

Can anyone offer any tips/advice. I would like to use a command button to
open up a report for the current open record. So for example i have
opened a
record up in the name of "Superted" on my form and I want to e-mail
Superted
with his "Spotty Report". What I am doing at the moment is clicking on my
command button to send an email of the report, for which i created a
macro,
but before it will attach the report, I am having to type in the data such
as
name and the date range i want to show on the report. My concern is that
someone may enter the wrong information and send the wrong report or
details.
All the information required for report is already stored on each of the
records. Is there anyway i can use the record information to generate the
report without entering the values and just use the values recorded on the
record?

Use the filter argument of the Open Report method:

DoCmd.OpenReport "rptMyReport", acViewPreview, , "[ID] = " & Me.txtID

I've assumed that you have a unique record identifier called ID and a text
box called txtID on your form bound to that field.

HTH - Keith.
www.keithwilby.co.uk

 

[Cheese_whiz]

Hi Mike,

Either of the two ways I described will work. Keith suggests the criteria
argument of the openreport method which is the preferred method (at least
it's MY preferred method), and provided syntax but only for one criterion
when you have three. That's why I was trying to get a better idea about your
query, because I wasn't sure whether or not you needed to include all three
criteria in the openreport method. Now, I'm thinking that you WOULD need to
include all three.

If you want to go that route, then you need to remove all those entries in
the criteria row of your query, and then use something like this in the click
event of your button:

_________________________

DoCmd.OpenReport "YourReportName", acviewpreview, , "EEID = " & txtID & "
And startDate = #" & Me.txtStartDate & "# And endDate = #" & Me.txtEndDate &
"#"
____________________________

That's all typed on one line. You can split it using the continuation
character in vba which is the underscore (_) (no parens).

That assumes:

EEID: the name of the column in your query that holds the primary key in the
table in your query where you store employee info (the main table for
employees). See 'note' below.

txtID: Name of the control on your form that holds the EEID value in your
form's records.

startDate: Name of the start date column in your query

txtStartDate: control on your form that holds the start date

endDate: Name of end date column in your query

txtEndDate: Name of control on your form that holds the end date value.

Note: your query was previously setup to use surnames entered by users, but
that's really a flawed way of doing things, though maybe necessary in the
confines of a parameter query. Most of the time, you want to use primary key
values instead of something like 'surname' which can cause trouble if you
have more than one person with the same surname in your application.

The code above assumes a numerical primary key EEID and uses it instead of
surname. If that column (or whatever you call it's equivalent) is not part
of your query, you may have to drag it down from your table in the top part
of the query design window to the grid at the bottom. I'm not 100% sure if
the value can be found by the code if it's not in the grid...

Finally, it's possible my syntax could be off. I think it's right, but I'm
far from being above making a mistake.

HTH,
CW

Hi CW,

Thanks for all you advise on this, it's much appreciated. Unfortunately the
query is independant from the actual form. I will try and explain just a
little about what the form is and function, so you have a clearer picture.

I have my main form which basically provides me with contact details with 3
subforms that contain various types of absence, such as annual, sick, study
leave.

Retrieval of a particular record is aquired by dropdown box, (I just found
this to be easier for me) and contains surname and firstname in that order.

I have a command button with the openreport macro, which runs the query,
which in turn asks for the values, in this case surname, start date and end
date, which then produces the report and attaches to an e-mail.

Is there a way i can just click on the command button and let it prepare the
report based on the name, start date and end date recorded on the main form.

Thanks again
Mike

Mike,

I went back and re-read your question, and there's some question in my mind
as to your situation.

If your form is based on the same query as your report, or even a similar
one, you MAY be able to use the openreport method with a single criteria
which might be the easiest way to go.

Let me know if you think that's the case.

CW

Hi Mike,

Always make sure you have a backup copy or two before making changes.
Backing up the entire app is best, and I do that regularly, but sometimes
between backups I'll just make a copy of an object I'm working on so if I
'mess up' I can delete the one I was working on and use the copy (after
copying it again!).

What you have is a parameter query. What you can do is replace the entries
like this:

[Enter Employee Name]

in the query criteria row with references to your form's control values like
this:

=Forms!YourFormName!YourControlName

where 'YourFormName is the name of your form, and 'YourControlName' is the
name of the control with the employee name.

There's a couple of 'gotchas' to be aware of. First, if you use that query
elsewhere in your application, and it's working in that other place now, and
you change it, well, it's not going to work in that other place anymore. I
think that's pretty unlikely with a parameter query, but not inconceivable.
You could, if necessary, just make a copy of it for each use, but you would
also have to make sure whatever needs to use the copy has the appropriate
changes made to point to it instead of the original.

Second, combo boxes can be a little tricky, because they usually have more
than one column in their rows (choices), even if only one column is visible.
You may need to check the rowsource of the combo box if one of your
references in your query points to a combo box. You can ask about that in
another question if you have trouble with it, or I'll try to watch this but
I'm in and out this weekend.

The broader point is to remember that whatever values your query column
holds (strings, numbers, dates, etc), you have to point to a control (or
column in a control box or list box if they have multiple columns) that has
that kind of value.

There is another way to solve your issue, and in the long term it's a better
way imo, but probably doesn't really matter here.
You could just remove all the things in the criteria row of the query and
use the openreport method on the click event of your button, but that can get
a little tricky with multiple criteria and the quotes if you aren't use to
doing it. It's 'cleaner', imo, because then your query doesn't have hard
coded criteria, but frequently it doesn't really matter if the query does
have hard coded criteria.

HTH,
CW


:

Hi,

Yes the report is run through a query which is filtered like you have
described.

Mike

:

Hi Mike,

Is your report based on a query? If so, if you open the query in design
view, do you see things like the following in the criteria row of the query
grid:

[Enter Employee Name]
[Input Begin Date]
[Enter End Date]

??

CW

:

Hi,

Can anyone offer any tips/advice. I would like to use a command button to
open up a report for the current open record. So for example i have opened a
record up in the name of "Superted" on my form and I want to e-mail Superted
with his "Spotty Report". What I am doing at the moment is clicking on my
command button to send an email of the report, for which i created a macro,
but before it will attach the report, I am having to type in the data such as
name and the date range i want to show on the report. My concern is that
someone may enter the wrong information and send the wrong report or details.
All the information required for report is already stored on each of the
records. Is there anyway i can use the record information to generate the
report without entering the values and just use the values recorded on the
record?

 

[Mike]

Hi CW,

Thank you very much for your help it's really appreciated and also a big
thanks for the step by step instructions.

Just to let you know that I entered the code into vba via the click event as
you described and the first part of the code worked a dream and opens up the
report for the current record which is great, however i'm hitting problems
with the second part of the string. I did at first copy and paste your
string and amended the names accordingly, however when i got to:

And Holiday Start = #" & Me.txtText145 & "# And Holiday End = #" &
Me.txtText147 &
"#"

it highlighted in red, i then typed this in all one line as you described,
but then it hightlighted the whole lot in red, am i missing something simple?
The following is exactly how i entered the code:

DoCmd.OpenReport "Holiday Record Card", acViewPreview, , "Record = " &
Record &" And Holiday Start = #" & Me.txtText145 & "# And Holiday End = #" &
Me.txtText147 & "#"

Thanks
Mike

Hi Mike,

Either of the two ways I described will work. Keith suggests the criteria
argument of the openreport method which is the preferred method (at least
it's MY preferred method), and provided syntax but only for one criterion
when you have three. That's why I was trying to get a better idea about your
query, because I wasn't sure whether or not you needed to include all three
criteria in the openreport method. Now, I'm thinking that you WOULD need to
include all three.

If you want to go that route, then you need to remove all those entries in
the criteria row of your query, and then use something like this in the click
event of your button:

_________________________

DoCmd.OpenReport "YourReportName", acviewpreview, , "EEID = " & txtID & "
And startDate = #" & Me.txtStartDate & "# And endDate = #" & Me.txtEndDate &
"#"
____________________________

That's all typed on one line. You can split it using the continuation
character in vba which is the underscore (_) (no parens).

That assumes:

EEID: the name of the column in your query that holds the primary key in the
table in your query where you store employee info (the main table for
employees). See 'note' below.

txtID: Name of the control on your form that holds the EEID value in your
form's records.

startDate: Name of the start date column in your query

txtStartDate: control on your form that holds the start date

endDate: Name of end date column in your query

txtEndDate: Name of control on your form that holds the end date value.

Note: your query was previously setup to use surnames entered by users, but
that's really a flawed way of doing things, though maybe necessary in the
confines of a parameter query. Most of the time, you want to use primary key
values instead of something like 'surname' which can cause trouble if you
have more than one person with the same surname in your application.

The code above assumes a numerical primary key EEID and uses it instead of
surname. If that column (or whatever you call it's equivalent) is not part
of your query, you may have to drag it down from your table in the top part
of the query design window to the grid at the bottom. I'm not 100% sure if
the value can be found by the code if it's not in the grid...

Finally, it's possible my syntax could be off. I think it's right, but I'm
far from being above making a mistake.

HTH,
CW

Hi CW,

Thanks for all you advise on this, it's much appreciated. Unfortunately the
query is independant from the actual form. I will try and explain just a
little about what the form is and function, so you have a clearer picture.

I have my main form which basically provides me with contact details with 3
subforms that contain various types of absence, such as annual, sick, study
leave.

Retrieval of a particular record is aquired by dropdown box, (I just found
this to be easier for me) and contains surname and firstname in that order.

I have a command button with the openreport macro, which runs the query,
which in turn asks for the values, in this case surname, start date and end
date, which then produces the report and attaches to an e-mail.

Is there a way i can just click on the command button and let it prepare the
report based on the name, start date and end date recorded on the main form.

Thanks again
Mike

Mike,

I went back and re-read your question, and there's some question in my mind
as to your situation.

If your form is based on the same query as your report, or even a similar
one, you MAY be able to use the openreport method with a single criteria
which might be the easiest way to go.

Let me know if you think that's the case.

CW

:

Hi Mike,

Always make sure you have a backup copy or two before making changes.
Backing up the entire app is best, and I do that regularly, but sometimes
between backups I'll just make a copy of an object I'm working on so if I
'mess up' I can delete the one I was working on and use the copy (after
copying it again!).

What you have is a parameter query. What you can do is replace the entries
like this:

[Enter Employee Name]

in the query criteria row with references to your form's control values like
this:

=Forms!YourFormName!YourControlName

where 'YourFormName is the name of your form, and 'YourControlName' is the
name of the control with the employee name.

There's a couple of 'gotchas' to be aware of. First, if you use that query
elsewhere in your application, and it's working in that other place now, and
you change it, well, it's not going to work in that other place anymore. I
think that's pretty unlikely with a parameter query, but not inconceivable.
You could, if necessary, just make a copy of it for each use, but you would
also have to make sure whatever needs to use the copy has the appropriate
changes made to point to it instead of the original.

Second, combo boxes can be a little tricky, because they usually have more
than one column in their rows (choices), even if only one column is visible.
You may need to check the rowsource of the combo box if one of your
references in your query points to a combo box. You can ask about that in
another question if you have trouble with it, or I'll try to watch this but
I'm in and out this weekend.

The broader point is to remember that whatever values your query column
holds (strings, numbers, dates, etc), you have to point to a control (or
column in a control box or list box if they have multiple columns) that has
that kind of value.

There is another way to solve your issue, and in the long term it's a better
way imo, but probably doesn't really matter here.
You could just remove all the things in the criteria row of the query and
use the openreport method on the click event of your button, but that can get
a little tricky with multiple criteria and the quotes if you aren't use to
doing it. It's 'cleaner', imo, because then your query doesn't have hard
coded criteria, but frequently it doesn't really matter if the query does
have hard coded criteria.

HTH,
CW


:

Hi,

Yes the report is run through a query which is filtered like you have
described.

Mike

:

Hi Mike,

Is your report based on a query? If so, if you open the query in design
view, do you see things like the following in the criteria row of the query
grid:

[Enter Employee Name]
[Input Begin Date]
[Enter End Date]

??

CW

:

Hi,

Can anyone offer any tips/advice. I would like to use a command button to
open up a report for the current open record. So for example i have opened a
record up in the name of "Superted" on my form and I want to e-mail Superted
with his "Spotty Report". What I am doing at the moment is clicking on my
command button to send an email of the report, for which i created a macro,
but before it will attach the report, I am having to type in the data such as
name and the date range i want to show on the report. My concern is that
someone may enter the wrong information and send the wrong report or details.
All the information required for report is already stored on each of the
records. Is there anyway i can use the record information to generate the
report without entering the values and just use the values recorded on the
record?

 

[Keith Wilby]

it highlighted in red, i then typed this in all one line as you described,
but then it hightlighted the whole lot in red, am i missing something
simple?
The following is exactly how i entered the code:

DoCmd.OpenReport "Holiday Record Card", acViewPreview, , "Record = " &
Record &" And Holiday Start = #" & Me.txtText145 & "# And Holiday End = #"
&
Me.txtText147 & "#"

PMFJI. What is the second "Record" in

"Record = " & Record

, a text box on your form? If so then try:

"Record = " & Me.Record

If the data type is Text then you'll need quotes:

"Record = ' " & Me.Record & " ' "

Keith.
www.keithwilby.co.uk

 

[Mike]

Hi keith,

As CW broke this down, i entered the information as per instructions. record
is the name of my column within the query and record is also on my form and
the txtID (this field is also the primary key) are you saying this isn't
right? As mentioned the first part of the code CW provided worked, but it
was the second half that posed the problem as i am needing to extract data
between two specific dates that are recorded on the form? I use a dropdown
list by surname and firstname so that i can move between records easily but
each record is assigned to a unique number ie the Record.

I'll probably kick myself when i've managed to sort this, as i think it's
just my misunderstanding.

Mike

 

[Cheese_whiz]

Hi Mike,

One thing you might need to do is use brackets around two word field names.
So something like:
Holiday Start

would become:
[Holiday Start]

See if that helps.

I've looked back at my syntax and I think it's right assuming the date
fields in your query represent fields that are actually date type, and not
string. I'll see if I can get a minute a little later to test things and
verify.

HTH,
CW

 

[Cheese_whiz]

Hi Mike,

Between is different than equal to.

Try this:

DoCmd.OpenReport "Holiday Record Card", acViewPreview, , "Record = " &
Me.Record & " And [Holiday Start] >= #" & Me.txtText145 & "# And [Holiday
End] <= #" & Me.txtText147 & "#"

Note: If Record is the name of a column in your query, you need to match it
to the value of a control on your form. It SHOULD be something like, as
Keith suggested:

"Record = " & Me.ControlName & " And blah blah blah

CW

Hi Mike,

One thing you might need to do is use brackets around two word field names.
So something like:
Holiday Start

would become:
[Holiday Start]

See if that helps.

I've looked back at my syntax and I think it's right assuming the date
fields in your query represent fields that are actually date type, and not
string. I'll see if I can get a minute a little later to test things and
verify.

HTH,
CW

Hi keith,

As CW broke this down, i entered the information as per instructions. record
is the name of my column within the query and record is also on my form and
the txtID (this field is also the primary key) are you saying this isn't
right? As mentioned the first part of the code CW provided worked, but it
was the second half that posed the problem as i am needing to extract data
between two specific dates that are recorded on the form? I use a dropdown
list by surname and firstname so that i can move between records easily but
each record is assigned to a unique number ie the Record.

I'll probably kick myself when i've managed to sort this, as i think it's
just my misunderstanding.

Mike

 

[Mike]

Hi CW,

Thanks for your patience on this, hopefully I am getting there now.

I entered the Event as you described and there was no highlighted areas this
time, however when i come to run the command it says I didn't specify any
search critera with a FindRecord action. I am assuming this has something to
do with the control on the form that you and Keith have mentioned.

Just to clarify what i am doing and what i believe you are saying. Record
is the name of my primary key, which uses the autonumber function, and is
listed as one of the columns on the query(ie is part of the search results)
On the form, I have clicked on properties for the field that contains the
Record Autonumber and the control source is also Record is this right or does
the control come from somewhere else?

Hi Mike,

Between is different than equal to.

Try this:

DoCmd.OpenReport "Holiday Record Card", acViewPreview, , "Record = " &
Me.Record & " And [Holiday Start] >= #" & Me.txtText145 & "# And [Holiday
End] <= #" & Me.txtText147 & "#"

Note: If Record is the name of a column in your query, you need to match it
to the value of a control on your form. It SHOULD be something like, as
Keith suggested:

"Record = " & Me.ControlName & " And blah blah blah

CW

Hi Mike,

One thing you might need to do is use brackets around two word field names.
So something like:
Holiday Start

would become:
[Holiday Start]

See if that helps.

I've looked back at my syntax and I think it's right assuming the date
fields in your query represent fields that are actually date type, and not
string. I'll see if I can get a minute a little later to test things and
verify.

HTH,
CW

Hi keith,

As CW broke this down, i entered the information as per instructions. record
is the name of my column within the query and record is also on my form and
the txtID (this field is also the primary key) are you saying this isn't
right? As mentioned the first part of the code CW provided worked, but it
was the second half that posed the problem as i am needing to extract data
between two specific dates that are recorded on the form? I use a dropdown
list by surname and firstname so that i can move between records easily but
each record is assigned to a unique number ie the Record.

I'll probably kick myself when i've managed to sort this, as i think it's
just my misunderstanding.

Mike



:


it highlighted in red, i then typed this in all one line as you described,
but then it hightlighted the whole lot in red, am i missing something
simple?
The following is exactly how i entered the code:

DoCmd.OpenReport "Holiday Record Card", acViewPreview, , "Record = " &
Record &" And Holiday Start = #" & Me.txtText145 & "# And Holiday End = #"
&
Me.txtText147 & "#"


PMFJI. What is the second "Record" in

"Record = " & Record

, a text box on your form? If so then try:

"Record = " & Me.Record

If the data type is Text then you'll need quotes:

"Record = ' " & Me.Record & " ' "

Keith.
www.keithwilby.co.uk

 

[Mike]

Following on from my message below. I can confirm that the following piece
of code does work

DoCmd.OpenReport "Holiday Record Card", acViewPreview, , "Record = " &
Me.Record & " "

From this it does open up the correct record, but the second part still
doesn't work? I have just realised however that the Holiday Start Date and
Holiday End Date are actually located on the subreport (Holiday Card), even
though the original data is recorded on the main contact table could this be
the reason it can't find the search criteria? I assume it would need to
point it to the main form and the subreport to collate the criteria needed to
produce the report. I could quite easly place this on the main form, however
i believe the reason for it being on the subreport is that the dates actually
relate to the holiday entitlement period and thererfore are best suited to
the subreport to the actual main form.

Mike

Hi CW,

Thanks for your patience on this, hopefully I am getting there now.

I entered the Event as you described and there was no highlighted areas this
time, however when i come to run the command it says I didn't specify any
search critera with a FindRecord action. I am assuming this has something to
do with the control on the form that you and Keith have mentioned.

Just to clarify what i am doing and what i believe you are saying. Record
is the name of my primary key, which uses the autonumber function, and is
listed as one of the columns on the query(ie is part of the search results)
On the form, I have clicked on properties for the field that contains the
Record Autonumber and the control source is also Record is this right or does
the control come from somewhere else?

Hi Mike,

Between is different than equal to.

Try this:

DoCmd.OpenReport "Holiday Record Card", acViewPreview, , "Record = " &
Me.Record & " And [Holiday Start] >= #" & Me.txtText145 & "# And [Holiday
End] <= #" & Me.txtText147 & "#"

Note: If Record is the name of a column in your query, you need to match it
to the value of a control on your form. It SHOULD be something like, as
Keith suggested:

"Record = " & Me.ControlName & " And blah blah blah

CW

Hi Mike,

One thing you might need to do is use brackets around two word field names.
So something like:
Holiday Start

would become:
[Holiday Start]

See if that helps.

I've looked back at my syntax and I think it's right assuming the date
fields in your query represent fields that are actually date type, and not
string. I'll see if I can get a minute a little later to test things and
verify.

HTH,
CW

:

Hi keith,

As CW broke this down, i entered the information as per instructions. record
is the name of my column within the query and record is also on my form and
the txtID (this field is also the primary key) are you saying this isn't
right? As mentioned the first part of the code CW provided worked, but it
was the second half that posed the problem as i am needing to extract data
between two specific dates that are recorded on the form? I use a dropdown
list by surname and firstname so that i can move between records easily but
each record is assigned to a unique number ie the Record.

I'll probably kick myself when i've managed to sort this, as i think it's
just my misunderstanding.

Mike



:


it highlighted in red, i then typed this in all one line as you described,
but then it hightlighted the whole lot in red, am i missing something
simple?
The following is exactly how i entered the code:

DoCmd.OpenReport "Holiday Record Card", acViewPreview, , "Record = " &
Record &" And Holiday Start = #" & Me.txtText145 & "# And Holiday End = #"
&
Me.txtText147 & "#"


PMFJI. What is the second "Record" in

"Record = " & Record

, a text box on your form? If so then try:

"Record = " & Me.Record

If the data type is Text then you'll need quotes:

"Record = ' " & Me.Record & " ' "

Keith.
www.keithwilby.co.uk

 

[Cheese_whiz]

Hi Mike,

Some final thoughts...

It makes a HUGE difference that some of the data you are trying to filter on
is in a sub report. As far as I know, that can NOT be accomplished using the
where clause of the openreport method. What I think you would have to do is
use the where clause for just that one field in your main report's
recordsource (the autonumber ID pk), and then use references to form controls
in the recordsource of your sub report for the other two the dates).

Though I'm not sure it would be the best way to go about doing it, you MIGHT
be able to combine the record sources from both the main report and the sub
report, and then get rid of the sub report and just use controls on the main
report to hold the data that now appears in the sub report. That almost
surely would work out IF the subreport is only ever going to have one record
in it. It MAY work out even if it might have more than one record by using
the 'grouping' feature of the report, but I don't know.

If you DID combine both report and sub report recordsources into one, then
the where clause I provided earlier that includes all three criteria would
then, presumably, work.

I'm not sure I can offer much more on this thread. Sorry I couldn't be of
more help. Good luck with it.

CW

Following on from my message below. I can confirm that the following piece
of code does work

DoCmd.OpenReport "Holiday Record Card", acViewPreview, , "Record = " &
Me.Record & " "

From this it does open up the correct record, but the second part still
doesn't work? I have just realised however that the Holiday Start Date and
Holiday End Date are actually located on the subreport (Holiday Card), even
though the original data is recorded on the main contact table could this be
the reason it can't find the search criteria? I assume it would need to
point it to the main form and the subreport to collate the criteria needed to
produce the report. I could quite easly place this on the main form, however
i believe the reason for it being on the subreport is that the dates actually
relate to the holiday entitlement period and thererfore are best suited to
the subreport to the actual main form.

Mike

Hi CW,

Thanks for your patience on this, hopefully I am getting there now.

I entered the Event as you described and there was no highlighted areas this
time, however when i come to run the command it says I didn't specify any
search critera with a FindRecord action. I am assuming this has something to
do with the control on the form that you and Keith have mentioned.

Just to clarify what i am doing and what i believe you are saying. Record
is the name of my primary key, which uses the autonumber function, and is
listed as one of the columns on the query(ie is part of the search results)
On the form, I have clicked on properties for the field that contains the
Record Autonumber and the control source is also Record is this right or does
the control come from somewhere else?

Hi Mike,

Between is different than equal to.

Try this:

DoCmd.OpenReport "Holiday Record Card", acViewPreview, , "Record = " &
Me.Record & " And [Holiday Start] >= #" & Me.txtText145 & "# And [Holiday
End] <= #" & Me.txtText147 & "#"

Note: If Record is the name of a column in your query, you need to match it
to the value of a control on your form. It SHOULD be something like, as
Keith suggested:

"Record = " & Me.ControlName & " And blah blah blah

CW

:

Hi Mike,

One thing you might need to do is use brackets around two word field names.
So something like:
Holiday Start

would become:
[Holiday Start]

See if that helps.

I've looked back at my syntax and I think it's right assuming the date
fields in your query represent fields that are actually date type, and not
string. I'll see if I can get a minute a little later to test things and
verify.

HTH,
CW

:

Hi keith,

As CW broke this down, i entered the information as per instructions. record
is the name of my column within the query and record is also on my form and
the txtID (this field is also the primary key) are you saying this isn't
right? As mentioned the first part of the code CW provided worked, but it
was the second half that posed the problem as i am needing to extract data
between two specific dates that are recorded on the form? I use a dropdown
list by surname and firstname so that i can move between records easily but
each record is assigned to a unique number ie the Record.

I'll probably kick myself when i've managed to sort this, as i think it's
just my misunderstanding.

Mike



:


it highlighted in red, i then typed this in all one line as you described,
but then it hightlighted the whole lot in red, am i missing something
simple?
The following is exactly how i entered the code:

DoCmd.OpenReport "Holiday Record Card", acViewPreview, , "Record = " &
Record &" And Holiday Start = #" & Me.txtText145 & "# And Holiday End = #"
&
Me.txtText147 & "#"


PMFJI. What is the second "Record" in

"Record = " & Record

, a text box on your form? If so then try:

"Record = " & Me.Record

If the data type is Text then you'll need quotes:

"Record = ' " & Me.Record & " ' "

Keith.
www.keithwilby.co.uk