Choose report by Combo Box Choice

[magmike]

I am trying to set up a button, that onClick will determine which
report to use, based on a combo box on the form.

THE FORM: ItemsSent - the data source of this form is the table
ItemsSent which stores the different pieces of collateral sent to each
contact. One of the fields in the table ItemsSent (and on the form) is
ItemID.

ItemID is from the table Items which stores the different items that
could be sent. Some of these items are PDF files, others are just hard
collateral. Yet, others are reports in the database that are basically
form letters. For these letters (or reports - which uses contact data
to personalize the letter) there is a field titled ItemReportName
which refers to the exact title of the report.

On the ItemsSent form, the user selects the item from a combo box (the
source of the combo box is ItemID, but shows the name associated with
that record) that they sent or want to send. If a letter is selected,
I want them to be able to click the Send Letter button, behind which
is the following code:

Private Sub SendReport_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[ItemsSentID]=" & Me![ItemsSentID]
stDocName = [ItemReportName]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_SendReport_Click:
Exit Sub

Err_SendReport_Click:
MsgBox Err.Description
Resume Exit_SendReport_Click
End Sub

However, because only the ItemID is stored in the table, I'm getting
an error stating that it can't find the report "13" (lol!). I can get
it to show the name that goes along with that ID, but I can't get it
to use it when deciding which report to use based on the code.

What am I doing wrong?

Thanks!

magmike

 

[Guest]

You need the combo box to be multi-column, with the report name in some column.
The columns are numbered from (0), so if you had the report name in the
second column use:
stDocName = ComboBoxName.Column(1)

-Dorian

 

[magmike]

You need the combo box to be multi-column, with the report name in some column.
The columns are numbered from (0), so if you had the report name in the
second column use:
stDocName = ComboBoxName.Column(1)

-Dorian

I am trying to set up a button, that onClick will determine which
report to use, based on a combo box on the form.

THE FORM: ItemsSent - the data source of this form is the table
ItemsSent which stores the different pieces of collateral sent to each
contact. One of the fields in the table ItemsSent (and on the form) is
ItemID.

ItemID is from the table Items which stores the different items that
could be sent. Some of these items are PDF files, others are just hard
collateral. Yet, others are reports in the database that are basically
form letters. For these letters (or reports - which uses contact data
to personalize the letter) there is a field titled ItemReportName
which refers to the exact title of the report.

On the ItemsSent form, the user selects the item from a combo box (the
source of the combo box is ItemID, but shows the name associated with
that record) that they sent or want to send. If a letter is selected,
I want them to be able to click the Send Letter button, behind which
is the following code:

Private Sub SendReport_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[ItemsSentID]=" & Me![ItemsSentID]
stDocName = [ItemReportName]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_SendReport_Click:
Exit Sub

Err_SendReport_Click:
MsgBox Err.Description
Resume Exit_SendReport_Click
End Sub

However, because only the ItemID is stored in the table, I'm getting
an error stating that it can't find the report "13" (lol!). I can get
it to show the name that goes along with that ID, but I can't get it
to use it when deciding which report to use based on the code.

What am I doing wrong?

magmike- Hide quoted text -

- Show quoted text -

Perfect! Thanks.

What could I do when the selected item is not a report and therefore
the particular column is blank, yet someone accidentally clicks on the
button only to get an error?

Thanks!

 

[magmike]

You need the combo box to be multi-column, with the report name in some column.
The columns are numbered from (0), so if you had the report name in the
second column use:
stDocName = ComboBoxName.Column(1)

-Dorian

I am trying to set up a button, that onClick will determine which
report to use, based on a combo box on the form.

THE FORM: ItemsSent - the data source of this form is the table
ItemsSent which stores the different pieces of collateral sent to each
contact. One of the fields in the table ItemsSent (and on the form) is
ItemID.

ItemID is from the table Items which stores the different items that
could be sent. Some of these items are PDF files, others are just hard
collateral. Yet, others are reports in the database that are basically
form letters. For these letters (or reports - which uses contact data
to personalize the letter) there is a field titled ItemReportName
which refers to the exact title of the report.

On the ItemsSent form, the user selects the item from a combo box (the
source of the combo box is ItemID, but shows the name associated with
that record) that they sent or want to send. If a letter is selected,
I want them to be able to click the Send Letter button, behind which
is the following code:

Private Sub SendReport_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[ItemsSentID]=" & Me![ItemsSentID]
stDocName = [ItemReportName]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_SendReport_Click:
Exit Sub

Err_SendReport_Click:
MsgBox Err.Description
Resume Exit_SendReport_Click
End Sub

However, because only the ItemID is stored in the table, I'm getting
an error stating that it can't find the report "13" (lol!). I can get
it to show the name that goes along with that ID, but I can't get it
to use it when deciding which report to use based on the code.

