Trouble opening a form from a subform combo box

[Guest]

I was able to do this once before using a macro, but I have had to make alot
of changes to my database, and now it won't work.

I have a macro to open my form Documents. I have the following code in the
where condtion.
[Part_Number]=Forms![ECO FORM]![subfrmAffectedParts]![tblPartNumber_ID]

I think my problem is Part_Number in Documents is text, and the
tblpartnumber_id is a number. tblpartnumber_id is a combo box.

Thanks

 

[Steve Schapel]

Tammy,

If the problem really is the data type mismatch, this may fix it...
[Part_Number]=Format([Forms]![ECO
FORM]![subfrmAffectedParts]![tblPartNumber_ID])

If tblPartNumber_ID is a number, and you are able to match it to the
data in the [Part_Number] field, then [Part_Number] must also be
comprised of digits, even though it is text type, right? So is there
any reason why you can't just change the data type of [Part_Number] to
Number?

 

[Guest]

Hi Steve,
Thanks for the idea, it just gives me a blank form and doesn’t bring up the
record I am looking for.
I am going to work on getting my Documents table straitened out and see if
that will help I know its not set up right, it is almost a separate
database. It is only set up the way it is because I had to pull all then info
out of a dbase program before IT deleted it or messed it up.

Thanks for the help
Tammy

Tammy,

If the problem really is the data type mismatch, this may fix it...
[Part_Number]=Format([Forms]![ECO
FORM]![subfrmAffectedParts]![tblPartNumber_ID])

If tblPartNumber_ID is a number, and you are able to match it to the
data in the [Part_Number] field, then [Part_Number] must also be
comprised of digits, even though it is text type, right? So is there
any reason why you can't just change the data type of [Part_Number] to
Number?

--
Steve Schapel, Microsoft Access MVP

I was able to do this once before using a macro, but I have had to make alot
of changes to my database, and now it won't work.

I have a macro to open my form Documents. I have the following code in the
where condtion.
[Part_Number]=Forms![ECO FORM]![subfrmAffectedParts]![tblPartNumber_ID]

I think my problem is Part_Number in Documents is text, and the
tblpartnumber_id is a number. tblpartnumber_id is a combo box.

Thanks

 

[Steve Schapel]

Tammy,

In that case, the value of [tblPartNumber_ID] is not being correctly
returned. If it is a combobox, then it could be that the Bound Column
of the combobox is not the same as the data that is displayed in the
control... check the properties of the combobox, in particular its Row
Source, Column Count, Bound Column, and Column Widths properties, and
see if there is a clue there.

 

[Guest]

Control Source: tblPartNumber_ID
Row Source type: Table/Query
Row Source: SELECT [ID] AS xyz_ID_xyz, [Part_Number] & '' AS
xyz_DispExpr_xyz, [Part_Number] FROM tblDocuments ORDER BY [Part_Number];
Bound Column: 1
Column Count: 4
Column widths: 0";0.0007";0.882";2"
Column Heads: Yes

This form is based on a Query if that helps. The query was made by the table
Analyzer. Here is the query SQL
SELECT tblAffectedParts.tblPartNumber_ID, tblAffectedParts.REVISION,
tblAffectedParts.BaseNumber, tblAffectedParts.Extension, tblPartNumber.APDesc
FROM tblAffectedParts LEFT JOIN tblPartNumber ON
tblAffectedParts.tblPartNumber_ID = tblPartNumber.ID;

Tammy,

In that case, the value of [tblPartNumber_ID] is not being correctly
returned. If it is a combobox, then it could be that the Bound Column
of the combobox is not the same as the data that is displayed in the
control... check the properties of the combobox, in particular its Row
Source, Column Count, Bound Column, and Column Widths properties, and
see if there is a clue there.

--
Steve Schapel, Microsoft Access MVP

Hi Steve,
Thanks for the idea, it just gives me a blank form and doesn’t bring up the
record I am looking for.
I am going to work on getting my Documents table straitened out and see if
that will help I know its not set up right, it is almost a separate
database. It is only set up the way it is because I had to pull all then info
out of a dbase program before IT deleted it or messed it up.

