By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

MS Access: Using the IIf Function in a form

P: n/a
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?
Nov 12 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a
"Terry" <tc*****@merseymail.com> schrieb im Newsbeitrag
news:38**************************@posting.google.c om...
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?


Hi Terry,

Use the After Update Edvent of the TextBox to set PASS
Something like:

IIf ... Me.PASS=True

Viel Erfolg,
Michael
Nov 12 '05 #2

P: n/a
You need this for the update and a similar sub for OnCurrent of Form.
It's nothing to do with what you key into the checkbox. This should be
locked anyway

Private Sub MARK_AfterUpdate()

If MARK >= Then
Pass = True
Else
Pass = False
End If

End Sub

Phil
"Terry" <tc*****@merseymail.com> wrote in message
news:38**************************@posting.google.c om...
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?

Nov 12 '05 #3

P: n/a
I put the sub into Afterupdate Event in the Mark control box and
although I get no error messages....nothing happens.
I couldn't put the sub in the OnCurrent of the form as I don't know
how to do this...did try help but found it much too complex to
understand. Is there an easy explanation of how to do this?

Terry

"Phil Stanton" <ph**@stantonfamily.co.uk> wrote in message news:<3f***********************@mercury.nildram.ne t>...
You need this for the update and a similar sub for OnCurrent of Form.
It's nothing to do with what you key into the checkbox. This should be
locked anyway

Private Sub MARK_AfterUpdate()

If MARK >= Then
Pass = True
Else
Pass = False
End If

End Sub

Phil
"Terry" <tc*****@merseymail.com> wrote in message
news:38**************************@posting.google.c om...
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?

Nov 12 '05 #4

P: n/a
What version of Access are you using

Phil
"Terry" <tc*****@merseymail.com> wrote in message
news:38**************************@posting.google.c om...
I put the sub into Afterupdate Event in the Mark control box and
although I get no error messages....nothing happens.
I couldn't put the sub in the OnCurrent of the form as I don't know
how to do this...did try help but found it much too complex to
understand. Is there an easy explanation of how to do this?

Terry

"Phil Stanton" <ph**@stantonfamily.co.uk> wrote in message

news:<3f***********************@mercury.nildram.ne t>...
You need this for the update and a similar sub for OnCurrent of Form.
It's nothing to do with what you key into the checkbox. This should be
locked anyway

Private Sub MARK_AfterUpdate()

If MARK >= Then
Pass = True
Else
Pass = False
End If

End Sub

Phil
"Terry" <tc*****@merseymail.com> wrote in message
news:38**************************@posting.google.c om...
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?

Nov 12 '05 #5

P: n/a
Phil Stanton previously wrote:
Private Sub MARK_AfterUpdate()
If me.MARK >= XXXXX Then 'what?
me.Pass = True Else
me.Pass = False
End If

End Sub


Regards

Peter Russell
Nov 12 '05 #6

P: n/a
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_AfterUpdate()

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

End Sub

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

- Jim
Nov 12 '05 #7

P: n/a
Terry, I'm wondering if you tried what I suggested in your other post
on this subject. Perhaps my instructions were not sufficiently clear.

At any rate consider what *event* you want to trigger this checking of
the check box. Then apply the code there.

- Jim

On 22 Nov 2003 15:53:44 -0800, tc*****@merseymail.com (Terry) wrote:
I put the sub into Afterupdate Event in the Mark control box and
although I get no error messages....nothing happens.
I couldn't put the sub in the OnCurrent of the form as I don't know
how to do this...did try help but found it much too complex to
understand. Is there an easy explanation of how to do this?

Terry

"Phil Stanton" <ph**@stantonfamily.co.uk> wrote in message news:<3f***********************@mercury.nildram.ne t>...
You need this for the update and a similar sub for OnCurrent of Form.
It's nothing to do with what you key into the checkbox. This should be
locked anyway

Private Sub MARK_AfterUpdate()

If MARK >= Then
Pass = True
Else
Pass = False
End If

End Sub

Phil
"Terry" <tc*****@merseymail.com> wrote in message
news:38**************************@posting.google.c om...
> 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?


Nov 12 '05 #8

P: n/a
Nay, Lad

The event should be triggered after he puts the mark in. The check box
basically is a pass or fail.

Possibly the problem is that it is a textbox and the 24 is being regarded as
a string rather than a number.

So use instaed ow what I suggested

Private Sub MARK_AfterUpdate()

If CSng(MARK) >= 24 Then ' This converts it to single number
with decimals
Pass = True
Else
Pass = False
End If

End Sub

