Form based on criteria from 2 other forms

[tMitch]

Hi,

I have a form (say Form X) that opens based on the ProjectID criteria in the query, which is based on a record selected from Form1 [ProjectID].

I want to be able to open Form X using information from 2 forms - Form1 or Form2, both using ProjectID. What I've done so far works, but I get an Enter Value Parameter looking for the other Form. How do I write the statement so that it reads Form1 [ProjectID] and if that is not open (null??), then it goes to Form2 [ProjectID] and vice versa?

What I have is:

IIf(("IsNull[Forms]![FrmAddPrjInfo]![ProjectID]"),[Forms]![FrmPrjRvwerMgrDue]![ProjectID],0) Or IIf(("IsNull[Forms]![FrmPrjRvwerMgrDue]![ProjectID]"),[Forms]![FrmAddPrjInfo]![ProjectID],0)

Variations of this haven't worked. Perhaps IIF statement is the wrong way to go about this?

Any suggestions? Thanks!

 

[tMitch]

Hi Pat,

Thanks for replying. I did as you said, but get an Enter Parameter Value looking for ReturnProjectID().

Any suggestions on where I might have gone wrong? I'm not at all familiar with global variables, but it sounds like that is what I need to make this work.

Thank you.

 

[tMitch]

Hi Pat,

I’m still trying to get this to work. It’s not reading my global variable. I don’t know how to write code, so I’m sure there is more detailed code that is needed.

I put the following in the Current event for each form that I want to be the current record in the 3rd form (FormX):

Private Sub Form_Current()
YourGlobalVariable = Me.ProjectID
End Sub

Public Function ReturnProjectID() As Variant
ReturnProjectID = YourGlobalVariable
End Function

And put [ReturnProjectID()] in the query criteria.

I put the info you gave me last time in a new Module. (Does that make it Standard as opposed to a form’s class module?)

I suspect there is more code that I need to put in here……Sorry to be so clueless, but any more help you can give me would be greatly appreciated. I’m once again in over my head.

Thanks!

 

[adevera]

Here I will do on this:

On the Module Section of the Database, define a Public (Global) variable:

i.e: nProjID as long (you can set your own data type)

Create a Public Funtion that Pat Hartman wrote (but this also on module section)

And then, on the exit on form ProjectID (wher you enter your project id), set the value of nProjID to the value of Projectid field:

i.e: name of the function = ProjectID (this is your field)

This way, once you exit the form, the value of your project id will be on nProjID which is a global.

And then put the function as criteria on the query which will be used by your formx...

or else, attached your db and we go from there...