Referencing a control on a sub form (Post 2) help, please?

[Tony Williams]

I'm reposting this question as I recognise that I didn't give enough
explanation in my first post. So here is the full story.

I have a combobox control (cmbstatus) on a subform (frmvehicle) which is on
a form (frmMain). The control source of cmbstatus is txtstatus of my main
table and the row source is a value List as this - "01";"02";"91";"92".
Although they are numbers, txtstatus is a text field because I wanted to
force the leading 0's.

On the same subform is an unbound control (txtstatusdesc). I want this value
to equal the value of a field also called txtstatus that is held in a table
called StatusCodes and where the the value of cmbstatus on my subform equals
the value of txtstatuscode in the StatusCode table.

I had the control source as :
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]=Forms![frmMain]![frmVehicle].Form![cmbstatus]")

but this gave me an #Error that blinked. So Daniel, in my previous post,
gave me this:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

But this gives me a #Name?

I thought my original formula follwed the correct protocol for referring to
controls on subforms. I've also tried:
1. using the control source txtstatus instead of the control name cmbstatus
in the formula
2. leaving the control source of the unbound control txtstatusdesc on the
sub form blank and putting the DLookUp formula in the Default value but I
still get the same problems

I've checked my spelling of the field and table names and they are correct.
This is really frustrating as I thought I was foing the right thing with the
formula and I don't know what else to do.
Could someone please help?
Thanks
Tony

 

[M Skabialka]

With frmMain open, test the syntax of your statements in the Immediate
Window available from your Visual Basic Code window.

Debug.print "[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")
You should see something like:
[txtstatuscode]='xyz'
Where xyz is your status, assuming it is text. Then try
Debug.print DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='xyz'")
Debug.print DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

You can try many different pieces of code in the immediate window to test
syntax, and make adjustment to your code based on error messages.

When you use an expression as your control source - don't use a field name
from your query or table - use a name that tells what you are going (e.g.
txtStatusLookup) or you will get the #NAME error.

Mich


With your

 

[Tony Williams]

Hi Mich. Thanks for the suggestions. However I'm not quite sure how to use
the Immediate window. I can only open that when the form is in design mode
can't I? I did try to do what you suggested and typed the first debug
statement into the window and pressed enter and nothing happened?

"When you use an expression as your control source - don't use a field name

from your query or table - use a name that tells what you are going (e.g.
txtStatusLookup) or you will get the #NAME error."

When you say this do you mean the control where the DLookupformula is the
control source? In this case that control is called txtstatusdesc which isn't
a field name.

Also I've just realised that the form frmVehicle is used to display a
tabcontrol and the control where the formula is is on the first page of that
tab control. I did see a reference to this in a Google lookup and it said
that shouldn't make any difference as the control is related to the subform
and not the tabcontrol. is that correct?
Thanks
Tony

With frmMain open, test the syntax of your statements in the Immediate
Window available from your Visual Basic Code window.

Debug.print "[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")
You should see something like:
[txtstatuscode]='xyz'
Where xyz is your status, assuming it is text. Then try
Debug.print DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='xyz'")
Debug.print DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

You can try many different pieces of code in the immediate window to test
syntax, and make adjustment to your code based on error messages.

When you use an expression as your control source - don't use a field name
from your query or table - use a name that tells what you are going (e.g.
txtStatusLookup) or you will get the #NAME error.

Mich


With your

I'm reposting this question as I recognise that I didn't give enough
explanation in my first post. So here is the full story.

I have a combobox control (cmbstatus) on a subform (frmvehicle) which is
on
a form (frmMain). The control source of cmbstatus is txtstatus of my main
table and the row source is a value List as this - "01";"02";"91";"92".
Although they are numbers, txtstatus is a text field because I wanted to
force the leading 0's.

On the same subform is an unbound control (txtstatusdesc). I want this
value
to equal the value of a field also called txtstatus that is held in a
table
called StatusCodes and where the the value of cmbstatus on my subform
equals
the value of txtstatuscode in the StatusCode table.

I had the control source as :
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]=Forms![frmMain]![frmVehicle].Form![cmbstatus]")

but this gave me an #Error that blinked. So Daniel, in my previous post,
gave me this:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

But this gives me a #Name?

I thought my original formula follwed the correct protocol for referring
to
controls on subforms. I've also tried:
1. using the control source txtstatus instead of the control name
cmbstatus
in the formula
2. leaving the control source of the unbound control txtstatusdesc on the
sub form blank and putting the DLookUp formula in the Default value but I
still get the same problems

I've checked my spelling of the field and table names and they are
correct.
This is really frustrating as I thought I was foing the right thing with
the
formula and I don't know what else to do.
Could someone please help?
Thanks
Tony

 

[M Skabialka]

You can get into the code window using Alt - F11, then View - Immediate
window.
Since you are referring to some data on [Forms]![frmMain]![frmVehicle], it
must be open where you can see that data, e.g. cmbstatus.

