Problem with using function in a drop down query of a row source

[Jack]

Function IsObjectOpen(strName As String, Optional intObjectType As Integer =
acForm) As Boolean
' acForm (value 2) Default
On Error Resume Next
IsObjectOpen = (SysCmd(acSysCmdGetObjectState, intObjectType, strName) <> 0)
If Err <> 0 Then
IsObjectOpen = False
End If
End Function

I have the above function to check for if a form is loaded or not.

I have the following query as a combo
box row source utilizing the above function


SELECT dbo_globaladdresslist_final_absolute.Row_id,
dbo_globaladdresslist_final_absolute.ActualName
FROM dbo_globaladdresslist_final_absolute
WHERE
(((dbo_globaladdresslist_final_absolute.displayname)=IIf((IsObjectOpen("frmActionRequestFiltered",2)),[Forms]![frmActionRequestFiltered]![cboAssignedTo],[Forms]![frmActionRequest]![cboAssignedTo])));


However the above query is not working I am not sure where I am going wrong.

I appreciate any help for resolution of this issue.
Thanks

 

[Jeff Boyce]

Jack

"Not working" is pretty ambiguous ... more detailed description may lead to
more detailed suggestions.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jack]

Ok if can be more clear that will be so helpful. The subform called approval
is in both the main form which is the data entry ActionRequest and the edit
form which is ActionRequestFiltered. Thus the subform approval is being
accessed by both form. Now all the code we are talking about is in a dropdown
called Signature combo box filed.

I cannot refer to a single form in this as the subform is in both form. The
sql is going to be the rowsource of this Signature combo box in the Approval
subform.

When I am having my rowsource of the combo box Signature in the Approval
subform as follows:
SELECT dbo_globaladdresslist_final_absolute.Row_id,
dbo_globaladdresslist_final_absolute.ActualName FROM
dbo_globaladdresslist_final_absolute WHERE
(((dbo_globaladdresslist_final_absolute.displayname)=[Forms]![frmActionRequestFiltered]![cboAssignedTo]));

this works when the filtered form is open. However when I am working on the
data entry form frmactionrequest this does not work as it
refers to the filtered form. So the idea is to make this whole thing work
with both the form using your direction

The sql in discussion is the sql in rowsource of the signature combo box in
the sub form Approval.

Not sure this is good enough.
Thanks

Jack

"Not working" is pretty ambiguous ... more detailed description may lead to
more detailed suggestions.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Function IsObjectOpen(strName As String, Optional intObjectType As Integer
=
acForm) As Boolean
' acForm (value 2) Default
On Error Resume Next
IsObjectOpen = (SysCmd(acSysCmdGetObjectState, intObjectType, strName) <>
0)
If Err <> 0 Then
IsObjectOpen = False
End If
End Function

I have the above function to check for if a form is loaded or not.

I have the following query as a combo
box row source utilizing the above function


SELECT dbo_globaladdresslist_final_absolute.Row_id,
dbo_globaladdresslist_final_absolute.ActualName
FROM dbo_globaladdresslist_final_absolute
WHERE
(((dbo_globaladdresslist_final_absolute.displayname)=IIf((IsObjectOpen("frmActionRequestFiltered",2)),[Forms]![frmActionRequestFiltered]![cboAssignedTo],[Forms]![frmActionRequest]![cboAssignedTo])));


However the above query is not working I am not sure where I am going
wrong.

I appreciate any help for resolution of this issue.
Thanks

.

 

[Jeff Boyce]

Since the subform is being used in two different main forms, you could take
the easy way out and create a second subform to go in your second form.

If you decide to keep a single subform, you'll have to add code/a procedure
that inspects what main form the subform is being displayed in, and modifies
the combobox rowsource accordingly. Seems like a lot more work...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Ok if can be more clear that will be so helpful. The subform called
approval
is in both the main form which is the data entry ActionRequest and the
edit
form which is ActionRequestFiltered. Thus the subform approval is being
accessed by both form. Now all the code we are talking about is in a
dropdown
called Signature combo box filed.

I cannot refer to a single form in this as the subform is in both form.
The
sql is going to be the rowsource of this Signature combo box in the
Approval
subform.

