Report based on fields in Form AND a Table

[Beth]

I'm trying to create an unbound report that will generate a Fax
coversheet based on a selected customer. It will get most of it's data
from a form, but I'd like to use one of those fields to get additional
data from an "unrelated" table. I'm also getting repeating records on
the report.

FAX FORM
- the record souce is a customer table (filtered based on a record in a
separate form)
- has a combobox that displays Employee name from Employee table. (for
Fax From
- has an unbound textbox that user types "subject" of fax.

FAX REPORT
- the report gets the customer data, subject and employee name from FAX
FORM
- would like to put additional employee data (title, email, phone, fax,
dept) on the Fax usng the employee name and the employee table (small
company, so the name is unique)

CUSTOMER TABLE
CustomerID
CustomerName
Address...
Phone
Fax

EMPLOYEE TABLE
EmployeeName
Title
Email
Phone
Fax

To get the other Employee data, I tried creating an unbound text box
with a SQL statement in the Control Source. I guessed at how it should
be written, so it might need some work.
=(select tblEmployee.Title from tblEmployee where Employee =
Forms![frmFax]![Combo12])

I am also getting repeating data. If I have 3 employees in the
Employee table, the data will repeat 3x (4 employees, 4x) I'm sure
this is because the customer and employee tables are not related, but
don't know how to fix it.

I've been focusing on getting the report working, but the fix might
need to be at the form level. I tried a subform to get the Employee
data, but there is no relationship between the Customer table and the
Employee table. I'm a rookie at VBA but can "translate" any code
suggestions. All help is appreciated.

Thanks.
Beth

 

[David Lloyd]

Beth:

One suggestion would be to use the DLookup function to find the other data
for the employee selected. For example in the Control Source property of
the textbox you might use:

=DLookup("[Title]", "Employee", "[Employee] = Forms![frmFax]![Combo12]")

The first parameter is the field you want to find. The second is the name
of the table (or query). The third is the WHERE clause without the word
"WHERE."

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I'm trying to create an unbound report that will generate a Fax
coversheet based on a selected customer. It will get most of it's data
from a form, but I'd like to use one of those fields to get additional
data from an "unrelated" table. I'm also getting repeating records on
the report.

FAX FORM
- the record souce is a customer table (filtered based on a record in a
separate form)
- has a combobox that displays Employee name from Employee table. (for
Fax From
- has an unbound textbox that user types "subject" of fax.

FAX REPORT
- the report gets the customer data, subject and employee name from FAX
FORM
- would like to put additional employee data (title, email, phone, fax,
dept) on the Fax usng the employee name and the employee table (small
company, so the name is unique)

CUSTOMER TABLE
CustomerID
CustomerName
Address...
Phone
Fax

EMPLOYEE TABLE
EmployeeName
Title
Email
Phone
Fax

To get the other Employee data, I tried creating an unbound text box
with a SQL statement in the Control Source. I guessed at how it should
be written, so it might need some work.
=(select tblEmployee.Title from tblEmployee where Employee =
Forms![frmFax]![Combo12])

I am also getting repeating data. If I have 3 employees in the
Employee table, the data will repeat 3x (4 employees, 4x) I'm sure
this is because the customer and employee tables are not related, but
don't know how to fix it.

I've been focusing on getting the report working, but the fix might
need to be at the form level. I tried a subform to get the Employee
data, but there is no relationship between the Customer table and the
Employee table. I'm a rookie at VBA but can "translate" any code
suggestions. All help is appreciated.

Thanks.
Beth

 

[Beth]

David,

Thanks - DLookup worked perfectly. And I fixed my repeating data with
a simple grouping.

Beth