Let's say cmbstatus is ABC on the form. Does this statement in the immediate
window return ABC after you hit enter?
Debug.Print [Forms]![frmMain]![frmVehicle].[Form]![cmbstatus]
<ENTER>
ABC
If nothing shows up then you are not referencing it properly - the form name
or control name is spelled wrong or you are using the wrong syntax for this
form/subform name. If this statement is wrong the DLookup will produce
nothing either.

Hi Mich. Thanks for the suggestions. However I'm not quite sure how to use
the Immediate window. I can only open that when the form is in design mode
can't I? I did try to do what you suggested and typed the first debug
statement into the window and pressed enter and nothing happened?

"When you use an expression as your control source - don't use a field
name

from your query or table - use a name that tells what you are going (e.g.
txtStatusLookup) or you will get the #NAME error."

When you say this do you mean the control where the DLookupformula is the
control source? In this case that control is called txtstatusdesc which
isn't
a field name.

Also I've just realised that the form frmVehicle is used to display a
tabcontrol and the control where the formula is is on the first page of
that
tab control. I did see a reference to this in a Google lookup and it said
that shouldn't make any difference as the control is related to the
subform
and not the tabcontrol. is that correct?
Thanks
Tony

With frmMain open, test the syntax of your statements in the Immediate
Window available from your Visual Basic Code window.

Debug.print "[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")
You should see something like:
[txtstatuscode]='xyz'
Where xyz is your status, assuming it is text. Then try
Debug.print
DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='xyz'")
Debug.print DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

You can try many different pieces of code in the immediate window to test
syntax, and make adjustment to your code based on error messages.

When you use an expression as your control source - don't use a field
name
from your query or table - use a name that tells what you are going (e.g.
txtStatusLookup) or you will get the #NAME error.

Mich


With your

I'm reposting this question as I recognise that I didn't give enough
explanation in my first post. So here is the full story.

I have a combobox control (cmbstatus) on a subform (frmvehicle) which
is
on
a form (frmMain). The control source of cmbstatus is txtstatus of my
main
table and the row source is a value List as this - "01";"02";"91";"92".
Although they are numbers, txtstatus is a text field because I wanted
to
force the leading 0's.

On the same subform is an unbound control (txtstatusdesc). I want this
value
to equal the value of a field also called txtstatus that is held in a
table
called StatusCodes and where the the value of cmbstatus on my subform
equals
the value of txtstatuscode in the StatusCode table.

I had the control source as :
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]=Forms![frmMain]![frmVehicle].Form![cmbstatus]")

but this gave me an #Error that blinked. So Daniel, in my previous
post,
gave me this:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

But this gives me a #Name?

I thought my original formula follwed the correct protocol for
referring
to
controls on subforms. I've also tried:
1. using the control source txtstatus instead of the control name
cmbstatus
in the formula
2. leaving the control source of the unbound control txtstatusdesc on
the
sub form blank and putting the DLookUp formula in the Default value but
I
still get the same problems

I've checked my spelling of the field and table names and they are
correct.
This is really frustrating as I thought I was foing the right thing
with
the
formula and I don't know what else to do.
Could someone please help?
Thanks
Tony

 

[Tony Williams]

Thanks Mich. Did that and got an error message that said
Runtime error 2465
Database can't find the field frmvehicle referred to in your expression

Odd because the frmvehicle is a form not a field?
Any ideas?
Thanks for sticking with me
Tony

You can get into the code window using Alt - F11, then View - Immediate
window.
Since you are referring to some data on [Forms]![frmMain]![frmVehicle], it
must be open where you can see that data, e.g. cmbstatus.

Let's say cmbstatus is ABC on the form. Does this statement in the immediate
window return ABC after you hit enter?
Debug.Print [Forms]![frmMain]![frmVehicle].[Form]![cmbstatus]
<ENTER>
ABC
If nothing shows up then you are not referencing it properly - the form name
or control name is spelled wrong or you are using the wrong syntax for this
form/subform name. If this statement is wrong the DLookup will produce
nothing either.

Hi Mich. Thanks for the suggestions. However I'm not quite sure how to use
the Immediate window. I can only open that when the form is in design mode
can't I? I did try to do what you suggested and typed the first debug
statement into the window and pressed enter and nothing happened?

"When you use an expression as your control source - don't use a field
name

from your query or table - use a name that tells what you are going (e.g.
txtStatusLookup) or you will get the #NAME error."

When you say this do you mean the control where the DLookupformula is the
control source? In this case that control is called txtstatusdesc which
isn't
a field name.

Also I've just realised that the form frmVehicle is used to display a
tabcontrol and the control where the formula is is on the first page of
that
tab control. I did see a reference to this in a Google lookup and it said
that shouldn't make any difference as the control is related to the
subform
and not the tabcontrol. is that correct?
Thanks
Tony

With frmMain open, test the syntax of your statements in the Immediate
Window available from your Visual Basic Code window.

