open a form through a subform in access 2000

I hope I can explain this correctly :-)

I have a form that has a subform. there is a field in the subform that I
want to double click on to open a totaly different form. It works great when
I open the subform as a form, but I cant get it to work once it is in my main
form. I am using the openform in a macro.

Form name: Documents
View: Form
Filter Name:
Where Condition: [Part_Number]"this is the control on form
Documents"=[Forms]![AFFECTED PARTS]![Part_Number] "this is the subform
information"

My main form name is ECO FORM which AFFECTED PARTS is the subform of.



Thanks for any or all help
Please be easy with me I am very new :-)

You're using a macro to do this, so it needs a full reference to the subform
through the main form:

[Part_Number]=[Forms]![MainFormName]![AFFECTED PARTS]![Part_Number]

Above expression assumes that the name of the subform control (the control
on the main form that is holding the subform) is also named AFFECTED PARTS.

However, to make the macro more generic, change the expression to this so
that it doesn't need the form reference at all:

[Part_Number]=[Part_Number]

If for some reason this errors, then use

"[Part_Number]=" & [Part_Number]


--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
I hope I can explain this correctly :-)

I have a form that has a subform. there is a field in the subform that I
want to double click on to open a totaly different form. It works great
when
I open the subform as a form, but I cant get it to work once it is in my
main
form. I am using the openform in a macro.

Form name: Documents
View: Form
Filter Name:
Where Condition: [Part_Number]"this is the control on form
Documents"=[Forms]![AFFECTED PARTS]![Part_Number] "this is the subform
information"

My main form name is ECO FORM which AFFECTED PARTS is the subform of.



Thanks for any or all help
Please be easy with me I am very new :-)

Hi Ken,
Thank you so much it works great. I couldnt get the generic one to work, it
opens the form okay, but it doesnt go to the correct record for some reason,
just the first one.

Maybe you could help me with another thing :-)
Same forms and everything but when I enter a new part number revision in the
sub form I would like for the description from Affected Parts to populate the
form.

I tried this:
=DLookUp("[Description]", "Documents", _"[Part_number] = " & Forms![ECO
FORM]![AFFECTED PARTS]![Description])

But it seems like I cant enter a new part number in the subform, I guess if
it doesnt find any matches it doesnt let me add one.

"Ken Snell [MVP]" wrote:

You're using a macro to do this, so it needs a full reference to the subform
through the main form:

[Part_Number]=[Forms]![MainFormName]![AFFECTED PARTS]![Part_Number]

Above expression assumes that the name of the subform control (the control
on the main form that is holding the subform) is also named AFFECTED PARTS.

However, to make the macro more generic, change the expression to this so
that it doesn't need the form reference at all:

[Part_Number]=[Part_Number]

If for some reason this errors, then use

"[Part_Number]=" & [Part_Number]


--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
I hope I can explain this correctly :-)

I have a form that has a subform. there is a field in the subform that I
want to double click on to open a totaly different form. It works great
when
I open the subform as a form, but I cant get it to work once it is in my
main
form. I am using the openform in a macro.

Form name: Documents
View: Form
Filter Name:
Where Condition: [Part_Number]"this is the control on form
Documents"=[Forms]![AFFECTED PARTS]![Part_Number] "this is the subform
information"

My main form name is ECO FORM which AFFECTED PARTS is the subform of.



Thanks for any or all help
Please be easy with me I am very new :-)

My last "generic" example was not quite correct:

However, to make the macro more generic, change the expression to this so
that it doesn't need the form reference at all:
[Part_Number]=[Part_Number]

If for some reason this errors, then use

="[Part_Number]=" & [Part_Number]

For your second question, is there a part description in the table for the
"new" part number? If yes, using DLookup function is one way to get that
result. The expression could be the control source of a textbox that will
show that result, so long as it's ok if that textbox is not needing to be
bound to a field in the subform's record source.

