Combo Box that runs a report

  • Thread starter Thread starter Jim/Chris
  • Start date Start date
J

Jim/Chris

Create the report. The code behind each button would read
Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ReportName"

stLinkCriteria = "[FormID]=" & Me![Customer_Name combo
box]
DoCmd.OpenReport stDocName, acViewPreview, ,
stLinkCriteria

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub

I actually got this from this site.
Good Luck

Jim
 
Jim/Chris,

Thanks for you reply. I am a bit of a novice VBA user and
have not been able to get this to run. My Form name that
contains both the combo box to select customers and the
button to run my queries is called SelectCustomerAnna. My
combo box for select customers is called ComboAnna. How
would I write the VBA code so that only the customer I
selected appears on the report. In my report, the actual
field name of the customer from my query is called
CustomerRef_FullName and is in the CustomerRef_FullName
header.

Thanks,

Haji



-----Original Message-----
Create the report. The code behind each button would read
Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ReportName"

stLinkCriteria = "[FormID]=" & Me![Customer_Name combo
box]
DoCmd.OpenReport stDocName, acViewPreview, ,
stLinkCriteria

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub

I actually got this from this site.
Good Luck

Jim

-----Original Message-----
Hello,

I am building an application for a sales person. I built a
query off of a customer table that lists the Customer_Name
which I used to create a combo box. Underneath the combo
box I want to create three command buttons. One runs all
transaction for the past four years, the other runs 2003
transactions and the third runs transactions over the past
60 days. I want the queries to then generate a report.

What I want to do is for the user to select a customer
from the drop down box and then click one of the command
buttons (i.e. transactions last 60 days). This would then
run a query based on the transactions table (i.e. the last
60 days) for this customer and return it in a report
form.

Does anyone know how to do this?

Thanks,

Haji
.
Click to expand...
.
Click to expand...
 
You need to substitute your report name. I am assuming
that ComboAnna is the unique record identifyer and is the
same as CustomerRef_FullName in the report
Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

' Enter report name below
stDocName = "ReportName"

stLinkCriteria = "[CustomerRef_FullName]=" & Me!
[ComboAnna]
DoCmd.OpenReport stDocName, acViewPreview, ,
stLinkCriteria

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub

Good Luck

Jim
-----Original Message-----
Jim/Chris,

Thanks for you reply. I am a bit of a novice VBA user and
have not been able to get this to run. My Form name that
contains both the combo box to select customers and the
button to run my queries is called SelectCustomerAnna. My
combo box for select customers is called ComboAnna. How
would I write the VBA code so that only the customer I
selected appears on the report. In my report, the actual
field name of the customer from my query is called
CustomerRef_FullName and is in the CustomerRef_FullName
header.

Thanks,

Haji



-----Original Message-----
Create the report. The code behind each button would read
Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ReportName"

stLinkCriteria = "[FormID]=" & Me![Customer_Name combo
box]
DoCmd.OpenReport stDocName, acViewPreview, ,
stLinkCriteria

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub

I actually got this from this site.
Good Luck

Jim

-----Original Message-----
Hello,

I am building an application for a sales person. I
Click to expand...
built
a
query off of a customer table that lists the Customer_Name
which I used to create a combo box. Underneath the combo
box I want to create three command buttons. One runs all
transaction for the past four years, the other runs 2003
transactions and the third runs transactions over the past
60 days. I want the queries to then generate a report.

What I want to do is for the user to select a customer
from the drop down box and then click one of the command
buttons (i.e. transactions last 60 days). This would then
run a query based on the transactions table (i.e. the last
60 days) for this customer and return it in a report
form.

Does anyone know how to do this?

Thanks,

Haji
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Jim

Sorry to keep bugging you about this. I ran the code you
gave me but got a syntax error saying there was a comma in
my expression. I took one of the comma out of the
expression after acViewPreview. It now reads like this:

DoCmd.OpenReport stDocName, acViewPreview,
stLinkLinkCriteria

The code runs but I get every customer in my query rather
than just the customer I selected. In my report, the
field name of the customer from my query is called
CustomerRef_FullName and is in the CustomerRef_FullName
header of the report. I did a grouping by this field when
I set up the report. It looks like the VBA code is not
searching for the particular field name that I selected
but instead running the entire query.

