querry from either of two forms

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

Guest

I would like to set up a query that can look at either of two forms for a
field and then link the query to a report. I can get both fields in the
criteria section using forms![form1]![field] and forms![form2]![field] but
depending on which form is open, I want the query to run using that form
only. When I run the query, it prompts for data in the other form. Is there
a way to set up the criteria to ignore the "blank" form (or closed form) and
only use the open form? I tried using an OR statement in the WHERE clause if
one field was NULL but it didn't work.

Or, does anyone have a suggestion to print a report from either of two
different forms based upon the same criteria without replicating the report
and changing the source. I think having two of the same reports with two
different sources is sloppy.

Thanks
 
hi,
can you use the underlaying tables instead.
i did something similar but used the tables. i put this
iif statement in the query field header.

Status: IIf(IsNull([WODates]![Issue Date]) And [WKO]!
[StatusCode]="Released",[WKO]![StatusCode],"Issued" & "-"
& [WODates]![Issue Date])

it checks 2 tbles WODates and WKO. If issue date in WOdate
is null and status code in wko is released then get the
status code from WKO else get the issued date from WODates
and put the word issued in front of it. some record got
info for one table while other record got info from the
other table.
don't know if that will help.
regards
 
What I have is a table with student infractions. I then have two forms: a
"write up form" where teachers enter the infractions and a "Referral
Management" form where we as the administrators can assign a consequence.
The teacher can also assign a consequence in the "write up form." What I
need to do is have one parent letter that can take the incident number (PK)
from either form and then relate the information to the parent letter and
print. My thought was a query based on the table tblInfractions with a
criteria based on the incident number which would be taken from either of the
forms that was open at the time.

[email protected] said:
hi,
can you use the underlaying tables instead.
i did something similar but used the tables. i put this
iif statement in the query field header.

Status: IIf(IsNull([WODates]![Issue Date]) And [WKO]!
[StatusCode]="Released",[WKO]![StatusCode],"Issued" & "-"
& [WODates]![Issue Date])

it checks 2 tbles WODates and WKO. If issue date in WOdate
is null and status code in wko is released then get the
status code from WKO else get the issued date from WODates
and put the word issued in front of it. some record got
info for one table while other record got info from the
other table.
don't know if that will help.
regards
-----Original Message-----
I would like to set up a query that can look at either of two forms for a
field and then link the query to a report. I can get both fields in the
criteria section using forms![form1]![field] and forms! [form2]![field] but
depending on which form is open, I want the query to run using that form
only. When I run the query, it prompts for data in the other form. Is there
a way to set up the criteria to ignore the "blank" form (or closed form) and
only use the open form? I tried using an OR statement in the WHERE clause if
one field was NULL but it didn't work.

Or, does anyone have a suggestion to print a report from either of two
different forms based upon the same criteria without replicating the report
and changing the source. I think having two of the same reports with two
different sources is sloppy.

Thanks
.
Click to expand...
Click to expand...
 
A couple of ways that you can handle this.

A) If you have another form that is always open, put a hidden control on it and
reference that in your query. On the two other forms you can populate the
hidden control with the desired value from the other form (perhaps by using the
on current event of the other forms)

B) Populate a global variable and use a function to get the value from the
global variable.

C) Write a function that determines which form is open and grabs the value from
the open form and returns that value. Then you replace the reference to the
form control with a call to the function.
 
John,

I like the idea of B or C because I don't want to really have another form
open all the time. Is B as simple as putting some code in the On Click area
such as:

Dim incnum As long
incnum=Me.IncidentNumber

and then in the query, call incnum as the criteria?

I say simple as that but I am not sure about how to set up the function to
call upon.


John Spencer (MVP) said:
A couple of ways that you can handle this.

A) If you have another form that is always open, put a hidden control on it and
reference that in your query. On the two other forms you can populate the
hidden control with the desired value from the other form (perhaps by using the
on current event of the other forms)

B) Populate a global variable and use a function to get the value from the
global variable.

C) Write a function that determines which form is open and grabs the value from
the open form and returns that value. Then you replace the reference to the
form control with a call to the function.


I would like to set up a query that can look at either of two forms for a
field and then link the query to a report. I can get both fields in the
criteria section using forms![form1]![field] and forms![form2]![field] but
depending on which form is open, I want the query to run using that form
only. When I run the query, it prompts for data in the other form. Is there
a way to set up the criteria to ignore the "blank" form (or closed form) and
only use the open form? I tried using an OR statement in the WHERE clause if
one field was NULL but it didn't work.

Or, does anyone have a suggestion to print a report from either of two
different forms based upon the same criteria without replicating the report
and changing the source. I think having two of the same reports with two
different sources is sloppy.

Thanks
Click to expand...
Click to expand...
 
You will have to use a module (not one attached to a form) to declare the global
variable. And get access to it.

My personal choice would be "A" or "C". To do "C" you would need code something
like the following. Then you could just stick the function into the query as
the criteria.

Field: YourField
Criteria: funGetParameterforReport()

Copy and paste the following into a module (UNTESTED)

Public Function funGetParameterforReport()
'*******************************************
Dim strFormName As String, varReturn As Variant

varReturn = Null

strFormName = "frm_dlgChooseReport"
If IsFormLoaded(strFormName) = False Then
strFormName = "frm_dlgChooseReport_Special"
If IsFormLoaded(strFormName) = False Then
strFormName = ""
End If
End If

Select Case strFormName
Case "frm_dlgChooseReport"
varReturn = Forms(strFormName).TheControlWithValue

Case "frm_dlgChooseReport_Special"
varReturn = Forms(strFormName).ADifferentControl

Case ""
varReturn = Null 'which may force an error
End Select

funGetParameterforReport = varReturn

End Function

Function IsFormLoaded(strFormName As String) As Boolean
'John Spencer UMBC-CHPDM
'Last Update: April 7, 2000
'Purpose: Returns True if the specified form is open in Form view or Datasheet view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsFormLoaded = True
End If
End If

End Function
John,

I like the idea of B or C because I don't want to really have another form
open all the time. Is B as simple as putting some code in the On Click area
such as:

Dim incnum As long
incnum=Me.IncidentNumber

and then in the query, call incnum as the criteria?

I say simple as that but I am not sure about how to set up the function to
call upon.

John Spencer (MVP) said:
A couple of ways that you can handle this.

A) If you have another form that is always open, put a hidden control on it and
reference that in your query. On the two other forms you can populate the
hidden control with the desired value from the other form (perhaps by using the
on current event of the other forms)

B) Populate a global variable and use a function to get the value from the
global variable.

C) Write a function that determines which form is open and grabs the value from
the open form and returns that value. Then you replace the reference to the
form control with a call to the function.


I would like to set up a query that can look at either of two forms for a
field and then link the query to a report. I can get both fields in the
criteria section using forms![form1]![field] and forms![form2]![field] but
depending on which form is open, I want the query to run using that form
only. When I run the query, it prompts for data in the other form. Is there
a way to set up the criteria to ignore the "blank" form (or closed form) and
only use the open form? I tried using an OR statement in the WHERE clause if
one field was NULL but it didn't work.

Or, does anyone have a suggestion to print a report from either of two
different forms based upon the same criteria without replicating the report
and changing the source. I think having two of the same reports with two
different sources is sloppy.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top