What am I doing wrong?

magmike- Hide quoted text -

- Show quoted text -

Or better yet, if ItemReportName Is Null, then open the linked file
(as the items, when applicable, are linked or embedded into the table
so the user can see what the collateral is)?

 

[Guest]

Well you should probably base the combo box on a query with a WHERE clause
saying 'WHERE [report name] is not NULL' so only valid report records appear
in the combo box.
If you want them to appear, you will have to check the column in the OnClick
event and then take appropriate action if the value is not a report.

-Dorian

You need the combo box to be multi-column, with the report name in some column.
The columns are numbered from (0), so if you had the report name in the
second column use:
stDocName = ComboBoxName.Column(1)

-Dorian

I am trying to set up a button, that onClick will determine which
report to use, based on a combo box on the form.

THE FORM: ItemsSent - the data source of this form is the table
ItemsSent which stores the different pieces of collateral sent to each
contact. One of the fields in the table ItemsSent (and on the form) is
ItemID.

ItemID is from the table Items which stores the different items that
could be sent. Some of these items are PDF files, others are just hard
collateral. Yet, others are reports in the database that are basically
form letters. For these letters (or reports - which uses contact data
to personalize the letter) there is a field titled ItemReportName
which refers to the exact title of the report.

On the ItemsSent form, the user selects the item from a combo box (the
source of the combo box is ItemID, but shows the name associated with
that record) that they sent or want to send. If a letter is selected,
I want them to be able to click the Send Letter button, behind which
is the following code:

Private Sub SendReport_Click()
Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[ItemsSentID]=" & Me![ItemsSentID]
stDocName = [ItemReportName]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_SendReport_Click:
Exit Sub

Err_SendReport_Click:
MsgBox Err.Description
Resume Exit_SendReport_Click
End Sub

However, because only the ItemID is stored in the table, I'm getting
an error stating that it can't find the report "13" (lol!). I can get
it to show the name that goes along with that ID, but I can't get it
to use it when deciding which report to use based on the code.

What am I doing wrong?

magmike- Hide quoted text -

- Show quoted text -

Perfect! Thanks.

What could I do when the selected item is not a report and therefore
the particular column is blank, yet someone accidentally clicks on the
button only to get an error?

Thanks!

 

[magmike]

Well you should probably base the combo box on a query with a WHERE clause
saying 'WHERE [report name] is not NULL' so only valid report records appear
in the combo box.
If you want them to appear, you will have to check the column in the OnClick
event and then take appropriate action if the value is not a report.

-Dorian

You need the combo box to be multi-column, with the report name in some column.
The columns are numbered from (0), so if you had the report name in the
second column use:
stDocName = ComboBoxName.Column(1)
-Dorian
:
I am trying to set up a button, that onClick will determine which
report to use, based on a combo box on the form.
THE FORM: ItemsSent - the data source of this form is the table
ItemsSent which stores the different pieces of collateral sent to each
contact. One of the fields in the table ItemsSent (and on the form) is
ItemID.
ItemID is from the table Items which stores the different items that
could be sent. Some of these items are PDF files, others are just hard
collateral. Yet, others are reports in the database that are basically
form letters. For these letters (or reports - which uses contact data
to personalize the letter) there is a field titled ItemReportName
which refers to the exact title of the report.
On the ItemsSent form, the user selects the item from a combo box (the
source of the combo box is ItemID, but shows the name associated with
that record) that they sent or want to send. If a letter is selected,
I want them to be able to click the Send Letter button, behind which
is the following code:
Private Sub SendReport_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "[ItemsSentID]=" & Me![ItemsSentID]
stDocName = [ItemReportName]
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
Exit_SendReport_Click:
Exit Sub
Err_SendReport_Click:
MsgBox Err.Description
Resume Exit_SendReport_Click
End Sub
However, because only the ItemID is stored in the table, I'm getting
an error stating that it can't find the report "13" (lol!). I can get
it to show the name that goes along with that ID, but I can't get it
to use it when deciding which report to use based on the code.
What am I doing wrong?
Thanks!
magmike- Hide quoted text -
- Show quoted text -

Perfect! Thanks.

What could I do when the selected item is not a report and therefore
the particular column is blank, yet someone accidentally clicks on the
button only to get an error?

Thanks!- Hide quoted text -

- Show quoted text -

Sorry, Dorian, but I am very new at this. How would I check the column
in the onClick, and then take appropriate action?

FYI - if the collateral is not a report, then in most cases, there is
an OLE link to a file on the server recorded in a field (ItemFile).
So, if report is null then I would want the user to view the OLE
object. There may be a couple of items not linked or reports, however.
Understanding that, how could I check the column and/or ItemFile link
and then take action?

thx

magmike