Thanks for your help.

Haji

-----Original Message-----
You need to substitute your report name. I am assuming
that ComboAnna is the unique record identifyer and is the
same as CustomerRef_FullName in the report
Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

' Enter report name below
stDocName = "ReportName"

stLinkCriteria = "[CustomerRef_FullName]=" & Me!
[ComboAnna]
DoCmd.OpenReport stDocName, acViewPreview, ,
stLinkCriteria

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub

Good Luck

Jim
-----Original Message-----
Jim/Chris,

Thanks for you reply. I am a bit of a novice VBA user and
have not been able to get this to run. My Form name that
contains both the combo box to select customers and the
button to run my queries is called SelectCustomerAnna. My
combo box for select customers is called ComboAnna. How
would I write the VBA code so that only the customer I
selected appears on the report. In my report, the actual
field name of the customer from my query is called
CustomerRef_FullName and is in the CustomerRef_FullName
header.

Thanks,

Haji



-----Original Message-----
Create the report. The code behind each button would read
Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ReportName"

stLinkCriteria = "[FormID]=" & Me![Customer_Name combo
box]
DoCmd.OpenReport stDocName, acViewPreview, ,
stLinkCriteria

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub

I actually got this from this site.
Good Luck

Jim


-----Original Message-----
Hello,

I am building an application for a sales person. I built
a
query off of a customer table that lists the
Customer_Name
which I used to create a combo box. Underneath the combo
box I want to create three command buttons. One runs all
transaction for the past four years, the other runs 2003
transactions and the third runs transactions over the
past
60 days. I want the queries to then generate a report.

What I want to do is for the user to select a customer
from the drop down box and then click one of the command
buttons (i.e. transactions last 60 days). This would then
run a query based on the transactions table (i.e. the
last
60 days) for this customer and return it in a report
form.

Does anyone know how to do this?

Thanks,

Haji
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
The comma is required. StLinkCritera (not
StLinkLinkcriteria) is on the same line as the 2 commas
Could you post all the code. There is always a problem
with wordwrap here.

Also make sure you use Option Explicit in your general
declarations section of your code. It forces you to
declare your variables resulting in easier debugging.

Jim
-----Original Message-----
Jim

Sorry to keep bugging you about this. I ran the code you
gave me but got a syntax error saying there was a comma in
my expression. I took one of the comma out of the
expression after acViewPreview. It now reads like this:

DoCmd.OpenReport stDocName, acViewPreview,
stLinkLinkCriteria

The code runs but I get every customer in my query rather
than just the customer I selected. In my report, the
field name of the customer from my query is called
CustomerRef_FullName and is in the CustomerRef_FullName
header of the report. I did a grouping by this field when
I set up the report. It looks like the VBA code is not
searching for the particular field name that I selected
but instead running the entire query.

Thanks for your help.

Haji

-----Original Message-----
You need to substitute your report name. I am assuming
that ComboAnna is the unique record identifyer and is the
same as CustomerRef_FullName in the report
Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

' Enter report name below
stDocName = "ReportName"

stLinkCriteria = "[CustomerRef_FullName]=" & Me!
[ComboAnna]
DoCmd.OpenReport stDocName, acViewPreview, ,
stLinkCriteria

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub

Good Luck

Jim
-----Original Message-----
Jim/Chris,

Thanks for you reply. I am a bit of a novice VBA user and
have not been able to get this to run. My Form name that
contains both the combo box to select customers and the
button to run my queries is called SelectCustomerAnna. My
combo box for select customers is called ComboAnna. How
would I write the VBA code so that only the customer I
selected appears on the report. In my report, the actual
field name of the customer from my query is called
CustomerRef_FullName and is in the CustomerRef_FullName
header.

Thanks,

Haji




-----Original Message-----
Create the report. The code behind each button would read
Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ReportName"

stLinkCriteria = "[FormID]=" & Me![Customer_Name
combo
box]
DoCmd.OpenReport stDocName, acViewPreview, ,
stLinkCriteria

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub

I actually got this from this site.
Good Luck

Jim


-----Original Message-----
Hello,

