MS Access: Using the IIf Function in a form

[Terry]

Hello,
I am designing a form and need to know how to get a Check Box(PASS)to
automatically update when the data in a Text Box (MARK), in the same
form, is >=24.
Using the Event dialogue box for the Check Box, AfterUpdate, I have
tried:
IIf([MARK]>=24,True,False )
Also:
IIf([MARK]>=24,1,0)
Also:
IIf([MARK]>=24,"Yes","No" )

Although I get no error messages, nothing seems to work.
Any suggestions?

[Terry]

Ji****@NOTdatac entricsolutions .com (Jim Allensworth) wrote in message news:<3f******* ******@netnews. comcast.net>...

On Sun, 23 Nov 2003 15:26 +0000 (GMT Standard Time), ru***@127.0.0.1
(Peter Russell) wrote:

Phil Stanton previously wrote:

Private Sub MARK_AfterUpdat e()

If me.MARK >= XXXXX Then 'what?
me.Pass = True

Else
me.Pass = False
End If

End Sub

Or simply...
Private Sub MARK_AfterUpdat e()
Me.PASS = Me.MARK >= 24
End Sub

- Jim

Jim, Tried this one first:

Private Sub Mark_AfterUpdat e()
If Me.MARK >= 24 Then
Me.Pass = True
Else
Me.Pass = False
End If
End Sub

This had no effect and showed no errors. So I tried the second one as follows:

Private Sub Mark_AfterUpdat e()
Me.Pass = Me.Mark >= 24
End Sub

Didn't get anything when I tried it out on the form, but when I closed
the form I got a runtime error '2113' "The value you have entered
isn't valid for this field" ....I clicked the Debug button. This
brought me back to the VBA code and the line:

Me.Pass = Me.Mark >= 24

was highlighted in yellow, with a yellow arrow to the left.

This has thrown me altogether as I couldn't find out how to actually
debug.

In the database I'm creating, I have made a relationship between the
"Student Details" table and the "Exam details" table so that I have a
single form with some fields from each table.

The Student Details Section Is at the top of the form, i.e ID, Name &
Location, and the Exam details are sort of embedded into this form in
the bottom half.

This means that a subform also exists for Exam details. I thought this
might have some baring on the result and placed the code in both the
form and the subform but the result is exactly the same.

[Phil Stanton]

Four things, Terry

1) At the top of the code window you should have the 2 lines
Option Compare Database
Option Explicit

Then there will be a faint line line

2) After you have keyed in the VBA Code are you going to Menu bar at the
top and selecting
Debug -> Compile XXXXXXX(Your project name)
This checks the syntax of your code, but not it's logic

3) On the properties box, if you select the All option, the Name of the
controls in question must be
"MARK" and "Pass" without the inverted commas

4) On the properties box, if you select the Event option
does the Form On Current have [Event Procedure] beside it
and
does the Text Box named MARK have [Event Procedure] beside it

Phil

"Terry" <tc*****@mersey mail.com> wrote in message
news:38******** *************** ***@posting.goo gle.com...

Ji****@NOTdatac entricsolutions .com (Jim Allensworth) wrote in message news:<3f******* ******@netnews. comcast.net>...

On Sun, 23 Nov 2003 15:26 +0000 (GMT Standard Time), ru***@127.0.0.1
(Peter Russell) wrote:

Phil Stanton previously wrote:

> Private Sub MARK_AfterUpdat e()
>
If me.MARK >= XXXXX Then 'what?
me.Pass = True
> Else
> me.Pass = False
> End If
>
> End Sub

Or simply...
Private Sub MARK_AfterUpdat e()
Me.PASS = Me.MARK >= 24
End Sub

- Jim

Jim, Tried this one first:

Private Sub Mark_AfterUpdat e()
If Me.MARK >= 24 Then
Me.Pass = True
Else
Me.Pass = False
End If
End Sub

This had no effect and showed no errors. So I tried the second one as

follows:
Private Sub Mark_AfterUpdat e()
Me.Pass = Me.Mark >= 24
End Sub

Didn't get anything when I tried it out on the form, but when I closed
the form I got a runtime error '2113' "The value you have entered
isn't valid for this field" ....I clicked the Debug button. This
brought me back to the VBA code and the line:

Me.Pass = Me.Mark >= 24

was highlighted in yellow, with a yellow arrow to the left.