Debug.print "[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")
You should see something like:
[txtstatuscode]='xyz'
Where xyz is your status, assuming it is text. Then try
Debug.print
DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='xyz'")
Debug.print DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

You can try many different pieces of code in the immediate window to test
syntax, and make adjustment to your code based on error messages.

When you use an expression as your control source - don't use a field
name
from your query or table - use a name that tells what you are going (e.g.
txtStatusLookup) or you will get the #NAME error.

Mich


With your
I'm reposting this question as I recognise that I didn't give enough
explanation in my first post. So here is the full story.

I have a combobox control (cmbstatus) on a subform (frmvehicle) which
is
on
a form (frmMain). The control source of cmbstatus is txtstatus of my
main
table and the row source is a value List as this - "01";"02";"91";"92".
Although they are numbers, txtstatus is a text field because I wanted
to
force the leading 0's.

On the same subform is an unbound control (txtstatusdesc). I want this
value
to equal the value of a field also called txtstatus that is held in a
table
called StatusCodes and where the the value of cmbstatus on my subform
equals
the value of txtstatuscode in the StatusCode table.

I had the control source as :
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]=Forms![frmMain]![frmVehicle].Form![cmbstatus]")

but this gave me an #Error that blinked. So Daniel, in my previous
post,
gave me this:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

But this gives me a #Name?

I thought my original formula follwed the correct protocol for
referring
to
controls on subforms. I've also tried:
1. using the control source txtstatus instead of the control name
cmbstatus
in the formula
2. leaving the control source of the unbound control txtstatusdesc on
the
sub form blank and putting the DLookUp formula in the Default value but
I
still get the same problems

I've checked my spelling of the field and table names and they are
correct.
This is really frustrating as I thought I was foing the right thing
with
the
formula and I don't know what else to do.
Could someone please help?
Thanks
Tony

 

[M Skabialka]

Let me get this right -
You have a main form called "frmMain"
This form has a subform called "frmVehicle" (are you sure?)
On that subform is a control named "cmbstatus"
cmbstatus shows information when frmMain is opened.

I tested this code in my own database and it worked properly, using the same
syntax you are using:
debug.Print Forms![frmMain]![frmFunctionalAreas subform].Form![ID]
672
Something is misspelled...

Thanks Mich. Did that and got an error message that said
Runtime error 2465
Database can't find the field frmvehicle referred to in your expression

Odd because the frmvehicle is a form not a field?
Any ideas?
Thanks for sticking with me
Tony

You can get into the code window using Alt - F11, then View - Immediate
window.
Since you are referring to some data on [Forms]![frmMain]![frmVehicle],
it
must be open where you can see that data, e.g. cmbstatus.

Let's say cmbstatus is ABC on the form. Does this statement in the
immediate
window return ABC after you hit enter?
Debug.Print [Forms]![frmMain]![frmVehicle].[Form]![cmbstatus]
<ENTER>
ABC
If nothing shows up then you are not referencing it properly - the form
name
or control name is spelled wrong or you are using the wrong syntax for
this
form/subform name. If this statement is wrong the DLookup will produce
nothing either.

Hi Mich. Thanks for the suggestions. However I'm not quite sure how to
use
the Immediate window. I can only open that when the form is in design
mode
can't I? I did try to do what you suggested and typed the first debug
statement into the window and pressed enter and nothing happened?

"When you use an expression as your control source - don't use a field
name
from your query or table - use a name that tells what you are going
(e.g.
txtStatusLookup) or you will get the #NAME error."

When you say this do you mean the control where the DLookupformula is
the
control source? In this case that control is called txtstatusdesc which
isn't
a field name.

Also I've just realised that the form frmVehicle is used to display a
tabcontrol and the control where the formula is is on the first page of
that
tab control. I did see a reference to this in a Google lookup and it
said
that shouldn't make any difference as the control is related to the
subform
and not the tabcontrol. is that correct?
Thanks
Tony



:

With frmMain open, test the syntax of your statements in the Immediate
Window available from your Visual Basic Code window.

Debug.print "[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")
You should see something like:
[txtstatuscode]='xyz'
Where xyz is your status, assuming it is text. Then try
Debug.print
DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='xyz'")
Debug.print DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='"
&
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

You can try many different pieces of code in the immediate window to
test
syntax, and make adjustment to your code based on error messages.

When you use an expression as your control source - don't use a field
name
from your query or table - use a name that tells what you are going
(e.g.
txtStatusLookup) or you will get the #NAME error.

Mich


With your
message
I'm reposting this question as I recognise that I didn't give enough
explanation in my first post. So here is the full story.

I have a combobox control (cmbstatus) on a subform (frmvehicle)
which
is
on
a form (frmMain). The control source of cmbstatus is txtstatus of my
main
table and the row source is a value List as this -
"01";"02";"91";"92".
Although they are numbers, txtstatus is a text field because I
wanted
to
force the leading 0's.