I am building an application for a sales person. I built
a
query off of a customer table that lists the
Customer_Name
which I used to create a combo box. Underneath the
combo
box I want to create three command buttons. One runs
all
transaction for the past four years, the other runs 2003
transactions and the third runs transactions over the
past
60 days. I want the queries to then generate a report.

What I want to do is for the user to select a customer
from the drop down box and then click one of the command
buttons (i.e. transactions last 60 days). This would
then
run a query based on the transactions table (i.e. the
last
60 days) for this customer and return it in a report
form.

Does anyone know how to do this?

Thanks,

Haji
.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Jim,

The Link written twice was just a typo on my part when
typing this note into this page. It was not in my VBA
editor. My data actually has commas in it. For instance,
I might select a person who has the name Abraham, David.
Would this matter and do I need to strip out the commas?
When I run this code I get an error that says "Syntax
Error, Comma in Query Expression '([CustomerRef_FullName]
= Abraham, David)'.

Here is all of my code.

---------------------------------------------------

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

Dim stDocName As String

stDocName = "Anna_2003"
DoCmd.OpenReport stDocName, acPreview

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub
Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "2003TrxAnna"

stLinkCriteria = "[CustomerRef_FullName]=" & Me!
[ComboAnna]

DoCmd.OpenReport stDocName, acViewPreview, ,
stLinkCriteria

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

------------------------------------------------

-----Original Message-----
The comma is required. StLinkCritera (not
StLinkLinkcriteria) is on the same line as the 2 commas
Could you post all the code. There is always a problem
with wordwrap here.

Also make sure you use Option Explicit in your general
declarations section of your code. It forces you to
declare your variables resulting in easier debugging.

Jim
-----Original Message-----
Jim

Sorry to keep bugging you about this. I ran the code you
gave me but got a syntax error saying there was a comma in
my expression. I took one of the comma out of the
expression after acViewPreview. It now reads like this:

DoCmd.OpenReport stDocName, acViewPreview,
stLinkLinkCriteria

The code runs but I get every customer in my query rather
than just the customer I selected. In my report, the
field name of the customer from my query is called
CustomerRef_FullName and is in the CustomerRef_FullName
header of the report. I did a grouping by this field when
I set up the report. It looks like the VBA code is not
searching for the particular field name that I selected
but instead running the entire query.

Thanks for your help.

Haji

-----Original Message-----
You need to substitute your report name. I am assuming
that ComboAnna is the unique record identifyer and is the
same as CustomerRef_FullName in the report
Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

' Enter report name below
stDocName = "ReportName"

stLinkCriteria = "[CustomerRef_FullName]=" & Me!
[ComboAnna]
DoCmd.OpenReport stDocName, acViewPreview, ,
stLinkCriteria

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub

Good Luck

Jim
-----Original Message-----
Jim/Chris,

Thanks for you reply. I am a bit of a novice VBA user
and
have not been able to get this to run. My Form name that
contains both the combo box to select customers and the
button to run my queries is called SelectCustomerAnna.
My
combo box for select customers is called ComboAnna. How
would I write the VBA code so that only the customer I
selected appears on the report. In my report, the actual
field name of the customer from my query is called
CustomerRef_FullName and is in the CustomerRef_FullName
header.

Thanks,

Haji




-----Original Message-----
Create the report. The code behind each button would
read
Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ReportName"

stLinkCriteria = "[FormID]=" & Me![Customer_Name
combo
box]
DoCmd.OpenReport stDocName, acViewPreview, ,
stLinkCriteria

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox Err.Description
Resume Exit_Button_Click

End Sub

I actually got this from this site.
Good Luck

Jim


-----Original Message-----
Hello,

I am building an application for a sales person. I
built
a
query off of a customer table that lists the
Customer_Name
which I used to create a combo box. Underneath the
combo
box I want to create three command buttons. One runs
all
transaction for the past four years, the other runs
2003
transactions and the third runs transactions over the
past
60 days. I want the queries to then generate a report.

What I want to do is for the user to select a customer
from the drop down box and then click one of the
command
buttons (i.e. transactions last 60 days). This would
then
run a query based on the transactions table (i.e. the
last
60 days) for this customer and return it in a report
form.

Does anyone know how to do this?

Thanks,

Haji
.

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top