Thanks for the help
Tammy

 

[Steve Schapel]

Tammy,

Well, I can't exactly follow what's going on here. But your original
Where Condition argument is obviously not correct.
[Part_Number]=Forms![ECO FORM]![subfrmAffectedParts]![tblPartNumber_ID]
will not work because for a start there is no field called [Part_Number]
in the report's record source (i.e. the query that you have posted).
And then, the value of the [tblPartNumber_ID] combobox is the [ID] field
from the Documents table, but I would imagine that what you are really
wanting to use for your report's comparison criteria is the
[Part_Number] field from the Documents table, so you will either need a
combobox whose bound column points to this field, or else in your where
condition use something like...
Forms![ECO FORM]![subfrmAffectedParts]![tblPartNumber_ID].[Column](2)
Somewhere in this your solution lies.

 

[Guest]

This one is giving me the following error:

unidentifed function
'Forms![ECO FORM]![subfrmAffectedParts]![tblPartNumber_ID]
..[Column]'
in expression

the the field part_number is in my form Documents and also in my
tblPartNumber (Calling it tblDocuments was a mistake) that you see in the
Query. tblPartnumber has the following fields
Part_Number (data type text)
APDesc (data type text)
ID (data type AutoNumber)

The the form Documents was created from a table called Documents and created
from the table not a query
has the following fields:
ID AutoNumber
Part_Number (data type text)
Desc (data type text)
Revision (data type text)
and a few other fields.

tblAffectedParts and has the following fields
Revision (text)
id1 (autonumber)
BaseNumber (text)
Extension (number)
tblPartNumber_ID (number) Lookup field SELECT [ID] AS xyz_ID_xyz,
[Part_Number] & '' AS xyz_DispExpr_xyz, [Part_Number] FROM tblpartnumber
ORDER BY [Part_Number];

and the query qryaffectedparts SQL
SELECT tblAffectedParts.tblPartNumber_ID, tblAffectedParts.REVISION,
tblAffectedParts.BaseNumber, tblAffectedParts.Extension, tblPartNumber.APDesc
FROM tblAffectedParts LEFT JOIN tblPartNumber ON
tblAffectedParts.tblPartNumber_ID = tblPartNumber.ID;

It seems to me that some how, I need to get to the Part_Number field in the
tblPartNumber. But isnt accually part of the subfrmAffectedParts

Tammy,

Well, I can't exactly follow what's going on here. But your original
Where Condition argument is obviously not correct.
[Part_Number]=Forms![ECO FORM]![subfrmAffectedParts]![tblPartNumber_ID]
will not work because for a start there is no field called [Part_Number]
in the report's record source (i.e. the query that you have posted).
And then, the value of the [tblPartNumber_ID] combobox is the [ID] field
from the Documents table, but I would imagine that what you are really
wanting to use for your report's comparison criteria is the
[Part_Number] field from the Documents table, so you will either need a
combobox whose bound column points to this field, or else in your where
condition use something like...
Forms![ECO FORM]![subfrmAffectedParts]![tblPartNumber_ID].[Column](2)
Somewhere in this your solution lies.

--
Steve Schapel, Microsoft Access MVP

Control Source: tblPartNumber_ID
Row Source type: Table/Query
Row Source: SELECT [ID] AS xyz_ID_xyz, [Part_Number] & '' AS
xyz_DispExpr_xyz, [Part_Number] FROM tblDocuments ORDER BY [Part_Number];
Bound Column: 1
Column Count: 4
Column widths: 0";0.0007";0.882";2"
Column Heads: Yes

This form is based on a Query if that helps. The query was made by the table
Analyzer. Here is the query SQL
SELECT tblAffectedParts.tblPartNumber_ID, tblAffectedParts.REVISION,
tblAffectedParts.BaseNumber, tblAffectedParts.Extension, tblPartNumber.APDesc
FROM tblAffectedParts LEFT JOIN tblPartNumber ON
tblAffectedParts.tblPartNumber_ID = tblPartNumber.ID;

 

[Steve Schapel]

Tammy,