On the same subform is an unbound control (txtstatusdesc). I want
this
value
to equal the value of a field also called txtstatus that is held in
a
table
called StatusCodes and where the the value of cmbstatus on my
subform
equals
the value of txtstatuscode in the StatusCode table.

I had the control source as :
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]=Forms![frmMain]![frmVehicle].Form![cmbstatus]")

but this gave me an #Error that blinked. So Daniel, in my previous
post,
gave me this:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

But this gives me a #Name?

I thought my original formula follwed the correct protocol for
referring
to
controls on subforms. I've also tried:
1. using the control source txtstatus instead of the control name
cmbstatus
in the formula
2. leaving the control source of the unbound control txtstatusdesc
on
the
sub form blank and putting the DLookUp formula in the Default value
but
I
still get the same problems

I've checked my spelling of the field and table names and they are
correct.
This is really frustrating as I thought I was foing the right thing
with
the
formula and I don't know what else to do.
Could someone please help?
Thanks
Tony

 

[Tony Williams]

Mich,
You wrote:
You have a main form called "frmMain"

This form has a subform called "frmVehicle" (are you sure?)
On that subform is a control named "cmbstatus"
cmbstatus shows information when frmMain is opened.

The title of the form in the database window is frmVehicle. As I mentioned
earlier the whole of frmvehicle is a tab control and the control cmbstatus is
on the first page of that tab control.

This returned the correct value in the Immediate window
Debug.print DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]")

But I get that message about frmvehicle being a field when I try to debug
the whole expression. So I think it must be something to do with the part of
the Dlookup after the = ie the contrtol reference on the subform.

I have checked all the spelling but still can't get it to work. It's so
frustrating because I've used Dlookup many times before without any problems.

Any other ideas?
Thanks, really appreciate this
Tony

Let me get this right -
You have a main form called "frmMain"
This form has a subform called "frmVehicle" (are you sure?)
On that subform is a control named "cmbstatus"
cmbstatus shows information when frmMain is opened.

I tested this code in my own database and it worked properly, using the same
syntax you are using:
debug.Print Forms![frmMain]![frmFunctionalAreas subform].Form![ID]
672
Something is misspelled...

Thanks Mich. Did that and got an error message that said
Runtime error 2465
Database can't find the field frmvehicle referred to in your expression

Odd because the frmvehicle is a form not a field?
Any ideas?
Thanks for sticking with me
Tony

You can get into the code window using Alt - F11, then View - Immediate
window.
Since you are referring to some data on [Forms]![frmMain]![frmVehicle],
it
must be open where you can see that data, e.g. cmbstatus.

Let's say cmbstatus is ABC on the form. Does this statement in the
immediate
window return ABC after you hit enter?
Debug.Print [Forms]![frmMain]![frmVehicle].[Form]![cmbstatus]
<ENTER>
ABC
If nothing shows up then you are not referencing it properly - the form
name
or control name is spelled wrong or you are using the wrong syntax for
this
form/subform name. If this statement is wrong the DLookup will produce
nothing either.


Hi Mich. Thanks for the suggestions. However I'm not quite sure how to
use
the Immediate window. I can only open that when the form is in design
mode
can't I? I did try to do what you suggested and typed the first debug
statement into the window and pressed enter and nothing happened?

"When you use an expression as your control source - don't use a field
name
from your query or table - use a name that tells what you are going
(e.g.
txtStatusLookup) or you will get the #NAME error."

When you say this do you mean the control where the DLookupformula is
the
control source? In this case that control is called txtstatusdesc which
isn't
a field name.

Also I've just realised that the form frmVehicle is used to display a
tabcontrol and the control where the formula is is on the first page of
that
tab control. I did see a reference to this in a Google lookup and it
said
that shouldn't make any difference as the control is related to the
subform
and not the tabcontrol. is that correct?
Thanks
Tony



:

With frmMain open, test the syntax of your statements in the Immediate
Window available from your Visual Basic Code window.

Debug.print "[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")
You should see something like:
[txtstatuscode]='xyz'
Where xyz is your status, assuming it is text. Then try
Debug.print
DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='xyz'")
Debug.print DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='"
&
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

You can try many different pieces of code in the immediate window to
test
syntax, and make adjustment to your code based on error messages.

When you use an expression as your control source - don't use a field
name
from your query or table - use a name that tells what you are going
(e.g.
txtStatusLookup) or you will get the #NAME error.

Mich


With your
message
I'm reposting this question as I recognise that I didn't give enough
explanation in my first post. So here is the full story.

I have a combobox control (cmbstatus) on a subform (frmvehicle)
which
is
on
a form (frmMain). The control source of cmbstatus is txtstatus of my
main
table and the row source is a value List as this -
"01";"02";"91";"92".
Although they are numbers, txtstatus is a text field because I
wanted
to
force the leading 0's.

On the same subform is an unbound control (txtstatusdesc). I want
this
value
to equal the value of a field also called txtstatus that is held in
a
table
called StatusCodes and where the the value of cmbstatus on my
subform
equals
the value of txtstatuscode in the StatusCode table.