This has thrown me altogether as I couldn't find out how to actually
debug.

In the database I'm creating, I have made a relationship between the
"Student Details" table and the "Exam details" table so that I have a
single form with some fields from each table.

The Student Details Section Is at the top of the form, i.e ID, Name &
Location, and the Exam details are sort of embedded into this form in
the bottom half.

This means that a subform also exists for Exam details. I thought this
might have some baring on the result and placed the code in both the
form and the subform but the result is exactly the same.

[Terry]

tc*****@merseym ail.com (Terry) wrote in message news:<38******* *************** ****@posting.go ogle.com>...

Ji****@NOTdatac entricsolutions .com (Jim Allensworth) wrote in message news:<3f******* ******@netnews. comcast.net>...

On Sun, 23 Nov 2003 15:26 +0000 (GMT Standard Time), ru***@127.0.0.1
(Peter Russell) wrote:

Phil Stanton previously wrote:

> Private Sub MARK_AfterUpdat e()
>
If me.MARK >= XXXXX Then 'what?
me.Pass = True
> Else
> me.Pass = False
> End If
>
> End Sub

Or simply...
Private Sub MARK_AfterUpdat e()
Me.PASS = Me.MARK >= 24
End Sub

- Jim

Jim, Tried this one first:

Private Sub Mark_AfterUpdat e()
If Me.MARK >= 24 Then
Me.Pass = True
Else
Me.Pass = False
End If
End Sub

This had no effect and showed no errors. So I tried the second one as follows:

Private Sub Mark_AfterUpdat e()
Me.Pass = Me.Mark >= 24
End Sub

Didn't get anything when I tried it out on the form, but when I closed
the form I got a runtime error '2113' "The value you have entered
isn't valid for this field" ....I clicked the Debug button. This
brought me back to the VBA code and the line:

Me.Pass = Me.Mark >= 24

was highlighted in yellow, with a yellow arrow to the left.

This has thrown me altogether as I couldn't find out how to actually
debug.

In the database I'm creating, I have made a relationship between the
"Student Details" table and the "Exam details" table so that I have a
single form with some fields from each table.

The Student Details Section Is at the top of the form, i.e ID, Name &
Location, and the Exam details are sort of embedded into this form in
the bottom half.

This means that a subform also exists for Exam details. I thought this
might have some baring on the result and placed the code in both the
form and the subform but the result is exactly the same.

UPDATE!

I discovered that the Check Box actually had the wrong name. When I
looked at the Check Box Name in it's properties I found it had been
given the default name of Check20, so I changed it and the code works,
up to a point, I'll get to that later.

I also need to do the same with another Check Box, Resit, from the
same control so I put in extra code as follows:

Private Sub Mark_AfterUpdat e()

If Me.Mark >= 24 Then
Me.Pass = True
Me.Resit = False
Else
Me.Pass = False
Me.Resit = True
End If

End Sub

And this works also, up to a point. The point being that I need to set
the two Check Boxes Pass & Resit if I have no data in the Mark Text
Box, which is by the way set to Number Double Datatype. This doesn't
happen if I delete the data from the Mark Box, the Check Boxes remain
ticked.
I tried creating a new sub as follows:

Private Sub Mark_AfterUpdat e()

If Me.Mark >= IsNull Then
Me.Pass = False
Me.Resit = False
End If

End Sub

But this created a compile error. Is there any way I can do this?

Terry

[Douglas J. Steele]

Private Sub Mark_AfterUpdat e()

If IsNull(Me.Mark) Then
Me.Pass = False
Me.Resit = False
Else
Me.Pass = (Me.Mark >= 24)
Me.Resit = Not Me.Pass
End If

End Sub
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Terry" <tc*****@mersey mail.com> wrote in message
news:38******** *************** ***@posting.goo gle.com...

tc*****@merseym ail.com (Terry) wrote in message

news:<38******* *************** ****@posting.go ogle.com>...

Ji****@NOTdatac entricsolutions .com (Jim Allensworth) wrote in message news:<3f******* ******@netnews. comcast.net>...

On Sun, 23 Nov 2003 15:26 +0000 (GMT Standard Time), ru***@127.0.0.1
(Peter Russell) wrote:

