Passing value from Form to Report

[Darryl]

Hi,

I have a form that contains a drop-down menu that allows the user to
select an employee currently in the database. Upon selection of an
employee, the employee's information (address, birth etc) show up on
the form to allow the user to modify said information.

There is a button on this form that should bring the user to a report
on the sales performance of the selected employee.

So here is my problem.

The data for the employees personal information and the employees
sales records are in two separate tables.

How do I get the report to actually show the selected employee's sales
numbers?

ie, please give detailed step-by-step as I am new to Access and
inexperienced.

 

[Michael Conroy]

Darryl:
There are a few things to explain first. It sounds like your data is
normalized, that is, you use different tables for specific information.
Hopefully you have a common number to relate the employee table with the
sales table. I will assume some kind of unique employee number is in the
employee table and the same number is in the sales table so you can relate
the two. If not, we have to back up.

If my assumptions above are correct, you can create a query with both tables
in it, and draw a line from the unique number in the employee table to the
unique number in the sales table. Build a report with this query as the
source. Then when you click on the report button, you can grab the unique ID
for the displayed person and run the report so that just the information for
the number is printed.

The trick to this is to get the UserID. Now when you select the person's
name in the combo box, you should have two columns for the combo, one is for
the UserID and the other is for the name. To do that, go to the table/query
property of the combo box and select the three dots, add in the employee
table, select the UserID first and the employee's name fields second. Here is
the trick, when you select the guy's name, you are selecting that record,
which now has both his name and UserID. However, you just want to see the
guy's name right, so set the first column width to zero, which hides the ID
(but it is still there), and maybe one inch for the second column: (0";1").
Now set the bound column to 1. This means whenever you reference that combo
box, you will get the UserID, even though the column is unseen and the combo
is displaying the name.

Once you are able to include the UserID in the combo box, you just have to
open the report with that filter is place. Here are the commands. Don't
include the asterisk or parentheses lines. On your form, you should have a
report button the person can click. In the on_click event of the report
button in the code section put:

*** if UserID is a number ***
dim criteria as string
dim target as integer
target = me.cboUserID <- whatever that combo box is called on the form
criteria = "UserID = " & [target]
DoCmd.OpenReport "rptSales", acViewNormal, , Criteria

*** if UserID is a string *** (letters & numbers)
dim criteria as string
dim target as string
target = me.cboUserID <- whatever that combo box is called on the form
criteria = "UserID = '" & [UserID] & "'"
DoCmd.OpenReport "rptSales", acViewNormal, , Criteria

(to the left of the first ampersand above it is single quote, then double
quote)
(to the right of the second ampersand above it is double, single and double)

This is a long answer, don't loose sight of the big picture. When you select
the name in the combo box you also want the UserID for that person. When you
click on the report button you want the report to print only what belongs to
that UserID. Hope that helps.

 

[Darryl]

Darryl:
There are a few things to explain first. It sounds like your data is
normalized, that is, you use different tables for specific information.
Hopefully you have a common number to relate the employee table with the
sales table. I will assume some kind of unique employee number is in the
employee table and the same number is in the sales table so you can relate
the two. If not, we have to back up.

If my assumptions above are correct, you can create a query with both tables
in it, and draw a line from the unique number in the employee table to the
unique number in the sales table. Build a report with this query as the
source. Then when you click on the report button, you can grab the unique ID
for the displayed person and run the report so that just the information for
the number is printed.

The trick to this is to get the UserID. Now when you select the person's
name in the combo box, you should have two columns for the combo, one is for
the UserID and the other is for the name. To do that, go to the table/query
property of the combo box and select the three dots, add in the employee
table, select the UserID first and the employee's name fields second. Here is
the trick, when you select the guy's name, you are selecting that record,
which now has both his name and UserID. However, you just want to see the
guy's name right, so set the first column width to zero, which hides the ID
(but it is still there), and maybe one inch for the second column: (0";1").
Now set the bound column to 1. This means whenever you reference that combo
box, you will get the UserID, even though the column is unseen and the combo
is displaying the name.

Once you are able to include the UserID in the combo box, you just have to
open the report with that filter is place. Here are the commands. Don't
include the asterisk or parentheses lines. On your form, you should have a
report button the person can click. In the on_click event of the report
button in the code section put:

*** if UserID is a number ***
dim criteria as string
dim target as integer
target = me.cboUserID <- whatever that combo box is called on the form
criteria = "UserID = " & [target]
DoCmd.OpenReport "rptSales", acViewNormal, , Criteria

*** if UserID is a string *** (letters & numbers)
dim criteria as string
dim target as string
target = me.cboUserID <- whatever that combo box is called on the form
criteria = "UserID = '" & [UserID] & "'"
DoCmd.OpenReport "rptSales", acViewNormal, , Criteria

