New If Statement Post

D

Dave Elliott

I have (2) sub-forms on my main form, the main form is named TimeCards
the sub that I wish to check the criteria on is named TimeCardPaymentSub
It is in this form that I wish to check criteria
The next sub-form is named Vendor_List
It is on the form on the first control after the update event that I wish
the code to run
If the control on the sub-form TimeCardPaymentSub (Payment) >0
and If the control (Balance) on the same sub-form <=0 Then
msgbox

If ("Forms!TimeCards![TimeCardPaymentSub].Form!Payment") > 0 And
("Forms!TimeCards![TimeCardPaymentSub]!Balance") <= 0 Then
MsgBox "Why is there more material on this job?"
End If
 
J

John Vinson

I have (2) sub-forms on my main form, the main form is named TimeCards
the sub that I wish to check the criteria on is named TimeCardPaymentSub
It is in this form that I wish to check criteria
The next sub-form is named Vendor_List
It is on the form on the first control after the update event that I wish
the code to run
If the control on the sub-form TimeCardPaymentSub (Payment) >0
and If the control (Balance) on the same sub-form <=0 Then
msgbox

If ("Forms!TimeCards![TimeCardPaymentSub].Form!Payment") > 0 And
("Forms!TimeCards![TimeCardPaymentSub]!Balance") <= 0 Then
MsgBox "Why is there more material on this job?"
End If
Click to expand...

You're comparing two text strings starting with the letter F to the
numeric value 0.

Remove the quote marks.

John W. Vinson[MVP]
 
D

Dave Elliott

Trying to check a sub-form for criteria, code is run from another sub-form
on the enter event.
Main form is TimeCards, form to check criteria is TimeCardPaymentSub, Code
is run on sub-form Vendor_List

If (Forms!TimeCards![TimeCardPaymentSub]!Balance) <= 0 Then
MsgBox "Why is there more material being put on this job?"
End If

John Vinson said:
I have (2) sub-forms on my main form, the main form is named TimeCards
the sub that I wish to check the criteria on is named TimeCardPaymentSub
It is in this form that I wish to check criteria
The next sub-form is named Vendor_List
It is on the form on the first control after the update event that I wish
the code to run
If the control on the sub-form TimeCardPaymentSub (Payment) >0
and If the control (Balance) on the same sub-form <=0 Then
msgbox

If ("Forms!TimeCards![TimeCardPaymentSub].Form!Payment") > 0 And
("Forms!TimeCards![TimeCardPaymentSub]!Balance") <= 0 Then
MsgBox "Why is there more material on this job?"
End If
Click to expand...

You're comparing two text strings starting with the letter F to the
numeric value 0.

Remove the quote marks.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

Trying to check a sub-form for criteria, code is run from another sub-form
on the enter event.
Main form is TimeCards, form to check criteria is TimeCardPaymentSub, Code
is run on sub-form Vendor_List

If (Forms!TimeCards![TimeCardPaymentSub]!Balance) <= 0 Then
MsgBox "Why is there more material being put on this job?"
End If
Click to expand...

Do you have a question, Dave?

Try

If [Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance] > 0

This assumes that the Name property of the Subform control on
[TimeCards] is the same as the name of the form within that control.
Access does it this way by default but it's possible that it's
different; the name of the Control is what you need, not the name of
the form.

John W. Vinson[MVP]
 
D

Dave Elliott

This code does nothing, no message box appears??? Referring to a sub-form
from a sub-form!

If [Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance] <= 0 Then
MsgBox "Why is there more material on this job?"
End If


John Vinson said:
Trying to check a sub-form for criteria, code is run from another
sub-form
on the enter event.
Main form is TimeCards, form to check criteria is TimeCardPaymentSub, Code
is run on sub-form Vendor_List

If (Forms!TimeCards![TimeCardPaymentSub]!Balance) <= 0 Then
MsgBox "Why is there more material being put on this job?"
End If
Click to expand...

Do you have a question, Dave?

Try

If [Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance] > 0

This assumes that the Name property of the Subform control on
[TimeCards] is the same as the name of the form within that control.
Access does it this way by default but it's possible that it's
different; the name of the Control is what you need, not the name of
the form.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

This code does nothing, no message box appears??? Referring to a sub-form
from a sub-form!

If [Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance] <= 0 Then
MsgBox "Why is there more material on this job?"
End If
Click to expand...

What's the context? What Event is this code called from, and what's
the rest of the Sub?

John W. Vinson[MVP]
 
D

Dave Elliott

The code is fired from a sub-form on the enter event of the first control.

John Vinson said:
This code does nothing, no message box appears??? Referring to a sub-form
from a sub-form!

If [Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance] <= 0 Then
MsgBox "Why is there more material on this job?"
End If
Click to expand...

What's the context? What Event is this code called from, and what's
the rest of the Sub?

John W. Vinson[MVP]
Click to expand...
 
D

Dave Elliott

The problem is that the control (Balance) is in the form footer of the
sub-form TimeCardPaymentSub
The event is being fired from another sub-form (Vendor_List) on the first
controls enter event.

John Vinson said:
This code does nothing, no message box appears??? Referring to a sub-form
from a sub-form!

If [Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance] <= 0 Then
MsgBox "Why is there more material on this job?"
End If
Click to expand...

What's the context? What Event is this code called from, and what's
the rest of the Sub?

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

The problem is that the control (Balance) is in the form footer of the
sub-form TimeCardPaymentSub
The event is being fired from another sub-form (Vendor_List) on the first
controls enter event.
Click to expand...

Is the Enter event actually firing? Try putting a breakpoint in this
code to see. You might also want to move the code to the control's
GotFocus event.

John W. Vinson[MVP]
 
J

John Vinson

