Help Sub Form Question

[Guest]

My Access 2003 project consists of a switchboard with several sub forms all
positioned on top of one another. On the switchboard is a number of command
buttons which makes each sub form visible or not. All works great.

On one of the subforms i have a cmdbutton which opens a new standalone form.
On that standalone form i have a text box with the following control source.

=DLookUp("[EmployeeFullName]","qryEmployeeFullName"," [Employee ID] =
Forms![sbfrmSwitchboardEmployees]![EmployeeID]")

Depending what record is selected on the subform depends what name is
displayed in the standalone form.

This works fine when the subform is opened by itself (no switchboard), but
when i open the switchboard and open the stand alone form the text is showing
Error.

I've changed my control source to
=DLookUp("[EmployeeFullName]","qryEmployeeFullName"," [Employee ID] = '" &
Me.EmployeeID & "'") but still no look.

Any one have any ideas please?

 

[strive4peace]

Hi Stu,

try this:

if Employee ID is a number...

=DLookUp(
"[EmployeeFullName]",
"qryEmployeeFullName",
"[Employee ID] = " &
nz(Forms![sbfrmSwitchboardEmployees]![EmployeeID])
)

if it is text...

=DLookUp(
"[EmployeeFullName]",
"qryEmployeeFullName",
"[Employee ID] = '" &
nz(Forms![sbfrmSwitchboardEmployees]![EmployeeID])
& "'")

you need to take the form reference out of the quoted criteria string...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Thanks for info.

Your code works fine when the sbfrm is opened by itself ( as a single form)
but when the sbfrm is opened via its it main form, ie switchboard then i get
?name in the field.

Any further ideas please?

Hi Stu,

try this:

if Employee ID is a number...

=DLookUp(
"[EmployeeFullName]",
"qryEmployeeFullName",
"[Employee ID] = " &
nz(Forms![sbfrmSwitchboardEmployees]![EmployeeID])
)

if it is text...

=DLookUp(
"[EmployeeFullName]",
"qryEmployeeFullName",
"[Employee ID] = '" &
nz(Forms![sbfrmSwitchboardEmployees]![EmployeeID])
& "'")

you need to take the form reference out of the quoted criteria string...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

My Access 2003 project consists of a switchboard with several sub forms all
positioned on top of one another. On the switchboard is a number of command
buttons which makes each sub form visible or not. All works great.

On one of the subforms i have a cmdbutton which opens a new standalone form.
On that standalone form i have a text box with the following control source.

=DLookUp("[EmployeeFullName]","qryEmployeeFullName"," [Employee ID] =
Forms![sbfrmSwitchboardEmployees]![EmployeeID]")

Depending what record is selected on the subform depends what name is
displayed in the standalone form.

This works fine when the subform is opened by itself (no switchboard), but
when i open the switchboard and open the stand alone form the text is showing
Error.

I've changed my control source to
=DLookUp("[EmployeeFullName]","qryEmployeeFullName"," [Employee ID] = '" &
Me.EmployeeID & "'") but still no look.

Any one have any ideas please?

 

[strive4peace]

Hi Stu,

I did notice that you used [Employee ID] and [EmployeeID] ... what is
your fieldname? What is your controlname?

Is the switchboard staying open? Since you reference it, it needs to be
open

also, I notice that the "formname" is "sbfrmSwitchboardEmployees" ... if
this is a subform, you need to change the reference...

ie:

nz(Forms!switchboard.form.[sbfrmSwitchboardEmployees]![EmployeeID])

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for info.

Your code works fine when the sbfrm is opened by itself ( as a single form)
but when the sbfrm is opened via its it main form, ie switchboard then i get
?name in the field.

Any further ideas please?

Hi Stu,

try this:

if Employee ID is a number...

=DLookUp(
"[EmployeeFullName]",
"qryEmployeeFullName",
"[Employee ID] = " &
nz(Forms![sbfrmSwitchboardEmployees]![EmployeeID])
)

if it is text...

=DLookUp(
"[EmployeeFullName]",
"qryEmployeeFullName",
"[Employee ID] = '" &
nz(Forms![sbfrmSwitchboardEmployees]![EmployeeID])
& "'")

you need to take the form reference out of the quoted criteria string...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

My Access 2003 project consists of a switchboard with several sub forms all
positioned on top of one another. On the switchboard is a number of command
buttons which makes each sub form visible or not. All works great.

On one of the subforms i have a cmdbutton which opens a new standalone form.
On that standalone form i have a text box with the following control source.

=DLookUp("[EmployeeFullName]","qryEmployeeFullName"," [Employee ID] =
Forms![sbfrmSwitchboardEmployees]![EmployeeID]")

Depending what record is selected on the subform depends what name is
displayed in the standalone form.

This works fine when the subform is opened by itself (no switchboard), but
when i open the switchboard and open the stand alone form the text is showing
Error.

I've changed my control source to
=DLookUp("[EmployeeFullName]","qryEmployeeFullName"," [Employee ID] = '" &
Me.EmployeeID & "'") but still no look.

Any one have any ideas please?