Where are you trying to use this expression that you posted? If in control
source, you need to get rid of the _ character that is between the 2nd and
3rd arguments.
--

Ken Snell
MS ACCESS MVP



"Tammy" wrote in message
...
Hi Ken,
Thank you so much it works great. I couldnt get the generic one to work,
it
opens the form okay, but it doesnt go to the correct record for some
reason,
just the first one.

Maybe you could help me with another thing :-)
Same forms and everything but when I enter a new part number revision in
the
sub form I would like for the description from Affected Parts to populate
the
form.

I tried this:
=DLookUp("[Description]", "Documents", _"[Part_number] = " & Forms![ECO
FORM]![AFFECTED PARTS]![Description])

But it seems like I cant enter a new part number in the subform, I guess
if
it doesnt find any matches it doesnt let me add one.

"Ken Snell [MVP]" wrote:

You're using a macro to do this, so it needs a full reference to the
subform
through the main form:

[Part_Number]=[Forms]![MainFormName]![AFFECTED PARTS]![Part_Number]

Above expression assumes that the name of the subform control (the
control
on the main form that is holding the subform) is also named AFFECTED
PARTS.

However, to make the macro more generic, change the expression to this
so
that it doesn't need the form reference at all:

[Part_Number]=[Part_Number]

If for some reason this errors, then use

"[Part_Number]=" & [Part_Number]


--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
I hope I can explain this correctly :-)

I have a form that has a subform. there is a field in the subform that
I
want to double click on to open a totaly different form. It works
great
when
I open the subform as a form, but I cant get it to work once it is in
my
main
form. I am using the openform in a macro.

Form name: Documents
View: Form
Filter Name:
Where Condition: [Part_Number]"this is the control on form
Documents"=[Forms]![AFFECTED PARTS]![Part_Number] "this is the subform
information"

My main form name is ECO FORM which AFFECTED PARTS is the subform of.



Thanks for any or all help
Please be easy with me I am very new :-)

Hi again and thanks!!,
But I still can’t get the generic one to work; it goes to the first record,
and not the one in the sub form. Was I supposed to put it in the macro? Or
does it go somewhere else?
The second question.
I know there has got to be away to do this :-) I probably just have my
fields mixed up.
Here are my fields in my table called AFFECTED PARTS
ID1, Autonumber
ECO_Number, text
Part_Number, text
REVISION, text
Description, text
I created the subform AFFECTED PARTS and put it in the main form ECO FORM
with the parent/child being the ECO number.
What I want to do, is be able to type the part number in the filed and then
have the form populate with the current revision and description. Which are
already in the table, AFFECTED PARTS.
When I tried =DLookUp("[Description]", "Documents", _"[Part_number] = " &
Forms![ECO FORM]![AFFECTED PARTS]![Description]) in the control source, I put
it in the description text box of affected parts, I just got #Name?, so that
tells me that the fields aren’t matching.

Thanks for your help!!
Tammy


"Ken Snell [MVP]" wrote:

My last "generic" example was not quite correct:

However, to make the macro more generic, change the expression to this so
that it doesn't need the form reference at all:
[Part_Number]=[Part_Number]

If for some reason this errors, then use

="[Part_Number]=" & [Part_Number]

For your second question, is there a part description in the table for the
"new" part number? If yes, using DLookup function is one way to get that
result. The expression could be the control source of a textbox that will
show that result, so long as it's ok if that textbox is not needing to be
bound to a field in the subform's record source.

Where are you trying to use this expression that you posted? If in control
source, you need to get rid of the _ character that is between the 2nd and
3rd arguments.
--

Ken Snell
MS ACCESS MVP



"Tammy" wrote in message
...
Hi Ken,
Thank you so much it works great. I couldnt get the generic one to work,
it
opens the form okay, but it doesnt go to the correct record for some
reason,
just the first one.

Maybe you could help me with another thing :-)
Same forms and everything but when I enter a new part number revision in
the
sub form I would like for the description from Affected Parts to populate
the
form.