(to the left of the first ampersand above it is single quote, then double
quote)
(to the right of the second ampersand above it is double, single and double)

This is a long answer, don't loose sight of the big picture. When you select
the name in the combo box you also want the UserID for that person. When you
click on the report button you want the report to print only what belongs to
that UserID. Hope that helps.
--
Michael Conroy
Stamford, CT

Hi,

I have a form that contains a drop-down menu that allows the user to
select an employee currently in the database. Upon selection of an
employee, the employee's information (address, birth etc) show up on
the form to allow the user to modify said information.

There is a button on this form that should bring the user to a report
on the sales performance of the selected employee.

So here is my problem.

The data for the employees personal information and the employees
sales records are in two separate tables.

How do I get the report to actually show the selected employee's sales
numbers?

ie, please give detailed step-by-step as I am new to Access and
inexperienced.- Hide quoted text -

- Show quoted text -

Thanks so much for your help Michael, I just have one small issue.

I've done what you said, and this is my code:

Dim criteria As String
Dim target As Integer
target = Me.cur_emp
criteria = "Employee ID = " & [target]
DoCmd.OpenReport "Annual Sales", acViewNormal, , criteria

where cur_emp is my combo box name, and Employee ID is my "UserID"
field, and "Annual Sales" being the report I wish to print.

However, a couple things are going wrong. First, each time I select a
name in the form, it is changes the last name selected to the new name
selected in the actual employee database. Second, when I click on the
button to generate the report, it is giving me a type mismatch and
highlighting the line "target = Me.cur_emp" in the debugger.

Any thoughts?

 

[pietlinden]

Darryl:
There are a few things to explain first. It sounds like your data is
normalized, that is, you use different tables for specific information.
Hopefully you have a common number to relate the employee table with the
sales table. I will assume some kind of unique employee number is in the
employee table and the same number is in the sales table so you can relate
the two. If not, we have to back up.

If my assumptions above are correct, you can create a query with both tables
in it, and draw a line from the unique number in the employee table to the
unique number in the sales table. Build a report with this query as the
source. Then when you click on the report button, you can grab the unique ID
for the displayed person and run the report so that just the information for
the number is printed.

The trick to this is to get the UserID. Now when you select the person's
name in the combo box, you should have two columns for the combo, one is for
the UserID and the other is for the name. To do that, go to the table/query
property of the combo box and select the three dots, add in the employee
table, select the UserID first and the employee's name fields second. Here is
the trick, when you select the guy's name, you are selecting that record,
which now has both his name and UserID. However, you just want to see the
guy's name right, so set the first column width to zero, which hides the ID
(but it is still there), and maybe one inch for the second column: (0";1").
Now set the bound column to 1. This means whenever you reference that combo
box, you will get the UserID, even though the column is unseen and the combo
is displaying the name.

Once you are able to include the UserID in the combo box, you just have to
open the report with that filter is place. Here are the commands. Don't
include the asterisk or parentheses lines. On your form, you should have a
report button the person can click. In the on_click event of the report
button in the code section put:

*** if UserID is a number ***
dim criteria as string
dim target as integer
target = me.cboUserID <- whatever that combo box is called on the form
criteria = "UserID = " & [target]
DoCmd.OpenReport "rptSales", acViewNormal, , Criteria

*** if UserID is a string *** (letters & numbers)
dim criteria as string
dim target as string
target = me.cboUserID <- whatever that combo box is called on the form
criteria = "UserID = '" & [UserID] & "'"
DoCmd.OpenReport "rptSales", acViewNormal, , Criteria

(to the left of the first ampersand above it is single quote, then double
quote)
(to the right of the second ampersand above it is double, single and double)

This is a long answer, don't loose sight of the big picture. When you select
the name in the combo box you also want the UserID for that person. When you
click on the report button you want the report to print only what belongs to
that UserID. Hope that helps.

- Show quoted text -

Thanks so much for your help Michael, I just have one small issue.

I've done what you said, and this is my code:

Dim criteria As String
Dim target As Integer
target = Me.cur_emp
criteria = "Employee ID = " & [target]
DoCmd.OpenReport "Annual Sales", acViewNormal, , criteria

where cur_emp is my combo box name, and Employee ID is my "UserID"
field, and "Annual Sales" being the report I wish to print.

However, a couple things are going wrong. First, each time I select a
name in the form, it is changes the last name selected to the new name
selected in the actual employee database. Second, when I click on the
button to generate the report, it is giving me a type mismatch and
highlighting the line "target = Me.cur_emp" in the debugger.

Any thoughts?

You don't need the square brackets around the "target" variable.
That's only if you're pointing at fields in a query.

it should read

criteria= "[Employee ID]=" & target

sounds like your combobox is bound (to a field in the form's
recordsource) and it should not be. Then the value won't be saved
anywhere... and you can change it all you want. So just remove the
Rowsource value from that control.