If Then Else Event to set Value in Textfield automatically.

[Vrolok]

Hi,

I hope some of you can help me. first of all I'm a totally ACCESS newbie and not very familiar with vba codes, so it would be great where I have to put things.

My question is: I have an Access form and five options: option1, option2... etc. and a textfield called [Group].You can choose the answer for all options from yes/no.

Now if all options are no -- the group should be 1 if any of the answers is yes the group is 2.

How can I manage this using the if then else event of Access? What is the correct code and where do i have to put it?

I tried this one:

   If Me.Option1 + Me.Option2 + Me.Option3 + Me.Option4 + Me.Option5 = 0 Then
        Me.Group = 1
      Else
        Me.Group = 2
    End If

But it doesn't work. Thank you for your help.

[NeoPa]

I can't see how this question makes sense. I assume that the controls Option1, Option2, etc are all RadioButton style Option Buttons. It would be a pretty dumb way to ask the question if they weren't, after all. That leaves me thinking that it's impossible to set them all to No. Why check for an impossible situation?

Maybe you should start by describing what it is you're trying to achieve and we can possibly explain to you how such a situation could sensibly be achieved.

[Mihail]

Agree, NeoPa.

Vrolok !
First, the If-Then-Else is not an event. It is a code structure.
Second, the radio button return a boolean value (True or False) depending of it's state.

Any way, the following code will work but I have serious doubts that will help you to design a good database.

Option Compare Database
Option Explicit

Private Sub Option1_AfterUpdate()
    Call SetGroup
End Sub

Private Sub Option2_AfterUpdate()
    Call SetGroup
End Sub

Private Sub Option3_AfterUpdate()
    Call SetGroup
End Sub

Private Sub Option4_AfterUpdate()
    Call SetGroup
End Sub

Private Sub Option5_AfterUpdate()
    Call SetGroup
End Sub


Private Sub SetGroup()
    If Not (Me.Option1 And Me.Option2 And Me.Option3 And Me.Option4 And Me.Option5) Then
        Group = 1
    Else
        Group = 2
    End If
End Sub

Return here and answer to NeoPa's question.
Good luck !

[Vrolok]

hi, sorry maybe I didn't described the problem good enough, what i Mean with option is a question. Question 1, Question 2... and the answer can be yes or no, so depending on the answers you give, you are put into a group (1 or 2)

The question (option) is not a button just a normal drop down field and you can choose yes no.

The question are for a medical database: too keep it simple the questions i use are different but it's like:

1. Diabetes Mellitus - yes /no
2. Reacting Allergic - yes/ no
3. Heart Disease - yes /no
4. Infections - yes/no
5. Antibiotic Treatment - yes/no

Group - 1/2

if all questions are answered no you are put into group 1
if any of the questions is answered with yes, group 2.

Does this make more sense to you now?

I tried the code you gave me, but i don't know where to put it. In what textfield and under which option.

It would be really nice if you if you could help me out. And sorry for the confusion I'm a absolute beginner with access.

Thank you

[Mihail]

I think that you can't explain your problem.
ZIP your database and attache it to your next post.
I'll take a look.

:) I know, NeoPa and SmileyCoder: you don't agree that. But he is a beginner as we all some years ago.

[NeoPa]

@Mihail
There is no problem with you, as a member offering help, requesting the OP send a copy of their database. See Attach Database (or other work) for how to do that properly. The only problem is when a member posts their question without explaining it clearly and they expect other members to look at their database to see what the question should have been.

@Vrolok
I find your situation much clearer now, but you still need to explain what type of field is bound to your ComboBox controls. The code is different depending on whether the control is bound to a Boolean field; bound to a String field; or not bound at all.

Mihail's code in post #2 is fundamentally along the right lines, but the control checks should be joined with Ors rather than Ands. Depending on whether or not the controls are bound, it may also need to be called from the Form_Current() event procedure too.

[Mihail]

Hi, NeoPa.
I think that you think :) for the future: If the controls are Combo Boxes, if the controls are bound and so on.

Assuming that controls are Option Buttons and based on first post of Vrolok, my Ands are Ok. Tested.

[NeoPa]

Mihail:
Assuming that controls are Option Buttons and based on first post of Vrolok, my Ands are Ok. Tested.

I beg your pardon. I overlooked the Not at the front. It is a strange way to write it, but it would nevertheless work.

A simpler way of writing it would, of course, be :

If Me.Option1 Or Me.Option2 Or Me.Option3 Or Me.Option4 Or Me.Option5) Then
    Group = 2
Else
    Group = 1
End If

[Vrolok]

Hi, I'm afraid i cannot attach the database, it's for a company and so it has to be kept confidential. Sorry i just had a crashcourse in access and the people here think, that should make it.

i'm sorry I don't understand the codes you made.

I have no strange comboBoxes etc. just a drop down Field ("yes","no", it's like you write some text in a textfield, and you write yes or no you are put in a different group.

Where do i have to put the code? into the group textfield? After update?

I think it is not too hard to understand what I'm trying to do.

I have 5 Questions (forget about the option thing)

1.Question to answer it - DropDownField - yes/no
2.Question to answer it - DropDownField - yes/no
3.Question to answer it - DropDownField - yes/no
4.Question to answer it - DropDownField - yes/no
5.Question to answer it - DropDownField - yes/no

Of course i could use a box with false true value but I had to do the drop down textfield so the anser is like "yes", "no", so this has to appear in the code.

Could it be that way?

If Me.question1 Or Me.question2 Or Me.question3 Or Me.question4 Or Me.question5 = "yes" Then 
    Group = 2 
Else 
    Group = 1 
End If

where do i have to put the code exactly???

I'm sorry that I don't know it better, how did you become ACCESS experts? is there a good book, I want to learn, but I have to start somewhere.

ok maybe i can zip the first page of the database but how do i attach filest here?

[NeoPa]

Look in Attach Database (or other work) for how to handle that. It is almost always possible to remove confidential information from a database. We are generally interested only in the structure and enough sample data to illustrate a problem.

[NeoPa]

Vrolok:
I think it is not too hard to understand what I'm trying to do.

I would suggest that indicates that you're not thinking very clearly.

If you ask a question without including the relevant technical information, and people come back asking you for that information, and you then fail to provide that information, then there are enough clues there to indicate your question is not clear - No?

I can think of a number of different scenarios, with different answers, that all match the question as we know so far. Until you clarify your actual situation by explaining the types of the fields that the ComboBox controls are bound to then we are left to guess, which isn't how it should be.

A first step is for you to understand the differences between table fields and form controls. You refer to controls as fields, which is not correct and allows you to stay confused (If you're confused then you will not be in a good position to state your question clearly).

[Vrolok]

the database

Hi, I attached the part of the database with the problem.

You will find the form with the questions and depending on the answers the patient should be enrolled into group 1 or 2. Hope the problem is much clearer to you now.

ok I forgot something, I'm sorry for that, if none of the questions is answered, i.e. if any of the fields is blank, than there shouldn't be a number generated in the group.

It's like when you first look on the question sheet, non of the answers are given, so no number in the group appears.

How Can I do that?

[Mihail]

I take a look from your database and my answer can be only this:
Read and UNDERSTAND http://bytes.com/topic/access/insigh...ble-structures

If you wish to design a database with more than one table you MUST understand this article.

Your database can't become a functional one without a major change in design.

Come back only AFTER you read and, I repeat, UNDERSTAND, the concept of normalization.

Until that every one from here will waste time trying to help yo.

Do not be worried about how to implement the normalization. We'll help you.

Good luck !