I tried this:
=DLookUp("[Description]", "Documents", _"[Part_number] = " & Forms![ECO
FORM]![AFFECTED PARTS]![Description])

But it seems like I cant enter a new part number in the subform, I guess
if
it doesnt find any matches it doesnt let me add one.

"Ken Snell [MVP]" wrote:

You're using a macro to do this, so it needs a full reference to the
subform
through the main form:

[Part_Number]=[Forms]![MainFormName]![AFFECTED PARTS]![Part_Number]

Above expression assumes that the name of the subform control (the
control
on the main form that is holding the subform) is also named AFFECTED
PARTS.

However, to make the macro more generic, change the expression to this
so
that it doesn't need the form reference at all:

[Part_Number]=[Part_Number]

If for some reason this errors, then use

"[Part_Number]=" & [Part_Number]


--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
I hope I can explain this correctly :-)

I have a form that has a subform. there is a field in the subform that
I
want to double click on to open a totaly different form. It works
great
when
I open the subform as a form, but I cant get it to work once it is in
my
main
form. I am using the openform in a macro.

Form name: Documents
View: Form
Filter Name:
Where Condition: [Part_Number]"this is the control on form
Documents"=[Forms]![AFFECTED PARTS]![Part_Number] "this is the subform
information"

My main form name is ECO FORM which AFFECTED PARTS is the subform of.



Thanks for any or all help
Please be easy with me I am very new :-)

Ah, made an error. Because you're calling the macro from the subform, it
won't find the Part Number control on the subform using the generic setup;
it'll look for that control on the main form instead. So forget that
approach :-)

You still have the _ character between the second and third arguments. Try
this:

=DLookUp("Description", "Documents", "[Part_number] = " & Forms![ECO
FORM]![AFFECTED PARTS]![Description])

--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
Hi again and thanks!!,
But I still can't get the generic one to work; it goes to the first
record,
and not the one in the sub form. Was I supposed to put it in the macro? Or
does it go somewhere else?
The second question.
I know there has got to be away to do this :-) I probably just have my
fields mixed up.
Here are my fields in my table called AFFECTED PARTS
ID1, Autonumber
ECO_Number, text
Part_Number, text
REVISION, text
Description, text
I created the subform AFFECTED PARTS and put it in the main form ECO FORM
with the parent/child being the ECO number.
What I want to do, is be able to type the part number in the filed and
then
have the form populate with the current revision and description. Which
are
already in the table, AFFECTED PARTS.
When I tried =DLookUp("[Description]", "Documents", _"[Part_number] = " &
Forms![ECO FORM]![AFFECTED PARTS]![Description]) in the control source, I
put
it in the description text box of affected parts, I just got #Name?, so
that
tells me that the fields aren't matching.

Thanks for your help!!
Tammy


"Ken Snell [MVP]" wrote:

My last "generic" example was not quite correct:

However, to make the macro more generic, change the expression to this
so
that it doesn't need the form reference at all:
[Part_Number]=[Part_Number]

If for some reason this errors, then use

="[Part_Number]=" & [Part_Number]

For your second question, is there a part description in the table for
the
"new" part number? If yes, using DLookup function is one way to get that
result. The expression could be the control source of a textbox that
will
show that result, so long as it's ok if that textbox is not needing to
be
bound to a field in the subform's record source.

Where are you trying to use this expression that you posted? If in
control
source, you need to get rid of the _ character that is between the 2nd
and
3rd arguments.
--

Ken Snell
MS ACCESS MVP



"Tammy" wrote in message
...
Hi Ken,
Thank you so much it works great. I couldnt get the generic one to
work,
it
opens the form okay, but it doesnt go to the correct record for some
reason,
just the first one.

Maybe you could help me with another thing :-)
Same forms and everything but when I enter a new part number revision
in
the
sub form I would like for the description from Affected Parts to
populate
the
form.