>Phil Stanton previously wrote:
>
>> Private Sub MARK_AfterUpdat e()
>>
> If me.MARK >= XXXXX Then 'what?
> me.Pass = True
>> Else
>> me.Pass = False
>> End If
>>
>> End Sub
>
Or simply...
Private Sub MARK_AfterUpdat e()
Me.PASS = Me.MARK >= 24
End Sub

- Jim

Jim, Tried this one first:

Private Sub Mark_AfterUpdat e()
If Me.MARK >= 24 Then
Me.Pass = True
Else
Me.Pass = False
End If
End Sub

This had no effect and showed no errors. So I tried the second one as follows:
Private Sub Mark_AfterUpdat e()
Me.Pass = Me.Mark >= 24
End Sub

Didn't get anything when I tried it out on the form, but when I closed
the form I got a runtime error '2113' "The value you have entered
isn't valid for this field" ....I clicked the Debug button. This
brought me back to the VBA code and the line:

Me.Pass = Me.Mark >= 24

was highlighted in yellow, with a yellow arrow to the left.

This has thrown me altogether as I couldn't find out how to actually
debug.

In the database I'm creating, I have made a relationship between the
"Student Details" table and the "Exam details" table so that I have a
single form with some fields from each table.

The Student Details Section Is at the top of the form, i.e ID, Name &
Location, and the Exam details are sort of embedded into this form in
the bottom half.

This means that a subform also exists for Exam details. I thought this
might have some baring on the result and placed the code in both the
form and the subform but the result is exactly the same.

UPDATE!

I discovered that the Check Box actually had the wrong name. When I
looked at the Check Box Name in it's properties I found it had been
given the default name of Check20, so I changed it and the code works,
up to a point, I'll get to that later.

I also need to do the same with another Check Box, Resit, from the
same control so I put in extra code as follows:

Private Sub Mark_AfterUpdat e()

If Me.Mark >= 24 Then
Me.Pass = True
Me.Resit = False
Else
Me.Pass = False
Me.Resit = True
End If

End Sub

And this works also, up to a point. The point being that I need to set
the two Check Boxes Pass & Resit if I have no data in the Mark Text
Box, which is by the way set to Number Double Datatype. This doesn't
happen if I delete the data from the Mark Box, the Check Boxes remain
ticked.
I tried creating a new sub as follows:

Private Sub Mark_AfterUpdat e()

If Me.Mark >= IsNull Then
Me.Pass = False
Me.Resit = False
End If

End Sub

But this created a compile error. Is there any way I can do this?

Terry

[Terry]

Thanks Doug, Jim & Phil,

I have used Doug's code, very impressive, and now have no problems:

Private Sub Mark_AfterUpdat e()

If IsNull(Me.Mark) Then
Me.Pass = False
Me.Resit = False
Else
Me.Pass = (Me.Mark >= 24)
Me.Resit = Not Me.Pass
End If

End Sub

In conclusion, my problem began because I didn't verify the name of my
Check Box as Pass, which the debugger didn't pick up on, hence no
error messages. Once this was corrected everything was OK.
Thanks for all your help.

[Terry]

Thanks Doug, very impressive, this works like a dream. Thanks also To Jim and Phil.

"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in message news:<Pr******* **********@twis ter01.bloor.is. net.cable.roger s.com>...

Private Sub Mark_AfterUpdat e()

If IsNull(Me.Mark) Then
Me.Pass = False
Me.Resit = False
Else
Me.Pass = (Me.Mark >= 24)
Me.Resit = Not Me.Pass
End If

End Sub

[Jim Allensworth]

Terry, regarding control names (and other objects), consider using a
naming convention such as Leszynski/Reddick. It is easy to simply
preface your control with a tag indicating what type they are. IMO, it
will make your code easier to maintain. For more ...

http://www.mvps.org/access/general/gen0012.htm

Good luck,
- Jim

On 24 Nov 2003 04:01:25 -0800, tc*****@merseym ail.com (Terry) wrote:

Thanks Doug, Jim & Phil,

I have used Doug's code, very impressive, and now have no problems:

Private Sub Mark_AfterUpdat e()

If IsNull(Me.Mark) Then
Me.Pass = False
Me.Resit = False
Else
Me.Pass = (Me.Mark >= 24)
Me.Resit = Not Me.Pass
End If

End Sub

In conclusion, my problem began because I didn't verify the name of my
Check Box as Pass, which the debugger didn't pick up on, hence no
error messages. Once this was corrected everything was OK.
Thanks for all your help.