I think the problem has to do with the correspondence of the data which
you are trying to match. I tried to explain this in my previous
response, but I will try again now. In effect, you are trying to relate
the values in the Part_Number field in the Documents table with the
values in the tblPartNumber_ID field in the tblAffectedParts table. I
do not think this is correct. I do not think these values relate. In
your tblPartNumber_ID combobox, what you will *see* when you select an
item from the drop-down list is the value of [Part_Number] & ''
(whatever the purpose of the "& ''" is a mystery, doesn't really seem to
make sense, but that's another issue). This is the second column of the
combobox's Row Source query, as you don't see the first column because
its Column Widths property sets the first column to 0" wide, i.e.
hidden. But the first column is the Bound Column, which is the ID
field, i.e. it has the same *values* as the ID field in the
tblPartnumber table, which is an AutoNumber. So, it seems to me, that
you are trying to open the Documents form where the Part_Number field is
equal to the value of the tblPartNumber_ID combobox (*not* what you
actually see in the combobox, which is not its value), which it will
never be equal to. I think this is what you need to sort out.

--
Steve Schapel, Microsoft Access MVP

This one is giving me the following error:

unidentifed function
'Forms![ECO FORM]![subfrmAffectedParts]![tblPartNumber_ID]
.[Column]'
in expression

the the field part_number is in my form Documents and also in my
tblPartNumber (Calling it tblDocuments was a mistake) that you see in the
Query. tblPartnumber has the following fields
Part_Number (data type text)
APDesc (data type text)
ID (data type AutoNumber)

The the form Documents was created from a table called Documents and created
from the table not a query
has the following fields:
ID AutoNumber
Part_Number (data type text)
Desc (data type text)
Revision (data type text)
and a few other fields.

tblAffectedParts and has the following fields
Revision (text)
id1 (autonumber)
BaseNumber (text)
Extension (number)
tblPartNumber_ID (number) Lookup field SELECT [ID] AS xyz_ID_xyz,
[Part_Number] & '' AS xyz_DispExpr_xyz, [Part_Number] FROM tblpartnumber
ORDER BY [Part_Number];

and the query qryaffectedparts SQL
SELECT tblAffectedParts.tblPartNumber_ID, tblAffectedParts.REVISION,
tblAffectedParts.BaseNumber, tblAffectedParts.Extension, tblPartNumber.APDesc
FROM tblAffectedParts LEFT JOIN tblPartNumber ON
tblAffectedParts.tblPartNumber_ID = tblPartNumber.ID;

It seems to me that some how, I need to get to the Part_Number field in the
tblPartNumber. But isnt accually part of the subfrmAffectedParts

 

[Guest]

Hi Steve,
Thanks alot, I do think I understood this from your previous post just
had a fumblying way of writing it down.
I will tell you what I did do, I am still having trouble but it is getting
closer, and maybe you can help me ad the finishing touch
I added Part_Number to my query so it is as follows:

SELECT tblAffectedParts.tblPartNumber_ID, tblAffectedParts.REVISION,
tblAffectedParts.BaseNumber, tblAffectedParts.Extension,
tblPartNumber.APDesc, tblPartNumber.Part_Number
FROM tblAffectedParts LEFT JOIN tblPartNumber ON
tblAffectedParts.tblPartNumber_ID = tblPartNumber.ID;
I also put a text box for it on my form and hid it behind another text box,

I can get the correct record to come up when I put the following code in the
double click of the tblPartNumber_ID in the subform:
[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number]
But when I add the main form
[Part_Number]=[Forms][ECO FORM]![subfrmaffectedparts]![docPart_number]
and try to double click I get the parameter box asking for "Forms!ECO, I
think I am almost there lol.

Thanks,
Tammy

Tammy,

I think the problem has to do with the correspondence of the data which
you are trying to match. I tried to explain this in my previous
response, but I will try again now. In effect, you are trying to relate
the values in the Part_Number field in the Documents table with the
values in the tblPartNumber_ID field in the tblAffectedParts table. I
do not think this is correct. I do not think these values relate. In
your tblPartNumber_ID combobox, what you will *see* when you select an
item from the drop-down list is the value of [Part_Number] & ''
(whatever the purpose of the "& ''" is a mystery, doesn't really seem to
make sense, but that's another issue). This is the second column of the
combobox's Row Source query, as you don't see the first column because
its Column Widths property sets the first column to 0" wide, i.e.
hidden. But the first column is the Bound Column, which is the ID
field, i.e. it has the same *values* as the ID field in the
tblPartnumber table, which is an AutoNumber. So, it seems to me, that
you are trying to open the Documents form where the Part_Number field is
equal to the value of the tblPartNumber_ID combobox (*not* what you
actually see in the combobox, which is not its value), which it will
never be equal to. I think this is what you need to sort out.

--
Steve Schapel, Microsoft Access MVP

This one is giving me the following error:

unidentifed function
'Forms![ECO FORM]![subfrmAffectedParts]![tblPartNumber_ID]
.[Column]'
in expression

the the field part_number is in my form Documents and also in my
tblPartNumber (Calling it tblDocuments was a mistake) that you see in the
Query. tblPartnumber has the following fields
Part_Number (data type text)
APDesc (data type text)
ID (data type AutoNumber)

The the form Documents was created from a table called Documents and created
from the table not a query
has the following fields:
ID AutoNumber
Part_Number (data type text)
Desc (data type text)
Revision (data type text)
and a few other fields.

tblAffectedParts and has the following fields
Revision (text)
id1 (autonumber)
BaseNumber (text)
Extension (number)
tblPartNumber_ID (number) Lookup field SELECT [ID] AS xyz_ID_xyz,
[Part_Number] & '' AS xyz_DispExpr_xyz, [Part_Number] FROM tblpartnumber
ORDER BY [Part_Number];

and the query qryaffectedparts SQL
SELECT tblAffectedParts.tblPartNumber_ID, tblAffectedParts.REVISION,
tblAffectedParts.BaseNumber, tblAffectedParts.Extension, tblPartNumber.APDesc
FROM tblAffectedParts LEFT JOIN tblPartNumber ON
tblAffectedParts.tblPartNumber_ID = tblPartNumber.ID;

It seems to me that some how, I need to get to the Part_Number field in the
tblPartNumber. But isnt accually part of the subfrmAffectedParts

 

[Guest]

<<drop-down list is the value of [Part_Number] & ''Oh the "& ''" might be because I had the part number and description in the
dropdown box before, can I just remove it? or do I have to replace it with
something?

Thanks again!!
Tammy

Hi Steve,
Thanks alot, I do think I understood this from your previous post just
had a fumblying way of writing it down.
I will tell you what I did do, I am still having trouble but it is getting
closer, and maybe you can help me ad the finishing touch
I added Part_Number to my query so it is as follows:

SELECT tblAffectedParts.tblPartNumber_ID, tblAffectedParts.REVISION,
tblAffectedParts.BaseNumber, tblAffectedParts.Extension,
tblPartNumber.APDesc, tblPartNumber.Part_Number
FROM tblAffectedParts LEFT JOIN tblPartNumber ON
tblAffectedParts.tblPartNumber_ID = tblPartNumber.ID;
I also put a text box for it on my form and hid it behind another text box,

I can get the correct record to come up when I put the following code in the
double click of the tblPartNumber_ID in the subform:
[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number]
But when I add the main form
[Part_Number]=[Forms][ECO FORM]![subfrmaffectedparts]![docPart_number]
and try to double click I get the parameter box asking for "Forms!ECO, I
think I am almost there lol.

Thanks,
Tammy

Tammy,

I think the problem has to do with the correspondence of the data which
you are trying to match. I tried to explain this in my previous
response, but I will try again now. In effect, you are trying to relate
the values in the Part_Number field in the Documents table with the
values in the tblPartNumber_ID field in the tblAffectedParts table. I
do not think this is correct. I do not think these values relate. In
your tblPartNumber_ID combobox, what you will *see* when you select an
item from the drop-down list is the value of [Part_Number] & ''
(whatever the purpose of the "& ''" is a mystery, doesn't really seem to
make sense, but that's another issue). This is the second column of the
combobox's Row Source query, as you don't see the first column because
its Column Widths property sets the first column to 0" wide, i.e.
hidden. But the first column is the Bound Column, which is the ID
field, i.e. it has the same *values* as the ID field in the
tblPartnumber table, which is an AutoNumber. So, it seems to me, that
you are trying to open the Documents form where the Part_Number field is
equal to the value of the tblPartNumber_ID combobox (*not* what you
actually see in the combobox, which is not its value), which it will
never be equal to. I think this is what you need to sort out.

--
Steve Schapel, Microsoft Access MVP

This one is giving me the following error:

unidentifed function
'Forms![ECO FORM]![subfrmAffectedParts]![tblPartNumber_ID]
.[Column]'
in expression

the the field part_number is in my form Documents and also in my
tblPartNumber (Calling it tblDocuments was a mistake) that you see in the
Query. tblPartnumber has the following fields
Part_Number (data type text)
APDesc (data type text)
ID (data type AutoNumber)

The the form Documents was created from a table called Documents and created
from the table not a query
has the following fields:
ID AutoNumber
Part_Number (data type text)
Desc (data type text)
Revision (data type text)
and a few other fields.

tblAffectedParts and has the following fields
Revision (text)
id1 (autonumber)
BaseNumber (text)
Extension (number)
tblPartNumber_ID (number) Lookup field SELECT [ID] AS xyz_ID_xyz,
[Part_Number] & '' AS xyz_DispExpr_xyz, [Part_Number] FROM tblpartnumber
ORDER BY [Part_Number];

and the query qryaffectedparts SQL
SELECT tblAffectedParts.tblPartNumber_ID, tblAffectedParts.REVISION,
tblAffectedParts.BaseNumber, tblAffectedParts.Extension, tblPartNumber.APDesc
FROM tblAffectedParts LEFT JOIN tblPartNumber ON
tblAffectedParts.tblPartNumber_ID = tblPartNumber.ID;

It seems to me that some how, I need to get to the Part_Number field in the
tblPartNumber. But isnt accually part of the subfrmAffectedParts

 

[Steve Schapel]

Tammy,

Am I imagining it, or do you keep changing the rules? This is the
first I have heard of "code in the double click of the
tblPartNumber_ID". I thought we were talking about the criteria in a
query. And it is the first time I have heard anything about a
docPart_number too.

Anyway, here's the deal... If this works without incident:
[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number]
.... then it meams that the subfrmaffectedparts form is open. But you
told me that subfrmaffectedparts is a subform on the ECO FORM form.
Something doesn't add up here, but I'm not sure what. If
subfrmaffectedparts is a subform on the ECO FORM form then it is not
open. The ECO FORM form is open, and subfrmaffectedparts is dispayed
within the subform control on the ECO FORM form. If so, the expression
will not work. This should be correct...
[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts]![docPart_number]
.... or, as some would have it...
[Part_Number]=[Forms]![ECO
FORM]![subfrmaffectedparts].[Form]![docPart_number]
.... although I notice you missed out a ! in the expression you posted,
was this a typo, or did you copy/paste from your actual code/SQL?

 

[Guest]

[Part_Number]=Format([Forms]![subfrmaffectedparts]![docPart_Number])

Am I imagining it, or do you keep changing the rules? This is the
first I have heard of "code in the double click of the
tblPartNumber_ID". I thought we were talking about the criteria in a
query. And it is the first time I have heard anything about a
docPart_number too.

Anyway, here's the deal... If this works without incident:
[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number]
.... then it meams that the subfrmaffectedparts form is open. But you
told me that subfrmaffectedparts is a subform on the ECO FORM form.
Something doesn't add up here, but I'm not sure what. If
subfrmaffectedparts is a subform on the ECO FORM form then it is not
open. The ECO FORM form is open, and subfrmaffectedparts is dispayed
within the subform control on the ECO FORM form. If so, the expression
will not work. This should be correct...
[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts]![docPart_number]
.... or, as some would have it...
[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts].[Form]![docPart_number]
.... although I notice you missed out a ! in the expression you posted,
was this a typo, or did you copy/paste from your actual code/SQL?

Hi Steve,
Sorry if it seems I am changing the rules Just trying to figure this
stuff out

This is the
first I have heard of "code in the double click of the

tblPartNumber_ID". <

I basicly put the code here just for testing purposes, so I can test the
subform. And it works from the subform.
I thought we were talking about the criteria in a

query. And it is the first time I have heard anything about a

docPart_number too.<

The subform is made from the query, so I don’t know if we are talking about
the subform or if we are talking about the query, because they are basicly
the same.
I had to put the doc in front of Part_Number because when I added the text
box to the form it said that i was using a control name that already was in
use.

Anyway, here's the deal... If this works without incident:

[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number]<

This works great from the subform, it opens my form on the correct record.
But when I add the main form to the code I get a parameter box asking for
“Forms!ECOâ€
The same thing happens when I use the two codes you gave me;

[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts]![docPart_number]
.... or, as some would have it...
[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts].[Form]![docPart_number]

I believe the missing ! was a type-o

I know it has to be just one little thing I am missing.

Thanks,
Tammy

Tammy,

Am I imagining it, or do you keep changing the rules? This is the
first I have heard of "code in the double click of the
tblPartNumber_ID". I thought we were talking about the criteria in a
query. And it is the first time I have heard anything about a
docPart_number too.

Anyway, here's the deal... If this works without incident:
[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number]
.... then it meams that the subfrmaffectedparts form is open. But you
told me that subfrmaffectedparts is a subform on the ECO FORM form.
Something doesn't add up here, but I'm not sure what. If
subfrmaffectedparts is a subform on the ECO FORM form then it is not
open. The ECO FORM form is open, and subfrmaffectedparts is dispayed
within the subform control on the ECO FORM form. If so, the expression
will not work. This should be correct...
[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts]![docPart_number]
.... or, as some would have it...
[Part_Number]=[Forms]![ECO
FORM]![subfrmaffectedparts].[Form]![docPart_number]
.... although I notice you missed out a ! in the expression you posted,
was this a typo, or did you copy/paste from your actual code/SQL?

--
Steve Schapel, Microsoft Access MVP

Hi Steve,
Thanks alot, I do think I understood this from your previous post just
had a fumblying way of writing it down.
I will tell you what I did do, I am still having trouble but it is getting
closer, and maybe you can help me ad the finishing touch
I added Part_Number to my query so it is as follows:

SELECT tblAffectedParts.tblPartNumber_ID, tblAffectedParts.REVISION,
tblAffectedParts.BaseNumber, tblAffectedParts.Extension,
tblPartNumber.APDesc, tblPartNumber.Part_Number
FROM tblAffectedParts LEFT JOIN tblPartNumber ON
tblAffectedParts.tblPartNumber_ID = tblPartNumber.ID;
I also put a text box for it on my form and hid it behind another text box,

I can get the correct record to come up when I put the following code in the
double click of the tblPartNumber_ID in the subform:
[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number]
But when I add the main form
[Part_Number]=[Forms][ECO FORM]![subfrmaffectedparts]![docPart_number]
and try to double click I get the parameter box asking for "Forms!ECO, I
think I am almost there lol.

Thanks,
Tammy

 

[Steve Schapel]

Tammy,

I am very sorry, I can't get past what I wrote in my last response. You
have a form named ECO FORM, right? And on this form, you have a subform
called subfrmaffectedparts, right? Are you sure? Do you have the ECO
FORM form open, and then you find that this works...
[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number]
Well, in my experience, this is not right. The value of
[Forms]![subfrmaffectedparts]![docPart_number] will not make sense to
Access, because the subfrmaffectedparts form is not open. So somehow I
have missed a vital ingredient in your meaning.

--
Steve Schapel, Microsoft Access MVP

[Part_Number]=Format([Forms]![subfrmaffectedparts]![docPart_Number])

Am I imagining it, or do you keep changing the rules? This is the
first I have heard of "code in the double click of the
tblPartNumber_ID". I thought we were talking about the criteria in a
query. And it is the first time I have heard anything about a
docPart_number too.

Anyway, here's the deal... If this works without incident:
[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number]
.... then it meams that the subfrmaffectedparts form is open. But you
told me that subfrmaffectedparts is a subform on the ECO FORM form.
Something doesn't add up here, but I'm not sure what. If
subfrmaffectedparts is a subform on the ECO FORM form then it is not
open. The ECO FORM form is open, and subfrmaffectedparts is dispayed
within the subform control on the ECO FORM form. If so, the expression
will not work. This should be correct...
[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts]![docPart_number]
.... or, as some would have it...
[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts].[Form]![docPart_number]
.... although I notice you missed out a ! in the expression you posted,
was this a typo, or did you copy/paste from your actual code/SQL?

Hi Steve,
Sorry if it seems I am changing the rules Just trying to figure this
stuff out

This is the
first I have heard of "code in the double click of the

tblPartNumber_ID". <

I basicly put the code here just for testing purposes, so I can test the
subform. And it works from the subform.
I thought we were talking about the criteria in a

query. And it is the first time I have heard anything about a

docPart_number too.<

The subform is made from the query, so I don’t know if we are talking about
the subform or if we are talking about the query, because they are basicly
the same.
I had to put the doc in front of Part_Number because when I added the text
box to the form it said that i was using a control name that already was in
use.

 

[Guest]

I am sorry I am not being clear.
I have a subform, subfrmaffectedparts and I have a main form called ECO FORM.
subfrmaffectedparts is the subform for ECO FORM.
[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number] the way this
code works is when I have my subform open. it will not work when I open the
ECO FORM, and I know why it wont. What I am trying to say is when I have the
ECO FORM open, I cant get the code to work, when I put the main form code in:
[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts]![docPart_number] what
I get is a parameter box asking for Forms!ECO

Tammy,

I am very sorry, I can't get past what I wrote in my last response. You
have a form named ECO FORM, right? And on this form, you have a subform
called subfrmaffectedparts, right? Are you sure? Do you have the ECO
FORM form open, and then you find that this works...
[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number]
Well, in my experience, this is not right. The value of
[Forms]![subfrmaffectedparts]![docPart_number] will not make sense to
Access, because the subfrmaffectedparts form is not open. So somehow I
have missed a vital ingredient in your meaning.

--
Steve Schapel, Microsoft Access MVP

[Part_Number]=Format([Forms]![subfrmaffectedparts]![docPart_Number])

Am I imagining it, or do you keep changing the rules? This is the
first I have heard of "code in the double click of the
tblPartNumber_ID". I thought we were talking about the criteria in a
query. And it is the first time I have heard anything about a
docPart_number too.

Anyway, here's the deal... If this works without incident:
[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number]
.... then it meams that the subfrmaffectedparts form is open. But you
told me that subfrmaffectedparts is a subform on the ECO FORM form.
Something doesn't add up here, but I'm not sure what. If
subfrmaffectedparts is a subform on the ECO FORM form then it is not
open. The ECO FORM form is open, and subfrmaffectedparts is dispayed
within the subform control on the ECO FORM form. If so, the expression
will not work. This should be correct...
[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts]![docPart_number]
.... or, as some would have it...
[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts].[Form]![docPart_number]
.... although I notice you missed out a ! in the expression you posted,
was this a typo, or did you copy/paste from your actual code/SQL?

Hi Steve,
Sorry if it seems I am changing the rules Just trying to figure this
stuff out

This is the
first I have heard of "code in the double click of the

tblPartNumber_ID". <

I basicly put the code here just for testing purposes, so I can test the
subform. And it works from the subform.
I thought we were talking about the criteria in a

query. And it is the first time I have heard anything about a

docPart_number too.<

The subform is made from the query, so I don’t know if we are talking about
the subform or if we are talking about the query, because they are basicly
the same.
I had to put the doc in front of Part_Number because when I added the text
box to the form it said that i was using a control name that already was in
use.

 

[Steve Schapel]

Tammy,

Ok, now I understand. Sorry.
Is [Part_Number]=[Forms]![ECO
FORM]![subfrmaffectedparts]![docPart_number] copy/pasted from your code,
or is it re-typed into your newsgroup post? If re-typed, can you please
copy/paste your actual code?

 

[Guest]

[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts]![docPart_number]

Tammy,

Ok, now I understand. Sorry.
Is [Part_Number]=[Forms]![ECO
FORM]![subfrmaffectedparts]![docPart_number] copy/pasted from your code,
or is it re-typed into your newsgroup post? If re-typed, can you please
copy/paste your actual code?

--
Steve Schapel, Microsoft Access MVP

I am sorry I am not being clear.
I have a subform, subfrmaffectedparts and I have a main form called ECO FORM.
subfrmaffectedparts is the subform for ECO FORM.
[Part_Number]=[Forms]![subfrmaffectedparts]![docPart_number] the way this
code works is when I have my subform open. it will not work when I open the
ECO FORM, and I know why it wont. What I am trying to say is when I have the
ECO FORM open, I cant get the code to work, when I put the main form code in:
[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts]![docPart_number] what
I get is a parameter box asking for Forms!ECO

 

[Steve Schapel]

Tammy,

Ok, sorry, another dumb question... is subfrmaffectedparts the name of
the subform control on the ECO FORM form, as well as being the name of
the actual form that you use for the subform?

Can you try a couple of experiments please? I can't see that this
should make any difference, to be honest, but hey, I've been surprised
by the unexpected before
1. Try renaming your main form so there is no space in the name, for
example EcoForm or just ECO, and change your Where expression
accordingly, and see what happens.
2. Try it with the full reference to the subform, i.e.
[Part_Number]=[Forms]![ECO
FORM]![subfrmaffectedparts].[Form]![docPart_number]

If this doesn't crack it, is there a chance you could email me the
relevant bits of your application? If so, I'll have a look at it. Just
remove the .ns from my return email address.

 

[Guest]

First I want to thank you very much for all the time you have spent on this
with me

I am not sure how to see if its same name, I checked all the properties but
didnt see anything. I created the subform and dropped it into the main form
ECO FORM, and then linked them.

But here is the funny thing, I came home and worked on the copy of the
database I have here, and its working just fine, whoo hoo. So I must of done
something to my database at work that was causing this problem. So I guess
the problem is solved though I cant figure out what the problem was lol.

So I will replace the query, and my forms when I get back to work and
hopefully everything will be grand!!
Thank you again, this site has been the best site I have ever found!!

Tammy

Tammy,

Ok, sorry, another dumb question... is subfrmaffectedparts the name of
the subform control on the ECO FORM form, as well as being the name of
the actual form that you use for the subform?

Can you try a couple of experiments please? I can't see that this
should make any difference, to be honest, but hey, I've been surprised
by the unexpected before
1. Try renaming your main form so there is no space in the name, for
example EcoForm or just ECO, and change your Where expression
accordingly, and see what happens.
2. Try it with the full reference to the subform, i.e.
[Part_Number]=[Forms]![ECO
FORM]![subfrmaffectedparts].[Form]![docPart_number]

If this doesn't crack it, is there a chance you could email me the
relevant bits of your application? If so, I'll have a look at it. Just
remove the .ns from my return email address.

--
Steve Schapel, Microsoft Access MVP

[Part_Number]=[Forms]![ECO FORM]![subfrmaffectedparts]![docPart_number]

:

 

[Steve Schapel]

Tammy

Truly awesome to know that we have made some progress. Please post back
if you find you still have problems with this. If it continues to work
correctly in one copy of the file and not in another, it could be some
type of corruption of the form.

 

[Guest]

I most certainly will!!
I wont be back to work until the 4th of January. So I wont be able to try it
until then.
My company was having a lot of trouble with their network, so maybe that is
where the trouble lay. While I was working on the database, the network kept
becoming unplugged?? Lol. So corruption may be a possibility for all the hair
on my keyboard.
Thank you again, and I know I will be back. I still need to combine my table
documents with my table tblaffectedparts, lol.
I wish you Happy Holidays and a very Happy New Year!!!
2005 I love the sound of it and I think it will be a very good year!!!
Thank you, you have been awesome, and I hope we have helped others!!
Tammy