If you have the form in Design View and click on the small square at the
top left of the form you should see the form's properties. If you can't, on
the top menubar, click View then Properties.
When you have the property box open, select the Events Tab. The top event is
On Current#
Key in [ yes a square bracket which the will become [Event Procedure]. At
the right end of the line are 3 dots which when you press will open up the
VBA module.
It will already say

Private Sub Form_Current()

End Sub

so key in or copy and paste

If CSng(MARK) >= 24 Then ' This converts it to single number
with decimals
Pass = True
Else
Pass = False
End If

between the Private "Sub Form_Current()" and "End Sub"

Do the same thing by clicking on your MARK box

Phil

"Jim Allensworth" <Ji****@NOTdatacentricsolutions.com> wrote in message
news:3f*************@netnews.comcast.net...
Terry, I'm wondering if you tried what I suggested in your other post
on this subject. Perhaps my instructions were not sufficiently clear.

At any rate consider what *event* you want to trigger this checking of
the check box. Then apply the code there.

- Jim

On 22 Nov 2003 15:53:44 -0800, tc*****@merseymail.com (Terry) wrote:
I put the sub into Afterupdate Event in the Mark control box and
although I get no error messages....nothing happens.
I couldn't put the sub in the OnCurrent of the form as I don't know
how to do this...did try help but found it much too complex to
understand. Is there an easy explanation of how to do this?

Terry

"Phil Stanton" <ph**@stantonfamily.co.uk> wrote in message news:<3f***********************@mercury.nildram.ne t>...
You need this for the update and a similar sub for OnCurrent of Form.
It's nothing to do with what you key into the checkbox. This should be
locked anyway

Private Sub MARK_AfterUpdate()

If MARK >= Then
Pass = True
Else
Pass = False
End If

End Sub

Phil
"Terry" <tc*****@merseymail.com> wrote in message
news:38**************************@posting.google.c om...
> 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?

Nov 12 '05 #9

P: n/a
On Sun, 23 Nov 2003 16:37:59 -0000, "Phil Stanton"
<ph**@stantonfamily.co.uk> wrote:
Nay, Lad Lad? It's obvious you don't know me at all. ;-)

The event should be triggered after he puts the mark in. The check box
basically is a pass or fail.

Possibly the problem is that it is a textbox and the 24 is being regarded as
a string rather than a number. A textbox doesn't care what it holds: numbers, strings, calculations,
special characters, etc. That has no relevance to the issue.

So use instaed ow what I suggested

Private Sub MARK_AfterUpdate()

If CSng(MARK) >= 24 Then ' This converts it to single number
with decimals
Pass = True
Else
Pass = False
End If

End Sub

You are too verbose - code wise. This will do what is needed

Me.PASS = Me.MARK >= 24

It could be placed in the forms On Current as well as the MARK After
Update. Although if it is a dataentry form then it is only needed for
the textbox.

- Jim
Nov 12 '05 #10

P: n/a
Ji****@NOTdatacentricsolutions.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_AfterUpdate()

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

End Sub

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

- Jim


Jim, Tried this one first:

Private Sub Mark_AfterUpdate()
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_AfterUpdate()
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.
Nov 12 '05 #11

P: n/a
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*****@merseymail.com> wrote in message
news:38**************************@posting.google.c om...
Ji****@NOTdatacentricsolutions.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_AfterUpdate()
>
If me.MARK >= XXXXX Then 'what?
me.Pass = True
> Else
> me.Pass = False
> End If
>
> End Sub

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

- Jim


Jim, Tried this one first:

Private Sub Mark_AfterUpdate()
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_AfterUpdate()
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.

Nov 12 '05 #12

P: n/a
tc*****@merseymail.com (Terry) wrote in message news:<38**************************@posting.google. com>...
Ji****@NOTdatacentricsolutions.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_AfterUpdate()
>
If me.MARK >= XXXXX Then 'what?
me.Pass = True
> Else
> me.Pass = False
> End If
>
> End Sub

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

- Jim


Jim, Tried this one first:

Private Sub Mark_AfterUpdate()
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_AfterUpdate()
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_AfterUpdate()

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_AfterUpdate()

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
Nov 12 '05 #13

P: n/a
Private Sub Mark_AfterUpdate()

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*****@merseymail.com> wrote in message
news:38**************************@posting.google.c om...
tc*****@merseymail.com (Terry) wrote in message

news:<38**************************@posting.google. com>...
Ji****@NOTdatacentricsolutions.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_AfterUpdate()
>>
> If me.MARK >= XXXXX Then 'what?
> me.Pass = True
>> Else
>> me.Pass = False
>> End If
>>
>> End Sub
>
Or simply...
Private Sub MARK_AfterUpdate()
Me.PASS = Me.MARK >= 24
End Sub

- Jim


Jim, Tried this one first:

Private Sub Mark_AfterUpdate()
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_AfterUpdate()
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_AfterUpdate()

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_AfterUpdate()

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

Nov 12 '05 #14

P: n/a
Thanks Doug, Jim & Phil,

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

Private Sub Mark_AfterUpdate()

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.
Nov 12 '05 #15

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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message news:<Pr*****************@twister01.bloor.is.net.c able.rogers.com>...
Private Sub Mark_AfterUpdate()

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

Nov 12 '05 #16

P: n/a
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*****@merseymail.com (Terry) wrote:
Thanks Doug, Jim & Phil,

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

Private Sub Mark_AfterUpdate()

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.


Nov 12 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.