I had the control source as :
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]=Forms![frmMain]![frmVehicle].Form![cmbstatus]")

but this gave me an #Error that blinked. So Daniel, in my previous
post,
gave me this:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

But this gives me a #Name?

I thought my original formula follwed the correct protocol for
referring
to
controls on subforms. I've also tried:
1. using the control source txtstatus instead of the control name
cmbstatus
in the formula
2. leaving the control source of the unbound control txtstatusdesc
on
the
sub form blank and putting the DLookUp formula in the Default value
but
I
still get the same problems

I've checked my spelling of the field and table names and they are
correct.
This is really frustrating as I thought I was foing the right thing
with
the
formula and I don't know what else to do.
Could someone please help?
Thanks
Tony

 

[Tony Williams]

Mich the cmbstatus control is a combobox where the control source is
txtstatus and the row source is a value list. Could it be that DLookup
doesn't like combo boxes?
Tony

Let me get this right -
You have a main form called "frmMain"
This form has a subform called "frmVehicle" (are you sure?)
On that subform is a control named "cmbstatus"
cmbstatus shows information when frmMain is opened.

I tested this code in my own database and it worked properly, using the same
syntax you are using:
debug.Print Forms![frmMain]![frmFunctionalAreas subform].Form![ID]
672
Something is misspelled...

Thanks Mich. Did that and got an error message that said
Runtime error 2465
Database can't find the field frmvehicle referred to in your expression

Odd because the frmvehicle is a form not a field?
Any ideas?
Thanks for sticking with me
Tony

You can get into the code window using Alt - F11, then View - Immediate
window.
Since you are referring to some data on [Forms]![frmMain]![frmVehicle],
it
must be open where you can see that data, e.g. cmbstatus.

Let's say cmbstatus is ABC on the form. Does this statement in the
immediate
window return ABC after you hit enter?
Debug.Print [Forms]![frmMain]![frmVehicle].[Form]![cmbstatus]
<ENTER>
ABC
If nothing shows up then you are not referencing it properly - the form
name
or control name is spelled wrong or you are using the wrong syntax for
this
form/subform name. If this statement is wrong the DLookup will produce
nothing either.


Hi Mich. Thanks for the suggestions. However I'm not quite sure how to
use
the Immediate window. I can only open that when the form is in design
mode
can't I? I did try to do what you suggested and typed the first debug
statement into the window and pressed enter and nothing happened?

"When you use an expression as your control source - don't use a field
name
from your query or table - use a name that tells what you are going
(e.g.
txtStatusLookup) or you will get the #NAME error."

When you say this do you mean the control where the DLookupformula is
the
control source? In this case that control is called txtstatusdesc which
isn't
a field name.

Also I've just realised that the form frmVehicle is used to display a
tabcontrol and the control where the formula is is on the first page of
that
tab control. I did see a reference to this in a Google lookup and it
said
that shouldn't make any difference as the control is related to the
subform
and not the tabcontrol. is that correct?
Thanks
Tony



:

With frmMain open, test the syntax of your statements in the Immediate
Window available from your Visual Basic Code window.

Debug.print "[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")
You should see something like:
[txtstatuscode]='xyz'
Where xyz is your status, assuming it is text. Then try
Debug.print
DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='xyz'")
Debug.print DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='"
&
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

You can try many different pieces of code in the immediate window to
test
syntax, and make adjustment to your code based on error messages.

When you use an expression as your control source - don't use a field
name
from your query or table - use a name that tells what you are going
(e.g.
txtStatusLookup) or you will get the #NAME error.

Mich


With your
message
I'm reposting this question as I recognise that I didn't give enough
explanation in my first post. So here is the full story.

I have a combobox control (cmbstatus) on a subform (frmvehicle)
which
is
on
a form (frmMain). The control source of cmbstatus is txtstatus of my
main
table and the row source is a value List as this -
"01";"02";"91";"92".
Although they are numbers, txtstatus is a text field because I
wanted
to
force the leading 0's.

On the same subform is an unbound control (txtstatusdesc). I want
this
value
to equal the value of a field also called txtstatus that is held in
a
table
called StatusCodes and where the the value of cmbstatus on my
subform
equals
the value of txtstatuscode in the StatusCode table.

I had the control source as :
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]=Forms![frmMain]![frmVehicle].Form![cmbstatus]")

but this gave me an #Error that blinked. So Daniel, in my previous
post,
gave me this:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

But this gives me a #Name?

I thought my original formula follwed the correct protocol for
referring
to
controls on subforms. I've also tried:
1. using the control source txtstatus instead of the control name
cmbstatus
in the formula
2. leaving the control source of the unbound control txtstatusdesc
on
the
sub form blank and putting the DLookUp formula in the Default value
but
I
still get the same problems

I've checked my spelling of the field and table names and they are
correct.
This is really frustrating as I thought I was foing the right thing
with
the
formula and I don't know what else to do.
Could someone please help?
Thanks
Tony

 

