Need help on entering "No"

[Bears84]

I have a form base on 3 tables. I am trying to get a text field on the form to have the word "No" inserted if there is nothing in a text field associated with.

Scenario:

If [coordinator name] is blank then [Approval] equals "No"

 

[Matt Greatorex]

=Iif(isnull(coordinator name,"No",[Approval])

Assuming that, if the coordinator name is not null, the value comes from a field in some undelying table, and that the field is also called 'Approval'.

 

[neileg]

Nz() does the same as Matt's formula:
=Nz([coordinator name], "No")

 

[Bears84]

Need more help

I tryed inserting the information provided. And yet it seems to want more.

The idea behind this if the coordinator does not put his Int' in the[Coordinators_Name] then the [Approval] will state "No". Once the coordinator put his Int' in the [Coordinators_Name] then the [Approval] will change to "Yes".

 

[jkl0]

In The After Update Property Put:

If IsNull(Me.[coordinator name]) Then
Me.Approval = "No"
Else
Me.Approval = "Yes"
End If

And also set the Default for the textbox = No If this is an input form.
If the form is for editing put the same code as above into the On Open event and leave the Default blank.

 

[Bears84]

Run time error
You can't assign a value to this object.
Me.Approval ="No" (Hightlighted)


Private Sub Form_Open(Cancel As Integer)
If IsNull(Me.[coordinator name]) Then
Me.Approval = "No"
Else
Me.Approval = "Yes"
End If
End Sub

 

[ghudson]

What is the name of the text box? The text box and the field name from the table can not be the same for you are confusing Access. If the field from the table is named Approval then the text box that is bound to the Approval field should be named txtApproval. Then this should work...

If IsNull([coordinator name]) or [coordinator name] = "" Then
     Me.txtApproval =  "No"
Else
     Me.txtApproval = "Yes"
End If

You should also not have spaces within the names of your objects. Search for "naming convention" to learn more.

 

[jkl0]

Is Approval a (True/False or Yes/No Field?) or Text Field?
Is your form a single form, continuous form or datasheet?

 

[Bears84]

Is Approval a (True/False or Yes/No Field?) or Text Field?

I have changed it to a text field.

The one thing that I have not mentioned is this table is in another data base and is linked to the one that I'm trying to make the chances in. I am intending on securing that data base so that only the coordinator will have write to that field.

Is your form a single form, continuous form or datasheet?

This a continuous form.

 

[Banana]

Why is your Yes/No field a textbox? Checkbox would make for easier maintence? Did you rename the textbox/checkbox on the form as Ghudson suggested to?

 

[Bears84]

I did rename the text box to Txt prefix as suggested.

I can make this a yes/no box if needed no problem.

But I still can not get the No to fill in on all the records that have no Coordinators Int. on the continuous form. Named (frmissue).
I can type it in each one. Just no auto update. And theres thousands of entries.

 

[workmad3]

create a query

UPDATE table SET Approval = "No" WHERE Coordinator IS NULL Or Coordinator LIKE ''

UPDATE table SET Approval = "Yes" WHERE Coordinator IS NOT NULL And Coordinator NOT LIKE ''

put that in as the SQL for 2 queries and run them. Changes you may need to make are:

The "yes"/"No" may need to be 0/1 if you change it back to a yes/no field data type.

Correct field and table names

 

[Bears84]

Thank all who have helped me with this effort