I tried this:
=DLookUp("[Description]", "Documents", _"[Part_number] = " &
Forms![ECO
FORM]![AFFECTED PARTS]![Description])

But it seems like I cant enter a new part number in the subform, I
guess
if
it doesnt find any matches it doesnt let me add one.

"Ken Snell [MVP]" wrote:

You're using a macro to do this, so it needs a full reference to the
subform
through the main form:

[Part_Number]=[Forms]![MainFormName]![AFFECTED PARTS]![Part_Number]

Above expression assumes that the name of the subform control (the
control
on the main form that is holding the subform) is also named AFFECTED
PARTS.

However, to make the macro more generic, change the expression to
this
so
that it doesn't need the form reference at all:

[Part_Number]=[Part_Number]

If for some reason this errors, then use

"[Part_Number]=" & [Part_Number]


--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
I hope I can explain this correctly :-)

I have a form that has a subform. there is a field in the subform
that
I
want to double click on to open a totaly different form. It works
great
when
I open the subform as a form, but I cant get it to work once it is
in
my
main
form. I am using the openform in a macro.

Form name: Documents
View: Form
Filter Name:
Where Condition: [Part_Number]"this is the control on form
Documents"=[Forms]![AFFECTED PARTS]![Part_Number] "this is the
subform
information"

My main form name is ECO FORM which AFFECTED PARTS is the subform
of.



Thanks for any or all help
Please be easy with me I am very new :-)

Thanks again, I am still getting #Name? in the description field on my sub
form.

"Ken Snell [MVP]" wrote:

Ah, made an error. Because you're calling the macro from the subform, it
won't find the Part Number control on the subform using the generic setup;
it'll look for that control on the main form instead. So forget that
approach :-)

You still have the _ character between the second and third arguments. Try
this:

=DLookUp("Description", "Documents", "[Part_number] = " & Forms![ECO
FORM]![AFFECTED PARTS]![Description])

--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
Hi again and thanks!!,
But I still can't get the generic one to work; it goes to the first
record,
and not the one in the sub form. Was I supposed to put it in the macro? Or
does it go somewhere else?
The second question.
I know there has got to be away to do this :-) I probably just have my
fields mixed up.
Here are my fields in my table called AFFECTED PARTS
ID1, Autonumber
ECO_Number, text
Part_Number, text
REVISION, text
Description, text
I created the subform AFFECTED PARTS and put it in the main form ECO FORM
with the parent/child being the ECO number.
What I want to do, is be able to type the part number in the filed and
then
have the form populate with the current revision and description. Which
are
already in the table, AFFECTED PARTS.
When I tried =DLookUp("[Description]", "Documents", _"[Part_number] = " &
Forms![ECO FORM]![AFFECTED PARTS]![Description]) in the control source, I
put
it in the description text box of affected parts, I just got #Name?, so
that
tells me that the fields aren't matching.

Thanks for your help!!
Tammy


"Ken Snell [MVP]" wrote:

My last "generic" example was not quite correct:

However, to make the macro more generic, change the expression to this
so
that it doesn't need the form reference at all:
[Part_Number]=[Part_Number]

If for some reason this errors, then use

="[Part_Number]=" & [Part_Number]

For your second question, is there a part description in the table for
the
"new" part number? If yes, using DLookup function is one way to get that
result. The expression could be the control source of a textbox that
will
show that result, so long as it's ok if that textbox is not needing to
be
bound to a field in the subform's record source.

Where are you trying to use this expression that you posted? If in
control
source, you need to get rid of the _ character that is between the 2nd
and
3rd arguments.
--

Ken Snell
MS ACCESS MVP



"Tammy" wrote in message
...
Hi Ken,
Thank you so much it works great. I couldnt get the generic one to
work,
it
opens the form okay, but it doesnt go to the correct record for some
reason,
just the first one.

