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

How to Set a Value

P: 21
Hi, I'm very new to MS Access.
Can anyone help with this please?

I am using Microsoft Access 2003.
I am using a form to enter data and wish to program it so if I enter a M in field1 it automatically updates field2 to be true and if I enter a F it automatically updates field3 to be true instead instead.

As a statement I assume it would probably be something like -
After update of field1 if field1="M" then setvalue of field2=true and if field1="F" then setvalue of field3=true ??????? - but I haven't written any MS Access code yet!

Any help greatly appreciated!
Thanks.
Kev
Nov 17 '08 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,494
In the Control Source of [Field2] have :
Expand|Select|Wrap|Line Numbers
  1. =([Field1]='M')
In the Control Source of [Field3] have :
Expand|Select|Wrap|Line Numbers
  1. =([Field1]='F')
Welcome to Bytes!
Nov 17 '08 #2

P: 21
Many thanks! It works! Can't believe it was so simple.
Kev
Nov 17 '08 #3

NeoPa
Expert Mod 15k+
P: 31,494
Sometimes it's not. This one's possibly not as simple as it looks. The value is actually the result returned by an assertion (sort of).

Alternatively an IIf() could be used, but in reality an assertion is all that's necessary (and shortest). Some prefer the alternative though simply because it's more obvious to the reader how it works, which isn't a bad reason as reasons go.
Nov 17 '08 #4

P: 21
Oops! I spoke too soon!
It works in so far as writing the tick into the screen form as appropriate, but it does not update the data in the table itself from which I run other queries and reports.

How do I get it to also update the table please?

Thanks again.
Kev
Nov 17 '08 #5

NeoPa
Expert Mod 15k+
P: 31,494
Well having bound controls will be a start. Otherwise, you need to explain your situation a little more clearly.

What are the names of the table(s), the fields, the form, the controls?
Nov 17 '08 #6

P: 21
Thank you - I'm way out of my depth now and really appreciate your help!
Details as follows -

Table - Asurvivortable
Form - Asurvivorformshort
Fieldnames - sex (text) / sexmale (yes/no) / sexfemale (yes/no)

The form is used for entering people's name, address etc. At present having to stop entering text and use a mouse to click on the True/False tick boxes slows down data entry. What I'm trying to achive is that the text letter M or F is entered instead and that then automatically places the tick in the appropriate box.

Kev
Nov 17 '08 #7

NeoPa
Expert Mod 15k+
P: 31,494
Ah, in that case, knowing that the space bar switches from True to False (and vice versa) in a CheckBox control may be of some assistance then?
Nov 17 '08 #8

P: 21
Yes!
Thanks.

.........
Nov 17 '08 #9

NeoPa
Expert Mod 15k+
P: 31,494
Twenty char limit huh?

I can take it that you're happy with that then and no longer need to proceed with the question?

Although I suppose an answer wouldn't hurt anyway.

For bound controls, the ControlSource must be set to the field that it's bound to (of course). This means that the other alternative, of setting the values explicitly in the code, must be used instead.

In the code module of [Asurvivorformshort], create an after-update event procedure for the [Sex] control similar to :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Sex_AfterUpdate()
  2.   With Me
  3.     Select Case .Sex
  4.     Case "F"
  5.       .SexFemale = True
  6.       .SexMale = False
  7.     Case "M"
  8.       .SexFemale = False
  9.       .SexMale = True
  10.     End Select
  11.   End With
  12. End Sub
It may also be necessary (desirable) to set the value of [Sex] whenever a new record is shown.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   With Me
  3.     .Sex = IIf(.SexFemale, "F", "M")
  4.   End With
  5. End Sub
Nov 18 '08 #10

P: 21
Fantastic!
Many thanks indeed.
Kev
Nov 19 '08 #11

NeoPa
Expert Mod 15k+
P: 31,494
You're welcome Kev.

It always helps to have an answer for a question if possible anyway. Even if the OP (you) no longer needs it.
Nov 19 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.