Running a report for a specific record from a form

[Momof2]

Hi,
Let me try to make this make sense. I have a query (Transactions Query)
which keeps records of all contractors and receipts that they are missing. I
have a report (Missing Receipt report) based on this query which is grouped
by the "contractor" field of the query. What I would like to do (and am
having no success doing so) is create a form with a combo box based from the
query, however I would only like for the combo box to pull each contractor
name only once and not for every time it is listed in the query. Then once
the contractor name is selected to run the report for only that contractor.
Unless there is a way when opening the report I can choose the contractor
name at the time and only pull there information. I just need help of getting
the report to only pull information for a specific contractor. I will take
any ideas to make this work.

 

[Stockwell43]

Hello,

Try this and see if it helps. This is a very helpful site and gives
downloads for visuals.

http://www.databasedev.co.uk/query_filter.html

Hope it helps!!

 

[Klatuu]

As to showing the contractor only once in your combo, you can use the
DISTINCT qualifer in your combo's row source query. It would be something
like
SELECT DISTINCT ContractorID, ContractorName FROM tblContractor.

If you need to do it in the query builder, You can open the query in design
view and either click the Total icon and make the fields all group by or you
can open the properties dialog and select Distinct Values in the properties.

As to filtering the report by contractor, as previously stated, you use the
OpenReport Where argument. It is just like an SQL Where clause without the
word Where. You refer to the name of a field in the report's record source
to do the filtering. For example, let's day you have ContractorID as a long
integer primary key for your contractor table and it is in the record source
for the report and you want to filter in based on the value in the Combo (as
I described above). It would be something like this:

Dim strWhere As String

strWhere = "[ContractorID] = & Me.cboContractor

Docmd.OpenReport "MyReport", acViewPreview, , strWhere

 

[Momof2]

Ok, I got the Distinct filter to work on the combo box. I used the following
in VBA

Dim strWhere As String

strWhere = "[Contractor] = & Me.cboContractor

Docmd.OpenReport "MyReport", acViewPreview, , strWhere

and I'm getting an error reading:
syntax error (missing operator) in query expression '([Contractor] = &
Me.cboContractor

any ideas on this?

As to showing the contractor only once in your combo, you can use the
DISTINCT qualifer in your combo's row source query. It would be something
like
SELECT DISTINCT ContractorID, ContractorName FROM tblContractor.

If you need to do it in the query builder, You can open the query in design
view and either click the Total icon and make the fields all group by or you
can open the properties dialog and select Distinct Values in the properties.

As to filtering the report by contractor, as previously stated, you use the
OpenReport Where argument. It is just like an SQL Where clause without the
word Where. You refer to the name of a field in the report's record source
to do the filtering. For example, let's day you have ContractorID as a long
integer primary key for your contractor table and it is in the record source
for the report and you want to filter in based on the value in the Combo (as
I described above). It would be something like this:

Dim strWhere As String

strWhere = "[ContractorID] = & Me.cboContractor

Docmd.OpenReport "MyReport", acViewPreview, , strWhere

Hi,
Let me try to make this make sense. I have a query (Transactions Query)
which keeps records of all contractors and receipts that they are missing.
I
have a report (Missing Receipt report) based on this query which is
grouped
by the "contractor" field of the query. What I would like to do (and am
having no success doing so) is create a form with a combo box based from
the
query, however I would only like for the combo box to pull each contractor
name only once and not for every time it is listed in the query. Then once
the contractor name is selected to run the report for only that
contractor.
Unless there is a way when opening the report I can choose the contractor
name at the time and only pull there information. I just need help of
getting
the report to only pull information for a specific contractor. I will take
any ideas to make this work.

 

[Klatuu]

Yes, you copied my incorrect syntax <g> Sorry about that. It should be:
strWhere = "[Contractor] = " & Me.cboContractor

Ok, I got the Distinct filter to work on the combo box. I used the
following
in VBA

Dim strWhere As String

strWhere = "[Contractor] = & Me.cboContractor

Docmd.OpenReport "MyReport", acViewPreview, , strWhere

and I'm getting an error reading:
syntax error (missing operator) in query expression '([Contractor] = &
Me.cboContractor

any ideas on this?

As to showing the contractor only once in your combo, you can use the
DISTINCT qualifer in your combo's row source query. It would be
something
like
SELECT DISTINCT ContractorID, ContractorName FROM tblContractor.

If you need to do it in the query builder, You can open the query in
design
view and either click the Total icon and make the fields all group by or
you
can open the properties dialog and select Distinct Values in the
properties.

As to filtering the report by contractor, as previously stated, you use
the
OpenReport Where argument. It is just like an SQL Where clause without
the
word Where. You refer to the name of a field in the report's record
source
to do the filtering. For example, let's day you have ContractorID as a
long
integer primary key for your contractor table and it is in the record
source
for the report and you want to filter in based on the value in the Combo
(as
I described above). It would be something like this:

Dim strWhere As String

strWhere = "[ContractorID] = & Me.cboContractor

Docmd.OpenReport "MyReport", acViewPreview, , strWhere