This code does nothing, no message box appears??? Referring to a sub-form
from a sub-form!

If [Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance] <= 0 Then
MsgBox "Why is there more material on this job?"
End If
Click to expand...

Ok... if you step through the code, what is actually in [Balance]? Is
it Text or numeric? Does it work if you use

Val([Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance])

instead of the bare reference? Which record on the subform is
selected, and how do you know?

John W. Vinson[MVP]
 
D

Dave Elliott

If I use your syntax, it does not work. The value is numeric, it should
either be zero or a negative number for the code to fire.


John Vinson said:
This code does nothing, no message box appears??? Referring to a sub-form
from a sub-form!

If [Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance] <= 0 Then
MsgBox "Why is there more material on this job?"
End If
Click to expand...

Ok... if you step through the code, what is actually in [Balance]? Is
it Text or numeric? Does it work if you use

Val([Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance])

instead of the bare reference? Which record on the subform is
selected, and how do you know?

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

If I use your syntax, it does not work. The value is numeric, it should
either be zero or a negative number for the code to fire.
Click to expand...
Ok. If you'll be kind enough to answer my questions, I'll try to help.
"Does not work" is not illuminating.

John W. Vinson[MVP]
 
D

Dave Elliott

If I use Val([Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance])
MsgBox "yada yada"
End If

No message box appears.
The control Balance contains a numeric value. (Currency)
The message box (message) will appear without the other code.

I am thinking some sort of IsNumeric or NZ statement
I believe this should answer your questions.
Thanks for trying to help.
 
J

John Vinson

If I use Val([Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance])
MsgBox "yada yada"
End If

No message box appears.
The control Balance contains a numeric value. (Currency)
The message box (message) will appear without the other code.

I am thinking some sort of IsNumeric or NZ statement
I believe this should answer your questions.
Thanks for trying to help.
Click to expand...
My specific question:

When you run the code with a breakpoint...

and type

?[Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance]

in the Immediate window when execution reaches the If statement...

What do you see?

John W. Vinson[MVP]
 
D

Dave Elliott

Block If without End If is the error message I get in the immediate
window using this code

If [Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance] <= 0 Then
MsgBox "Why is there more material on this job?"
'End If

John Vinson said:
If I use
Val([Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance])
MsgBox "yada yada"
End If

No message box appears.
The control Balance contains a numeric value. (Currency)
The message box (message) will appear without the other code.

I am thinking some sort of IsNumeric or NZ statement
I believe this should answer your questions.
Thanks for trying to help.
Click to expand...
My specific question:

When you run the code with a breakpoint...

and type

?[Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance]

in the Immediate window when execution reaches the If statement...

What do you see?

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

Block If without End If is the error message I get in the immediate
window using this code

If [Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance] <= 0 Then
MsgBox "Why is there more material on this job?"
'End If
Click to expand...

Ok. Remove the ' which is commenting out the End If.

Note that you should - after EVERY edit of code - select Debug...
Compile <my project> and correct any compilation errors. If you don't,
your code cannot be assumed to be correct.

John W. Vinson[MVP]
 
D

Dave Elliott

I dont understand, in the debug window it doesnt work, error Block If
Without End If
If Forms!TimeCards![TimeCardPaymentSub].Form![Balance] <= 0 Then
MsgBox "Why is there more material on this job?"
End If
Yet when I compile, I dont get any errors.
Still the code does not work!???

John Vinson said:
Block If without End If is the error message I get in the immediate
window using this code

If [Forms]![TimeCards]![TimeCardPaymentSub].Form![Balance] <= 0 Then
MsgBox "Why is there more material on this job?"
'End If
Click to expand...

Ok. Remove the ' which is commenting out the End If.

Note that you should - after EVERY edit of code - select Debug...
Compile <my project> and correct any compilation errors. If you don't,
your code cannot be assumed to be correct.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

I dont understand, in the debug window it doesnt work, error Block If
Without End If
If Forms!TimeCards![TimeCardPaymentSub].Form![Balance] <= 0 Then
MsgBox "Why is there more material on this job?"
End If
Yet when I compile, I dont get any errors.
Still the code does not work!???
Click to expand...

Please try again in the immediate window.

Run the code with a breakpoint on the If line. In the Immediate window
type EXACTLY

?Forms!TimeCards![TimeCardPaymentSub].Form![Balance]

and then press Enter.

Select the ENTIRE text in the immediate window, type Ctrl-C to copy
it, and then paste it into a message on this newsgroup.

Also please copy and paste - don't retype - the entire Sub that you're
using.

John W. Vinson[MVP]
 
D

Dave Elliott

It says cant find the form TimeCards

John Vinson said:
I dont understand, in the debug window it doesnt work, error Block If
Without End If
If Forms!TimeCards![TimeCardPaymentSub].Form![Balance] <= 0 Then
MsgBox "Why is there more material on this job?"
End If
Yet when I compile, I dont get any errors.
Still the code does not work!???
Click to expand...

Please try again in the immediate window.

Run the code with a breakpoint on the If line. In the Immediate window
type EXACTLY

?Forms!TimeCards![TimeCardPaymentSub].Form![Balance]

and then press Enter.

Select the ENTIRE text in the immediate window, type Ctrl-C to copy
it, and then paste it into a message on this newsgroup.

Also please copy and paste - don't retype - the entire Sub that you're
using.

John W. Vinson[MVP]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

If Statement for sub-form needed! 11
Label is Visible if Value is Negative 3
If Statement, Customer Overpays 4
If Statement From sub-form to Main Form 1
If Statement Referencing Sub-Forms 6
If Statement For Sub-Form 1
Missing End If in Code ?? Where? 7
If VB Yes, make label visble, How? 3

Top