Maybe you could help me with another thing :-)
Same forms and everything but when I enter a new part number revision
in
the
sub form I would like for the description from Affected Parts to
populate
the
form.

I tried this:
=DLookUp("[Description]", "Documents", _"[Part_number] = " &
Forms![ECO
FORM]![AFFECTED PARTS]![Description])

But it seems like I cant enter a new part number in the subform, I
guess
if
it doesnt find any matches it doesnt let me add one.

"Ken Snell [MVP]" wrote:

You're using a macro to do this, so it needs a full reference to the
subform
through the main form:

[Part_Number]=[Forms]![MainFormName]![AFFECTED PARTS]![Part_Number]

Above expression assumes that the name of the subform control (the
control
on the main form that is holding the subform) is also named AFFECTED
PARTS.

However, to make the macro more generic, change the expression to
this
so
that it doesn't need the form reference at all:

[Part_Number]=[Part_Number]

If for some reason this errors, then use

"[Part_Number]=" & [Part_Number]


--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
I hope I can explain this correctly :-)

I have a form that has a subform. there is a field in the subform
that
I
want to double click on to open a totaly different form. It works
great
when
I open the subform as a form, but I cant get it to work once it is
in
my
main
form. I am using the openform in a macro.

Form name: Documents
View: Form
Filter Name:
Where Condition: [Part_Number]"this is the control on form
Documents"=[Forms]![AFFECTED PARTS]![Part_Number] "this is the
subform
information"

My main form name is ECO FORM which AFFECTED PARTS is the subform
of.



Thanks for any or all help
Please be easy with me I am very new :-)

You're using the control named Description as the source of the "part
number" value that is being used by the DLookup .. I think that is creating
a circular process? Use the correct name for the control that contains the
part number.

=DLookUp("Description", "Documents", "[Part_number] = " & Forms![ECO
FORM]![AFFECTED PARTS]![PutCorrectNameHere])

--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
Thanks again, I am still getting #Name? in the description field on my
sub
form.

"Ken Snell [MVP]" wrote:

Ah, made an error. Because you're calling the macro from the subform, it
won't find the Part Number control on the subform using the generic
setup;
it'll look for that control on the main form instead. So forget that
approach :-)

You still have the _ character between the second and third arguments.
Try
this:

=DLookUp("Description", "Documents", "[Part_number] = " & Forms![ECO
FORM]![AFFECTED PARTS]![Description])

--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
Hi again and thanks!!,
But I still can't get the generic one to work; it goes to the first
record,
and not the one in the sub form. Was I supposed to put it in the
macro? Or
does it go somewhere else?
The second question.
I know there has got to be away to do this :-) I probably just have my
fields mixed up.
Here are my fields in my table called AFFECTED PARTS
ID1, Autonumber
ECO_Number, text
Part_Number, text
REVISION, text
Description, text
I created the subform AFFECTED PARTS and put it in the main form ECO
FORM
with the parent/child being the ECO number.
What I want to do, is be able to type the part number in the filed and
then
have the form populate with the current revision and description.
Which
are
already in the table, AFFECTED PARTS.
When I tried =DLookUp("[Description]", "Documents", _"[Part_number] =
" &
Forms![ECO FORM]![AFFECTED PARTS]![Description]) in the control
source, I
put
it in the description text box of affected parts, I just got #Name?,
so
that
tells me that the fields aren't matching.

Thanks for your help!!
Tammy


"Ken Snell [MVP]" wrote:

My last "generic" example was not quite correct:

However, to make the macro more generic, change the expression to
this
so
that it doesn't need the form reference at all:
[Part_Number]=[Part_Number]

If for some reason this errors, then use

="[Part_Number]=" & [Part_Number]

For your second question, is there a part description in the table
for
the
"new" part number? If yes, using DLookup function is one way to get
that
result. The expression could be the control source of a textbox that
will
show that result, so long as it's ok if that textbox is not needing
to
be
bound to a field in the subform's record source.

