open a form through a subform in access 2000

[Guest]

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

 

[Ken Snell [MVP]]

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]

 

[Guest]

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.

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>

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

 

[Ken Snell [MVP]]

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>

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.

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>

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

 

[Guest]

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

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>

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.

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>

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

 

[Ken Snell [MVP]]

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>

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

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>

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.

:

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>

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

 

[Guest]

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

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>

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

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>



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.

:

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>

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

 

[Ken Snell [MVP]]

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>

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

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>

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


:

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>



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.

:

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>

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

 

[Guest]

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

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>

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

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>

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


:

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>



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.

:

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>

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

 

[Ken Snell [MVP]]

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>

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

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>

Thanks again, I am still getting #Name? in the description field on

my
sub

form.

:

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>

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


:

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>



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.

:

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>

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

 

[Guest]

Is there an easy way to get this information? Or do I have to go into each
form and copy and paste?

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>

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

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>

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

:

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>

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


:

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>



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.

:

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>

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

 

[Guest]

Okay, let me know if you need more info, to bad we cant attach pictures to
the forum :-(

Main form Comes from the Table ECO LOG Control
ECO NUMBER ECO_NUMBER
DATE: DATE_RELEASED
CHANGE CHANGE
STATUS (this comes from a separate table) STATUS
PRODUCT PRODUCT

Sub Form Comes from the table AFFECTED PARTS
Part_Number Part_Number
REVISION REVISION
Description Description

The forms are linked by ECO_NUMBER

The Table AFFECTED PARTS has the following fields
ID1, Autonumber
ECO_Number, text
Part_Number, text
REVISION, text
Description, text

I want to be able to type the part number in the subform and it bring up the
latest revision and description, then when I change the revision it will
write a new record, with the part number, revision and the description. These
are all plain text boxes on the forms.

Thanks for going to so much trouble!
Tammy

Is there an easy way to get this information? Or do I have to go into each
form and copy and paste?

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>

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

:

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>

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

:

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>

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


:

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>



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.

:

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>

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

 

[Ken Snell [MVP]]

First thing I note here is that your DLookup function expression is using a
table or query named "Documents", but none of the table information that
you've provided shows a table named Documents. Are you sure that the DLookup
function is using the right name for the table/query (second argument in the
function's arguments)?

From your description, you are wanting to display the current record and
then, if you make any change to that record, you want the form to create a
new record with that edited information. Unless you're doing this by copying
the current record into a new record and then editing that new record, the
form won't do this automatically for you. To do it automatically, your
entire form setup will need to be changed to not use the table as the
recordsource, but rather use a temporary table that is then bound to the
form, fill that temporary table with the records from the table, and use
code to create new records whenever a control's value becomes dirty on the
form because of editing (all must be done with programming!). So, I'm not
completely clear here on what your setup is intending to do, but I offer
this information just to provide some perspective.

I cannot tell from your posted info what the two columns of info are -- is
the left column the name of the controls on the forms, and the right column
the name of the field to which the control is bound?

Note that you're using Date as the name of a field or control on the main
form. This is not good practice, as Date is the name of a VBA function, and
this also can confuse ACCESS. For more info on which words should not be
used as field names, control names, etc., see this Knowledge Base article:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

I am assuming, based on your previous success, that you're using the correct
names for the subform control in your reference to the control on the
subform. Remember that the full reference is

Forms!NameOfMainForm!NameOfSubformControlOnMainForm!NameOfControlOnSubform

NameOfSubformControlOnMainForm is the name of the subform control on the
main form (the control that actually holds the subform object); its name may
or may not be the same as the form that is serving as the Source Object for
that control.

Let me know if this info helps move things forward..
--

Ken Snell
<MS ACCESS MVP>

Okay, let me know if you need more info, to bad we cant attach pictures to
the forum :-(

Main form Comes from the Table ECO LOG Control
ECO NUMBER ECO_NUMBER
DATE: DATE_RELEASED
CHANGE CHANGE
STATUS (this comes from a separate table) STATUS
PRODUCT PRODUCT

Sub Form Comes from the table AFFECTED PARTS
Part_Number Part_Number
REVISION REVISION
Description Description

The forms are linked by ECO_NUMBER

The Table AFFECTED PARTS has the following fields
ID1, Autonumber
ECO_Number, text
Part_Number, text
REVISION, text
Description, text

I want to be able to type the part number in the subform and it bring up the
latest revision and description, then when I change the revision it will
write a new record, with the part number, revision and the description. These
are all plain text boxes on the forms.

Thanks for going to so much trouble!
Tammy

Is there an easy way to get this information? Or do I have to go into each
form and copy and paste?

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>



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

:

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>

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

:

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>

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


:

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>



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.

:

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>

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