[John W. Vinson]

Mich the cmbstatus control is a combobox where the control source is
txtstatus and the row source is a value list. Could it be that DLookup
doesn't like combo boxes?

DLookUp can look up data in a Table or a Query. It cannot look on a Form.

 

[M Skabialka]

Try this as the control source for txtstatusdesc:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" & Me![cmbstatus]
& "'")

Mich the cmbstatus control is a combobox where the control source is
txtstatus and the row source is a value list. Could it be that DLookup
doesn't like combo boxes?
Tony

Let me get this right -
You have a main form called "frmMain"
This form has a subform called "frmVehicle" (are you sure?)
On that subform is a control named "cmbstatus"
cmbstatus shows information when frmMain is opened.

I tested this code in my own database and it worked properly, using the
same
syntax you are using:
debug.Print Forms![frmMain]![frmFunctionalAreas subform].Form![ID]
672
Something is misspelled...

Thanks Mich. Did that and got an error message that said
Runtime error 2465
Database can't find the field frmvehicle referred to in your expression

Odd because the frmvehicle is a form not a field?
Any ideas?
Thanks for sticking with me
Tony

:

You can get into the code window using Alt - F11, then View -
Immediate
window.
Since you are referring to some data on
[Forms]![frmMain]![frmVehicle],
it
must be open where you can see that data, e.g. cmbstatus.

Let's say cmbstatus is ABC on the form. Does this statement in the
immediate
window return ABC after you hit enter?
Debug.Print [Forms]![frmMain]![frmVehicle].[Form]![cmbstatus]
<ENTER>
ABC
If nothing shows up then you are not referencing it properly - the
form
name
or control name is spelled wrong or you are using the wrong syntax for
this
form/subform name. If this statement is wrong the DLookup will
produce
nothing either.


message
Hi Mich. Thanks for the suggestions. However I'm not quite sure how
to
use
the Immediate window. I can only open that when the form is in
design
mode
can't I? I did try to do what you suggested and typed the first
debug
statement into the window and pressed enter and nothing happened?

"When you use an expression as your control source - don't use a
field
name
from your query or table - use a name that tells what you are going
(e.g.
txtStatusLookup) or you will get the #NAME error."

When you say this do you mean the control where the DLookupformula
is
the
control source? In this case that control is called txtstatusdesc
which
isn't
a field name.

Also I've just realised that the form frmVehicle is used to display
a
tabcontrol and the control where the formula is is on the first page
of
that
tab control. I did see a reference to this in a Google lookup and it
said
that shouldn't make any difference as the control is related to the
subform
and not the tabcontrol. is that correct?
Thanks
Tony



:

With frmMain open, test the syntax of your statements in the
Immediate
Window available from your Visual Basic Code window.

Debug.print "[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")
You should see something like:
[txtstatuscode]='xyz'
Where xyz is your status, assuming it is text. Then try
Debug.print
DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='xyz'")
Debug.print
DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='"
&
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

You can try many different pieces of code in the immediate window
to
test
syntax, and make adjustment to your code based on error messages.

When you use an expression as your control source - don't use a
field
name
from your query or table - use a name that tells what you are going
(e.g.
txtStatusLookup) or you will get the #NAME error.

Mich


With your
message
I'm reposting this question as I recognise that I didn't give
enough
explanation in my first post. So here is the full story.

I have a combobox control (cmbstatus) on a subform (frmvehicle)
which
is
on
a form (frmMain). The control source of cmbstatus is txtstatus of
my
main
table and the row source is a value List as this -
"01";"02";"91";"92".
Although they are numbers, txtstatus is a text field because I
wanted
to
force the leading 0's.

On the same subform is an unbound control (txtstatusdesc). I want
this
value
to equal the value of a field also called txtstatus that is held
in
a
table
called StatusCodes and where the the value of cmbstatus on my
subform
equals
the value of txtstatuscode in the StatusCode table.

I had the control source as :
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]=Forms![frmMain]![frmVehicle].Form![cmbstatus]")

but this gave me an #Error that blinked. So Daniel, in my
previous
post,
gave me this:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

But this gives me a #Name?

I thought my original formula follwed the correct protocol for
referring
to
controls on subforms. I've also tried:
1. using the control source txtstatus instead of the control name
cmbstatus
in the formula
2. leaving the control source of the unbound control
txtstatusdesc
on
the
sub form blank and putting the DLookUp formula in the Default
value
but
I
still get the same problems

I've checked my spelling of the field and table names and they
are
correct.
This is really frustrating as I thought I was foing the right
thing
with
the
formula and I don't know what else to do.
Could someone please help?
Thanks
Tony

 

[Tony Williams]

Hi John The Dlookup is looking up data in a table and comparing it with a
control on the subform.
That should be OK shouldn't it?
Tony
PS I'm in the UK and it's 18.40 so calling it a day till tomorrow so if I
don't reply it's because I'm leaving it till tomorrow.

 

