If Then statement, having trouble since two forms are involved

[SD23]

I have one form (called Sheet 1) that has a pull down box called status. On a second form (called Sheet 2), I also have a pull down box called status. However they have different values in the pull down box.

If the user in form one selects "Ready, " and clicks submit, I want the form 2 status pull down box to automatically select the value " filed".

If form 1 value for status=Ready
Then form 2 value for status=Filed

I would appreciate some help. Thankyou.

 

[Matt Greatorex]

As code behind the After Update event of Form1's 'Status' field:

If [Forms]![Form1]![Status] = "Ready" then
[Forms]![Form2]![Status] = "Filed"
End if

 

[SD23]

I wrote

If [Forms]![Invention Disclosure Input Form]![Status] = "DP Ready" Then
[Forms]![Dockets Data Input Form].[Status] = "Ready"
End If

but it gives me an error saying that it cant find my DOckets Data Input Form. That is what the form is called. I am not quite sure what the problem is.

 

[Matt Greatorex]

Is the 'Dockets Data Input Form' a subform? if so, you have to include that in the path.

Is it definitely open at the time you're setting the value? If not, you'll need to open it.

 

[SD23]

It is a subform, how would I include that in the path? What do you mean by if its definetely open?

 

[Banana]

Bookmark this.

 

[Matt Greatorex]

Nice link, Banana.

SD, I meant that if you're trying to set a value on a form which isn't actually open, you won't be able to. If it's a subform of the one you're working on, then it is open, so you don't need to worry about it.

 

[SD23]

hey thanks alot. The link was helpful.

 

[SD23]

I dont think the form is a subform. They are two different forms. When I click a button that is when the second form opens. Is there a way I can set a value on the second form based on the value of the first form?

 

[Matt Greatorex]

In the On Open event of the second form, enter

If [Forms]![Invention Disclosure Input Form]![Status] = "DP Ready" Then
[Forms]![Dockets Data Input Form].[Status] = "Ready"
End If

Note: If you close the Invention Disclosure Input Form form before opening the Dockets Data Input Form form, this WILL NOT work.