Where are you trying to use this expression that you posted? If in
control
source, you need to get rid of the _ character that is between the
2nd
and
3rd arguments.
--

Ken Snell
MS ACCESS MVP



"Tammy" wrote in message
...
Hi Ken,
Thank you so much it works great. I couldnt get the generic one to
work,
it
opens the form okay, but it doesnt go to the correct record for
some
reason,
just the first one.

Maybe you could help me with another thing :-)
Same forms and everything but when I enter a new part number
revision
in
the
sub form I would like for the description from Affected Parts to
populate
the
form.

I tried this:
=DLookUp("[Description]", "Documents", _"[Part_number] = " &
Forms![ECO
FORM]![AFFECTED PARTS]![Description])

But it seems like I cant enter a new part number in the subform, I
guess
if
it doesnt find any matches it doesnt let me add one.

"Ken Snell [MVP]" wrote:

You're using a macro to do this, so it needs a full reference to
the
subform
through the main form:

[Part_Number]=[Forms]![MainFormName]![AFFECTED
PARTS]![Part_Number]

Above expression assumes that the name of the subform control
(the
control
on the main form that is holding the subform) is also named
AFFECTED
PARTS.

However, to make the macro more generic, change the expression
to
this
so
that it doesn't need the form reference at all:

[Part_Number]=[Part_Number]

If for some reason this errors, then use

"[Part_Number]=" & [Part_Number]


--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
I hope I can explain this correctly :-)

I have a form that has a subform. there is a field in the
subform
that
I
want to double click on to open a totaly different form. It
works
great
when
I open the subform as a form, but I cant get it to work once
it is
in
my
main
form. I am using the openform in a macro.

Form name: Documents
View: Form
Filter Name:
Where Condition: [Part_Number]"this is the control on form
Documents"=[Forms]![AFFECTED PARTS]![Part_Number] "this is the
subform
information"

My main form name is ECO FORM which AFFECTED PARTS is the
subform
of.



Thanks for any or all help
Please be easy with me I am very new :-)

Still cant get it to work, maybe I cant use DlookUp and need to see about
doing it some other way :-( oh well. I will keep trying different things,
thank you for your help!!
Tammy

"Ken Snell [MVP]" wrote:

You're using the control named Description as the source of the "part
number" value that is being used by the DLookup .. I think that is creating
a circular process? Use the correct name for the control that contains the
part number.

=DLookUp("Description", "Documents", "[Part_number] = " & Forms![ECO
FORM]![AFFECTED PARTS]![PutCorrectNameHere])

--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
Thanks again, I am still getting #Name? in the description field on my
sub
form.

"Ken Snell [MVP]" wrote:

Ah, made an error. Because you're calling the macro from the subform, it
won't find the Part Number control on the subform using the generic
setup;
it'll look for that control on the main form instead. So forget that
approach :-)

You still have the _ character between the second and third arguments.
Try
this:

=DLookUp("Description", "Documents", "[Part_number] = " & Forms![ECO
FORM]![AFFECTED PARTS]![Description])

--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
Hi again and thanks!!,
But I still can't get the generic one to work; it goes to the first
record,
and not the one in the sub form. Was I supposed to put it in the
macro? Or
does it go somewhere else?
The second question.
I know there has got to be away to do this :-) I probably just have my
fields mixed up.
Here are my fields in my table called AFFECTED PARTS
ID1, Autonumber
ECO_Number, text
Part_Number, text
REVISION, text
Description, text
I created the subform AFFECTED PARTS and put it in the main form ECO
FORM
with the parent/child being the ECO number.
What I want to do, is be able to type the part number in the filed and
then
have the form populate with the current revision and description.
Which
are
already in the table, AFFECTED PARTS.
When I tried =DLookUp("[Description]", "Documents", _"[Part_number] =
" &
Forms![ECO FORM]![AFFECTED PARTS]![Description]) in the control
source, I
put
it in the description text box of affected parts, I just got #Name?,
so
that
tells me that the fields aren't matching.