[Tony Williams]

Hi Mich thanks for that.
I'm in the UK and it's 18.40 so calling it a day till tomorrow so if I don't
reply it's because I'm leaving it till tomorrow.
Really really appreciate it I'll let you know how I get on tomorrow.
Tony

Try this as the control source for txtstatusdesc:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" & Me![cmbstatus]
& "'")

Mich the cmbstatus control is a combobox where the control source is
txtstatus and the row source is a value list. Could it be that DLookup
doesn't like combo boxes?
Tony

Let me get this right -
You have a main form called "frmMain"
This form has a subform called "frmVehicle" (are you sure?)
On that subform is a control named "cmbstatus"
cmbstatus shows information when frmMain is opened.

I tested this code in my own database and it worked properly, using the
same
syntax you are using:
debug.Print Forms![frmMain]![frmFunctionalAreas subform].Form![ID]
672
Something is misspelled...

Thanks Mich. Did that and got an error message that said
Runtime error 2465
Database can't find the field frmvehicle referred to in your expression

Odd because the frmvehicle is a form not a field?
Any ideas?
Thanks for sticking with me
Tony

:

You can get into the code window using Alt - F11, then View -
Immediate
window.
Since you are referring to some data on
[Forms]![frmMain]![frmVehicle],
it
must be open where you can see that data, e.g. cmbstatus.

Let's say cmbstatus is ABC on the form. Does this statement in the
immediate
window return ABC after you hit enter?
Debug.Print [Forms]![frmMain]![frmVehicle].[Form]![cmbstatus]
<ENTER>
ABC
If nothing shows up then you are not referencing it properly - the
form
name
or control name is spelled wrong or you are using the wrong syntax for
this
form/subform name. If this statement is wrong the DLookup will
produce
nothing either.


message
Hi Mich. Thanks for the suggestions. However I'm not quite sure how
to
use
the Immediate window. I can only open that when the form is in
design
mode
can't I? I did try to do what you suggested and typed the first
debug
statement into the window and pressed enter and nothing happened?

"When you use an expression as your control source - don't use a
field
name
from your query or table - use a name that tells what you are going
(e.g.
txtStatusLookup) or you will get the #NAME error."

When you say this do you mean the control where the DLookupformula
is
the
control source? In this case that control is called txtstatusdesc
which
isn't
a field name.

Also I've just realised that the form frmVehicle is used to display
a
tabcontrol and the control where the formula is is on the first page
of
that
tab control. I did see a reference to this in a Google lookup and it
said
that shouldn't make any difference as the control is related to the
subform
and not the tabcontrol. is that correct?
Thanks
Tony



:

With frmMain open, test the syntax of your statements in the
Immediate
Window available from your Visual Basic Code window.

Debug.print "[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")
You should see something like:
[txtstatuscode]='xyz'
Where xyz is your status, assuming it is text. Then try
Debug.print
DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='xyz'")
Debug.print
DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='"
&
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

You can try many different pieces of code in the immediate window
to
test
syntax, and make adjustment to your code based on error messages.

When you use an expression as your control source - don't use a
field
name
from your query or table - use a name that tells what you are going
(e.g.
txtStatusLookup) or you will get the #NAME error.

Mich


With your
message
I'm reposting this question as I recognise that I didn't give
enough
explanation in my first post. So here is the full story.

I have a combobox control (cmbstatus) on a subform (frmvehicle)
which
is
on
a form (frmMain). The control source of cmbstatus is txtstatus of
my
main
table and the row source is a value List as this -
"01";"02";"91";"92".
Although they are numbers, txtstatus is a text field because I
wanted
to
force the leading 0's.

On the same subform is an unbound control (txtstatusdesc). I want
this
value
to equal the value of a field also called txtstatus that is held
in
a
table
called StatusCodes and where the the value of cmbstatus on my
subform
equals
the value of txtstatuscode in the StatusCode table.

I had the control source as :
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]=Forms![frmMain]![frmVehicle].Form![cmbstatus]")

but this gave me an #Error that blinked. So Daniel, in my
previous
post,
gave me this:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

But this gives me a #Name?

I thought my original formula follwed the correct protocol for
referring
to
controls on subforms. I've also tried:
1. using the control source txtstatus instead of the control name
cmbstatus
in the formula
2. leaving the control source of the unbound control
txtstatusdesc
on
the
sub form blank and putting the DLookUp formula in the Default
value
but
I
still get the same problems

I've checked my spelling of the field and table names and they
are
correct.
This is really frustrating as I thought I was foing the right
thing
with
the
formula and I don't know what else to do.
Could someone please help?
Thanks
Tony

 

[Tony Williams]

Good morning Mich. I thought that after a night's sleep the solution would
come to mind in a flash, unfortunately it didn't! Anyway I tried your last
suggestion and that gave me #name? too. Is John Vinson's comment relevant? I
thought that you could use Dlookup to compare values from a table to a form?