Hi,
Let me try to make this make sense. I have a query (Transactions
Query)
which keeps records of all contractors and receipts that they are
missing.
I
have a report (Missing Receipt report) based on this query which is
grouped
by the "contractor" field of the query. What I would like to do (and am
having no success doing so) is create a form with a combo box based
from
the
query, however I would only like for the combo box to pull each
contractor
name only once and not for every time it is listed in the query. Then
once
the contractor name is selected to run the report for only that
contractor.
Unless there is a way when opening the report I can choose the
contractor
name at the time and only pull there information. I just need help of
getting
the report to only pull information for a specific contractor. I will
take
any ideas to make this work.

 

[Momof2]

I'm getting closer, but no im getting another error
compile error:
method or data member not found

when the error pops up it highlights the .cboContractor in blue and once I
click ok the "Private Sub Command4_Click()" highlights in yellow. Any ideas
of how to fix this?

I am completely new to VBA.

Yes, you copied my incorrect syntax <g> Sorry about that. It should be:
strWhere = "[Contractor] = " & Me.cboContractor

Ok, I got the Distinct filter to work on the combo box. I used the
following
in VBA

Dim strWhere As String

strWhere = "[Contractor] = & Me.cboContractor

Docmd.OpenReport "MyReport", acViewPreview, , strWhere

and I'm getting an error reading:
syntax error (missing operator) in query expression '([Contractor] = &
Me.cboContractor

any ideas on this?

As to showing the contractor only once in your combo, you can use the
DISTINCT qualifer in your combo's row source query. It would be
something
like
SELECT DISTINCT ContractorID, ContractorName FROM tblContractor.

If you need to do it in the query builder, You can open the query in
design
view and either click the Total icon and make the fields all group by or
you
can open the properties dialog and select Distinct Values in the
properties.

As to filtering the report by contractor, as previously stated, you use
the
OpenReport Where argument. It is just like an SQL Where clause without
the
word Where. You refer to the name of a field in the report's record
source
to do the filtering. For example, let's day you have ContractorID as a
long
integer primary key for your contractor table and it is in the record
source
for the report and you want to filter in based on the value in the Combo
(as
I described above). It would be something like this:

Dim strWhere As String

strWhere = "[ContractorID] = & Me.cboContractor

Docmd.OpenReport "MyReport", acViewPreview, , strWhere

Hi,
Let me try to make this make sense. I have a query (Transactions
Query)
which keeps records of all contractors and receipts that they are
missing.
I
have a report (Missing Receipt report) based on this query which is
grouped
by the "contractor" field of the query. What I would like to do (and am
having no success doing so) is create a form with a combo box based
from
the
query, however I would only like for the combo box to pull each
contractor
name only once and not for every time it is listed in the query. Then
once
the contractor name is selected to run the report for only that
contractor.
Unless there is a way when opening the report I can choose the
contractor
name at the time and only pull there information. I just need help of
getting
the report to only pull information for a specific contractor. I will
take
any ideas to make this work.

 

[Klatuu]

My examples have made up names. Use your actual names.

I'm getting closer, but no im getting another error
compile error:
method or data member not found

when the error pops up it highlights the .cboContractor in blue and once I
click ok the "Private Sub Command4_Click()" highlights in yellow. Any
ideas
of how to fix this?

I am completely new to VBA.

Yes, you copied my incorrect syntax <g> Sorry about that. It should be:
strWhere = "[Contractor] = " & Me.cboContractor

Ok, I got the Distinct filter to work on the combo box. I used the
following
in VBA

Dim strWhere As String

strWhere = "[Contractor] = & Me.cboContractor

Docmd.OpenReport "MyReport", acViewPreview, , strWhere

and I'm getting an error reading:
syntax error (missing operator) in query expression '([Contractor] = &
Me.cboContractor

any ideas on this?

:

As to showing the contractor only once in your combo, you can use the
DISTINCT qualifer in your combo's row source query. It would be
something
like
SELECT DISTINCT ContractorID, ContractorName FROM tblContractor.

If you need to do it in the query builder, You can open the query in
design
view and either click the Total icon and make the fields all group by
or
you
can open the properties dialog and select Distinct Values in the
properties.

As to filtering the report by contractor, as previously stated, you
use
the
OpenReport Where argument. It is just like an SQL Where clause
without
the
word Where. You refer to the name of a field in the report's record
source
to do the filtering. For example, let's day you have ContractorID as
a
long
integer primary key for your contractor table and it is in the record
source
for the report and you want to filter in based on the value in the
Combo
(as
I described above). It would be something like this:

Dim strWhere As String

strWhere = "[ContractorID] = & Me.cboContractor

Docmd.OpenReport "MyReport", acViewPreview, , strWhere

Hi,
Let me try to make this make sense. I have a query (Transactions
Query)
which keeps records of all contractors and receipts that they are
missing.
I
have a report (Missing Receipt report) based on this query which is
grouped
by the "contractor" field of the query. What I would like to do (and
am
having no success doing so) is create a form with a combo box based
from
the
query, however I would only like for the combo box to pull each
contractor
name only once and not for every time it is listed in the query.
Then
once
the contractor name is selected to run the report for only that
contractor.
Unless there is a way when opening the report I can choose the
contractor
name at the time and only pull there information. I just need help
of
getting
the report to only pull information for a specific contractor. I
will
take
any ideas to make this work.