Auto open new form based on expression

[reezebeez]

Hi, hope this is the right place to post this question. So I have a database (I've attached it for you). I'd like to set it up so that in the "incentive scheme" form when the field [term 1 allow] is changed and is more than 0 then have the form "permissible explanation" appear with that record so the user can add an explanation, they should not be allowed to leave it empty if the [term 1 allow] is still >0 and then save and return to the "incentive scheme" form for further input.

Hows that? clear as mud right.

Thank you for reading.

Can you help me?

 

[reezebeez]

Hi again, so I've had a crack at it and I can get the window to automatically pop up - but How do I make it pick up the current record I was editing?
I need to know if this is possible or I thought of another way - I could place a hidden field that appears for input each time the Permissable field is changed, what do you reckon?

 

[pbaldy]

Can't look at the db right now, but does this work for you?

http://www.baldyweb.com/wherecondition.htm

 

[reezebeez]

Thanks, you have me searching for the right terms now. I don't know what controlname is about though, still trying to work it out, have been dreaming about what the answer it!

 

[pbaldy]

Was this not helpful?

ControlName is the name of the control on the current form that contains the value to be shown on the second form

A control is a textbox, combo, etc.

 

[reezebeez]

I have tried everything to no avail. i am going round and round in circles. this is what i have tried:

Macro:
If [term 1 allow]>0 then
openform
form name permissible explanation
view form
filter name current record
where condition = [id]=[forms]![permissible explanantion]![id]
data mode edit
window mode normal.

this opens new form correctly filtered to no records.

then i tried the code thing:
Private Sub Permissable_AfterUpdate()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Permissible Explanation"

stLinkCriteria = "[id]" = " & Me![id]"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

this opens new form every time (cuz its missing the if statement) and filtered to no records.

then i try:

Private Sub Permissable_AfterUpdate()

DoCmd.OpenForm "Permissible Explanation", , , "ID = " & Me.ID

End Sub

this opens new form every time (cuz its missing the if statement) and filtered to no records.

Where am i going wrong?:banghead:

 

[pbaldy]

The last one looks correct for a numeric data type. What's the Data Entry property of the form being opened?

 

[reezebeez]

The last one looks correct for a numeric data type. What's the Data Entry property of the form being opened?

I assume you mean the field ID - it is an autonumber field.
and the data entry property of the form being opened? so scrolling down the list i see one called 'data entry' its set to yes.

 

[pbaldy]

There's your problem; that property says you want the form for adding new records only.

 

[reezebeez]

There's your problem; that property says you want the form for adding new records only.

Oh my Gosh! you are a freaking Access genius! it works! so now to get the if statement in there and I'm all ready for the next question, can I get an unbound text box to save its entered text? (or should I post a new question?)

 

[reezebeez]

woo hoo, if statement works too!

 

[pbaldy]

Happy to help!