syntax for where condition

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone tell me what the correct syntax is for opening a form to a
certain record based on a value from a combo box in a subform? I am using
open form in the macro and I have tried the following in the Where Condition
and when the Action Failed pop up comes up I can see in the aurguments that
my expression just as it is in the macro which tells me it couldn't evluate
my expression ( I think??).

[tblscriptID]=[Forms]![tblprogram].[tblprogramlabor
subform]![tblscriptIDcstcom]

[tblscriptID]=([Forms]![tblprogram].[tblprogramlabor
subform]![tblscriptIDcstcom])

[tblscriptID]=[Forms]![tblprogram].[tblprogramlabor
subform]![tblscriptIDcstcom].[Column](1)
 
Check http://www.mvps.org/access/forms/frm0031.htm at "The Access Web"

The generic syntax is:

[Forms]![NameOfForm]![NameOfSubformContainer].Form![NameOfControl]

Note that NameOfSubformContainer isn't always the same as the name of the
form being used as the subform. If you created the subform by dragging the
one form onto the other, the name of the subform container should be the
same as the name of the form being used. However, if you used the subform
container from the toolbox (whether or not you used the Wizard), the subform
container will normally be named something like Child0
 
Thank you for the referance to that site. It will be very hepful in my
future endevours.

However I have tried the following without success:

[tblscriptID]=[Forms]![tblprogram]![tblprogramlabor
subform].[Form]![tblscriptIDcstcom]

[tblscriptID]=[Forms]![tblprogram]![tblprogramlabor
subform].[Form]![tblscriptIDcstcom].[Column](1)

I have verified that the name of the name of the subform is "tblprogramlabor
subform" (listed as source object in properties). I have verified all the
remaining form/field names are correct also.

Am I missing something in the first part? [tblscriptID] is a field on the
form and is also the Key for that table.

Would the fact that this is in an ADP project rather than MDB have any
bearing on my problem?

Thanks,

Ernie

Douglas J. Steele said:
Check http://www.mvps.org/access/forms/frm0031.htm at "The Access Web"

The generic syntax is:

[Forms]![NameOfForm]![NameOfSubformContainer].Form![NameOfControl]

Note that NameOfSubformContainer isn't always the same as the name of the
form being used as the subform. If you created the subform by dragging the
one form onto the other, the name of the subform container should be the
same as the name of the form being used. However, if you used the subform
container from the toolbox (whether or not you used the Wizard), the subform
container will normally be named something like Child0

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ernie said:
Can someone tell me what the correct syntax is for opening a form to a
certain record based on a value from a combo box in a subform? I am using
open form in the macro and I have tried the following in the Where
Condition
and when the Action Failed pop up comes up I can see in the aurguments
that
my expression just as it is in the macro which tells me it couldn't
evluate
my expression ( I think??).

[tblscriptID]=[Forms]![tblprogram].[tblprogramlabor
subform]![tblscriptIDcstcom]

[tblscriptID]=([Forms]![tblprogram].[tblprogramlabor
subform]![tblscriptIDcstcom])

[tblscriptID]=[Forms]![tblprogram].[tblprogramlabor
subform]![tblscriptIDcstcom].[Column](1)
Click to expand...
Click to expand...
 
Ernie,

No, the Source Object property is not what will give you the name of the
subform control. It is the Name property. As Doug mentioned, these
will often be the same, but may not be, so check that first.
 
Guys,
In this case the name property is the same as the source property.

Steve Schapel said:
Ernie,

No, the Source Object property is not what will give you the name of the
subform control. It is the Name property. As Doug mentioned, these
will often be the same, but may not be, so check that first.

--
Steve Schapel, Microsoft Access MVP
Thank you for the referance to that site. It will be very hepful in my
future endevours.

However I have tried the following without success:

[tblscriptID]=[Forms]![tblprogram]![tblprogramlabor
subform].[Form]![tblscriptIDcstcom]

[tblscriptID]=[Forms]![tblprogram]![tblprogramlabor
subform].[Form]![tblscriptIDcstcom].[Column](1)

I have verified that the name of the name of the subform is "tblprogramlabor
subform" (listed as source object in properties). I have verified all the
remaining form/field names are correct also.

Am I missing something in the first part? [tblscriptID] is a field on the
form and is also the Key for that table.

Would the fact that this is in an ADP project rather than MDB have any
bearing on my problem?
Click to expand...
Click to expand...
 
Try

"[tblscriptID]=" & [Forms]![tblprogram]![tblprogramlabor
subform].[Form]![tblscriptIDcstcom]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ernie said:
Guys,
In this case the name property is the same as the source property.

Steve Schapel said:
Ernie,

No, the Source Object property is not what will give you the name of the
subform control. It is the Name property. As Doug mentioned, these
will often be the same, but may not be, so check that first.

--
Steve Schapel, Microsoft Access MVP
Thank you for the referance to that site. It will be very hepful in my
future endevours.

However I have tried the following without success:

[tblscriptID]=[Forms]![tblprogram]![tblprogramlabor
subform].[Form]![tblscriptIDcstcom]

[tblscriptID]=[Forms]![tblprogram]![tblprogramlabor
subform].[Form]![tblscriptIDcstcom].[Column](1)

I have verified that the name of the name of the subform is
"tblprogramlabor
subform" (listed as source object in properties). I have verified all
the
remaining form/field names are correct also.

Am I missing something in the first part? [tblscriptID] is a field on
the
form and is also the Key for that table.

Would the fact that this is in an ADP project rather than MDB have any
bearing on my problem?
Click to expand...
Click to expand...
Click to expand...
 
Yes that works now. Thank you very much!

Douglas J. Steele said:
Try

"[tblscriptID]=" & [Forms]![tblprogram]![tblprogramlabor
subform].[Form]![tblscriptIDcstcom]


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ernie said:
Guys,
In this case the name property is the same as the source property.

Steve Schapel said:
Ernie,

No, the Source Object property is not what will give you the name of the
subform control. It is the Name property. As Doug mentioned, these
will often be the same, but may not be, so check that first.

--
Steve Schapel, Microsoft Access MVP

Ernie wrote:
Thank you for the referance to that site. It will be very hepful in my
future endevours.

However I have tried the following without success:

[tblscriptID]=[Forms]![tblprogram]![tblprogramlabor
subform].[Form]![tblscriptIDcstcom]

[tblscriptID]=[Forms]![tblprogram]![tblprogramlabor
subform].[Form]![tblscriptIDcstcom].[Column](1)

I have verified that the name of the name of the subform is
"tblprogramlabor
subform" (listed as source object in properties). I have verified all
the
remaining form/field names are correct also.

Am I missing something in the first part? [tblscriptID] is a field on
the
form and is also the Key for that table.

Would the fact that this is in an ADP project rather than MDB have any
bearing on my problem?
Click to expand...
Click to expand...
Click to expand...
 
Ernie,

In that case, if the macro is being triggered from an event on the
subform itself, this should also work....
"[tblscriptID]=" & [tblscriptIDcstcom]
 
Back
Top