It did occur to me that Icould perhaps use some if statements instead using,
given that there are only 4 codes but agree this is cumbersome and means that
the user would have to get into the if statement if they added more codes.
What do you think?
Thanks again
Tony

Try this as the control source for txtstatusdesc:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" & Me![cmbstatus]
& "'")

Mich the cmbstatus control is a combobox where the control source is
txtstatus and the row source is a value list. Could it be that DLookup
doesn't like combo boxes?
Tony

Let me get this right -
You have a main form called "frmMain"
This form has a subform called "frmVehicle" (are you sure?)
On that subform is a control named "cmbstatus"
cmbstatus shows information when frmMain is opened.

I tested this code in my own database and it worked properly, using the
same
syntax you are using:
debug.Print Forms![frmMain]![frmFunctionalAreas subform].Form![ID]
672
Something is misspelled...

Thanks Mich. Did that and got an error message that said
Runtime error 2465
Database can't find the field frmvehicle referred to in your expression

Odd because the frmvehicle is a form not a field?
Any ideas?
Thanks for sticking with me
Tony

:

You can get into the code window using Alt - F11, then View -
Immediate
window.
Since you are referring to some data on
[Forms]![frmMain]![frmVehicle],
it
must be open where you can see that data, e.g. cmbstatus.

Let's say cmbstatus is ABC on the form. Does this statement in the
immediate
window return ABC after you hit enter?
Debug.Print [Forms]![frmMain]![frmVehicle].[Form]![cmbstatus]
<ENTER>
ABC
If nothing shows up then you are not referencing it properly - the
form
name
or control name is spelled wrong or you are using the wrong syntax for
this
form/subform name. If this statement is wrong the DLookup will
produce
nothing either.


message
Hi Mich. Thanks for the suggestions. However I'm not quite sure how
to
use
the Immediate window. I can only open that when the form is in
design
mode
can't I? I did try to do what you suggested and typed the first
debug
statement into the window and pressed enter and nothing happened?

"When you use an expression as your control source - don't use a
field
name
from your query or table - use a name that tells what you are going
(e.g.
txtStatusLookup) or you will get the #NAME error."

When you say this do you mean the control where the DLookupformula
is
the
control source? In this case that control is called txtstatusdesc
which
isn't
a field name.

Also I've just realised that the form frmVehicle is used to display
a
tabcontrol and the control where the formula is is on the first page
of
that
tab control. I did see a reference to this in a Google lookup and it
said
that shouldn't make any difference as the control is related to the
subform
and not the tabcontrol. is that correct?
Thanks
Tony



:

With frmMain open, test the syntax of your statements in the
Immediate
Window available from your Visual Basic Code window.

Debug.print "[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")
You should see something like:
[txtstatuscode]='xyz'
Where xyz is your status, assuming it is text. Then try
Debug.print
DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='xyz'")
Debug.print
DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='"
&
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

You can try many different pieces of code in the immediate window
to
test
syntax, and make adjustment to your code based on error messages.

When you use an expression as your control source - don't use a
field
name
from your query or table - use a name that tells what you are going
(e.g.
txtStatusLookup) or you will get the #NAME error.

Mich


With your
message
I'm reposting this question as I recognise that I didn't give
enough
explanation in my first post. So here is the full story.

I have a combobox control (cmbstatus) on a subform (frmvehicle)
which
is
on
a form (frmMain). The control source of cmbstatus is txtstatus of
my
main
table and the row source is a value List as this -
"01";"02";"91";"92".
Although they are numbers, txtstatus is a text field because I
wanted
to
force the leading 0's.

On the same subform is an unbound control (txtstatusdesc). I want
this
value
to equal the value of a field also called txtstatus that is held
in
a
table
called StatusCodes and where the the value of cmbstatus on my
subform
equals
the value of txtstatuscode in the StatusCode table.

I had the control source as :
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]=Forms![frmMain]![frmVehicle].Form![cmbstatus]")

but this gave me an #Error that blinked. So Daniel, in my
previous
post,
gave me this:
=DLookUp("[txtstatus]","[StatusCodes]","[txtstatuscode]='" &
[Forms]![frmMain]![frmVehicle].[Form]![cmbstatus] & "'")

But this gives me a #Name?

I thought my original formula follwed the correct protocol for
referring
to
controls on subforms. I've also tried:
1. using the control source txtstatus instead of the control name
cmbstatus
in the formula
2. leaving the control source of the unbound control
txtstatusdesc
on
the
sub form blank and putting the DLookUp formula in the Default
value
but
I
still get the same problems

I've checked my spelling of the field and table names and they
are
correct.
This is really frustrating as I thought I was foing the right
thing
with
the
formula and I don't know what else to do.
Could someone please help?
Thanks
Tony

 

[Tony Williams]

Hi success! Of a sorts. I ended up using a nested IIf statement and got the
result I was looking for. A bit cumbersome but at least I can now move on to
my next problem.

many thanks for sticking with me Mich, still can't understand why it didn't
work?

Tony