Thanks for your help!!
Tammy


"Ken Snell [MVP]" wrote:

My last "generic" example was not quite correct:

However, to make the macro more generic, change the expression to
this
so
that it doesn't need the form reference at all:
[Part_Number]=[Part_Number]

If for some reason this errors, then use

="[Part_Number]=" & [Part_Number]

For your second question, is there a part description in the table
for
the
"new" part number? If yes, using DLookup function is one way to get
that
result. The expression could be the control source of a textbox that
will
show that result, so long as it's ok if that textbox is not needing
to
be
bound to a field in the subform's record source.

Where are you trying to use this expression that you posted? If in
control
source, you need to get rid of the _ character that is between the
2nd
and
3rd arguments.
--

Ken Snell
MS ACCESS MVP



"Tammy" wrote in message
...
Hi Ken,
Thank you so much it works great. I couldnt get the generic one to
work,
it
opens the form okay, but it doesnt go to the correct record for
some
reason,
just the first one.

Maybe you could help me with another thing :-)
Same forms and everything but when I enter a new part number
revision
in
the
sub form I would like for the description from Affected Parts to
populate
the
form.

I tried this:
=DLookUp("[Description]", "Documents", _"[Part_number] = " &
Forms![ECO
FORM]![AFFECTED PARTS]![Description])

But it seems like I cant enter a new part number in the subform, I
guess
if
it doesnt find any matches it doesnt let me add one.

"Ken Snell [MVP]" wrote:

You're using a macro to do this, so it needs a full reference to
the
subform
through the main form:

[Part_Number]=[Forms]![MainFormName]![AFFECTED
PARTS]![Part_Number]

Above expression assumes that the name of the subform control
(the
control
on the main form that is holding the subform) is also named
AFFECTED
PARTS.

However, to make the macro more generic, change the expression
to
this
so
that it doesn't need the form reference at all:

[Part_Number]=[Part_Number]

If for some reason this errors, then use

"[Part_Number]=" & [Part_Number]


--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
I hope I can explain this correctly :-)

I have a form that has a subform. there is a field in the
subform
that
I
want to double click on to open a totaly different form. It
works
great
when
I open the subform as a form, but I cant get it to work once
it is
in
my
main
form. I am using the openform in a macro.

Form name: Documents
View: Form
Filter Name:
Where Condition: [Part_Number]"this is the control on form
Documents"=[Forms]![AFFECTED PARTS]![Part_Number] "this is the
subform
information"

My main form name is ECO FORM which AFFECTED PARTS is the
subform
of.



Thanks for any or all help
Please be easy with me I am very new :-)

The DLookup function should work (the name error you're getting is because
the expression is using a name that the form cannot recognize, or that you
have a control and a field named the same but the control is not bound to
that field with the same name).

Can you give me more info about the controls and fields that are in the
subform - names, what fields the controls are bound to, etc.?
--

Ken Snell
MS ACCESS MVP



"Tammy" wrote in message
...
Still cant get it to work, maybe I cant use DlookUp and need to see about
doing it some other way :-( oh well. I will keep trying different things,
thank you for your help!!
Tammy

"Ken Snell [MVP]" wrote:

You're using the control named Description as the source of the "part
number" value that is being used by the DLookup .. I think that is
creating
a circular process? Use the correct name for the control that contains
the
part number.

=DLookUp("Description", "Documents", "[Part_number] = " & Forms![ECO
FORM]![AFFECTED PARTS]![PutCorrectNameHere])

--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
Thanks again, I am still getting #Name? in the description field on
my
sub
form.

"Ken Snell [MVP]" wrote:

Ah, made an error. Because you're calling the macro from the
subform, it
won't find the Part Number control on the subform using the generic
setup;
it'll look for that control on the main form instead. So forget that
approach :-)

