if query criteria, looking at form field return a null then look at field in table

[@stan]

Ok

Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.

Thank you

 

[billmeye]

I've never used this in a query criteria, but you can try:

IIf(CurrentProject.AllForms("frmMyForm").IsLoaded,[Forms]![frmMyForm]![MyCtl],"*")

 

[pr2-eugin]

@stan, I think it would prompt for a Enter Parameter Value if the Form is Closed.. So you should consider moving to a User Defined function maybe? The function would test if a Form is opened or closed.. If open return the values, return all..

 

[@stan]

@stan, I think it would prompt for a Enter Parameter Value if the Form is Closed.. So you should consider moving to a User Defined function maybe? The function would test if a Form is opened or closed.. If open return the values, return all..


Sounds interesting, how would that look?

 

[pr2-eugin]

What I was about to suggest has been provided by Bill.. Have you given that a try? My method is going to do the same, but use a Function to return the value..

 

[@stan]

IIf(CurrentProject.AllForms("frmMyForm").IsLoaded,[Forms]![frmMyForm]![MyCtl],"*")

opens up a "enter parameter value" box????

 

[@stan]

What I have and what I am trying to do is to build into one Query the ability to show that current record just entered from a form field and it that form is closed to open all records in the table. it is an attempt to use one report that has the query build into it instead of creating two reports.

 

[billmeye]

Did you replace frmMyForm and [Forms]![frmMyForm]![MyCtl] with the correct name of your form and control?

 

[pr2-eugin]

Okay copy the following code into a Module,

Public Function qryParam() As String
    If CurrentProject.AllForms("yourFormNameHere").IsLoaded Then
        qryParam = Forms!yourFormNameHere!controlName
    Else
        qryParam = "*"
    End If
End Function

Make sure you Name the module other than qryParam..

Then your query becomes..

SELECT allThatYouWant FROM theTable
WHERE someField LIKE qryParam();

 

[pr2-eugin]

Did you replace frmMyForm and [Forms]![frmMyForm]![MyCtl] with the correct name of your form and control?

It came up with the following for me Bill..

Also on top of that IIF behaves differently than the normal If Else.. So it would still prompt for the Enter parameter value..

 

[billmeye]

Well, that's what I get for not trying it out first. Your correct Paul and using the function as you described does work.

 

[pr2-eugin]

Well, that's what I get for not trying it out first.

Ha ha, I am just so jobless, that I am trying out everything.. So do not beat yourself up.. I have done that several times..

 

[@stan]

Public Function locateincidentnumber() As String
If CurrentProject.AllForms([Forms]![frm-Staff-Client Information Navigation Panel]![frm-Staff-CIR Form]).IsLoaded Then
locateincidentnumber = [Forms]![frm-Staff-Client Information Navigation Panel]![frm-Staff-CIR Form].[Form]![Incident No]
Else
locateincidentnumber = "*"
End If
End Function

I am getting a run time error 13
type mismatch.

the form that the field resides on is a subform. would that make a difference?

Thank you for all of your help.

 

[pr2-eugin]

You are not using that for a Number field are you? What is your Query?

 

[@stan]

yes in fact what it is looking for is the tables "AutoNumber"
In the form it will be the last one added, but if is null then I need to have it return all Numbers in that field. Does this change things.

I was about to give up and just create two reports each with its own Query (I hate creating more overhead then I need to.

Thank you for your help
Stan

 

[pr2-eugin]

I just used it in mine and it seems to work without throwing any Type mismatch error..

 

[@stan]

I wonder what I screwed up, i'll give it another run.... thank you for your help