Using a form to open a report

[vladi16]

I have seen several posts which come close to explaining what I'd like to do
but nothing with the whole package... So here goes:

I have a form that I enter employee observations on that includes the date
of the observation and the employee's name. I have a report (rptByEmployee)
that I would like to have opened by a form that would allow me to filter the
observations by the employee's name (combo box) and between two dates.

So basically, I need three fields on the form: Employee (combo box),
Starting Date, and Ending Date.

Does this make sense and is this enough info?

Thanks,

 

[Rob Parker]

Makes sense, and is not difficult.

Assuming that your controls for Starting Date and Ending Date are named
txtStartDate and txtEndDate (these should be unbound controls), that your
combobox is named cboxEmployee (also unbound), that the Employee field is of
text datatype, and that the observation date field is a date/time field
named ObservationDate, the following should be sufficient:

DoCmd.OpenReport "rptByEmployee", acViewPreview, ,"Employee = '" &
cboxEmployee & "' AND ObservationDate Between #" & txtStartDate & "# and #"
& txtEndDate & "#"

Note the ' delimiters for the text field, and the # delimiters for the two
date/time fields. If the Employee field could contain a ' character (eg. a
person named O'Hara), then replace the ' delimiters with a pair of
double-quote characters. I'd also include code to check that each control
contains a valid entry before opening the report, and/or include a default
value (via an nz function) if nothing is entered; eg. for the txtStartDate,
use something like:
nz(txtStartDate,#01/01/1900#)
and for txtEndDate use something like:
nz(txtEndDate,Date())
to default to the current date.

Change acViewPreview to acViewNormal if you want to print the report
immediately, without previewing it. Change the names of controls/fields to
match your names.

If you run this code from the data entry form itself, rather than a separate
form whose only purpose is to provide the parameters for the report, then
you should probably include the following line before opening the report:
If Me.Dirty Then Me.Dirty = False 'save any pending edits


HTH,

Rob

 

[vladi16]

Rob,

When it comes to the code stufI am as green as it gets... Please elaborate
on exactly where that code goes....

Thanks.

 

[Rob Parker]

I'm assuming that you've got a command button on your form to open the
report, using the entries in the form controls. The code I gave you would
go in the Click event of the button.

More detailed instructions, if you need them:

1. Drag a button from the toolbox onto your form. I'd normally place this
sort of button in the form footer, but if it's an unbound form used only for
entering the report's parameters, it doesn't really matter where you put it.
Give the button an appropriate caption (which users will see) and an
appropriate name (rather than the default Commandx, where x is a number -
I'd call it something like cmdOpenReport); you set the Name of the button in
the Other tab of the property sheet.

2. With the button selected, move to the Event tab of the property sheet,
and double-click in the field to the right of the On Click event. This will
generate the entry "[Event Procedure]" in that field.

3. Click on the ellipsis (three dots) to the right of the field, to open
the VBA editor. You will find that you have a subroutine "stub", with the
following lines:

Private Sub cmdOpenReport_Click()

End Sub

4. Place the code I suggested (modified to suit your field/control names)
between these lines.

HTH,

Rob

 

[vladi16]

Not sure what is wrong but the code you wrote, that I cut and pasted to the
On Click event of the command button, is red!!

Let me start over and tell you what I have:

I have a form, fmTOs, that my users will enter their tests and observations
on and that information goes to a table… tbltretests. This table has several
fields, two of which are: testdate (data type = date/time) and employeetested
(data type = text).

I have a report: rptByEmployee, which I want to open by way of a form.

On that form, I called it fmEmployeeFilter… I have a comb box, named
cboxEmployee (unbound), whose Row Source is a query I have made that puts the
employee’s last name first & first name last. That form also has two text
boxes, txtStartDate and txtEndDate (both unbound) formatted to short date.
The form also has a command button (unbound) named cmdOpenReport .

I took your code and edited to use my field/control names (see below) and am
having no luck… Any thoughts?

DoCmd.OpenReport "rptByEmployee", acViewPreview, ,"Employee = '" &
cboxEmployee & "' AND ObservationDate Between #" & txtStartDate & "# and #"
& txtEndDate & "#"

I am sorry this is so difficuly for me to get.

I'm assuming that you've got a command button on your form to open the
report, using the entries in the form controls. The code I gave you would
go in the Click event of the button.

More detailed instructions, if you need them:

1. Drag a button from the toolbox onto your form. I'd normally place this
sort of button in the form footer, but if it's an unbound form used only for
entering the report's parameters, it doesn't really matter where you put it.
Give the button an appropriate caption (which users will see) and an
appropriate name (rather than the default Commandx, where x is a number -
I'd call it something like cmdOpenReport); you set the Name of the button in
the Other tab of the property sheet.

2. With the button selected, move to the Event tab of the property sheet,
and double-click in the field to the right of the On Click event. This will
generate the entry "[Event Procedure]" in that field.

3. Click on the ellipsis (three dots) to the right of the field, to open
the VBA editor. You will find that you have a subroutine "stub", with the
following lines:

Private Sub cmdOpenReport_Click()

End Sub

4. Place the code I suggested (modified to suit your field/control names)
between these lines.

HTH,

Rob

Rob,

When it comes to the code stufI am as green as it gets... Please
elaborate on exactly where that code goes....

Thanks.

 

[Rob Parker]

The VBA editor changes code that it can't understand to red. I suspect that
you cut/pasted from the newsgroup posting, where the code line I gave you
has broken into multiple lines. If that's the case, then you can either
remove the line breaks in the VBA editor, or insert the line continuation
character pair - space and underscore - at the end of each broken line in
the VBA editor.

Also, from your description of your fields in tbltretests (which I assume is
what rptByEmployee is based on), I suspect that you'll need to change
ObservationDate to testdate in the Where parameter of the .OpenReport
statement.

Don't feel too bad about it being difficult to get - if you don't have a
programming background, VBA can be rather daunting at first. Just keep at
it, and it'll get to become second nature ;-)

Rob

Not sure what is wrong but the code you wrote, that I cut and pasted
to the On Click event of the command button, is red!!

Let me start over and tell you what I have:

I have a form, fmTOs, that my users will enter their tests and
observations on and that information goes to a table. tbltretests.
This table has several fields, two of which are: testdate (data type
= date/time) and employeetested (data type = text).

I have a report: rptByEmployee, which I want to open by way of a form.

On that form, I called it fmEmployeeFilter. I have a comb box, named
cboxEmployee (unbound), whose Row Source is a query I have made that
puts the employee's last name first & first name last. That form
also has two text boxes, txtStartDate and txtEndDate (both unbound)
formatted to short date. The form also has a command button (unbound)
named cmdOpenReport .

I took your code and edited to use my field/control names (see below)
and am having no luck. Any thoughts?

DoCmd.OpenReport "rptByEmployee", acViewPreview, ,"Employee = '" &
cboxEmployee & "' AND ObservationDate Between #" & txtStartDate & "#
and #" & txtEndDate & "#"

I am sorry this is so difficuly for me to get.

I'm assuming that you've got a command button on your form to open
the report, using the entries in the form controls. The code I gave
you would go in the Click event of the button.

More detailed instructions, if you need them:

1. Drag a button from the toolbox onto your form. I'd normally
place this sort of button in the form footer, but if it's an unbound
form used only for entering the report's parameters, it doesn't
really matter where you put it. Give the button an appropriate
caption (which users will see) and an appropriate name (rather than
the default Commandx, where x is a number - I'd call it something
like cmdOpenReport); you set the Name of the button in the Other tab
of the property sheet.

2. With the button selected, move to the Event tab of the property
sheet, and double-click in the field to the right of the On Click
event. This will generate the entry "[Event Procedure]" in that
field.

3. Click on the ellipsis (three dots) to the right of the field, to
open the VBA editor. You will find that you have a subroutine
"stub", with the following lines:

Private Sub cmdOpenReport_Click()

End Sub

4. Place the code I suggested (modified to suit your field/control
names) between these lines.

HTH,

Rob

Rob,

When it comes to the code stufI am as green as it gets... Please
elaborate on exactly where that code goes....

Thanks.

:

Makes sense, and is not difficult.

Assuming that your controls for Starting Date and Ending Date are
named txtStartDate and txtEndDate (these should be unbound
controls), that your combobox is named cboxEmployee (also
unbound), that the Employee field is of text datatype, and that the
observation date field is a date/time field named ObservationDate,
the following should be sufficient:

DoCmd.OpenReport "rptByEmployee", acViewPreview, ,"Employee = '" &
cboxEmployee & "' AND ObservationDate Between #" & txtStartDate &
"# and #" & txtEndDate & "#"

Note the ' delimiters for the text field, and the # delimiters for
the two date/time fields. If the Employee field could contain a '
character (eg. a person named O'Hara), then replace the '
delimiters with a pair of double-quote characters. I'd also
include code to check that each control contains a valid entry
before opening the report, and/or include a default value (via an
nz function) if nothing is entered; eg. for the txtStartDate, use
something like: nz(txtStartDate,#01/01/1900#)
and for txtEndDate use something like:
nz(txtEndDate,Date())
to default to the current date.

Change acViewPreview to acViewNormal if you want to print the
report immediately, without previewing it. Change the names of
controls/fields to match your names.

If you run this code from the data entry form itself, rather than a
separate form whose only purpose is to provide the parameters for
the report, then you should probably include the following line
before opening the report: If Me.Dirty Then Me.Dirty = False
'save any pending edits


HTH,

Rob

vladi16 wrote:
I have seen several posts which come close to explaining what I'd
like to do but nothing with the whole package... So here goes:

I have a form that I enter employee observations on that includes
the date of the observation and the employee's name. I have a
report (rptByEmployee) that I would like to have opened by a form
that would allow me to filter the observations by the employee's
name (combo box) and between two dates.

So basically, I need three fields on the form: Employee (combo
box), Starting Date, and Ending Date.

Does this make sense and is this enough info?

Thanks,

 

[vladi16]

That is exactly what I did.. I cut/pasted it off this site. Now it looks
like it is in paragraph form, LOL... Can you explain to me how to remove the
line breaks?

Thanks...

The VBA editor changes code that it can't understand to red. I suspect that
you cut/pasted from the newsgroup posting, where the code line I gave you
has broken into multiple lines. If that's the case, then you can either
remove the line breaks in the VBA editor, or insert the line continuation
character pair - space and underscore - at the end of each broken line in
the VBA editor.

Also, from your description of your fields in tbltretests (which I assume is
what rptByEmployee is based on), I suspect that you'll need to change
ObservationDate to testdate in the Where parameter of the .OpenReport
statement.

Don't feel too bad about it being difficult to get - if you don't have a
programming background, VBA can be rather daunting at first. Just keep at
it, and it'll get to become second nature ;-)

Rob

Not sure what is wrong but the code you wrote, that I cut and pasted
to the On Click event of the command button, is red!!

Let me start over and tell you what I have:

I have a form, fmTOs, that my users will enter their tests and
observations on and that information goes to a table. tbltretests.
This table has several fields, two of which are: testdate (data type
= date/time) and employeetested (data type = text).

I have a report: rptByEmployee, which I want to open by way of a form.

On that form, I called it fmEmployeeFilter. I have a comb box, named
cboxEmployee (unbound), whose Row Source is a query I have made that
puts the employee's last name first & first name last. That form
also has two text boxes, txtStartDate and txtEndDate (both unbound)
formatted to short date. The form also has a command button (unbound)
named cmdOpenReport .

I took your code and edited to use my field/control names (see below)
and am having no luck. Any thoughts?

DoCmd.OpenReport "rptByEmployee", acViewPreview, ,"Employee = '" &
cboxEmployee & "' AND ObservationDate Between #" & txtStartDate & "#
and #" & txtEndDate & "#"

I am sorry this is so difficuly for me to get.

I'm assuming that you've got a command button on your form to open
the report, using the entries in the form controls. The code I gave
you would go in the Click event of the button.

More detailed instructions, if you need them:

1. Drag a button from the toolbox onto your form. I'd normally
place this sort of button in the form footer, but if it's an unbound
form used only for entering the report's parameters, it doesn't
really matter where you put it. Give the button an appropriate
caption (which users will see) and an appropriate name (rather than
the default Commandx, where x is a number - I'd call it something
like cmdOpenReport); you set the Name of the button in the Other tab
of the property sheet.

2. With the button selected, move to the Event tab of the property
sheet, and double-click in the field to the right of the On Click
event. This will generate the entry "[Event Procedure]" in that
field.

3. Click on the ellipsis (three dots) to the right of the field, to
open the VBA editor. You will find that you have a subroutine
"stub", with the following lines:

Private Sub cmdOpenReport_Click()

End Sub

4. Place the code I suggested (modified to suit your field/control
names) between these lines.

HTH,

Rob


vladi16 wrote:
Rob,

When it comes to the code stufI am as green as it gets... Please
elaborate on exactly where that code goes....

Thanks.

:

Makes sense, and is not difficult.

Assuming that your controls for Starting Date and Ending Date are
named txtStartDate and txtEndDate (these should be unbound
controls), that your combobox is named cboxEmployee (also
unbound), that the Employee field is of text datatype, and that the
observation date field is a date/time field named ObservationDate,
the following should be sufficient:

DoCmd.OpenReport "rptByEmployee", acViewPreview, ,"Employee = '" &
cboxEmployee & "' AND ObservationDate Between #" & txtStartDate &
"# and #" & txtEndDate & "#"

Note the ' delimiters for the text field, and the # delimiters for
the two date/time fields. If the Employee field could contain a '
character (eg. a person named O'Hara), then replace the '
delimiters with a pair of double-quote characters. I'd also
include code to check that each control contains a valid entry
before opening the report, and/or include a default value (via an
nz function) if nothing is entered; eg. for the txtStartDate, use
something like: nz(txtStartDate,#01/01/1900#)
and for txtEndDate use something like:
nz(txtEndDate,Date())
to default to the current date.

Change acViewPreview to acViewNormal if you want to print the
report immediately, without previewing it. Change the names of
controls/fields to match your names.

If you run this code from the data entry form itself, rather than a
separate form whose only purpose is to provide the parameters for
the report, then you should probably include the following line
before opening the report: If Me.Dirty Then Me.Dirty = False
'save any pending edits


HTH,

Rob

vladi16 wrote:
I have seen several posts which come close to explaining what I'd
like to do but nothing with the whole package... So here goes:

I have a form that I enter employee observations on that includes
the date of the observation and the employee's name. I have a
report (rptByEmployee) that I would like to have opened by a form
that would allow me to filter the observations by the employee's
name (combo box) and between two dates.

So basically, I need three fields on the form: Employee (combo
box), Starting Date, and Ending Date.

Does this make sense and is this enough info?

Thanks,

 

[Rob Parker]

In the VBA editor, either:
Go to the end of a line and hit the Delete key
or
Go to the start of the following line and hit the Backspace key

If doing that runs two words together, add a space character.

Rob

That is exactly what I did.. I cut/pasted it off this site. Now it
looks like it is in paragraph form, LOL... Can you explain to me how
to remove the line breaks?

Thanks...

The VBA editor changes code that it can't understand to red. I
suspect that you cut/pasted from the newsgroup posting, where the
code line I gave you has broken into multiple lines. If that's the
case, then you can either remove the line breaks in the VBA editor,
or insert the line continuation character pair - space and
underscore - at the end of each broken line in the VBA editor.

Also, from your description of your fields in tbltretests (which I
assume is what rptByEmployee is based on), I suspect that you'll
need to change ObservationDate to testdate in the Where parameter of
the .OpenReport statement.

Don't feel too bad about it being difficult to get - if you don't
have a programming background, VBA can be rather daunting at first.
Just keep at it, and it'll get to become second nature ;-)

Rob

Not sure what is wrong but the code you wrote, that I cut and pasted
to the On Click event of the command button, is red!!

Let me start over and tell you what I have:

I have a form, fmTOs, that my users will enter their tests and
observations on and that information goes to a table. tbltretests.
This table has several fields, two of which are: testdate (data type
= date/time) and employeetested (data type = text).

I have a report: rptByEmployee, which I want to open by way of a
form.

On that form, I called it fmEmployeeFilter. I have a comb box, named
cboxEmployee (unbound), whose Row Source is a query I have made that
puts the employee's last name first & first name last. That form
also has two text boxes, txtStartDate and txtEndDate (both unbound)
formatted to short date. The form also has a command button
(unbound) named cmdOpenReport .

I took your code and edited to use my field/control names (see
below) and am having no luck. Any thoughts?

DoCmd.OpenReport "rptByEmployee", acViewPreview, ,"Employee = '" &
cboxEmployee & "' AND ObservationDate Between #" & txtStartDate &
"# and #" & txtEndDate & "#"

I am sorry this is so difficuly for me to get.


:

I'm assuming that you've got a command button on your form to open
the report, using the entries in the form controls. The code I
gave you would go in the Click event of the button.

More detailed instructions, if you need them:

1. Drag a button from the toolbox onto your form. I'd normally
place this sort of button in the form footer, but if it's an
unbound form used only for entering the report's parameters, it
doesn't really matter where you put it. Give the button an
appropriate caption (which users will see) and an appropriate name
(rather than the default Commandx, where x is a number - I'd call
it something like cmdOpenReport); you set the Name of the button
in the Other tab of the property sheet.

2. With the button selected, move to the Event tab of the property
sheet, and double-click in the field to the right of the On Click
event. This will generate the entry "[Event Procedure]" in that
field.

3. Click on the ellipsis (three dots) to the right of the field,
to open the VBA editor. You will find that you have a subroutine
"stub", with the following lines:

Private Sub cmdOpenReport_Click()

End Sub

4. Place the code I suggested (modified to suit your field/control
names) between these lines.

HTH,

Rob


vladi16 wrote:
Rob,

When it comes to the code stufI am as green as it gets... Please
elaborate on exactly where that code goes....

Thanks.

:

Makes sense, and is not difficult.

Assuming that your controls for Starting Date and Ending Date are
named txtStartDate and txtEndDate (these should be unbound
controls), that your combobox is named cboxEmployee (also
unbound), that the Employee field is of text datatype, and that
the observation date field is a date/time field named
ObservationDate, the following should be sufficient:

DoCmd.OpenReport "rptByEmployee", acViewPreview, ,"Employee = '"
& cboxEmployee & "' AND ObservationDate Between #" &
txtStartDate & "# and #" & txtEndDate & "#"

Note the ' delimiters for the text field, and the # delimiters
for the two date/time fields. If the Employee field could
contain a ' character (eg. a person named O'Hara), then replace
the ' delimiters with a pair of double-quote characters. I'd
also include code to check that each control contains a valid
entry before opening the report, and/or include a default value
(via an nz function) if nothing is entered; eg. for the
txtStartDate, use something like:
nz(txtStartDate,#01/01/1900#)
and for txtEndDate use something like:
nz(txtEndDate,Date())
to default to the current date.

Change acViewPreview to acViewNormal if you want to print the
report immediately, without previewing it. Change the names of
controls/fields to match your names.

If you run this code from the data entry form itself, rather
than a separate form whose only purpose is to provide the
parameters for the report, then you should probably include the
following line before opening the report: If Me.Dirty Then
Me.Dirty = False 'save any pending edits


HTH,

Rob

vladi16 wrote:
I have seen several posts which come close to explaining what
I'd like to do but nothing with the whole package... So here
goes:

I have a form that I enter employee observations on that
includes the date of the observation and the employee's name.
I have a report (rptByEmployee) that I would like to have
opened by a form that would allow me to filter the observations
by the employee's name (combo box) and between two dates.

So basically, I need three fields on the form: Employee (combo
box), Starting Date, and Ending Date.

Does this make sense and is this enough info?

Thanks,