When I am having my rowsource of the combo box Signature in the Approval
subform as follows:
SELECT dbo_globaladdresslist_final_absolute.Row_id,
dbo_globaladdresslist_final_absolute.ActualName FROM
dbo_globaladdresslist_final_absolute WHERE
(((dbo_globaladdresslist_final_absolute.displayname)=[Forms]![frmActionRequestFiltered]![cboAssignedTo]));

this works when the filtered form is open. However when I am working on
the
data entry form frmactionrequest this does not work as it
refers to the filtered form. So the idea is to make this whole thing work
with both the form using your direction

The sql in discussion is the sql in rowsource of the signature combo box
in
the sub form Approval.

Not sure this is good enough.
Thanks

Jack

"Not working" is pretty ambiguous ... more detailed description may lead
to
more detailed suggestions.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Function IsObjectOpen(strName As String, Optional intObjectType As
Integer
=
acForm) As Boolean
' acForm (value 2) Default
On Error Resume Next
IsObjectOpen = (SysCmd(acSysCmdGetObjectState, intObjectType, strName)
<>
0)
If Err <> 0 Then
IsObjectOpen = False
End If
End Function

I have the above function to check for if a form is loaded or not.

I have the following query as a combo
box row source utilizing the above function


SELECT dbo_globaladdresslist_final_absolute.Row_id,
dbo_globaladdresslist_final_absolute.ActualName
FROM dbo_globaladdresslist_final_absolute
WHERE
(((dbo_globaladdresslist_final_absolute.displayname)=IIf((IsObjectOpen("frmActionRequestFiltered",2)),[Forms]![frmActionRequestFiltered]![cboAssignedTo],[Forms]![frmActionRequest]![cboAssignedTo])));


However the above query is not working I am not sure where I am going
wrong.

I appreciate any help for resolution of this issue.
Thanks

.

 

[Jack]

Thanks for your help Jeff. I appreciate it
The idea of using another subform did not occur to me
I will try the easy way. Thanks.

Since the subform is being used in two different main forms, you could take
the easy way out and create a second subform to go in your second form.

If you decide to keep a single subform, you'll have to add code/a procedure
that inspects what main form the subform is being displayed in, and modifies
the combobox rowsource accordingly. Seems like a lot more work...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Ok if can be more clear that will be so helpful. The subform called
approval
is in both the main form which is the data entry ActionRequest and the
edit
form which is ActionRequestFiltered. Thus the subform approval is being
accessed by both form. Now all the code we are talking about is in a
dropdown
called Signature combo box filed.

I cannot refer to a single form in this as the subform is in both form.
The
sql is going to be the rowsource of this Signature combo box in the
Approval
subform.

When I am having my rowsource of the combo box Signature in the Approval
subform as follows:
SELECT dbo_globaladdresslist_final_absolute.Row_id,
dbo_globaladdresslist_final_absolute.ActualName FROM
dbo_globaladdresslist_final_absolute WHERE
(((dbo_globaladdresslist_final_absolute.displayname)=[Forms]![frmActionRequestFiltered]![cboAssignedTo]));

this works when the filtered form is open. However when I am working on
the
data entry form frmactionrequest this does not work as it
refers to the filtered form. So the idea is to make this whole thing work
with both the form using your direction

The sql in discussion is the sql in rowsource of the signature combo box
in
the sub form Approval.

Not sure this is good enough.
Thanks

Jack

"Not working" is pretty ambiguous ... more detailed description may lead
to
more detailed suggestions.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Function IsObjectOpen(strName As String, Optional intObjectType As
Integer
=
acForm) As Boolean
' acForm (value 2) Default
On Error Resume Next
IsObjectOpen = (SysCmd(acSysCmdGetObjectState, intObjectType, strName)
<>
0)
If Err <> 0 Then
IsObjectOpen = False
End If
End Function

I have the above function to check for if a form is loaded or not.

I have the following query as a combo
box row source utilizing the above function


SELECT dbo_globaladdresslist_final_absolute.Row_id,
dbo_globaladdresslist_final_absolute.ActualName
FROM dbo_globaladdresslist_final_absolute
WHERE
(((dbo_globaladdresslist_final_absolute.displayname)=IIf((IsObjectOpen("frmActionRequestFiltered",2)),[Forms]![frmActionRequestFiltered]![cboAssignedTo],[Forms]![frmActionRequest]![cboAssignedTo])));


However the above query is not working I am not sure where I am going
wrong.

I appreciate any help for resolution of this issue.
Thanks


.

.