You still have the _ character between the second and third
arguments.
Try
this:

=DLookUp("Description", "Documents", "[Part_number] = " & Forms![ECO
FORM]![AFFECTED PARTS]![Description])

--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
Hi again and thanks!!,
But I still can't get the generic one to work; it goes to the
first
record,
and not the one in the sub form. Was I supposed to put it in the
macro? Or
does it go somewhere else?
The second question.
I know there has got to be away to do this :-) I probably just
have my
fields mixed up.
Here are my fields in my table called AFFECTED PARTS
ID1, Autonumber
ECO_Number, text
Part_Number, text
REVISION, text
Description, text
I created the subform AFFECTED PARTS and put it in the main form
ECO
FORM
with the parent/child being the ECO number.
What I want to do, is be able to type the part number in the filed
and
then
have the form populate with the current revision and description.
Which
are
already in the table, AFFECTED PARTS.
When I tried =DLookUp("[Description]", "Documents",
_"[Part_number] =
" &
Forms![ECO FORM]![AFFECTED PARTS]![Description]) in the control
source, I
put
it in the description text box of affected parts, I just got
#Name?,
so
that
tells me that the fields aren't matching.

Thanks for your help!!
Tammy


"Ken Snell [MVP]" wrote:

My last "generic" example was not quite correct:

However, to make the macro more generic, change the expression
to
this
so
that it doesn't need the form reference at all:
[Part_Number]=[Part_Number]

If for some reason this errors, then use

="[Part_Number]=" & [Part_Number]

For your second question, is there a part description in the
table
for
the
"new" part number? If yes, using DLookup function is one way to
get
that
result. The expression could be the control source of a textbox
that
will
show that result, so long as it's ok if that textbox is not
needing
to
be
bound to a field in the subform's record source.

Where are you trying to use this expression that you posted? If
in
control
source, you need to get rid of the _ character that is between
the
2nd
and
3rd arguments.
--

Ken Snell
MS ACCESS MVP



"Tammy" wrote in message
...
Hi Ken,
Thank you so much it works great. I couldnt get the generic
one to
work,
it
opens the form okay, but it doesnt go to the correct record
for
some
reason,
just the first one.

Maybe you could help me with another thing :-)
Same forms and everything but when I enter a new part number
revision
in
the
sub form I would like for the description from Affected Parts
to
populate
the
form.

I tried this:
=DLookUp("[Description]", "Documents", _"[Part_number] = " &
Forms![ECO
FORM]![AFFECTED PARTS]![Description])

But it seems like I cant enter a new part number in the
subform, I
guess
if
it doesnt find any matches it doesnt let me add one.

"Ken Snell [MVP]" wrote:

You're using a macro to do this, so it needs a full
reference to
the
subform
through the main form:

[Part_Number]=[Forms]![MainFormName]![AFFECTED
PARTS]![Part_Number]

Above expression assumes that the name of the subform
control
(the
control
on the main form that is holding the subform) is also named
AFFECTED
PARTS.

However, to make the macro more generic, change the
expression
to
this
so
that it doesn't need the form reference at all:

[Part_Number]=[Part_Number]

If for some reason this errors, then use

"[Part_Number]=" & [Part_Number]


--

Ken Snell
MS ACCESS MVP

"Tammy" wrote in message
...
I hope I can explain this correctly :-)

I have a form that has a subform. there is a field in the
subform
that
I
want to double click on to open a totaly different form.
It
works
great
when
I open the subform as a form, but I cant get it to work
once
it is
in
my
main
form. I am using the openform in a macro.

Form name: Documents
View: Form
Filter Name:
Where Condition: [Part_Number]"this is the control on form
Documents"=[Forms]![AFFECTED PARTS]![Part_Number] "this is
the
subform
information"

My main form name is ECO FORM which AFFECTED PARTS is the
subform
of.



Thanks for any or all help
Please be easy with me I am very new :-)